实现对数据的CRUD操作
对Blob字段进行CRUD操作
实际开发中涉及到关闭资源必须使用try-catch-finally语句,为了提高阅读性,本文使用抛出异常的方式处理
-
插入数据
@Test public void insertBlob() throws Exception{ Connection conn = JDBCUtils.getConnection(); String sql = "insert into customers (name,email,birth,photo) values(?,?,?,?)"; PreparedStatement ps = conn.prepareStatement(sql); ps.setObject(1,"孟子"); ps.setObject(2,"mengzi@263.com"); ps.setObject(3,"1980-02-04"); FileInputStream fis = new FileInputStream("4k.jpg"); ps.setBlob(4,fis); ps.execute(); fis.close(); JDBCUtils.closeConnection(conn,ps); }
-
查询数据
@Test public void queryBlob() throws Exception{ Connection conn = JDBCUtils.getConnection(); String sql = "select id,name,email,birth,photo from customers where id = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setObject(1,16); ResultSet resultSet = ps.executeQuery(); InputStream is = null; FileOutputStream fos = null; if(resultSet.next()){ int id = resultSet.getInt("id"); String name = resultSet.getString("name"); String email = resultSet.getString("email"); Date birth = resultSet.getDate("birth"); Customers customers = new Customers(id, name, email, birth); System.out.println(customers); Blob photo = resultSet.getBlob("photo"); is = photo.getBinaryStream(); fos = new FileOutputStream("zy.jpg"); byte[] bytes = new byte[1024]; int len; while ((len = is.read(bytes)) != -1){ fos.write(bytes,0,len); } } fos.close(); is.close(); JDBCUtils.closeConnection(conn,ps,resultSet); }
实现数据的批量操作,DML语句中,删除,修改本身自带批量操作,所以我们这里重点研究批量插入
我们还是一层一层深入,看有什么地方可以优化的
-
第一种方式:插入20000条数据
@Test public void test1() throws Exception{ Connection conn = JDBCUtils.getConnection(); String sql = "insert into goods (foodsName) values (?)"; PreparedStatement ps = conn.prepareStatement(sql); long start = System.currentTimeMillis(); for (int i = 0; i < 20000; i++) { ps.setObject(1,"name_" + i); ps.execute(); } long end = System.currentTimeMillis(); System.out.println("耗时:" + (end-start));//20000条数据:48073 ps.close(); conn.close(); }
通过测试我们可以发现插入20000条数据需要用时48秒,这个速度实在是太慢了,难以忍受。
我们可以对比IO流中的下载文件,我们把byte数组大小设置为1024,使他可以一次性读取1024的长度,再将他下载下来。
我们每次调用ps.execute()时,他都会与数据库进行一次连接,也就是说我们插入20000条数据连接了20000次,那我们可不可以通过类似于IO流中的形式,每隔一段时间连接一次呢?
PreparedStatement提供了三个方法
-
第二种方式:使用addBatch(),executeBatch(), ps.clearBatch()
-
mysql默认服务器是关闭批处理的,我们通过参数开启批处理
rewriteBatchedStatements=true 写在配置文件中的url后面
@Test public void test2() throws Exception{ Connection conn = JDBCUtils.getConnection(); String sql = "insert into goods (foodsName) values (?)"; PreparedStatement ps = conn.prepareStatement(sql); long start = System.currentTimeMillis(); for (int i = 1; i < 1000001; i++) { ps.setObject(1,"name_" + i); //攒sql ps.addBatch(); if(i % 500 == 0){ //执行sql ps.executeBatch(); //释放batch ps.clearBatch(); } } long end = System.currentTimeMillis(); System.out.println("耗时:" + (end-start));//20000条数据:48073 --> 301 JDBCUtils.closeConnection(conn,ps); //1000000条数据:13625 }
我们可以看到插入20000条数据只需0.3秒,基数太小,我们试着插入一百万条数据,需要13秒,那我们还有没有办法对此进行优化呢?
在学习sql的时候,我们知道DML语句每次执行之后都会自动提交,那么我们是否可以让sql先暂时的关闭自动提交,等所有数据都攒好了之后一次性提交呢?
-
-
第三种方式:关闭自动提交
@Test public void test3() throws Exception{ Connection conn = JDBCUtils.getConnection(); //关闭自动提交 conn.setAutoCommit(false); String sql = "insert into goods (foodsName) values (?)"; PreparedStatement ps = conn.prepareStatement(sql); long start = System.currentTimeMillis(); for (int i = 1; i < 1000001; i++) { ps.setObject(1,"name_" + i); //攒sql ps.addBatch(); if(i % 500 == 0){ //执行sql ps.executeBatch(); //释放batch ps.clearBatch(); } } //一起提交 conn.commit(); long end = System.currentTimeMillis(); System.out.println("耗时:" + (end-start));//20000条数据:48073 --> 301 JDBCUtils.closeConnection(conn,ps); //1000000条数据:13625 --> 7460 }
可以看出我们的目的达到了,插入1000000条数据相较于之前的方法,提升了一半的时间