Mysql中,Blob是一个二进制大型对象,是一个可以存储大量数据的容器,它能容纳不同大小的数据
MySQL 的四种Blob类型:(除了在存储的最大信息量上不同外,它们是等同的,如果存储的文件过大,数据库的性能会下降)
类型 | 大小(单位:字节) |
---|---|
TinyBlob | 最大 255byte |
Blob | 最大 65k |
MediumBlob | 最大 16M |
LongBlob | 最大 4G |
public class JDBCTest03 {
// 插入图片,在数据库设置对应图片字段的Blob类型
// 插入Blob类型的数据必须使用PreparedStatement,因为Blob类型的数据无法使用字符串拼写
public void testInsertBlob() throws ClassNotFoundException, SQLException, FileNotFoundException {
String sql = "INSERT INTO customers(name, email, birth, picture)"
+ "VALUES(?,?,?,?)";
String driverClass = "com.mysql.jdbc.Driver";
String jdbcurl = "";
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(jdbcurl,"user","password");
PreparedStatement preparedStatement = connection.prepareStatement(sql);
InputStream inputStream = new FileInputStream("picture.jpg");
preparedStatement.setBlob(4,inputStream);
preparedStatement.executeUpdate();
preparedStatement.close();
connection.close();
}
// 读取图片
// 读取blob数据:
// 1. 使用getBlob方法读取到Blob对象
// 2.调用Blob的getBinaryStream()方法得到输入流。再使用IO操作即可
public void readBlob() throws ClassNotFoundException, SQLException, IOException {
String sql = "SELECT id, name customerName, email, birth, picture "
+ "FROM customers WHERE id = 13";
String driverClass = "com.mysql.jdbc.Driver";
String jdbcurl = "";
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(jdbcurl,"user","password");
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Blob picture = resultSet.getBlob(5);
InputStream in = picture.getBinaryStream();
OutputStream out = new FileOutputStream("newPicture.jpg");
byte[] buffer = new byte[1024];
int len = 0;
while ((len = in.read(buffer)) != -1){
out.write(buffer,0,len);
}
in.close();
out.close();
}
resultSet.close();
preparedStatement.close();
connection.close();
}
}