心得6--JDBC回顾-如何处理大文本和图像声音案例分析
1. 基础知识回顾
基本概念：大数据也称之为LOB(Large Objects)，LOB又分为：clob（用于存储大文本，如：Text）和blob（blob用于存储二进制数据，例如图像、声音、二进制文等）
对MySQL而言只有blob，而没有clob，mysql存储大文本采用的是Text，Text和blob分别又分为：
? TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
? TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
2. 具体案例分析
（1）运用jdbc插入图片（二进制）和输出图片
packagecom.bigText;
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.FileOutputStream;
importjava.io.InputStream;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importcom.Db.DbManager;
publicclass BlobTest {
public void insert(){
Connection con =DbManager.getConnection();
PreparedStatement st = null;
try {
String sql = "insert into dbBlob(image) values(?)";
st = con.prepareStatement(sql);
File f = new File("src/1.jpg");
FileInputStream fs = newFileInputStream(f);
st.setBinaryStream(1,fs,f.length()); //注意length长度须设置，并且设置为int型
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功！！");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbManager.closeDB(con, st, null);
}
}
public void select(){
Connection con =DbManager.getConnection();
PreparedStatement st = null;
ResultSet rs = null;
try {
String sql = "select image fromdbBlob";
st = con.prepareStatement(sql);
rs = st.executeQuery();
while(rs.next()){
InputStream is = rs.getBinaryStream("image");
FileOutputStream fs = new FileOutputStream("D:\\2.jpg");
byte[] b = new byte[1024];
int count = 0;
while((count=is.read(b,0,1024))!=-1){
fs.write(b,0,count);
}
is.close();
fs.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbManager.closeDB(con, st, null);
}
}
public static void main(String[] args) {
BlobTest ct = new BlobTest();
ct.select();
}
}
（2）插入大文本和遍历大文本内容
packagecom.bigText;
importjava.io.BufferedReader;
importjava.io.File;
importjava.io.FileReader;
importjava.io.Reader;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importcom.Db.DbManager;
publicclass ClobTest {
public void insert(){
Connection con =DbManager.getConnection();
PreparedStatement st = null;
try {
String sql = "insert into dbClob values(?)";
st = con.prepareStatement(sql);
File f = new File("src/com/code/Demo1.java");
BufferedReader br = newBufferedReader(new FileReader(f));
st.setCharacterStream(1,br,f.length());
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功！！");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbManager.closeDB(con, st, null);
}
}
public void select(){
Connection con =DbManager.getConnection();
PreparedStatement st = null;
ResultSet rs = null;
try {
String sql = "select member fromdbClob";
st = con.prepareStatement(sql);
//st.setInt(1, 1);
rs = st.executeQuery();
while(rs.next()){
Reader r = rs.getCharacterStream("member");
BufferedReader br = new BufferedReader(r);
String line = null;
while((line=br.readLine())!=null){
System.out.println(line);
}
r.close();
br.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbManager.closeDB(con, st, null);
}
}
public static void main(String[] args) {
ClobTest ct = new ClobTest();
ct.select();
}
}
（3）一个综合案例
向person表中插入数据（姓名、头像、资料）等
package com.zuoye;
importjava.io.BufferedReader;
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.FileReader;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importcom.Db.DbManager;
publicclass Person {
publicvoid insert(){
Connection con = DbManager.getConnection();
PreparedStatement st = null;
try {
String sql = "insertinto person values(?,?,?,?)";
st = con.prepareStatement(sql);
File f1 = new File("src/Eclipse.txt");
File f2 = new File("src/1.jpg");
BufferedReader br =newBufferedReader(new FileReader(f1));
FileInputStream fs = new FileInputStream(f2);
st.setInt(1,1);
st.setString(2, "789");
st.setBinaryStream(3, fs, f2.length());
st.setCharacterStream(4,br,f1.length());
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功！！");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbManager.closeDB(con, st, null);
}
}
publicstaticvoid main(String[]args) {
Person p = new Person();
p.insert();
}
}