JDBC核心技术四（Blob字段和批量插入）

系列笔记目录

1. JDBC核心技术一（概述）
2. JDBC核心技术二（获取数据库连接）
3. JDBC核心技术三（PreparedStatement）
4. JDBC核心技术四（Blob字段和批量插入）
5. JDBC核心技术五 （数据库事务）
6. JDBC核心技术六（数据库连接池）
7. JDBC核心技术七（CallableStatement)

实际开发业务中可能会有数据库存储图片视频等（一般存放这些资源的URL）和批量插入操作，探讨一下如何采用JDBC实现；

一、JDBC操作Blob类型字段

1. MySQL BLOB类型

• MySQL中，BLOB是一个二进制大类型对象，可以存储大量数据的容器，容纳不同大小的数据。
• BLOB类型无法通过拼接字符串的SQL实现操作，必须得是PreparedStatement来进行操作；
• MySQL的四种BLOB类型：
  
• 数据库中存入的文件过大，数据库的性能会下降（一般不直接存储大文件在数据库）；
• 如需存储大容量的数据，需修改mysql的配置文件中的my.ini的配置参数：

#自定义大小
max_allowed_packet = 16M

2、向数据库插入大数据类型（BLOB）

• 插入BLOB类型数据的代码如下所示：

//获取连接
Connection conn = JDBCUtils.getConnection();

//预编译SQL，获取PreparedStatement对象		
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
ps.setString(1, "张三");
ps.setString(2, "zs@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));

// 操作Blob类型的变量，"xx.png"为本地磁盘上的相片
FileInputStream fis = new FileInputStream("xxx.png");
ps.setBlob(4, fis);

//执行
ps.execute();		
fis.close();
JDBCUtils.closeResource(conn, ps);

3. 修改数据表中的BLOB类型字段

Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("xx.png");
ps.setBlob(1, fis);
ps.setInt(2, 25);

ps.execute();
fis.close();
JDBCUtils.closeResource(conn, ps);

4. 从数据表中读取BLOB大数据类型

//获取数据库连接
conn = getConnection();

//预编译MySQL，获取PreparedStatement对象操作数据库
String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
ps = conn.prepareStatement(sql);

//填充参数，并查询
ps.setInt(1, 8);
rs = ps.executeQuery();

//获取查询结果
if(rs.next()){
	Integer id = rs.getInt(1);
    String name = rs.getString(2);
	String email = rs.getString(3);
    Date birth = rs.getDate(4);
	Customer cust = new Customer(id, name, email, birth);
    System.out.println(cust); 
    
    //读取Blob类型的字段
	Blob photo = rs.getBlob(5);
	InputStream is = photo.getBinaryStream();
	OutputStream os = new FileOutputStream("c.jpg");
	byte [] buffer = new byte[1024];
	int len = 0;
	while((len = is.read(buffer)) != -1){
		os.write(buffer, 0, len);
	}

	//关闭资源
    JDBCUtils.closeResource(conn, ps, rs);	
	if(is != null){
		is.close();
	}	
	if(os !=  null){
		os.close();
	}
}

操作BLOB类型数据，就是调用setBlob()和getBlob()来操作，其他的操作与Java IO操作相同

二、批量插入

• 当需要成批插入或更新记录时，可采用Java的批量更新机制，允许多条SQL语句一次提交给数据库批量处理，通常比单独提交处理更有效率；
• JDBC批量处理语句包括以下三个方法：
  • addBatch(String sql)：添加需要批量处理的SQL语句或参数；
  • executeBatch()：批量执行SQL语句；
  • clearBatch()：清空缓存的数据；
• 两种情况的批量插入：
  • 多条SQL语句的批量处理；
  • 一个SQL语句的批量传参；

1. 批量插入

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

• 创建goods表，创建如下：

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

2. Statement批量插入

//向数据库中插入20000条数据
    public void testInsert1(){
        Connection conn = null;
        Statement statement = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();
            statement = conn.createStatement();
            for (int i = 0; i < 20000; i++) {
                String sql = "insert into goods (name) values ('name_" + i + " ')";
                statement.execute(sql);
            }
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));  //38785
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, statement);
        }
    }

• Statement批量插入相当于编译执行了20000次SQL语句，执行时间很慢；
• 执行时间为：

花费的时间为：48690 ms

3. PreparedStatement批量插入

    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.setString(1, "name_" + i);
                ps.execute();
            }
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start) + " ms"); //38529
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
    }

• PreparedStatement采用预编译和缓存的特点，相比较Statement能快一些，但是同样不理想；
• 执行时间：

花费的时间为：40209 ms

4. PreparedStatement中的executeBatch()批量操作

    public void testInsert3(){
        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);
            // 每500次批量处理一次
            for (int i = 0; i <= 20000; i++) {
                ps.setString(1, "name_" + i);
                ps.addBatch();
                if(i % 500 == 0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start)); //34021
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
    }

• PreparedStatement使用addBatch()攒SQL
• 使用executeBatch()执行操作；
• 使用clearBatch()清空操作；
  执行时间为：

花费的时间为：36039 ms

• 注意的点： MySQL的默认配置为自动提交，即在没到500次之前其实已经提交了（不是真的批处理）；

5. PreparedStatement设置不默认提交的批量操作

    public void testInsert4(){
        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.setString(1, "name_" + i);
                ps.addBatch();
                if(i % 500 == 0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start)); //3071
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
    }

• 执行的花费时间:

花费的时间为：3155 ms

• 设置不自动提交后的执行时间大大的缩短了；