GBase8c 测试jdbc 插入blob类型数据
为了检验jdbc插入数据报错ERROR: invalid hexadecimal digit: ""问题
测试代码:
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/**
* create table t_blob (id serial primary key,blob_column blob);
*/
public class GBase8cInsertBlobExample {
public static void main(String[] args) {
// String url = "jdbc:postgresql://192.168.122.33:15400/test?blobMode=off";
String url = "jdbc:opengauss://192.168.122.33:15400/test?blobMode=off";
String username = "gbase8c";
String password = "Database@123";
try (Connection connection = DriverManager.getConnection(url, username, password)) {
connection.setAutoCommit(false);
String sql = "INSERT INTO t_blob (blob_column) VALUES (?)";
PreparedStatement statement = connection.prepareStatement(sql);
// 方法一:会报错 ERROR: invalid hexadecimal digit: "\"
// 解决方法:alter database test set dolphin.b_compatibility_mode = on; (只支持B模式)
// 或者在SQL执行前执行一句,set dolphin.b_compatibility_mode = 1;
// 读取 BLOB 文件到 InputStream
// InputStream inputStream = new FileInputStream("/root/test.blob");
// 设置 BLOB 参数
// statement.setBinaryStream(1, inputStream);
// 方法二: 成功
// 读取 BLOB 文件到 byte 数组
// File file = new File("/root/test.blob");
// byte[] fileData = new byte[(int) file.length()];
// FileInputStream inputStream = new FileInputStream(file);
// inputStream.read(fileData);
// // 将 byte 数组转换为十六进制字符串
// String hexString = javax.xml.bind.DatatypeConverter.printHexBinary(fileData);
// // 设置 BLOB 参数
// statement.setString(1, hexString);
// 方法三: 成功
// 读取 BLOB 文件到 InputStream
File file = new File("/root/test.blob");
InputStream inputStream = new FileInputStream(file);
// 设置 BLOB 参数
statement.setBlob(1, inputStream);
// 执行 SQL 语句
statement.executeUpdate();
connection.commit();
// 关闭资源
statement.close();
inputStream.close();
System.out.println("BLOB inserted successfully.");
} catch (Exception e) {
e.printStackTrace();
}
}
}