【java/jdbc/二进制文件】将雇员表信息写入二进制文件 及 将二进制文件的雇员信息读取出来插进同构的另一张雇员表

【从CSV转向二进制文件的原因】

1.csv文件的分隔符逗号，可能出现在用户所选的字段中，分段失误后会导致信息丢失。

2.CSV文件信息容易被人读取，二进制文件则相对安全些。

【源表、数据及目的表】

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<21;

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

程序将从emp表读取数据写二进制文件，然后读取二进制文件写入目标表emp_to。

【读表写二进制文件程序】

package com.hy.datamasking;

import java.io.DataOutputStream;
import java.io.FileOutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 读取emp表，将数据写入二进制文件
 */
class Db2Binfile {
    //-- 以下为连接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){
        String fieldLine=String.join(",",fields);

        Connection conn = null;
        Statement stmt = null;

        List<Map<String,String>> lines=new ArrayList<>();
        try{
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PSWD);

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

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

            while (rs.next()) {
                Map<String,String> map=new LinkedHashMap<>();

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

                    map.put(field,value);
                }

                lines.add(map);
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        write2File(filename,lines);
    }

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

            out.writeInt(lines.size());

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

               for(Map.Entry<String,String> 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<size;i++){
            char c=0;
            if(i<str.length()){
                c=str.charAt(i);
            }
            out.writeChar(c);
        }
    }

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

【读二进制文件写表的程序】

package com.hy.datamasking;

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";

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

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

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

                Map<String,String> map=new LinkedHashMap<>();

                for(int j=0;j<fieldCnt;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<size;i++){
            char c=in.readChar();

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

        return sb.toString();
    }

    public void insert(String tablename,List<Map<String,String>> lines){
        Connection conn = null;
        Statement stmt = null;

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

            for (Map<String,String> map:lines) {
                List<String> keys=new ArrayList<>();
                List<String> values=new ArrayList<>();

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

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

                String sql=String.format("insert into %s(%s) values(%s)",tablename,String.join(",",keys),String.join(",",values));
                stmt.execute(sql);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

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

【最后emp_to表的数据】

SQL> select * from emp_to;

        ID NAME                        AGE
---------- -------------------- ----------
         1 EYUVOYRXUI                   21
         2 SKCTTMGJEG                   21
         3 QDTQKDAAKQ                   55
         4 UXOBXKCXWG                   19
         5 TFPNXOJVBO                   37
         6 BSYEZYNNIR                   52
         7 XIGBNCCJJI                   25
         8 RKVMFJXVQM                   27
         9 JPKEXVOQOV                   41
        10 RMELXQHSCU                   45
        11 HKJYEANGYQ                   28

        ID NAME                        AGE
---------- -------------------- ----------
        12 YRAYESTIXA                   57
        13 ZEOSGWBRZT                   24
        14 WCWNMUVPEF                   35
        15 IBBZUOHHPX                   47
        16 XOXYKJLUUX                   38
        17 NOXBGIEIGP                   48
        18 LIYORVMTRU                   37
        19 EHJYCWSVQY                   20
        20 XLPVNKMIXW                   39

【精髓点评】

写二进制文件的关键在于在每个字符串前面写有其长度。这样做以后，读取时先用readInt读取长度，再以此长度读取字符串就毫不费力了。

另一个技巧是把字段名和字段值写在一起，这样组合插入语句就很方便了。

原先设想还要取字段的metadata，现在看当时是多虑了。

有趣的编程有着莫大的创造式快乐，这是我工作的最大动力。

END