JDBC对LOB的读写
在JDBC中提供了java.sql.Blob和java.sql.Clob，两个类分别代表BLOB和CLOB数据。
· BLOB(Binary Large Object)：用于存储大量的二进制数据；
· CLOB(Character Large Object)：用于存储大量的文本数据。
MySQL中的大容量字段类型：
BLOB、TEXT最大64KB。BLOB是大小敏感的，而TEXT不是大写敏感的。MEDIUMBLOB、MEDIUMTEXT最大16MB。MEBIUMBLOB是大小写敏感的，而MEDIUMTEXT不是大小写敏感的。LONGBLOB、LONGTEXT最大4GB。LONGBLOB是大小写敏感的。
1）创建数据库表stuinfo，有id字段，姓名字段，简介字段，头像字段。头像字段设置为LONGBLOB类型。
create table stuinfo(
id int(11) not null auto_increment,
name varchar(20) default null,
content longText,
image longBlob,
primary key(id)
)ENGINE=InnoDB default charset=utf8 row_format=redundant;
2）LOB写入文本文件和图片文件
注意：过低的mysql.jar版本会导致本案例不能正常运行，因此将mysql.jar包换成/mysql-connector-java-5.1.22-bin.jar
package com.yyq.blob;
import com.yyq.factory.CloseFactory;
import com.yyq.factory.ConnectionFactory;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Created by gao on 16-4-13.
*/
public class BlobInsertTest {
public static void insert() throws SQLException{
Connection conn = ConnectionFactory.getConnection();
PreparedStatement pstmt = null;
String sql = "insert into stuinfo(name,content,image) values(?,?,?)";
BufferedReader brtxt = null;
InputStream isimg = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"test");
// 从文件中获取输入流--读取文本
InputStream istxt = Thread.currentThread().getContextClassLoader()
.getResourceAsStream("test.txt");
brtxt = new BufferedReader(new InputStreamReader(istxt));
//设置Blob
pstmt.setCharacterStream(2,brtxt);
//从文件中获取输入流--读取图片
isimg = Thread.currentThread().getContextClassLoader()
.getResourceAsStream("panda.png");
pstmt.setBinaryStream(3,isimg);
if (pstmt.executeUpdate() == 1){
System.out.println("恭喜成功添加一条记录！");
}else{
System.out.println("添加记录失败！");
}
}catch (SQLException e){
e.printStackTrace();
}finally {
try {
brtxt.close();
}catch (IOException e){
e.printStackTrace();
}
try{
isimg.close();
}catch (IOException e){
e.printStackTrace();
}
CloseFactory.close(pstmt,conn);
}
}
public static void main(String[] args) throws SQLException {
BlobInsertTest.insert();
}
}
3）LOB的读取
package com.yyq.blob;
import com.yyq.factory.CloseFactory;
import com.yyq.factory.ConnectionFactory;
import java.io.*;
import java.net.ConnectException;
import java.sql.*;
/**
* Created by gao on 16-4-13.
*/
public class BlobSelectTest {
public static void select() {
Connection conn = ConnectionFactory.getConnection();
PreparedStatement pstmt = null;
String sql = "select id,name,content,image from stuinfo where name=?";
ResultSet rs = null;
BufferedReader br = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "test");
rs = pstmt.executeQuery();
while (rs.next()) {
Reader rd = rs.getCharacterStream(3);
br = new BufferedReader(rd);
String str = null;
while ((str = br.readLine()) != null) {
System.out.println(str);
}
Blob blob = rs.getBlob(4);
BufferedInputStream bis = new BufferedInputStream(blob.getBinaryStream());
BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream("newpanda.png"));
byte[] bys = new byte[1024];
int len = 0;
while ((len = bis.read(bys, 0, 1024)) != -1) {
bos.write(bys, 0, len);
}
bos.flush();
bos.close();
System.out.println("\n-------------图片写好了！");
}
}catch (SQLException e){
e.printStackTrace();
}catch (IOException e){
e.printStackTrace();
}finally {
CloseFactory.close(pstmt,conn);
}
}
public static void main(String[] args) {
BlobSelectTest.select();
}
}