MySQL BLOB类型简介:
向Customers表中插入一条记录,包含BLOB类型(图片)
删除和修改与插入一致,在这里不再演示(增删改大概一致)
package test_blob;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
/*
* 向Customers表中插入BLOB类型的字段(例如图片)
*/
import org.junit.Test;
import myjdbc.utils.JDBCUtil;
public class TestInsertPhoto {
@Test
public void insertPhoto() throws Exception {
//获取连接
Connection conn = JDBCUtil.getConnection();
//获取PreparedStatement实例
String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
//填充占位符
ps.setObject(1, "庄周");
ps.setObject(2, "zhuanzhou@qq.com");
ps.setObject(3, "1300-1-1");
FileInputStream fis = new FileInputStream(new File("D:\\eclipse-workspace\\idea_test\\src\\photo.jpg"));
ps.setBlob(4, fis);
//执行
ps.execute();
//关闭资源
JDBCUtil.closeResource(conn, ps);
}
}
查询图片保存到本地(第二种方式是通过元素的别名来获取)
详细代码如下:
package test_blob;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import myjdbc.utils.JDBCUtil;
import preparedstatement.curd.Customers;
public class TestQueryBLOB {
@Test
public void queryBLOB() throws Exception {
//获取连接
Connection conn = JDBCUtil.getConnection();
//获取PreparedStatement实例
String sql = "select id,name,email,birth,photo from customers where name=?";
PreparedStatement ps = conn.prepareStatement(sql);
//填充占位符
ps.setObject(1, "庄周");
//获得结果集
ResultSet rs = ps.executeQuery();
//打印记录的基本信息,把BLOB对象(图片)使用流保存在本地
if(rs.next()) {
// int id = rs.getInt(1); //也可使用下标的方式去获得属性
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customers customers = new Customers(id,name,email,birth);
System.out.println(customers);
Blob photo = rs.getBlob("photo");
InputStream is = photo.getBinaryStream();
FileOutputStream fos = new FileOutputStream(new File("src/zhuangzhou.jpg"));
int len;
byte[] buffer = new byte[1024];
while( (len = is.read( buffer ) ) !=-1) {
fos.write(buffer, 0, len);
System.out.println("*");
}
//关闭资源
fos.close();
is.close();
JDBCUtil.closeResource(conn, ps,rs);
}
}
}