tpch2.java

数据库的加密以及密态查询

import java.sql.*;
import java.util.HashMap;
//import java.util.HashSet;
import java.util.TreeSet;
//import java.util.Comparator ;
import java.util.Iterator;
import java.math.BigDecimal;
import java.io.BufferedReader;
import java.io.InputStreamReader;

public class TPCH2 {
        // 定义DM JDBC驱动串
        String jdbcString = "dm.jdbc.driver.DmDriver";
        // 定义DM URL连接串
        String urlString = "jdbc:dm://localhost:12345";
        // 定义连接用户名
        String userName = "TPCH";
        // 定义连接用户口令
        String password = "123456";
        // 定义连接对象
        Connection conn = null;
        Statement stmt = null;

        private PrivateCrypto aes;

        private HashMap map1 = null;
        private HashMap map2 = null;
        private HashMap map3 = null;
        private HashMap map4 = null;
        private HashMap map5 = null;
        private HashMap map6 = null;
        private HashMap map7 = null;

	long tbeforeQuery;
	long tafterQuery;

        public TPCH2() throws SQLException {
                loadJdbcDriver();
                connect();
                map1 = new HashMap();
                map2 = new HashMap();
                map3 = new HashMap();
                map4 = new HashMap();
                map5 = new HashMap();
                map6 = new HashMap();
                map7 = new HashMap();

                Q1Partition();
                Q2Partition();
                aes = new PrivateCrypto();
                aes.readKey();
        }

        public void loadJdbcDriver() throws SQLException {
                try {
                        //System.out.println("Loading JDBC Driver...");
                        // 加载JDBC驱动程序
                        Class.forName(jdbcString);
                } catch (ClassNotFoundException e) {
                        throw new SQLException("Load JDBC Driver Error : " + e.getMessage());
                } catch (Exception ex) {
                        throw new SQLException("Load JDBC Driver Error : " + ex.getMessage());
                }
        }

        public void connect() throws SQLException {
                try {
                        //System.out.println("Connecting to DM Server...");
                        // 连接DM数据库
                        conn = DriverManager.getConnection(urlString, userName, password);
                        //注意以下参数的设置，否则ResultSet将无法使用absolute等移动函数
                        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
                } catch (SQLException e) {
                        throw new SQLException("Connect to DM Server Error : "
                                               + e.getMessage());
                }
        }

        public void disConnect() throws SQLException {
                try {
                        // 关闭连接
                        stmt.close();
                        conn.close();
                } catch (SQLException e) {
                        throw new SQLException("close connection error : " + e.getMessage());
                }
        }

