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