重拾JDBC（九）处理BLob二进制数据

处理BLob二进制数据

Mysql Blob类型介绍

• large objects，二进制大型对象；
• Mysql的四种Blob类型（只有最大存储容量的区别）：

• 存储文件过大，数据库性能下降。

插入图片示例代码：

/**
 * 插入 BLOB 类型的数据必须使用 PreparedStatement：因为 BLOB 类型
 * 的数据时无法使用字符串拼写的。
 * 
 * 调用 setBlob(int index, InputStream inputStream)
 */
@Test
public void testInsertBlob(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try {
        connection = JDBCTools.getConnection();
        String sql = "INSERT INTO customers(name, email, birth, picture)" 
                + "VALUES(?,?,?,?)";
        preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setString(1, "ABCDE");
        preparedStatement.setString(2, "abcde@atguigu.com");
        preparedStatement.setDate(3, 
                new Date(new java.util.Date().getTime()));

        InputStream inputStream = new FileInputStream("Hydrangeas.jpg");
        preparedStatement.setBlob(4, inputStream);

        preparedStatement.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally{
        JDBCTools.releaseDB(null, preparedStatement, connection);
    }
}

读取Blob示例代码：

/**
 * 读取 blob 数据: 
 * 1. 使用 getBlob 方法读取到 Blob 对象
 * 2. 调用 Blob 的 getBinaryStream() 方法得到输入流。再使用 IO 操作即可. 
 */
@Test
public void readBlob(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
        connection = JDBCTools.getConnection();
        String sql = "SELECT id, name customerName, email, birth, picture " 
                + "FROM customers WHERE id = 13";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();

        if(resultSet.next()){
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);

            System.out.println(id + ", " + name  + ", " + email);
            Blob picture = resultSet.getBlob(5);
            //获取输入流
            InputStream in = picture.getBinaryStream();
            System.out.println(in.available()); 

            OutputStream out = new FileOutputStream("flower.jpg");

            byte [] buffer = new byte[1024];
            int len = 0;
            while((len = in.read(buffer)) != -1){
                out.write(buffer, 0, len);
            }

            in.close();
            out.close();
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally{
        JDBCTools.releaseDB(resultSet, preparedStatement, connection);
    }
}