操作BLOB类型字段和批量插入

操作BLOB类型字段

MySQL BLOB类型

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据

插入BLOB类型的数据必须使用 PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写成

MySQL的四种BLOB类型 (除过存储的最大信息量不同，他们之间是等同的)

实际使用中根据存入的数据大小定义不同的BLOB类型

需要注意的是：如果存储的数据过大，数据库的性能会下降

如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务

向数据表中插入大数据类型

public class BlobTest {
    //向数据表customers 中添加 Blob字段
    @Test
    public void testInsert() throws Exception {
        Connection connection = JDBCutils.getConnection();

        String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1,"冰冰");
        ps.setObject(2,"bingbing@qq.com");
        ps.setObject(3,"1992-01-19");

        FileInputStream fis = new FileInputStream(new File("01.jpg"));
        ps.setBlob(4,fis);

        ps.execute();

        JDBCutils.closeResourse(connection,ps);
    }
}

修改数据表中的Blob类型字段

public class BlobTest {
    //向数据表customers 中添加 Blob字段
    @Test
    public void testInsert() throws Exception {
        Connection connection = JDBCutils.getConnection();

        String sql = "update customers set photo = ? where id = ?";

        PreparedStatement ps = connection.prepareStatement(sql);
		
		
        FileInputStream fis = new FileInputStream(new File("coffee.png"));
        ps.setBlob(1,fis);
        ps.setInt(2,25)

        ps.execute();

        JDBCutils.closeResourse(connection,ps);
    }
}

从数据表中读取大数据类型

public class BlobTest {
    //查询数据表customers中Blob类型的字段
    @Test
    public void testQuery() throws Exception {
        Connection connection = JDBCutils.getConnection();


        String sql = "select id,name,email,birth,photo from customers where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setInt(1,21);

        ResultSet 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);

            //方式二
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String email = rs.getString("email");
            Date birth = rs.getDate("birth");

            Customers cust = new Customers(id, name, email, birth);

            //将Blob类型的字段下载下来,以文件的方式保存在本地
            Blob photo = rs.getBlob("photo");
            InputStream is = photo.getBinaryStream();

            FileOutputStream fos = new FileOutputStream(new File("02.jpg"));
            byte[] buffer = new byte[1024];
            int len;
            while ((len = is.read(buffer)) != -1){
                fos.write(buffer,0,len);
            }

            JDBCutils.closeResourse(connection,ps,rs);
            is.close();
            fos.close();
        }
    }
}

批量插入

批量执行SQL语句

当需要成批插入或者更新记录时，可以采用 Java 的批量更新机制，这一机制允许多条语句一次性提交个数据库批量的处理通常情况下比单独提交处理更有效率

JDBC的批量处理语句包括以下三个方法：

• addBatch(String)：添加需要批量处理的 SQL语句或是参数
• executeBatch()：执行批量处理语句
• clearBatch()：清空缓存的数据

通常我们会遇到两种批量执行的 SQL 语句的情况：

• 多条 SQL语句的批量传参
• 一个 SQL语句的批量传参

高效的批量插入

举例：向数据表中插入 20000条数据

• 数据库提供一个 goods表。创建如下：

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(25)
);

使用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.executeUpdate(sql);
}

使用PreparedStatement

public class BatchInsert {
    //方式二 使用PreparedStatement
    @Test
    public void BatchInsert2(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();

            connection = JDBCutils.getConnection();
            
            String sql = "insert into goods(name)values(?)";
            ps = connection.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));
            //花费的时间为：40646
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResourse(connection,ps);
        }

    }
}

实现层次三

使用 addBatch() / executeBatch() / clearBatch()
mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持
	?rewriteBatchedStatements=true 写在配置文件的url后面
使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar

public class BatchInsert {
	    @Test
    public void BatchInsert3(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();

            connection = JDBCutils.getConnection();

            String sql = "insert into goods(name)values(?)";
            ps = connection.prepareStatement(sql);

            for(int i = 1;i <= 20000;i++){
                ps.setObject(1,"name_" + i);
                //1."攒"Batch
                ps.addBatch();
                if(i % 500 == 0){
                    //2.执行Batch
                    ps.executeBatch();
                    //3.清空Batch
                    ps.clearBatch();
                }
            }

            long end = System.currentTimeMillis();

            System.out.println("花费的时间为：" + (end - start));
            //花费的时间为：1758
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResourse(connection,ps);
        }

    }
}

实现层次四

在层次三的基础上，使用Connection 的 setAutoCommit(false) / commit()

public class BatchInsert {
	@Test
    public void BatchInsert4(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();

            connection = JDBCutils.getConnection();

            //设置不允许自动提交数据
            connection.setAutoCommit(false);
            String sql = "insert into goods(name)values(?)";
            ps = connection.prepareStatement(sql);

            for(int i = 1;i <= 20000;i++){
                ps.setObject(1,"name_" + i);
                //1."攒"Batch
                ps.addBatch();
                if(i % 500 == 0){
                    //2.执行Batch
                    ps.executeBatch();
                    //3.清空Batch
                    ps.clearBatch();
                }
            }

            //统一提交数据
            connection.commit();
            long end = System.currentTimeMillis();

            System.out.println("花费的时间为：" + (end - start));
            //未测试
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResourse(connection,ps);
        }

    }
}

参考文献

链接：尚硅谷JDBC核心技术