【JDBC】三、Blob和批处理操作

Blob字段的操作

• TinyBlob:255B
• Blob:65K
• Medium:16M
• LongBlob:4G

Blob进行插入操作

将Blob视为一个流，进行流一样的插入

     @Test
    public void testInsert(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCutils.getConnection();
            String sql = "insert into customers(name, email, birth, photo)values(?,?,?,?)";
            ps = conn.prepareStatement(sql);

            ps.setObject(1, "石原里美");
            ps.setObject(2, "bozhi@qq.com");
            ps.setObject(3, "1998-01-02");
            FileInputStream is = new FileInputStream(new File("c77581f805.jpg"));
            ps.setBlob(4, is);

            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(conn, ps);
        }
    }

Blob进行查询操作

   @Test
    public void testQuery(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        InputStream is = null;
        FileOutputStream fos = null;
        try {
            conn = JDBCutils.getConnection();
            String sql = "select id, name, email, birth, photo from customers where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, 20);
            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);

                Customer cust = new Customer(id, email, name, birth);
                System.out.println(cust);


                Blob photo = rs.getBlob("photo");
                is = photo.getBinaryStream();

                fos = new FileOutputStream("石原里美.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();
            }
            JDBCutils.closeResource(conn, ps, rs);
        }

    }

批量操作

基础方式：

    @Test
    public void testInsert(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCutils.getConnection();
            String sql = "insert into goods(NAME) values(?)";
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < 20000; i++){
                ps.setObject(1, "name_" + i);
                ps.execute();
            }
            long end = System.currentTimeMillis();
            System.out.println(end - start);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(conn, ps);
        }
    }

进阶方式（使用批量缓存操作，大量减少数据IO，不用每一条插入一次）：
在5.17中要对properties进行配置：

jdbc:mysql://localhost:3306/testuse?rewriteBatchedStatements=true

在8.0中应有如下配置（8.0也得声明rewriteBatchedStatements=true）：

url=jdbc:mysql://localhost:3306/************自己的数据库名写在这里************?Unicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver

具体实现如下：

    @Test
    public void testInsert2(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCutils.getConnection();
            
            String sql = "insert into goods(NAME) values(?)";
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < 20000; i++){
                ps.setObject(1, "name_" + i);

                ps.addBatch();
                if( i % 500 == 0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            
            System.out.println(end - start);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(conn, ps);
        }
    }

最终方式(关闭自动提交，再次减少IO)

    @Test
    public void testInsert2(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCutils.getConnection();
            conn.setAutoCommit(false);      //关闭自动提交
            String sql = "insert into goods(NAME) values(?)";
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < 20000; i++){
                ps.setObject(1, "name_" + i);

                ps.addBatch();
                if( i % 500 == 0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            conn.commit();      //手动提交
            System.out.println(end - start);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(conn, ps);
        }
    }