【JDBC - 核心技术】04 操作BLOB类型字段

---

第04章 操作BLOB类型字段

创作日期：2021-12-08

---

4.1 MySQL BLOB类型

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

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

---

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

package com.lmq.preparedstatement_curd;

import com.lmq.util.JDBCUtils;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * @Description:通过PreparedStatement操作BLOB类型字段
 * @author: XFDQ.lmq
 * @create: 2022-04-24 14:06
 */
public class PreparedStatementUpdate3 {

    public static void update(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.预编译sql语句，返回PreparedStatement的实例
            preparedStatement = connection.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1,args[i]);
            }
            //4.执行操作
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,preparedStatement);
        }
    }
    
//资源的关闭
    public static void closeResource(Connection connection, PreparedStatement preparedStatement) {
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (preparedStatement != null)
                preparedStatement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Test
    public void testInsert() throws FileNotFoundException {
        String sql = "insert into blobs(photo)values(?)";
        FileInputStream inputStream = new FileInputStream("D:\\JDBC\\src\\com\\lmq\\util\\1.jpg");
        PreparedStatementUpdate3.update(sql,inputStream);
    }
}

---

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

package com.lmq.preparedstatement_curd;

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

import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @Description:通过PreparedStatement操作BLOB类型字段
 * @author: XFDQ.lmq
 * @create: 2022-04-24 14:06
 */
public class PreparedStatementUpdate4 {

    //获取数据库连接
    public static Connection getConnection() throws Exception {
        //1.读取配置文件
        InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(inputStream);

        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        //2.加载驱动
        Class.forName(driverClass);

        //3.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    //读取数据
    @Test
    public void testInsert2(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "select photo from blobs where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                Blob photo = resultSet.getBlob("photo");
                InputStream binaryStream = photo.getBinaryStream();
                FileOutputStream fileOutputStream = new FileOutputStream("D:\\JDBC\\src\\com\\lmq\\util\\2.jpg2.jpg");
                byte[] bytes = new byte[1024];
                int len;
                while ((len = binaryStream.read(bytes)) != -1) {
                    fileOutputStream.write(bytes, 0, len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (resultSet != null)
                    resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (preparedStatement != null)
                    preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

---

4.4 批量插入方法

package com.lmq.test;

import com.lmq.util.JDBCUtils;

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

/**
 * @Description: 批量插入优化操作
 * @author: XFDQ.lmq
 * @create: 2022-05-14 09:55
 */
public class Test {

    @org.junit.Test
    public void play() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            long l = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();
            connection.setAutoCommit(false);
            String sql = "insert into id values (?)";
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i <= 200000; i++) {
                preparedStatement.setObject(1, i);
                preparedStatement.addBatch();
                if (i % 500 == 0) {
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
            }
            connection.commit();
            System.out.println(System.currentTimeMillis() - l);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }
}

---

上一节：

下一节：