4.操作Blob类型字段&批量插入

一、MySQL BLOB类型

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据；插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接；MySQL的四种BLOB类型，除了在存储的最大信息量上不同外，他们是等同的

类型	大小(单位：字节)
TinyBlob	最大255
Blob	最大65k
MediumBlob	最大16M
LongBlob	最大4G

• 实际使用中根据需要存入的数据大小定义不同的BLOB类型
• 需要注意的是：如果存储的文件过大，数据库的性能会下降
• 如果在指定了相关的BLOB类型以后，还报错：xxx too large，那么在MySQL的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动MySQL服务

1.向数据表中插入BLOB类型数据

package blob5;

import org.junit.Test;
import util.JDBCUtils;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * 测试使用preparedStatement操作Blob类型的数据
 */
public class BlobTest {
    //向数据表customers中插入Blob类型字段
    @Test
    public void testInsert() throws Exception {
        //1.获取数据库连接
        Connection conn = JDBCUtils.getConnection();
        //2.预编译sql语句，返回prepareStatement的实例
        String sql = "INSERT INTO customers(`name`,email,birth,photo)VALUES(?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        //3.填充占位符
        ps.setObject(1, "李白");
        ps.setObject(2, "libai@qq.com");
        ps.setObject(3, "2015-09-22");
        FileInputStream is = new FileInputStream(new File("/Users/mcc/IdeaProjects/JDBC_1/src/b151f8198618367a1dee3e70271442d2b21ce5a2.jpeg"));
        ps.setBlob(4, is);
        //4.执行
        ps.execute();
        //5.关闭资源
        JDBCUtils.closeResource(conn, ps);
    }
}

2.从数据表中读取BLOB类型数据

//查询数据表customers中Blob类型字段
@Test
public void testQuery() {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet resultSet = null;
    InputStream is = null;
    FileOutputStream fos = null;
    try {
        //1.获取数据库连接
        conn = JDBCUtils.getConnection();
        //2.预编译sql语句，返回prepareStatement的实例
        String sql = "SELECT id,`name`,email,birth,photo FROM customers WHERE id = ?";
        ps = conn.prepareStatement(sql);
        //3.填充占位符
        ps.setObject(1,26);
        //4.执行并返回结果集
        resultSet = ps.executeQuery();
        //5.处理结果集
        if (resultSet.next()){
//            //方式一：
//            int id = resultSet.getInt(1);
//            String name = resultSet.getString(2);
//            String email = resultSet.getString(3);
//            Date birth = resultSet.getDate(4);
            //方式二：不许考虑sql语句内的顺序
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String email = resultSet.getString("email");
            Date birth = resultSet.getDate("birth");
            //前四个内容封装在customer对象中
            Customer customer = new Customer(id, name, email, birth);
            System.out.println(customer);
            //以流的方式获取大数据photo，其实就是将该图片下载下来，以文件的方式保存在本地
            Blob photo = resultSet.getBlob("photo");
            is = photo.getBinaryStream();
            fos = new FileOutputStream(new File("libai.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 {
        //6.关闭资源
        JDBCUtils.closeResource(conn,ps,resultSet);
        try {
            if (is != null)
                is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            if (fos != null)
                fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

二、批量插入

1.批量插入数据的操作

update、delete本身就具有批量操作的效果，这里说的批量操作，主要指的是批量插入，那么使用PreparedStatement如何实现更高效的批量插入？

# 向goods表中插入两万条数据
CREATE TABLE goods ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR ( 25 ) );

package blob5;

import org.junit.Test;
import util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * 使用PreparedStatement实现批量数据的操作
 */
public class InsertTest {
    @Test
    public void testInsert1() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();//开始时间
            //1.获取数据库连接
            conn = JDBCUtils.getConnection();
            //2.预编译sql语句，返回prepareStatement的实例
            String sql = "INSERT INTO goods(`NAME`)VALUES(?) ";
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < 20000; i++) {
                ps.setObject(1, "name_" + i);
                //4.执行
                ps.execute();
            }
            long end = System.currentTimeMillis();//结束时间
            System.out.println("花费的时间为：" + (end - start));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JDBCUtils.closeResource(conn, ps);
        }
    }
}

2.批量插入数据优化

优化：减少io，使用addBatch()、executeBatch()、clearBatch()
MySQL服务器默认是关闭批处理的，我们需要通过一个参数，让MySQL开启批处理的支持
?rewriteBatchedStatements=true 写在配置文件的url后面

@Test
public void testInsert2() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long start = System.currentTimeMillis();//开始时间
        //1.获取数据库连接
        conn = JDBCUtils.getConnection();
        //2.预编译sql语句，返回prepareStatement的实例
        String sql = "INSERT INTO goods(`NAME`)VALUES(?) ";
        ps = conn.prepareStatement(sql);
        //3.填充占位符
        for (int i = 0; i < 20000; i++) {
            ps.setObject(1, "name_" + i);
            //4."攒"SQL，不立即执行
            ps.addBatch();
            if (i % 500 == 0){
                ps.executeBatch();//攒到500的时候执行一次
                ps.clearBatch();//清空Batch
            }
        }
        long end = System.currentTimeMillis();//结束时间
        System.out.println("花费的时间为：" + (end - start));
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //5.关闭资源
        JDBCUtils.closeResource(conn, ps);
    }
}

3.批量插入数据最终优化☆

优化方式：设置连接不允许自动提交数据

@Test
public void testInsert3() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long start = System.currentTimeMillis();//开始时间
        //1.获取数据库连接
        conn = JDBCUtils.getConnection();
        //设置不允许自动提交数据
        conn.setAutoCommit(false);
        //2.预编译sql语句，返回prepareStatement的实例
        String sql = "INSERT INTO goods(`NAME`)VALUES(?) ";
        ps = conn.prepareStatement(sql);
        //3.填充占位符
        for (int i = 0; i < 20000; i++) {
            ps.setObject(1, "name_" + i);
            //4."攒"SQL，不立即执行
            ps.addBatch();
            if (i % 500 == 0) {
                ps.executeBatch();//攒到500的时候执行一次
                ps.clearBatch();//清空Batch
            }
        }
        //统一提交数据
        conn.commit();
        long end = System.currentTimeMillis();//结束时间
        System.out.println("花费的时间为：" + (end - start));
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //5.关闭资源
        JDBCUtils.closeResource(conn, ps);
    }
}