        private void Q1Partition() {
                try {
                        String sqlquery = "select rownum as num, l_shipdate from lineitem order by l_shipdate";
                        ResultSet rs = stmt.executeQuery(sqlquery);

                        //计算总行数
                        rs.last();
                        int count = rs.getRow();
                        rs.beforeFirst();
                        datePartition(count, rs, map1);

                        sqlquery = "select rownum as num, l_discount from lineitem order by l_discount";
                        rs = stmt.executeQuery(sqlquery);
                        rs.beforeFirst();
                        bgPartition(count, rs, map2);

                        sqlquery = "select rownum as num, l_quantity from lineitem order by l_quantity";
                        rs = stmt.executeQuery(sqlquery);
                        rs.beforeFirst();
                        bgPartition(count, rs, map3);

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

        private void Q2Partition() {

                try {
                        String sqlquery = "select rownum as num, o_custkey from orders order by o_custkey";
                        ResultSet rs = stmt.executeQuery(sqlquery);

                        //计算总行数
                        rs.last();
                        int count = rs.getRow();
                        rs.beforeFirst();
                        intPartition(count, rs, map4);

                        sqlquery = "select rownum as num, o_orderdate from orders order by o_orderdate";
                        rs = stmt.executeQuery(sqlquery);
                        rs.beforeFirst();
                        datePartition(count, rs, map5);

                        sqlquery = "select rownum as num, o_shippriority from orders order by o_shippriority";
                        rs = stmt.executeQuery(sqlquery);
                        rs.beforeFirst();
                        intPartition(count, rs, map6);

                        sqlquery = "select rownum as num, c_custkey from customer order by c_custkey";
                        rs = stmt.executeQuery(sqlquery);
                        rs.beforeFirst();
                        rs.last();
                        count = rs.getRow();
                        rs.beforeFirst();
                        intPartition(count, rs, map7);

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

        }

        private void datePartition(int count, ResultSet rs, HashMap map) {
                try {
                        int i = 0 ;
                        int range = count / 10;
                        Pair p1;
                        Pair p2;

                        while ( i < 10 ) {
                                rs.absolute(range * i++ + 1);
                                p1 = new Pair(rs.getInt(1), rs.getDate(2));

                                if (i * range != count) {
                                        if (i != 10) {
                                                rs.absolute(range * i + 1);
                                                p2 = new Pair(rs.getInt(1), rs.getDate(2));
                                        } else {
                                                rs.last();
                                                p2 = new Pair(rs.getInt(1) + 1, new Date(rs.getDate(2).getTime() + 1000 * 24 * 60));
                                        }

                                } else {
                                        rs.absolute(range * i);
                                        p2 = new Pair(rs.getInt(1) + 1, new Date(rs.getDate(2).getTime() + 1000 * 24 * 60));
                                }

                                map.put(i, new PairRange(p1, p2));
                        }
                } catch (Exception ex) {
                        ex.printStackTrace();
                }

        }

        private void bgPartition(int count, ResultSet rs, HashMap map) {
                try {
                        int i = 0 ;
                        int range = count / 10;
                        Pair p1;
                        Pair p2;

                        while ( i < 10 ) {
                                rs.absolute(range * i++ + 1);
                                p1 = new Pair(rs.getInt(1), rs.getBigDecimal(2));

                                if (i * range != count) {
                                        if (i != 10) {
                                                rs.absolute(range * i + 1);
                                                p2 = new Pair(rs.getInt(1), rs.getBigDecimal(2));
                                        } else {
                                                rs.last();
                                                p2 = new Pair(rs.getInt(1) + 1, rs.getBigDecimal(2).add(new BigDecimal("1.00")));
                                        }

                                } else {
                                        rs.absolute(range * i);
                                        p2 = new Pair(rs.getInt(1) + 1, rs.getBigDecimal(2).add(new BigDecimal("1.00")));
                                }

                                map.put(i, new PairRange(p1, p2));
                        }
                } catch (Exception ex) {
                        ex.printStackTrace();
                }

        }

        private void intPartition(int count, ResultSet rs, HashMap map) {
                try {
                        int i = 0 ;
                        int range = count / 10;
                        Pair p1;
                        Pair p2;

                        while ( i < 10 ) {
                                rs.absolute(range * i++ + 1);
                                p1 = new Pair(rs.getInt(1), Integer.valueOf(rs.getInt(2)));
                                //System.out.println("i = " + i);
                                //System.out.println(rs.getInt(1) + ", " + rs.getDate(2));

                                if (i * range != count) {
                                        if (i != 10) {
                                                rs.absolute( range * i + 1);
                                                p2 = new Pair(rs.getInt(1), Integer.valueOf(rs.getInt(2)));
                                        } else {
                                                rs.last();
                                                p2 = new Pair(rs.getInt(1) + 1, Integer.valueOf(rs.getInt(2) + 1));
                                        }

                                } else {
                                        rs.absolute(range * i);
                                        p2 = new Pair(rs.getInt(1) + 1, Integer.valueOf(rs.getInt(2) + 1));
                                }

                                map.put(i, new PairRange(p1, p2));
                        }
                } catch (Exception ex) {
                        ex.printStackTrace();
                }

        }

        public void createEncItem() throws SQLException {
                // 插入语句
                String sqlinsert = "INSERT INTO ENC_LINEITEM2 VALUES(?, ?, ?, ?);";
                String sqlquery = "select rownum as num, * from tpch.lineitem";

                StringBuffer strbuf = new StringBuffer(200);
                int shipdateIdx;
                int dicountIdx;
                int quantityIdx;
                byte[] toEnc;

                ResultSet rs = stmt.executeQuery(sqlquery);
                ResultSetMetaData rsmd = rs.getMetaData();
                PreparedStatement pstmt = conn.prepareStatement(sqlinsert);

                // 取得结果集所包含的列数
                int numCols = rsmd.getColumnCount();
                //rs.beforeFirst();

                while (rs.next()) {
                        for (int i = 2; i <= numCols; i++) {
                                //stem.out.println(rs.getString(i));
                                strbuf.append(rs.getString(i).trim() + "|");
                        }

                        shipdateIdx = getIndent(map1, rs.getInt(1), rs.getDate(12));

                        dicountIdx = getIndent2(map2, rs.getInt(1), rs.getBigDecimal(8));
                        quantityIdx = getIndent2(map3, rs.getInt(1), rs.getBigDecimal(6));