JDBC 对Blob数据类型的操作以及大批量插入数据

对Blob数据类型的操作

/**
 * @Auther: Alex
 * @Date: 2020/12/26 - 12 - 26 -23:28
 * @Description: 测试使用ProparedStatement操作Blob类型的数据
 * @Verxion: 1.0
 */
public class BlobTest1 {
    //向customers中插入Blob类型的字段
    @Test
    public void test1() 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,"wangjie@gmail.com");
        ps.setObject(3,"1980-01-01");
        //插入Blob类型的数据
        FileInputStream is = new FileInputStream(new File("王杰.jpg"));
        ps.setBlob(4,is);

        //执行
        ps.execute();

        //关闭资源
        is.close();
        JDBCUtils.closeResource(conn,ps);
    }

    @Test
    public void test2() throws Exception {
        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.setInt(1,19);

            rs = ps.executeQuery();
            if(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birth = rs.getDate("birth");

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

                //将Blob类型的字段下载下来，以文件的方式保存
                Blob photo = rs.getBlob("photo");
                is = photo.getBinaryStream();
                fos = new FileOutputStream("王杰2.jpg");

                byte[] byteBuffer = new byte[1024];
                int len;
                while ((len = is.read(byteBuffer)) != -1){
                    fos.write(byteBuffer,0,len);
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            is.close();
            fos.close();
            JDBCUtils.closeResource(conn,ps,rs);
        }


    }
}

大批量插入数据

/**
 * @Auther: Alex
 * @Date: 2020/12/27 - 12 - 27 -18:03
 * @Description: BlobTest
 * @Verxion: 1.0
 */

/*
使用PreparedStatement实现批量的操作
说明：
1、update、delete 本身就具有批量操作的效果
2、此时的批量操作，主要是批量插入，使用preparedStatement
   如何实现更高效的批量插入
*/
public class InsertTest {
    /*向goods表中插入20000条数据
    方式一：使用statement
    Connection conn = JDBCUtils.getConnection();
    Statement st = conn.CreateStatement();
    for(int i = 1;i <= 20000;i++){
        String sql = "insert into goods(name) values ('name_"+ i +"')"
        st.execute(sql);
    }
     */
    //方式二：使用preparedStatement
    @Test
    public void test() throws Exception {
        long start = System.currentTimeMillis();
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into goods (name) values (?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < 20000; i++) {
            ps.setObject(1,"naem_" + i);
            ps.execute();
        }
        JDBCUtils.closeResource(conn,ps);
        long end = System.currentTimeMillis();
        System.out.println(end - start);
    }

    /*方式三：
    1、addBatch():
    2、executeBatch()
    3、clearBatch()

    注意：
    1、mysql默认情况是关闭批处理的，我们需要通过一个参数，让mysql开启批处理支持
    2、 ?rewriteBatchedStatements=true写在配置文件的url后面
    3、使用更新的mysql驱动，导入jar包
     */
    @Test
    public void test2() throws Exception {
        long start = System.currentTimeMillis();
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into goods (name) values (?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < 200000; i++) {
            ps.setObject(1,"naem_" + i);
            //1、addBatch()：积攒 sql 语句
            ps.addBatch();
            //2、executeBatch():执行积攒的 sql 语句
            if(i % 1000 == 0){
                ps.executeBatch();
                //3、清空积攒的 sql 语句
                ps.clearBatch();
            }
        }
        JDBCUtils.closeResource(conn,ps);
        long end = System.currentTimeMillis();
        System.out.println(end - start);
    }

    /*
    方式四:设置不允许自动提交数据

     */
    @Test
    public void test3() throws Exception {
        long start = System.currentTimeMillis();
        Connection conn = JDBCUtils.getConnection();

        //设置不允许自动提交数据
        conn.setAutoCommit(false);

        String sql = "insert into goods (name) values (?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < 200000; i++) {
            ps.setObject(1,"naem_" + i);
            //1、addBatch()：积攒 sql 语句
            ps.addBatch();
            //2、executeBatch():执行积攒的 sql 语句
            if(i % 1000 == 0){
                ps.executeBatch();
                //3、清空积攒的 sql 语句
                ps.clearBatch();
            }
        }

        //统一提交数据
        conn.commit();
        JDBCUtils.closeResource(conn,ps);
        long end = System.currentTimeMillis();
        System.out.println(end - start);
    }
}