JDBC 处理 CLOB 与 BLOB

Blob 是一个二进制大型对象（文件），在 MySQL 中有四种 Blob 类型，区别是容量不同

TinyBlob	255B
Blob	65KB
MediumBlob	16MB
LongBlob	4GB

 

插入数据

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

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

public class BlobTest {

    private Connection connection;
    private ResultSet resultSet;
    private PreparedStatement preparedStatement;

    @BeforeEach
    public void start() throws Exception {
        Properties properties = new Properties();
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        properties.load(in);

        String driver = properties.getProperty("driver");
        String jdbcUrl = properties.getProperty("jdbcUrl");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        Class.forName(driver);

        connection = DriverManager.getConnection(jdbcUrl, user, password);
    }

    @AfterEach
    public void end() throws Exception {
        if (resultSet != null) {
            resultSet.close();
        }
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }

    /**
     * 插入 BLOB 类型的数据必须使用 PreparedStatement：因为 BLOB 类型的数据时无法使用字符串拼写的。
     * 可封装成 Blob 对象，也可直接使用IO流，调用 setBlob 或 setBinaryStream
     */
    @Test
    public void testInsertBlob() {
        try {
            String sql = "INSERT INTO blob_test (file, name) VALUES (?,?)";
            preparedStatement = connection.prepareStatement(sql);

            Blob blob = connection.createBlob();
            InputStream in = this.getClass().getClassLoader().getResourceAsStream("file.png");
            OutputStream out = blob.setBinaryStream(1);

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

            preparedStatement.setBlob(1, blob);
//            preparedStatement.setBlob(1, in);
//            preparedStatement.setBinaryStream(1, in);

            preparedStatement.setString(2, "ABCDE");
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

读取数据

/**
 * getBlob 方法读取到 Blob 对象，调用 Blob 的 getBinaryStream() 方法得到输入流
 * 或者直接 getBinaryStream 得到 IO 流
 */
@Test
public void testReadBlob() {
    try {
        String sql = "SELECT id, file, name FROM blob_test WHERE id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 13);
        resultSet = preparedStatement.executeQuery();

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


            InputStream in = file.getBinaryStream();
//                InputStream in = resultSet.getBinaryStream(2);
            System.out.println(name + "\t" + in.available());

            OutputStream out = new FileOutputStream("newfile.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();
    }
}

 

MySQL 中无 Clob 类型，在 Oracle 中才有，可以在 MySQL 用 text 或者 varchar 替换，它相当于 String。

与 Blob 类型对比：https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

Clob Type	Blob Type	Storage Required
TINYTEXT	TINYBLOB	L + 1 bytes，其中 L < 2**8  (255 B)
TEXT	BLOB	L + 2 bytes，其中 L < 2**16 (64 K)
MEDIUMTEXT	MEDIUMBLOB	L + 3 bytes，其中 L < 2**24 (16 MB)
LONGTEXT	LONGBLOB	L + 4 bytes，其中 L < 2**32 (4 GB)

 

插入数据

/**
 * 就是插入字符串
 */
@Test
public void testInsertClob() {
    try {
        Clob myClob = connection.createClob();
        Writer clobWriter = myClob.setCharacterStream(1);
        String str = readFile("clob.txt", clobWriter);
        myClob.setString(1, str);
        System.out.println("Clob 的长度:" + myClob.length());

        String sql = "INSERT INTO clob_test (file, name) VALUES(?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setClob(1, myClob);
//            preparedStatement.setString(1,str);
        preparedStatement.setString(2, "ABCDE");
        preparedStatement.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

private String readFile(String fileName, Writer writerArg) throws IOException {
    BufferedReader br = new BufferedReader(new FileReader(this.getClass().getClassLoader().getResource(fileName).getPath()));
    String nextLine = "";
    StringBuffer sb = new StringBuffer();
    while ((nextLine = br.readLine()) != null) {
        writerArg.write(nextLine);
        sb.append(nextLine);
    }
    return sb.toString();
}

读取数据

@Test
public void testReadClob() {
    try {
        String sql = "SELECT id, file, name FROM clob_test WHERE id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 3);
        resultSet = preparedStatement.executeQuery();

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

            InputStream in = file.getAsciiStream();
            System.out.println(name + "\t" + in.available());

            StringBuilder sb = new StringBuilder();
            byte[] buffer = new byte[1024];
            while (in.read(buffer) != -1) {
                sb.append(new String(buffer));
            }
            in.close();

//                String str = resultSet.getString(2);
            System.out.println(sb);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

 

 

---

https://docs.oracle.com/javase/tutorial/jdbc/basics/blob.html