使用PrepareStatement实现操作Blob类型的数据（库里图片）
1、向数据库中添加一条记录（包含blob类型的字段）
添加之前的数据库的数据
数据表customer结构
这里的photo使用的是mediumBlob数据类型
代码：
public void testInsertCustomPhoto() {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JDBCUtils.getConnection();
String sql="insert into customer values(null,?,?,?,?)";
ps = connection.prepareStatement(sql);
ps.setObject(1,"张宇浩");
ps.setObject(2,"2020-09-08");
ps.setObject(3,"zhang@qq.com");
ps.setBlob(4,new FileInputStream(new File("E:\\code\\idealUProjects\\mysql_jdbc\\库里1.jpg")));
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,ps);
}
}
2、查询数据表customer的photo字段（Blob类型）
将查询到的photo保存到本地：
代码：
@Test
public void testGetCustomer() {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
String sql="select * from customer where name=?";
ps = connection.prepareStatement(sql);
ps.setObject(1,"张宇浩");
rs = ps.executeQuery();
if(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
Date birth = rs.getDate("birth");
String email = rs.getString("email");
Customer customer = new Customer(id, name, birth, email);
System.out.println(customer);
Blob photo = rs.getBlob("photo");
InputStream is= photo.getBinaryStream();
FileOutputStream fos = new FileOutputStream(new File("zhangyuhao.jpg"));
byte[] bys=new byte[1024];
int len;
while((len=is.read(bys))!=-1){
fos.write(bys,0,len);
}
if(is!=null){
is.close();
}
if(fos!=null){
fos.close();
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,ps,rs);
}
-插入Blob字段特殊情况的说明：
上面存储的图片采用的是mediumBlob数据类型
虽然medium最大的限制大小是16M，但是当你的图片超过1MB时，需要设置参数：
报错信息：
D:\java\jdk-1.8\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 -javaagent:D:\idea_install\IntelliJ_IDEA_2018.3\lib\idea_rt.jar=64828:D:\idea_install\IntelliJ_IDEA_2018.3\bin -Dfile.encoding=UTF-8 -classpath D:\idea_install\IntelliJ_IDEA_2018.3\lib\idea_rt.jar;D:\idea_install\IntelliJ_IDEA_2018.3\plugins\junit\lib\junit-rt.jar;D:\idea_install\IntelliJ_IDEA_2018.3\plugins\junit\lib\junit5-rt.jar;D:\java\jdk-1.8\jre\lib\charsets.jar;D:\java\jdk-1.8\jre\lib\deploy.jar;D:\java\jdk-1.8\jre\lib\ext\access-bridge-64.jar;D:\java\jdk-1.8\jre\lib\ext\cldrdata.jar;D:\java\jdk-1.8\jre\lib\ext\dnsns.jar;D:\java\jdk-1.8\jre\lib\ext\jaccess.jar;D:\java\jdk-1.8\jre\lib\ext\jfxrt.jar;D:\java\jdk-1.8\jre\lib\ext\localedata.jar;D:\java\jdk-1.8\jre\lib\ext\nashorn.jar;D:\java\jdk-1.8\jre\lib\ext\sunec.jar;D:\java\jdk-1.8\jre\lib\ext\sunjce_provider.jar;D:\java\jdk-1.8\jre\lib\ext\sunmscapi.jar;D:\java\jdk-1.8\jre\lib\ext\sunpkcs11.jar;D:\java\jdk-1.8\jre\lib\ext\zipfs.jar;D:\java\jdk-1.8\jre\lib\javaws.jar;D:\java\jdk-1.8\jre\lib\jce.jar;D:\java\jdk-1.8\jre\lib\jfr.jar;D:\java\jdk-1.8\jre\lib\jfxswt.jar;D:\java\jdk-1.8\jre\lib\jsse.jar;D:\java\jdk-1.8\jre\lib\management-agent.jar;D:\java\jdk-1.8\jre\lib\plugin.jar;D:\java\jdk-1.8\jre\lib\resources.jar;D:\java\jdk-1.8\jre\lib\rt.jar;E:\code\idealUProjects\mysql_jdbc\out\production\mysql_jdbc;E:\code\idealUProjects\mysql_jdbc\hamcrest-core-1.3.jar;E:\code\idealUProjects\mysql_jdbc\junit-4.12.jar;E:\code\idealUProjects\mysql_jdbc\mysql-connector-java-5.1.36-bin.jar;E:\code\idealUProjects\mysql_jdbc\druid-1.1.10.jar;E:\code\idealUProjects\mysql_jdbc\commons-dbutils-1.6.jar com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 myself_test.commonControl.Common,testUpdateCustomPhoto
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (3611685 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3552)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2429)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)
at myself_test.commonControl.Common.testUpdateCustomPhoto(Common.java:253)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
这就是因为数据库里面的字段设置的是mediumBlob类型，
max_allowed_packet=16M
现在就可以存储这个
在这里插入图片描述