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
https://blog.csdn.net/qq_34514384/article/details/51134416
