java代码将16进制字符串转换为图片，jdbc入库blob字段，解决ORA-01704，PLS-00172，ORA-06550，字符串文字太长等问题
从Oracle导出SQL文件中的insert语句包含blob字段，语句HEXTORAW函数将16进制的字符串入库，由于字符串太长，insert失败
下面的代码读取完整的insert语句，将HEXTORAW函数连同16进制的字符串替换为NULL，先将字段置空插入记录，然后使用PreparedStatement对图片文件读流更新入库
import org.apache.commons.io.FileUtils;
import javax.imageio.ImageIO;
import javax.imageio.stream.FileImageOutputStream;
import java.awt.image.BufferedImage;
import java.io.*;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
public class ImageUtils {
public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException {
File file = new File("C:\\Users\\Nihaorz\\Desktop\\b_file_insert.sql");
List<String> list = FileUtils.readLines(file, "UTF-8");
for (String s : list) {
String imageFilePath = null;
String id = null;
String[] array = s.split(",");
StringBuilder sb = new StringBuilder();
for (String s1 : array) {
if (s1.startsWith(" HEXTORAW(")) {
id = sb.toString();
id = id.substring("INSERT INTO \"B_FILE\" VALUES ('".length());
id = id.substring(0, id.indexOf("'"));
sb.append(" NULL");
String hexString = s1.trim();
hexString = hexString.substring("HEXTORAW('".length(), hexString.length() - 1);
imageFilePath = "C:\\Users\\Nihaorz\\Desktop\\b_file_images\\" + id + ".jpg";
hexToImage(imageFilePath, hexString);
} else {
sb.append(s1);
}
sb.append(",");
}
sb.deleteCharAt(sb.length() - 1);
sb.deleteCharAt(sb.length() - 1);
insert2Kingbase(sb.toString(), imageFilePath, id);
}
}
public static boolean insert2Oracle(String insertSql, String imgPath, String id) throws ClassNotFoundException, SQLException, FileNotFoundException {
boolean flag = false;
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@x.x.x.x:1521:orcl", "xxx", "xxx");
conn.createStatement().execute(insertSql);
// 打印除blob字段之外的insert语句
System.out.println(insertSql);
String sql = "update b_file set file_tx = ? where file_id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
InputStream is = new FileInputStream(imgPath);
pstmt.setBlob(1, is);
//还可以通过二进制流的方法存放图片pstmt.setBinaryStream(1,is);
pstmt.setString(2, id);
int i = pstmt.executeUpdate();
if (i > 0) {
flag = true;
System.out.println("插入图片成功");
} else {
System.out.println("插入图片失败");
}
close(pstmt, conn, is);
return flag;
}
public static boolean insert2Kingbase(String insertSql, String imgPath, String id) throws ClassNotFoundException, SQLException, FileNotFoundException {
boolean flag = false;
Class.forName("com.kingbase8.Driver");
Connection conn = DriverManager.getConnection("jdbc:kingbase8://x.x.x.x:54321/xxx", "xxx", "xxx");
conn.createStatement().execute(insertSql);
// 打印除blob字段之外的insert语句
System.out.println(insertSql);
String sql = "update b_file set file_tx = ? where file_id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
InputStream is = new FileInputStream(imgPath);
pstmt.setBlob(1, is);
//还可以通过二进制流的方法存放图片pstmt.setBinaryStream(1,is);
pstmt.setString(2, id);
int i = pstmt.executeUpdate();
if (i > 0) {
flag = true;
System.out.println("插入图片成功");
} else {
System.out.println("插入图片失败");
}
close(pstmt, conn, is);
return flag;
}
private static void close(AutoCloseable... closeables) {
for (AutoCloseable autoCloseable : closeables) {
if (autoCloseable != null) {
try {
autoCloseable.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 将图片转换成十六进制字符串
*/
static String imageToHex(String filePath) {
File f = new File(filePath);
String suffix = filePath.substring(filePath.lastIndexOf(".") + 1);
BufferedImage bi;
try {
bi = ImageIO.read(f);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(bi, suffix, baos);
byte[] bytes = baos.toByteArray();
return new BigInteger(1, bytes).toString(16);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 将十六进制字符串转化成图片
*/
static void hexToImage(String filePath, String hexString) {
byte[] bytes = stringToByte(hexString);
try {
FileImageOutputStream imageOutput = new FileImageOutputStream(new File(filePath));
imageOutput.write(bytes, 0, bytes.length);
imageOutput.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static byte[] stringToByte(String s) {
int length = s.length() / 2;
byte[] bytes = new byte[length];
for (int i = 0; i < length; i++) {
bytes[i] = (byte) ((Character.digit(s.charAt(i * 2), 16) << 4) | Character.digit(s.charAt((i * 2) + 1), 16));
}
return bytes;
}
}
b_file_insert.sql 示例，HEXTORAW('xxxxxx') 仅做示例，正常的是合法的16进制字符串
INSERT INTO "B_FILE" VALUES ('f7fb0c74-8bdc-4edd-8bae-14e9b923c1c0', '长安.jpg', 'image/jpeg', '510368', NULL, HEXTORAW('xxxxxx'), TO_TIMESTAMP('2020-11-22 22:43:54.780000', 'SYYYY-MM-DD HH24:MI:SS:FF6'), '标准地图')
INSERT INTO "B_FILE" VALUES ('ca6a7638-7ab5-437d-a486-f354fd210308', '麻涌.jpg', 'image/jpeg', '441326', NULL, HEXTORAW('xxxxxx'), TO_TIMESTAMP('2020-11-22 22:46:23.792000', 'SYYYY-MM-DD HH24:MI:SS:FF6'), '标准地图')
INSERT INTO "B_FILE" VALUES ('df5be551-653e-4640-bfd6-7581cdce36fb', '洪梅.jpg', 'image/jpeg', '394404', NULL, HEXTORAW('xxxxxx'), TO_TIMESTAMP('2020-11-22 22:48:19.201000', 'SYYYY-MM-DD HH24:MI:SS:FF6'), '标准地图')
INSERT INTO "B_FILE" VALUES ('abe056c6-d2a5-41b6-bc96-a05e6364b59b', '道滘.jpg', 'image/jpeg', '480304', NULL, HEXTORAW('xxxxxx'), TO_TIMESTAMP('2020-11-22 22:48:58.139000', 'SYYYY-MM-DD HH24:MI:SS:FF6'), '标准地图')
INSERT INTO "B_FILE" VALUES ('7c99a74a-f8c4-4cdc-9e59-6735564bc126', '黄江.jpg', 'image/jpeg', '554472', NULL, HEXTORAW('xxxxxx'), TO_TIMESTAMP('2020-11-22 22:50:23.518000', 'SYYYY-MM-DD HH24:MI:SS:FF6'), '标准地图')
INSERT INTO "B_FILE" VALUES ('409f586f-f086-48a8-a367-4d42fba26890', '谢岗.jpg', 'image/jpeg', '562335', NULL, HEXTORAW('xxxxxx'), TO_TIMESTAMP('2020-11-22 22:51:08.539000', 'SYYYY-MM-DD HH24:MI:SS:FF6'), '标准地图')
INSERT INTO "B_FILE" VALUES ('cf923739-6938-448f-af06-bc8d70678dac', '桥头.jpg', 'image/jpeg', '519045', NULL, HEXTORAW('xxxxxx'), TO_TIMESTAMP('2020-11-22 22:51:49.255000', 'SYYYY-MM-DD HH24:MI:SS:FF6'), '标准地图')
b_file表结构
-- Create table
create table B_FILE
(
file_id VARCHAR2(64) not null,
file_name VARCHAR2(64),
file_type VARCHAR2(100),
file_size VARCHAR2(16),
file_small BLOB,
file_tx BLOB,
file_createtime TIMESTAMP(6),
file_remark VARCHAR2(512)
)
tablespace DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table B_FILE
add primary key (FILE_ID)
using index
tablespace DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
说明
1、此示例中b_file表存在两个blob字段，且所有记录的file_small字段均为null，如果处理的表不止一个blob字段，需根据实际情况调整代码逻辑
2、对于此代码造成的数据丢失本人概不负责
参考链接：
https://blog.csdn.net/rexueqingchun/article/details/78150877
浙公网安备 33010602011771号