【java/JdbcTemplate/二进制文件】将雇员表信息写入二进制文件脱敏name字段 及 将二进制文件的雇员信息读取出来批量插进同构的另一张雇员表 及还原脱敏字段的程序

【前篇】

【主要改进点】

用JdbcTemplate取代了原有的JDBC代码，改单条插入为批量插入。

【原表数据及目的表】

 create table emp(
       id number(12),
       name nvarchar2(10),
       age number(3),
        primary key(id)
);

insert into emp
    select rownum,
           dbms_random.string('*',dbms_random.value(10,10)),
           dbms_random.value(18,60)
    from dual
    connect by level<51;

create table emp_newto(
        id number(12),
        name nvarchar2(10),
        age number(3),
        primary key(id)
);

【读表写二进制文件】

package com.hy.datamasking5;

import com.hy.lab.TranspositionCipher;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import java.io.DataOutputStream;
import java.io.FileOutputStream;

public class Table2Binfile {
    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";

    private JdbcTemplate getJdbcTemplate(){
        DataSourceBuilder dsb=DataSourceBuilder.create();
        dsb.driverClassName(DRIVER);
        dsb.url(URL);
        dsb.username(USER);
        dsb.password(PSWD);

        DataSource ds=dsb.build();
        JdbcTemplate tplt=new JdbcTemplate(ds);

        return tplt;
    }

    public void export(String filename,String table,String[] fields,String[] cipherFields){
        String fieldLine=String.join(",",fields);
        String sql=String.format("select %s from %s order by 1",fieldLine,table);

        List> lines=new ArrayList<>();
        JdbcTemplate tplt=getJdbcTemplate();
        tplt.query(sql, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Map map=new LinkedHashMap<>();

                for(String field:fields){
                    String value = rs.getString(field);

                    for(String cf:cipherFields){
                        if(field.equals(cf)){
                            value= TranspositionCipher.encrypt(value,3);
                        }
                    }

                    map.put(field,value);
                }

                lines.add(map);
            }
        });

        write2File(filename,lines);
    }

    private void write2File(String filename,List> lines){
        try {
            DataOutputStream out = new DataOutputStream(new FileOutputStream(filename));

            out.writeInt(lines.size());

            for (Map map:lines) {
                int fieldCnt=map.size();
                out.writeInt(fieldCnt);

                for(Map.Entry entry:map.entrySet()){
                    String key=entry.getKey();
                    int n=key.length();
                    out.writeInt(n);
                    writeString(key,n,out);

                    String value=entry.getValue();
                    n=value.length();
                    out.writeInt(n);
                    writeString(value,n,out);
                }
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }
    }

    // 写入最大长度为size的字符串，不足写0
    private static void writeString(String str,int size,DataOutputStream out) throws Exception{
        for(int i=0;i){
            char c=0;
            if(i<str.length()){
                c=str.charAt(i);
            }
            out.writeChar(c);
        }
    }

    public static void main(String[] args){
        Table2Binfile t2bf=new Table2Binfile();
        t2bf.export("c:\\temp\\emp220311.dat","emp",new String[]{"id","name","age"},new String[]{"name"});
    }
}

【读文件批量插表】

package com.hy.datamasking5;

