MySQL中,BLOB是一个二进制大型对象,是一个可以存储大量数据的容器,它能容纳不同大小的数据。BLOB类型实际是个类型系列(TinyBlob、Blob、MediumBlob、LongBlob),除了在存储的最大信息量上不同外,他们是等同的。
MySQL的四种Blob类型
TinyBlob 最大 255B
Blob 最大 65K
MediumBlob 最大 16M
LongBlob 最大 4G
PreparedStatement操作Blob类型的变量
写入操作的方法:
setBlob(InputStream is);
读取操作的方法:
Blob blob = getBlob(int index);
InputStream is = blob.getBinaryStream();
具体的插入操作(向person表中插入Blob类型的字段)
public void blobInsert() throws Exception{
Connection conn = JDBCUtils.getConnection();
String sql = "insert into person(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1,"张三");
ps.setObject(2, "zhangS@qq.com");
ps.setObject(3,"1992-09-08");
FileInputStream is = new FileInputStream(new File("photo1.jpg"));
ps.setBlob(4, is);
ps.execute();
JDBCUtils.closeResource(conn, ps);
}
具体的查询操作(读取person表中的Blob类型的字段)
public void blobQuery(){
Connection conn = null;
PreparedStatement ps = null;
InputStream is = null;
FileOutputStream fos = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,email,birth,photo from person where id = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 21);
rs = ps.executeQuery();
if(rs.next()){
//方式一:
//int id = rs.getInt(1);
//String name = rs.getString(2);
//String email = rs.getString(3);
//Date birth = rs.getDate(4);
//方式二:
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Person = new Person(id, name, email, birth);
System.out.println(cust);
//将Blob类型的字段下载下来,以文件的方式保存在本地
Blob photo = rs.getBlob("photo");
is = photo.getBinaryStream();
fos = new FileOutputStream("photo1.jpg");
byte[] buffer = new byte[1024];
int len;
while((len = is.read(buffer)) != -1){
fos.write(buffer, 0, len);
}
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(is != null)
is.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
if(fos != null)
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
JDBCUtils.closeResource(conn, ps, rs);
}
}
注意:
如果在指定了相关的Blob类型之后,还报错:xxx too large,则在MySql的安装目录下,找到my.ini文件加上配置参数:max_allowed_packet=16M,重启MySql。