📄 tpch.java
字号:
int orderdateIdx = getIndent(Date.valueOf("1995-03-15"));
String sqlquery = "select o_encvalue, o_orderdate_idx, o_shippriority_idx from enc_orders, enc_customer 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);
}
//System.out.println(set.toString());
System.out.println("---------------");
System.out.println("this query used " + interval + " miniseconds");
//System.out.println(set);
//System.out.println(set.size());
} catch (Exception ex) {
ex.printStackTrace();
}
}
public void insertCust() {
try
{
//1500 Customer#000001500 4zaoUzuWUTNFiNPbmu43 5 15-200-872-4790 6910.79 MACHINERY s boost blithely above the fluffily ironic dolphins! ironic accounts
String sqlinsert = "insert into customer values(1511,'Customer#000001501','4zaoUzuXYTNFiNPbmu42',6,'16-225-722-4890',2576.62,'MACHINERY','insert test')";
String forinsert = "INSERT INTO ENC_CUSTOMER VALUES(?, ?);";
StringBuffer strbuf = new StringBuffer(250);
int customerIdx;
byte[] toEnc;
PreparedStatement pstmt = conn.prepareStatement(forinsert);
String[] element = {"1511", "Customer#000001501", "4zaoUzuXYTNFiNPbmu42", "6", "16-225-722-4890", "2576.62", "MACHINERY", "insert test"};
//String value = sqlinsert.substring(sqlinsert.indexOf('(') + 1, sqlinsert.lastIndexOf(')'));
//System.out.println(value);
//String[] element = value.split("\\,");
//System.out.println(element);
for (int i = 0; i < element.length; i++)
{
//System.out.println(element[i]);
//if (element[i].trim().codePointAt(0) == 39 && element[i].trim().codePointAt(element[i].length()-1) == 39);
//{
// int t = element[i].length();
//System.out.println(t);
// element[i] = element[i].substring(1, t);
strbuf.append(element[i] + "|");
//}
//System.out.println(element[i]);
}
customerIdx = getIndent4(Integer.parseInt(element[0]));
System.out.println(customerIdx);
//System.out.println(Integer.parseInt(element[0]));
toEnc = aes.encrypt(strbuf.toString().trim().getBytes());
pstmt.setBytes(1, toEnc);
pstmt.setInt(2, customerIdx);
pstmt.executeUpdate();
pstmt.close();
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public void delFromCust() {
try
{
//String sql = "delete from customer where c_custkey = 1511";
int customerIdx = getIndent4(1555);
String plainStr;
String[] element;
String sql = "select * from enc_customer where c_custkey_idx = " + customerIdx;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next())
{
plainStr = new String(aes.decrypt(rs.getBytes(1)));
element = plainStr.split("\\|");
System.out.println(element[0]);
if ( element[0].equals("1555") ) {
rs.deleteRow();
break;
}
}
rs.close();
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
/* 修改厂商登记表数据
* @throws SQLException 异常 */
public void updateCust() throws SQLException{
// 更新数据语句
//String sql = "UPDATE customer SET c_custkey = ? " + " WHERE c_custkey = 1511;";
// 创建语句对象
//PreparedStatement pstmt = conn.prepareStatement(sql);
//String sql = "delete from customer where c_custkey = 1511";
try
{
int customerIdx = getIndent4(1511);
int newIdx;
String plainStr;
String[] element;
byte[] newbytes;
String sql = "select * from enc_customer where c_custkey_idx = " + customerIdx;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next())
{
plainStr = new String(aes.decrypt(rs.getBytes(1)));
element = plainStr.split("\\|");
if ( element[0].equals("1511") ) {
//rs.updateInt(0, 1555);
//System.out.println(element[0]);
element[0] = "1555";
StringBuffer strbuf = new StringBuffer(250);
for (int i = 0; i < element.length; i++)
{
strbuf.append(element[i] + "|");
}
newbytes = aes.encrypt(strbuf.toString().trim().getBytes());
rs.updateBytes(1, newbytes);
newIdx = getIndent4(1555);
rs.updateInt(2, newIdx);
rs.updateRow();
break;
}
}
rs.close();
}
catch (Exception ex)
{
ex.printStackTrace();
}
// 为参数赋值
//pstmt.setString(1,"北京");
// 执行语句
//pstmt.executeUpdate();
// 关闭语句
//pstmt.close();
}
//this function is never used,it is just a template function for copy!
private void displayResultSet(ResultSet rs)throws SQLException {
// 取得结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 取得结果集所包含的列数
int numCols = rsmd.getColumnCount();
// 显示列标头
for (int i = 1; i <= numCols; i++) {
if (i > 1)
System.out.print(",");
System.out.print(rsmd.getColumnLabel(i));
}
System.out.println("");
while (rs.next()) {
for (int i = 1; i <= numCols; i++) {
if (i > 1)
System.out.print(",");
System.out.print(rs.getString(i));
}
System.out.println("");
}
}
private int getIndent(Date date) {
for (int i = 0; i < map1.size(); i++) {
if (((DateRange)map1.get(i + 1)).contains(date)) {
return i + 1;
}
}
return 0;
}
private int getIndent2(BigDecimal bg) {
for (int i = 0; i < map2.size(); i++) {
if (((DecimalRange)map2.get(i + 1)).contains(bg)) {
//System.out.println("here");
return i + 1;
}
}
return 0;
}
private int getIndent3(BigDecimal bg) {
for (int i = 0; i < map3.size(); i++) {
if (((DecimalRange)map3.get(i + 1)).contains(bg)) {
return i + 1;
}
}
return 0;
}
private int getIndent4(int xx) {
for (int i = 0; i < map4.size(); i++) {
if (((IntRange)map4.get(i + 1)).contains(xx)) {
return i + 1;
}
}
return 0;
}
private long getInterval(long xx, long yy) {
long t = (yy - xx);
return t;
}
//test whether the Range class works!
public void test() {
for (int i = 0; i < map1.size(); i++) {
((DateRange)map1.get(i + 1)).print();
}
for (int i = 0; i < map2.size(); i++) {
System.out.println((DecimalRange)map2.get(i + 1));
}
for (int i = 0; i < map3.size(); i++) {
System.out.println((DecimalRange)map3.get(i + 1));
}
for (int i = 0; i < map4.size(); i++) {
((IntRange)map4.get(i + 1)).print();
}
}
public static void usage() {
System.out.println("usage: you can type those options below");
System.out.println("[options]: createEncItem:encrypt the lineitem");
System.out.println(" createEncCust:encrypt the customer");
System.out.println(" createEncOrders:encrypt the orders");
System.out.println(" doQuery1:execute the tpch query1");
System.out.println(" doQuery2:execute the tpch query2");
System.out.println(" insertCust:test insert new value into enc_customer(id = 1551)");
System.out.println(" updateCust:test update a value of enc_customer(id: 1551-->1555)");
System.out.println(" delFromCust:test delete a value from enc_customer(id = 1555)");
System.out.println("-----------------------------------------------------------------------------");
}
public static void main(String args[]) {
/*try {
if (args.length != 1) {
TPCH.usage();
} else {
TPCH tpch = new TPCH();
if (args[0].equals("-createEncItem")) {
tpch.createEncItem();
} else if (args[0].equals("-createEncCust")) {
tpch.createEncCust();
} else if (args[0].equals("-createEncOrders")) {
tpch.createEncOrders();
} else if (args[0].equals("-doQuery1")) {
tpch.doQuery1();
} else if (args[0].equals("-doQuery2")) {
tpch.doQuery2();
} else if (args[0].equals("-insertCust")) {
//String sql = "insert into customer values(1511, 'Customer#000001501', '4zaoUzuXYTNFiNPbmu42', 6, '16-225-722-4890', 2576.62, 'MACHINERY', 'insert test')";
tpch.insertCust();
} else if (args[0].equals("-delFromCust")) {
tpch.delFromCust();
} else if (args[0].equals("-updateCust")) {
tpch.updateCust();
} else {
TPCH.usage();
}
tpch.disConnect();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}*/
try {
TPCH tpch = new TPCH();
TPCH.usage();
String cmd;
BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
while (!(cmd = in.readLine()).equals("exit")) {
if (cmd.equals("createEncItem")) {
tpch.createEncItem();
System.out.println("done!");
System.out.println("***********");
} else if (cmd.equals("createEncCust")) {
tpch.createEncCust();
System.out.println("done!");
System.out.println("***********");
} else if (cmd.equals("createEncOrders")) {
tpch.createEncOrders();
System.out.println("done!");
System.out.println("***********");
} else if (cmd.equals("doQuery1")) {
tpch.doQuery1();
} else if (cmd.equals("doQuery2")) {
tpch.doQuery2();
} else if (cmd.equals("insertCust")) {
//String sql = "insert into customer values(1511, 'Customer#000001501', '4zaoUzuXYTNFiNPbmu42', 6, '16-225-722-4890', 2576.62, 'MACHINERY', 'insert test')";
tpch.insertCust();
System.out.println("done!");
System.out.println("***********");
} else if (cmd.equals("delFromCust")) {
tpch.delFromCust();
System.out.println("done!");
System.out.println("***********");
} else if (cmd.equals("updateCust")) {
tpch.updateCust();
System.out.println("done!");
System.out.println("***********");
} else {
TPCH.usage();
}
}
tpch.disConnect();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -