tpch.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 TPCH {
        // 定义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;

	long tbeforeQuery;
	long tafterQuery;

        public TPCH() throws SQLException {
                loadJdbcDriver();
                connect();
                map1 = new HashMap();
                map2 = new HashMap();
                map3 = new HashMap();
                map4 = new HashMap();

		tbeforeQuery = 0;
		tafterQuery = 0;

                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);
                        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() {
                map1.put(1, new DateRange(Date.valueOf("1992-01-01"), Date.valueOf("1993-01-01")));
                map1.put(2, new DateRange(Date.valueOf("1993-01-01"), Date.valueOf("1994-01-01")));
                map1.put(3, new DateRange(Date.valueOf("1994-01-01"), Date.valueOf("1995-01-01")));
                map1.put(4, new DateRange(Date.valueOf("1995-01-01"), Date.valueOf("1996-01-01")));
                map1.put(5, new DateRange(Date.valueOf("1996-01-01"), Date.valueOf("1997-01-01")));
                map1.put(6, new DateRange(Date.valueOf("1997-01-01"), Date.valueOf("1998-01-01")));
                map1.put(7, new DateRange(Date.valueOf("1998-01-01"), Date.valueOf("1999-01-01")));

                map2.put(1, new DecimalRange("0.00", "0.01"));
                map2.put(2, new DecimalRange("0.01", "0.02"));
                map2.put(3, new DecimalRange("0.02", "0.03"));
                map2.put(4, new DecimalRange("0.03", "0.04"));
                map2.put(5, new DecimalRange("0.04", "0.05"));
                map2.put(6, new DecimalRange("0.05", "0.06"));
                map2.put(7, new DecimalRange("0.06", "0.07"));
                map2.put(8, new DecimalRange("0.07", "0.08"));
                map2.put(9, new DecimalRange("0.08", "0.09"));
                map2.put(10, new DecimalRange("0.09", "0.11"));

                map3.put(1, new DecimalRange("0.00", "5.00"));
                map3.put(2, new DecimalRange("5.00", "10.00"));
                map3.put(3, new DecimalRange("10.00", "15.00"));
                map3.put(4, new DecimalRange("15.00", "20.00"));
                map3.put(5, new DecimalRange("20.00", "25.00"));
                map3.put(6, new DecimalRange("25.00", "30.00"));
                map3.put(7, new DecimalRange("30.00", "35.00"));
                map3.put(8, new DecimalRange("35.00", "40.00"));
                map3.put(9, new DecimalRange("40.00", "45.00"));
                map3.put(10, new DecimalRange("45.00", "51.00"));
        }

        private void Q2Partition() {
                map4.put(1, new IntRange(0, 151));
                map4.put(2, new IntRange(151, 301));
                map4.put(3, new IntRange(301, 451));
                map4.put(4, new IntRange(451, 601));
                map4.put(5, new IntRange(601, 750));
                map4.put(6, new IntRange(751, 901));
                map4.put(7, new IntRange(901, 1050));
                map4.put(8, new IntRange(1051, 1201));
                map4.put(9, new IntRange(1201, 1350));
                map4.put(10, new IntRange(1351, 1601));
        }

        public void createEncItem() throws SQLException {
                // 插入语句
                String sqlinsert = "INSERT INTO ENC_LINEITEM VALUES(?, ?, ?, ?);";
                String sqlquery = "select * 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();

                while (rs.next()) {
                        for (int i = 1; i <= numCols; i++) {
                                strbuf.append(rs.getString(i).trim() + "|");
                        }

                        //System.out.println(strbuf);
                        shipdateIdx = getIndent(rs.getDate(11));

                        dicountIdx = getIndent2(rs.getBigDecimal(7));

                        quantityIdx = getIndent3(rs.getBigDecimal(5));

                        toEnc = aes.encrypt(strbuf.toString().trim().getBytes());

                        pstmt.setBytes(1, toEnc);

                        pstmt.setInt(2, shipdateIdx);

                        pstmt.setInt(3, dicountIdx);

                        pstmt.setInt(4, quantityIdx);

                        // 执行语句
                        pstmt.executeUpdate();

                        //important! we must clear strbuf before use it agin!
                        strbuf.delete(0, strbuf.length());
                }

                // 关闭语句
                pstmt.close();

                rs.close();
        }


        public void createEncCust() throws SQLException {
                // 插入语句
                String sqlinsert = "INSERT INTO ENC_CUSTOMER VALUES(?, ?);";
                String sqlquery = "select * from tpch.customer";

                StringBuffer strbuf = new StringBuffer(250);
                //StringBuffer lenbuf;
                int customerIdx;
                byte[] toEnc;
                ResultSet rs = stmt.executeQuery(sqlquery);

                ResultSetMetaData rsmd = rs.getMetaData();
                PreparedStatement pstmt = conn.prepareStatement(sqlinsert);

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

                while (rs.next()) {
                        for (int i = 1; i <= numCols; i++) {
                                strbuf.append(rs.getString(i).trim() + "|");
                        }

                        customerIdx = getIndent4(rs.getInt(1));

                        toEnc = aes.encrypt(strbuf.toString().trim().getBytes());
                        pstmt.setBytes(1, toEnc);
                        pstmt.setInt(2, customerIdx);

                        // 执行语句
                        pstmt.executeUpdate();
                        //important! we must clear strbuf before use it agin!
                        strbuf.delete(0, strbuf.length());
                }

                // 关闭语句
                pstmt.close();

                rs.close();
        }

        public void createEncOrders() throws SQLException {
                // 插入语句
                String sqlinsert = "INSERT INTO ENC_ORDERS VALUES(?, ?, ?, ?);";
                String sqlquery = "select * from tpch.orders";

                StringBuffer strbuf = new StringBuffer(170);
                //StringBuffer lenbuf;
                int customerIdx;
                int orderdateIdx;
                int priorityIdx;

                byte[] toEnc;

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

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

                while (rs.next()) {
                        for (int i = 1; i <= numCols; i++) {
                                strbuf.append(rs.getString(i).trim() + "|");
                        }

                        customerIdx = getIndent4(rs.getInt(2));

                        orderdateIdx = getIndent(rs.getDate(5));
                        priorityIdx = getIndent4(rs.getInt(8));

                        toEnc = aes.encrypt(strbuf.toString().trim().getBytes());
                        pstmt.setBytes(1, toEnc);
                        pstmt.setInt(2, customerIdx);
                        pstmt.setInt(3, orderdateIdx);
                        pstmt.setInt(4, priorityIdx);

                        // 执行语句
                        pstmt.executeUpdate();
                        //important! we must clear strbuf before use it agin!
                        strbuf.delete(0, strbuf.length());
                }

                // 关闭语句
                pstmt.close();

                rs.close();
        }

        //note! this function is just for test!
        public void queryEncItem()throws SQLException {
                try {
                        String sqlquery = "select * from tpch.enc_lineitem";
                        String plainStr;
                        String[] element;

                        System.out.println("querying table...");
                        // 执行查询
                        ResultSet rs = stmt.executeQuery(sqlquery);
                        int limit = 0;

                        while (rs.next()) {
                                plainStr = new String(aes.decrypt(rs.getBytes(1)));
                                element = plainStr.split("\\|");

                                for (int i = 0; i < element.length; i++) {
                                        System.out.println(element[i]);
                                }

                                System.out.println(rs.getInt(2));

                                System.out.println(rs.getInt(3));
                                System.out.println(rs.getInt(4));
                                //if (limit++ == 10)
                                //{
                                break;
                                //}

                        }

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

        //do tpch query1
        public void doQuery1() {
                try {
                        int shipdateLowIdx = getIndent(Date.valueOf("1994-01-01"));
                        int shipdateUpIdx = getIndent(Date.valueOf("1995-01-01"));
                        int discountLowIdx = getIndent2(new BigDecimal("0.05"));
                        int discountUpIdx = getIndent2(new BigDecimal("0.07"));
                        int quantityUpIdx = getIndent3(new BigDecimal("24.00"));
                        /*System.out.println(shipdateLowIdx);
                        System.out.println(shipdateUpIdx);
                        System.out.println(discountLowIdx);
                        System.out.println(discountUpIdx);
                        System.out.println(quantityUpIdx);*/

                        String sqlquery = "select encvalue from enc_lineitem where shipdate_idx >= "
                                          + shipdateLowIdx +  " and shipdate_idx <= " + shipdateUpIdx
                                          + " and discount_idx between " + discountLowIdx + " and " + discountUpIdx
                                          + " and quantity_idx <= " + quantityUpIdx;

			tbeforeQuery = new java.util.Date().getTime();

                        ResultSet rs = stmt.executeQuery(sqlquery);
                        exactQuery1(rs);
                        rs.close();
                } catch (Exception ex) {
                        ex.printStackTrace();
                } finally {}

        }

        private void exactQuery1(ResultSet rs) {
                try {
                        Date shipdateLow = Date.valueOf("1994-01-01");
                        Date shipdateUp = Date.valueOf("1995-01-01");
                        BigDecimal discountLow = new BigDecimal("0.05");
                        BigDecimal discountUp = new BigDecimal("0.07");
                        BigDecimal quantityUp = new BigDecimal("24.00");

                        String plainStr;
                        String []element;
                        Date shipDate;
                        BigDecimal discount;
                        BigDecimal quantity;
                        BigDecimal extendedPrice;
                        BigDecimal sum = new BigDecimal("0");
                        ;

                        int limit = 0;

                        while (rs.next()) {
                                //System.out.println(new String(aes.decrypt(rs.getBytes(1))));
                                plainStr = new String(aes.decrypt(rs.getBytes(1)));
                                //System.out.println(plainStr);

                                //split函数要求是传入正则表达式，"|"在特殊字符的时候可能不起作用，故传入"\\|"
                                element = plainStr.split("\\|");
                                shipDate = Date.valueOf(element[10]);
                                discount = new BigDecimal(element[6]);
                                quantity = new BigDecimal(element[4]);
                                extendedPrice = new BigDecimal(element[5]);

                                if ((shipDate.compareTo(shipdateLow) >= 0 && shipDate.compareTo(shipdateUp) < 0 )
                                                && (discount.compareTo(discountLow) >= 0 && discount.compareTo(discountUp) <= 0)
                                                && quantity.compareTo(quantityUp) < 0) {
                                        sum = sum.add(extendedPrice.multiply(discount));
                                }

                        }
			
			tafterQuery = new java.util.Date().getTime();
			long interval = getInterval(tbeforeQuery, tafterQuery);
                        System.out.println("revenue");

                        System.out.println("---------------");
                        System.out.println(sum.doubleValue());
			System.out.println("---------------");
			System.out.println("this query used " + interval + " miniseconds");
                } catch (Exception ex) {
                        ex.printStackTrace();
                }
        }

        public void doQuery2() {
                try {