一、在MySQL中 BLOB是二进制数据对象,他是一个可以存储大数据的容器,在MySQL中有四中BLOB 类型,这四种类型分别对应存储不同容量的数据:
1.TINYBLOB 最大存储255字节
2.BLOB 最大65K
3.MEDIUMBLOB 最大16M
4.LONGBLOB 最大4G
实际使用中要根据需要存入的内容选择定义不同的BLOB 类型,如果存入的数据过大,可能会降低数据库的性能。
二、在插入数据的时候要用PreparedStatement 如果用Statement的话,在SQL语句中没法拼接,只能将BLOB数据作为一个对象传递进来。
public void testInsertBlob() {
Connection connection = null;
PreparedStatement preparedstatement = null;
try {
connection = new GetMyConnection().getMyConnection();
String sql = "INSERT INTO customer (name, email, picture) "
+ "VALUES(?,?,?)";
preparedstatement = connection.prepareStatement(sql);
preparedstatement.setString(1, "马六");
preparedstatement.setString(2, "maliu@163.com");
//
InputStream inputStream = new FileInputStream("17ccfc.jpg");
preparedstatement.setBlob(3, inputStream);
preparedstatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally{
GetMyConnection.closeMy(null, preparedstatement, connection);
}
三、ResultSet API提供的getBlob()方法返回的是一个Blob类型,所以我们要创建一个Blob对象来接收,然后用I/O的方式将他读出来:
public void testGetBlob(){
Connection connection = null;
PreparedStatement preparedstatement = null;
ResultSet resultset = null;
try {
connection = new GetMyConnection().getMyConnection();
String sql = "SELECT id, name, email, picture FROM customer "
+ "WHERE id = ?";
preparedstatement = connection.prepareStatement(sql);
preparedstatement.setInt(1, 2);
resultset = preparedstatement.executeQuery();
if(resultset.next()){
int id = resultset.getInt("id");
String name = resultset.getString("name");
String email = resultset.getString("email");
System.out.println("id=" + id + ", name= " + name + ", email= "
+ email);
Blob picture = resultset.getBlob("picture");
InputStream in = picture.getBinaryStream();
OutputStream out = new FileOutputStream("narcissus.jpg");
byte[] buffer = new byte[1024];
int i = 0;
while((i = in.read(buffer)) != -1){
out.write(buffer, 0, i);
}
out.close();
in.close();
}
} catch (Exception e) {
e.printStackTrace();
} finally{
GetMyConnection.closeMy(resultset, preparedstatement, connection);
}