第4章 操作BLOB类型字段
*MySQL中,BLOB是一个二进制大型对象,是一个可以存储大量数据的容器
*插入BLOB类型的数据必须使用PreparedStatement
*MySQL的四种BLOB类型:
TinyBlob:最大255;Blob:最大65k;MediumBlob:最大16M;LongBlob:最大4G
1.向数据表中插入Blob类型数据
public void testInsert() {
Connection conn = null;
PreparedStatement pr = null;
try {
conn = JDBCUtiles.getConnection();
String sql="insert into customers (name,email,birth,photo) values (?,?,?,?)";
pr = conn.prepareStatement(sql);
pr.setObject(1,"桃桃");
pr.setObject(2,"tao@163.com");
pr.setObject(3,"2018-12-26");
FileInputStream fis = new FileInputStream(new File("Figure_1.png"));
pr.setBlob(4,fis);
pr.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtiles.closeResource(conn,pr);
}
}
2.从数据表中读取Blob类型数据
public void testQuery() {
Connection conn = null;
PreparedStatement pr = null;
InputStream is = null;
FileOutputStream os = null;
ResultSet query=null;
try {
String sql="select * from customers where id=?";
conn = JDBCUtiles.getConnection();
pr = conn.prepareStatement(sql);
pr.setObject(1,16);
query = pr.executeQuery();
while (query.next()){
Blob photo = query.getBlob("photo");
is = photo.getBinaryStream();
os = new FileOutputStream("zhuyin.jpg");
byte[] bytes = new byte[1024];
int len;
while ((len= is.read(bytes))!=-1){
os.write(bytes,0,len);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtiles.closeResource(conn,pr,query);
try {
assert is != null;
is.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
assert os != null;
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
*如果在指定了相关的Blob类型以后,还报错xxx too large,那么在MySQL的安装目录下,找my.ini文件加上如下的配置参数:
max_allowed_packet=16M,注意修改了my.ini文件后要重启MySQL服务:右键我的电脑à管理à服务和应用程序à服务à找到MySQLà右键à重新启动
第5章 批量插入
*MySQL服务器默认是关闭批处理的,需要在配置文件的url后面写如下语句:?rewriteBatchedStatements=true
//使用addBatch()、executeBatch()、clearBatch()
//向goods表中插入20000条数据
@Test
public void insertTest1(){
Connection conn = null;
PreparedStatement pr = null;
try {
conn = JDBCUtiles.getConnection();
//设置不允许自动提交数据
conn.setAutoCommit(false);
String sql="insert into goods (name) values (?)";
pr = conn.prepareStatement(sql);
for (int i = 1; i <= 20000; i++) {
pr.setString(1,"name_"+i);
//批量(例如批量500执行一次)
pr.addBatch();
if(i%500==0){
//批量执行
pr.executeBatch();
//清空
pr.clearBatch();
}
}
//提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtiles.closeResource(conn,pr);
}
}