tpch2.java

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

                        //System.out.println(rs.getBigDecimal(6).toString());
                        //System.out.println(quantityIdx);
                        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_CUSTOMER2 VALUES(?, ?);";
                String sqlquery = "select rownum as num, * 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 = 2; i <= numCols; i++) {
                                strbuf.append(rs.getString(i).trim() + "|");
                        }

                        customerIdx = getIndent3(map7, rs.getInt(1), rs.getInt(2));

                        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_ORDERS2 VALUES(?, ?, ?, ?);";
                String sqlquery = "select rownum as num, * 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 = 2; i <= numCols; i++) {
                                strbuf.append(rs.getString(i).trim() + "|");
                        }

                        //getIndent(map1, rs.getInt(1), rs.getDate(12));
                        customerIdx = getIndent3(map4, rs.getInt(1), rs.getInt(3));

                        orderdateIdx = getIndent(map5, rs.getInt(1), rs.getDate(6));

                        priorityIdx = getIndent3(map6, rs.getInt(1), rs.getInt(9));

                        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(map1, Date.valueOf("1994-01-01"));
                        int shipdateUpIdx = getIndent_(map1, Date.valueOf("1995-01-01"));
                        int discountLowIdx = _getIndent2(map2, new BigDecimal("0.05"));
                        int discountUpIdx = getIndent2_(map2, new BigDecimal("0.07"));
                        int quantityUpIdx = getIndent2_(map3, new BigDecimal("24.00"));


                        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;
                        ResultSet rs = stmt.executeQuery(sqlquery);
			tbeforeQuery = new java.util.Date().getTime();

                        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 {
                        int orderdateIdx = getIndent_(map5, Date.valueOf("1995-03-15"));

                        String sqlquery = "select o_encvalue, o_orderdate_idx, o_shippriority_idx from enc_orders2, enc_customer2 where o_custkey_idx = c_custkey_idx"
                                          + " and o_orderdate_idx <= " + orderdateIdx
                                          + " group by o_encvalue, o_orderdate_idx, o_shippriority_idx order by o_orderdate_idx" ;
                        ResultSet rs = stmt.executeQuery(sqlquery);
			tbeforeQuery = new java.util.Date().getTime();

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


        private void exactQuery2(ResultSet rs) {
                try {
                        Date orderdateUp = Date.valueOf("1995-03-15");

                        String plainStr;
                        String []element;
                        Date orderDate;

                        TreeSet set = new TreeSet();//TreeSet 是排序的容器，而且可以消除重复元素，为最佳选择！

                        int count = 0;

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

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

                                if ( orderDate.compareTo(orderdateUp) < 0 ) {
                                        set.add(element[4] + ", " + element[7]);
                                        ++count;
                                }
                        }

			tafterQuery = new java.util.Date().getTime();
			long interval = getInterval(tbeforeQuery, tafterQuery);
                        Iterator it = set.iterator();

                        while (it.hasNext()) {
                                Object o = it.next();
                                System.out.println(o);
                        }