操作BLOB类型字段
mysql中的BLOB类型
向数据表中插入Blob类型数据
//向数据表(customers表)中插入Blob类型的字段
@Test
public void testInsert() throws Exception {
Connection conn = JDBCUtil.getConnection();
String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1,"张三");
ps.setObject(2,"zhangsan@qq.com");
ps.setObject(3,"2022-03-22");
//将图片存入数据库
FileInputStream is = new FileInputStream(new File("1.jpg"));//相对路径
ps.setBlob(4,is);
ps.execute();
JDBCUtil.closeResource(conn,ps);
}
修改数据表中的Blob类型字段
Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
// 填充占位符
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis);
ps.setInt(2, 25);
ps.execute();
fis.close();
JDBCUtils.closeResource(conn, ps);
从数据表中读取Blob类型数据
//查询数据表customers中Blob类型的字段
@Test
public void testQuery() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
InputStream is= null;
FileOutputStream fos=null;
try {
conn = JDBCUtil.getConnection();
String sql = "select id,name,email,birth,photo from customers 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");
Customers cust = new Customers(id, name, email, birth);
Blob photo = rs.getBlob("photo");
//将Blob类型的字段下载下来,以文件的方式保存到本地
is = photo.getBinaryStream();
fos = new FileOutputStream(new File("zansan.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();
}
JDBCUtil.closeResource(conn,ps,rs);
}
}
插入Blob字段特殊情况说明
如果在指定了相关的Blob类型以后,还报错:xxx too large,那么在mysql的安装目录下,找my.ini文件加上如下的配置参数: max_allowed_packet=16M。同时注意:修改了my.ini文件之后,需要重新启动mysql服务。