【Java/Oracle】怎样在同构的含有Clob字段的表中迁移数据

有两个表是这样的：

create table emp430_1(
    id number(12),
    name nvarchar2(20),
    profile clob,
    primary key(id)
);

create table emp430_2(
    id number(12),
    name nvarchar2(20),
    profile clob,
    primary key(id)
);

可以看出，除了表名，两表一模一样。

 

如果用Java程序在两表之间迁移数据，可以这样写：

package com.hy.lab.clob;

import java.io.Reader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ClobTransfer {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public static void main(String[] args) throws Exception{
        Connection conn = null;
        Statement stmt = null;

        try{
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PSWD);

            String sql="select id,name,profile from emp430_1";
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            List<Emp> emps=new ArrayList<>();
            while (rs.next()) {
                long id=rs.getInt("id");
                String name=rs.getString("name");
                String profile=extractTxtFrom(rs.getClob("profile"));
                emps.add(new Emp(id,name,profile));
            }
//--------分割线，以上为从1表取数据，以下为往2表塞数据-------------------------
            conn.setAutoCommit(false);
            PreparedStatement pstmt = null;
            String insertSql="insert into emp430_2(id,name,profile) values(?,?,?)";
            pstmt = conn.prepareStatement(insertSql);

            for(Emp emp:emps){
                pstmt.setLong(1,emp.id);
                pstmt.setString(2,emp.name);
                Clob clob=conn.createClob();
                clob.setString(1,emp.profile);
                pstmt.setClob(3,clob);

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            conn.commit();

            System.out.println("传输完成");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static String extractTxtFrom(Clob clob) throws Exception{
        Reader reader=clob.getCharacterStream();
        char[] arr=new char[1024];
        int length=reader.read(arr);
        StringBuilder sb=new StringBuilder();
        while(length!=-1){
            String part=new String(arr,0,length);
            sb.append(part);
            length=reader.read(arr);
        }
        return sb.toString();
    }
}

END