import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.io.DataInput;
import java.io.RandomAccessFile;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class Binfile2Db {
    //-- 以下为连接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";

    private JdbcTemplate getJdbcTemplate(){
        DataSourceBuilder dsb=DataSourceBuilder.create();
        dsb.driverClassName(DRIVER);
        dsb.url(URL);
        dsb.username(USER);
        dsb.password(PSWD);

        DataSource ds=dsb.build();
        JdbcTemplate tplt=new JdbcTemplate(ds);

        return tplt;
    }

    public List> read(String filename){
        List> lines=new ArrayList<>();

        try {
            RandomAccessFile in = new RandomAccessFile(filename, "r");

            int count=in.readInt();
            for(int i=0;i){
                int fieldCnt=in.readInt();

                Map map=new LinkedHashMap<>();

                for(int j=0;j){
                    int n=in.readInt();
                    String key=readString(n,in);

                    n=in.readInt();
                    String value=readString(n,in);

                    map.put(key,value);
                    //String msg=String.format("%s:%s,",key,value);
                    //System.out.print(msg);
                }

                lines.add(map);
                //System.out.println();
            }

        }catch(Exception ex){
            ex.printStackTrace();
        }

        return lines;
    }

    // 读入最大长度为size的字符串
    private static String readString(int size, DataInput in) throws Exception{
        StringBuilder sb=new StringBuilder();

        for(int i=0;i){
            char c=in.readChar();

            if(c==0){
                continue;
            }else{
                sb.append(c);
            }
        }

        return sb.toString();
    }

    public void insert(String tablename,List> lines){
        StringBuilder sb=new StringBuilder();

        sb.append(" INSERT ALL");
        for (Map map:lines) {
            List keys = new ArrayList<>();
            List values = new ArrayList<>();

            for (Map.Entry entry : map.entrySet()) {
                String key = entry.getKey();
                keys.add(key);

                String value = entry.getValue();
                values.add("'" + value + "'");
            }

            String sql = String.format(" into %s(%s) values(%s)", tablename, String.join(",", keys), String.join(",", values));
            sb.append(sql);
        }
        sb.append(" SELECT 1 from DUAL");

        JdbcTemplate tplt = getJdbcTemplate();
        tplt.execute(sb.toString());
    }

    public static void main(String[] args){
        Binfile2Db imp=new Binfile2Db();
        List> lines=imp.read("c:\\temp\\emp220311.dat");
        imp.insert("emp_newto",lines);
    }
}

【还原表程序】

package com.hy.datamasking5;

import com.hy.lab.TranspositionCipher;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class TableRestorer {
    //-- 以下为连接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";

    private JdbcTemplate getJdbcTemplate(){
        DataSourceBuilder dsb=DataSourceBuilder.create();
        dsb.driverClassName(DRIVER);
        dsb.url(URL);
        dsb.username(USER);
        dsb.password(PSWD);

        DataSource ds=dsb.build();
        JdbcTemplate tplt=new JdbcTemplate(ds);

        return tplt;
    }


    public void restore(String table,String field){
        String sql=String.format("select id,%s from %s",field,table);

        Map map=new HashMap();
        JdbcTemplate tplt=getJdbcTemplate();
        tplt.query(sql, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                String id = rs.getString("id");
                String value = rs.getString(field);

                map.put(id,value);
            }
        });

        for(Map.Entry entry:map.entrySet()){
            String id=entry.getKey();
            String value= entry.getValue();
            value=TranspositionCipher.decrypt(value,3);

            sql=String.format("update %s set %s='%s' where id='%s'",table,field,value,id);
            tplt.execute(sql);
        }
    }

    public static void main(String[] args){
        TableRestorer restorer=new TableRestorer();
        restorer.restore("emp_newto","name");
    }
}

【进行置换加解密的类】

package com.hy.lab;

/**
 * 置换加解密器
 */
public class TranspositionCipher {
    public static String encrypt(String raw,int seed){
        String[] arr=new String[seed];
        for(int i=0;i){
            arr[i]="";
        }

        int n=raw.length();
        for(int i=0;i){
            char c=raw.charAt(i);
            arr[i%seed]+=c;
        }

        return String.join("",arr);
    }

    public static String decrypt(String raw,int seed){
        int n=raw.length();
        int cols=(int)(Math.ceil(((double)n)/((double)seed)));
        int rows=seed;
        int spaceCnt=rows*cols-n;

        String[] arr=new String[cols];
        for(int i=0;i){
            arr[i]="";
        }

        int index=0;
        int row=0;
        int column=0;

        for(int i=0;i){
            char c=raw.charAt(i);
            arr[column]+=c;
            column++;

            if(column==cols || (column==cols-1 && row>=rows-spaceCnt) ){
                column=0;
                row+=1;
            }
        }

        return String.join("",arr);
    }

    public static void main(String[] args){
        String[] arr=new String[]{"Common sense is not so common.","秦时明月汉时关万里长征人未还但使龙城飞将在不教胡马度阴山","ABCDEFGHJds","2131kgfjhj321","32132132132132ewqewq"};
        for(String str:arr){
            String en=encrypt(str,8);
            String de=decrypt(en,8);
            String msg=String.format("原文=%s 密文=%s 还原文=%s",str,en,de);
            System.out.println(msg);
        }
    }
}

END