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
| 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();
}
}
