【Oracle/jdbc/file】将oracle某表数个字段读出成csv文件及读取csv文件写入目标表的两段程序（改进版）

【建表及充值】

create table emp_from(
    id number(12),
    name varchar2(10),
    age number(3),
    salary number(6),
    primary key(id)
)

insert into emp_from
    select  rownum,
            dbms_random.string('*',dbms_random.value(10,10)),
            dbms_random.value(18,65),
            dbms_random.value(1000,50000)
    from dual
    connect by level<10001

create table emp_to(
    id number(12),
    name varchar2(10),
    age number(3),
    salary number(6),
    primary key(id)
)

【从emp_from表取值存成文件】

package com.hy.datamasking3;

import com.hy.datamasting.TimeUtil;

import java.io.FileNotFoundException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

class JdbcExp {
    //-- 以下为连接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 void export(String filename,String table,String[] fields){
        List<String> datas=new ArrayList<>();
        String fieldLine=String.join(",",fields);

        Connection conn = null;
        Statement stmt = null;

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

            String sql=String.format("select %s from %s order by id",fieldLine,table);

            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                List<String> ls=new ArrayList<>();

                for(String fd:fields){
                    String segment = rs.getString(fd);

                    // 脱敏处理
                    if(fd.equals("name")){
                        segment=JdbcExp.encrypt(segment);
                    }

                    ls.add(segment);
                }

                String line=String.join(",",ls);
                datas.add(line);
            }
            rs.close();
        } catch (Exception e) {
            System.out.print(e.getMessage());
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }

        write2File(filename,table,fieldLine,datas);
    }

    // 加密
    private static String encrypt(String str){
        char[] arr=str.toCharArray();

        for(int i=0;i<arr.length;i++){
            arr[i]+=25;
        }

        return String.valueOf(arr);
    }

    private void write2File(String filename,String table,String fieldLine,List<String> datas){
        PrintWriter out;
        try {
            out = new PrintWriter(filename);

            // 输出文本
            out.println(table);
            out.println(fieldLine);
            for(String line:datas){
                out.println(line);
            }
            out.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args){
        long startMs=System.currentTimeMillis();

        JdbcExp exp=new JdbcExp();
        exp.export("c:\\temp\\exp.csv","emp_from_final",new String[]{"id","name","age","salary"});

        long endMs=System.currentTimeMillis();
        System.out.println("Time elapsed:"+ TimeUtil.ms2DHMS(startMs,endMs));
    }
}

【从文件取值存入emp_from表】

package com.hy.datamasking3;

import com.hy.datamasting.TimeUtil;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

class JdbcBatchImp {
    //-- 以下为连接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 void readFromFile(String filename){
        String table="";
        String fieldLine="";
        List<String> datas=new ArrayList<>();

        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(filename), "UTF-8"));
            String line = null;
            int idx=0;
            while( ( line = br.readLine() ) != null ) {
                idx++;

                if(idx==1){
                    table=line;
                }else if(idx==2){
                    fieldLine=line;
                }else{
                    datas.add(line);
                }
            }
            br.close();
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }

        insertDatas("emp_to",fieldLine,datas);
    }

    private void insertDatas(String tableName,String fieldLine,List<String> datas){
        Connection conn = null;
        Statement stmt = null;

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

            stmt = conn.createStatement();

            int start=0;
            int end=0;
            final int STEP=200;
            int n=datas.size();

            for(;;){
                end=start+STEP;

                if(end>n){
                    end=n;
                    System.out.println("Start="+start+" End="+end);
                    batchInsert(start,end,datas,tableName,fieldLine,conn,stmt);
                    break;
                }

                System.out.println("Start="+start+" End="+end);
                batchInsert(start,end,datas,tableName,fieldLine,conn,stmt);
                start=end;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }

    private void batchInsert(int start,int end,List<String> datas,String tableName,String fieldLine,Connection conn,Statement stmt) throws Exception{
        List<String> partDatas=datas.subList(start,end);

        StringBuilder sb=new StringBuilder();
        sb.append("INSERT ALL ");
        for(String dt:partDatas){
            String[] arr=dt.split(",");
            String values=String.format("'%s'",String.join("','",arr));

            String partSql=String.format(" into %s(%s) values(%s)",tableName,fieldLine,values);
            sb.append(partSql);

        }
        sb.append("select * from dual");

        conn.setAutoCommit(false);
        stmt.execute(sb.toString());
        conn.commit();
    }

    public static void main(String[] args){
        long startMs=System.currentTimeMillis();

        JdbcBatchImp imp=new JdbcBatchImp();
        imp.readFromFile("c:\\temp\\exp.csv");

        long endMs=System.currentTimeMillis();
        System.out.println("Time elapsed:"+ TimeUtil.ms2DHMS(startMs,endMs));
    }
}

END