10.06JavaWeb之PreparedStatement向表中插入Blob类型数据

什么是Blob类型

概念：

二进制的大数据，可容纳不同大小的数据
Blob类型的数据必须使用PreparedStatement，因为该类型不支持字符串拼接
指定Blob类型以后还报错需要在mysql安装目录下的my.ini初始化文件配置参数max_allowed_packet大小

四种Blob类型文件的大小：

类型	大小(单位:字节)
`TinyBlob`	255
`Blob`	65K
`MediumBlob`	16M
`LongBlob`	4G

注意：

如果存储的文件过大，数据库性能会下降

插入Blob类型数据实例

package JDBCPreparedStatementBlob;

import JDBCStatementCRUD.JDBCUtils;
import org.testng.annotations.Test;

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

/**
 * 测试使用PreparedStatement来操作数据库当中Blob类型的数据
 * @since JDK 1.8
 * @date 2021/10/07
 * @author Lucifer
 */
public class BlobTest {

    //向数据表customers表中插入blobl类型的字段
    @Test
    public void testInsert() throws Exception {
        //获取链接
        Connection conn = JDBCUtils.getConnection();
        //sql语句
        String sql = "insert into customers (`name`, `email`, `birthday`, `photo`)" +
                "values (?, ?, ?, ?)";
        //预编译sql
        PreparedStatement ps = conn.prepareStatement(sql);

        //填充位符
        ps.setObject(1, "法外狂徒");
        ps.setObject(2, "zhangsan@qq.com");
        ps.setObject(3, "1999-09-09");
        //因为第四个是Blob类型，所以插入的时候要用setblo类型--->流传输
        //流文件--->File识别的是当前project下的文件
        FileInputStream is = new FileInputStream(new File("King.jpg"));
        ps.setBlob(4, is);

        //执行语句
        ps.execute();

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

查询数据表当中的Blob类型字段

关键：

将文件以流的形式存入数据库并以流的形式输出到本地计算机

    //查询数据表当中的Blob类型字段
    @Test
    public void testQuery() throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        InputStream is = null;
        FileOutputStream fos = null;
        try {
            //获取链接
            conn = JDBCUtils.getConnection();
            //sql语句
            String sql = "select `id`,`name`,`email`,`birthday`,`photo`" +
                    "from customers where `id` = ?";
            //预编译sql
            ps = conn.prepareStatement(sql);

            //填充占位符
            ps.setInt(1, 21);

            //执行语句
            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 id1 = rs.getInt("id");
                String name1 = rs.getString("name");
                String email1 = rs.getString("email");
                Date birth1 = rs.getDate("birthday");

                //获取Customer对象
                Customer cust = new Customer(id1, name1, email1, birth1);
                System.out.println(cust);

                //Blob类型的数据必须要以流的方式去获取
                Blob photo = rs.getBlob("photo");
                //将该字段下载下来以文件的方式保存在本地上--->二进制流文件
                is = photo.getBinaryStream();
                //自己手写输出流
                fos = new FileOutputStream("zongzhen.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);
        }
    }

注意：

如果图片的大小大于Blob类型数据可存的大小会报错。所以需要设置最大的允许的大小。
- Blob类型限制大小
- Packet限制大小
需要先设置Packet的大小，数据库会自动的定义Blob类型的大小

PreparedStatement最大可能提高性能

特点：

语句在被DBServer的编译器编译后的执行代码被缓存下来，下次调用相同的编译语句不需要编译，直接将参数直接传入编译过的语句执行
对于Statement来说，每执行一次sql都要对传入的语句编译一次。所以内存消耗很大。

批量插入的方式

方式一：使用Statement进行批量插入

    1、Connection conn = JDBCUtils.getConnection();
    2、Statement st = conn.createStatement();
    3、循环添加--->内存负担很大,每执行一次会生成一个sql语句,占用一些内存空间
        for(int i = 1; i<=20000; i++) {
            String sql = "insert into goods (`name`) values('name_" + i + "')";
            //执行sql
            st.execute(sql);
        }

方式二：使用PreparedStatement进行批量插入

    //使用PreparedStatement进行批量插入的替换
    @Test
    public void testInsert1() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
             long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();
            //预编译sql
            String sql = "insert into goods(`name`) values (?)";
            //执行sql
            ps = conn.prepareStatement(sql);
            //填充占位符
            for (int i=1; 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);
        }
    }

方式三：使用addBatch()、executeBatch()、clearBatch()方法进行拉取

注意：

mysql服务器默认是关闭批处理的。在配置文件的url后加入参数?rewriteBatchedStataments=true放在配置文件的URL后
- 再数据库后加上这个参数
5.1.7之后的mysql驱动支持批处理操作

    //批量插入方式三
    /*
    1、减少与数据库的交互，提升效率
    2、采用I/O流的byte缓冲数组思想
        1、addBatch()、executeBatch()、clearBatch()
     */
    @Test
    public void testInsert2() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();
            //预编译sql
            String sql = "insert into goods(`name`) values (?)";
            //执行sql
            ps = conn.prepareStatement(sql);
            //填充占位符
            for (int i=1; i<=20000; i++) {
                ps.setObject(1, "name_" + i);
//                //执行语句
//                ps.execute();
                //攒sql
                ps.addBatch();
                if (i % 1000 == 0) {
                    //攒1000个sql执行一次
                    //执行batch
                    ps.executeBatch();

                    //清空batch
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();

            //输出花费的时间
            System.out.println("花费的时间为:" + (end - start));
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtils.closeResource(conn, ps);
        }
    }

方式四：通过事务不自动提交的方式进行效率提升

设置事务不自动提交：

AutoCommit = false

    //批量插入方式四
    /*
    1、控制数据库的连接--->数据库的提交事务
        1、设置不允许自动提交数据
        2、setAutoCommit = false
     */
    @Test
    public void testInsert3() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();
            
            //设置事务不自动提交
            conn.setAutoCommit(false);
            
            //预编译sql
            String sql = "insert into goods(`name`) values (?)";
            //执行sql
            ps = conn.prepareStatement(sql);
            //填充占位符
            for (int i=1; i<=20000; i++) {
                ps.setObject(1, "name_" + i);
//                //执行语句
//                ps.execute();
                //攒sql
                ps.addBatch();
                if (i % 1000 == 0) {
                    //攒1000个sql执行一次
                    //执行batch
                    ps.executeBatch();

                    //清空batch
                    ps.clearBatch();
                }
            }
            
            //手动的提交事务
            conn.commit();
            
            long end = System.currentTimeMillis();

            //输出花费的时间
            System.out.println("花费的时间为:" + (end - start));
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtils.closeResource(conn, ps);
        }
    }

效果：

对于百万级的数据插入操作耗时仅为事务提交操作的三分之一

It's a lonely road!!!