1 package com.quikj.server.framework; 2 3 import java.sql.*; 4 import java.io.*; 5 import java.util.*; 6 7 public class AceSQL implements AceCompareMessageInterface 8 { 9 public AceSQL(Connection db_conn) 10 { 11 dbConnection = db_conn; 12 } 13 14 public void dispose() 15 { 16 if (quit == false) 17 { 18 quit = true; 19 try 20 { 21 if (dbConnection != null) 22 { 23 dbConnection.close(); dbConnection = null; 25 } 26 } 27 catch (SQLException ex) 28 { 29 AceLogger.Instance().log(AceLogger.ERROR, 30 AceLogger.SYSTEM_LOG, 31 "AceSQL.dispose() -- Database error while closing connection : " 32 + ex.getMessage()); 33 } 34 } 35 } 36 37 public int executeSQL(Statement[] statements, 38 String [] sql_statements, 39 AceThread cthread, 40 Object user_parm, 41 boolean return_multiple_query_results) 42 43 49 { 50 int next_id = -1; 51 52 Thread caller = cthread; 53 if (caller == null) 54 { 55 caller = Thread.currentThread(); 56 } 57 58 if ((caller instanceof AceThread) == false) 59 { 60 writeErrorMessage("The calling thread must be an instance of AceThread"); 61 return -1; 62 } 63 64 try 65 { 66 if (statements == null) 67 { 68 if (return_multiple_query_results == true) 70 { 71 statements = new Statement[sql_statements.length]; 72 for (int i = 0; i < statements.length; i++) 73 { 74 statements[i] = dbConnection.createStatement(); 75 } 76 } 77 else 78 { 79 statements = new Statement[1]; 80 statements[0] = dbConnection.createStatement(); 81 } 82 } 83 } 84 catch (SQLException ex) 85 { 86 writeErrorMessage("Failed to create an SQL Statement object : " 87 + ex.getMessage()); 88 return -1; 89 } 90 91 try 92 { 93 synchronized (nextOperationIdLock) 94 { 95 next_id = nextOperationId++; 96 } 97 98 99 AceSQLThread sql = new AceSQLThread(next_id, 100 statements, 101 sql_statements, 102 (AceThread)caller, 103 user_parm); 104 105 sql.start(); } 107 catch (IOException ex1) 108 { 109 writeErrorMessage("Could not create thread to execute the SQL statement : " 110 + ex1.getMessage()); 111 return -1; 112 } 113 114 return next_id; 115 } 116 117 public int executeSQL(String sql_statement, 118 AceThread cthread, 119 Object user_parm) 120 { 121 String [] sql_statements = new String [1]; 122 sql_statements[0] = sql_statement; 123 return executeSQL(null, sql_statements, cthread, user_parm, false); 124 } 125 126 public int executeSQL(Statement statement, 127 String sql_statement, 128 Object user_parm) 129 { 130 Statement[] statements = new Statement[1]; 131 statements[0] = statement; 132 String [] sql_statements = new String [1]; 133 sql_statements[0] = sql_statement; 134 return executeSQL(statements, sql_statements, null, user_parm, false); 135 } 136 137 public int executeSQL(String sql_statement, 138 Object user_parm) 139 { 140 String [] sql_statements = new String [1]; 141 sql_statements[0] = sql_statement; 142 return executeSQL(null, sql_statements, null, user_parm, false); 143 } 144 145 public int executeSQL(String [] sql_statements, 146 AceThread cthread, 147 Object user_parm, 148 boolean return_multiple_query_results) 149 { 150 return executeSQL(null, sql_statements, cthread, user_parm, return_multiple_query_results); 151 } 152 153 public int executeSQL(Statement statement, 154 String [] sql_statements, 155 Object user_parm) 156 { 157 Statement[] statements = new Statement[1]; 158 statements[0] = statement; 159 return executeSQL(statements, sql_statements, null, user_parm, false); 160 } 161 162 public int executeSQL(Statement statement, 163 String [] sql_statements, 164 Object user_parm, 165 boolean return_multiple_query_results) 166 { 167 Statement[] statements = new Statement[1]; 168 statements[0] = statement; 169 return executeSQL(statements, sql_statements, null, user_parm, return_multiple_query_results); 170 } 171 172 public int executeSQL(Statement[] statements, 173 String [] sql_statements, 174 Object user_parm) 175 { 176 return executeSQL(statements, sql_statements, null, user_parm, true); 177 } 178 179 public int executeSQL(String [] sql_statements, 180 Object user_parm, 181 boolean return_multiple_query_results) 182 { 183 return executeSQL(null, sql_statements, null, user_parm, return_multiple_query_results); 184 } 185 186 public boolean cancelSQL(int id, 187 AceThread cthread) 188 { 189 boolean ret = false; 190 191 AceSQLThread thr = (AceSQLThread)pendingOperations.get(new Integer (id)); 193 194 if (thr != null) { 196 thr.dispose(); ret = true; 198 } 199 200 if (cthread == null) 203 { 204 Thread cur_thr = Thread.currentThread(); 205 206 if ((cur_thr instanceof AceThread) == true) 207 { 208 cthread = (AceThread)cur_thr; 209 } 210 else 211 { 212 return ret; 213 } 214 } 215 216 ret = cthread.removeMessage(new AceSQLMessage(0, id, cthread, cthread, 0, null), 217 this); 218 219 return ret; 220 } 221 222 public boolean cancelSQL(int id) 223 { 224 return cancelSQL(id, null); 225 } 226 227 public AceMessageInterface waitSQLResult(int id) 228 { 229 Thread thr = Thread.currentThread(); 230 231 if ((thr instanceof AceThread) == false) 232 { 233 writeErrorMessage("This method is not being called from an object which is a sub-class of type AceThread"); 234 return null; 235 } 236 237 AceThread cthread = (AceThread)thr; 239 240 241 while (true) 242 { 243 AceMessageInterface msg = cthread.waitMessage(); 244 if ((msg instanceof AceSQLMessage) == true) 245 { 246 if (((AceSQLMessage)msg).getOperationId() == id) 247 { 248 return msg; 249 } 250 } 251 else if ((msg instanceof AceSignalMessage) == true) 252 { 253 return msg; 254 } 255 } 256 } 257 258 259 public Connection getConnection() 260 { 261 return dbConnection; 262 } 263 264 private void writeErrorMessage(String error) 265 { 266 Thread cthread = Thread.currentThread(); 267 268 if ((cthread instanceof AceThread) == true) 269 { 270 ((AceThread)cthread).dispatchErrorMessage(error); 271 } 272 else 273 { 274 AceLogger.Instance().log(AceLogger.ERROR, 275 AceLogger.SYSTEM_LOG, 276 "AceSQL.writeErrorMessage() : " 277 + error); 278 } 279 } 280 281 public boolean same(AceMessageInterface obj1, AceMessageInterface obj2) 282 { 283 boolean ret = false; 284 285 if (((obj1 instanceof AceSQLMessage) == true) && 286 ((obj2 instanceof AceSQLMessage) == true)) 287 { 288 if (((AceSQLMessage)obj1).getOperationId() == ((AceSQLMessage)obj2).getOperationId()) 289 { 290 ret = true; 291 } 292 } 293 294 return ret; 295 } 296 297 private boolean quit = false; 298 private Connection dbConnection = null; 299 private Hashtable pendingOperations = new Hashtable(); 300 301 private static Object nextOperationIdLock = new Object (); 302 private static int nextOperationId = 0; 303 304 class AceSQLThread extends AceThread 305 { 306 public AceSQLThread(int operation_id, 307 Statement[] statements, 308 String [] sql_statements, 309 AceThread cthread, 310 Object user_parm) 311 throws IOException 312 { 313 super("AceSQLThread", true); 314 315 this.statements = statements; 316 sqlStatements = sql_statements; 317 userParm = user_parm; 318 parent = cthread; 319 operationId = operation_id; 320 } 321 322 public void dispose() 323 { 324 if (quitThread == false) 325 { 326 quitThread = true; 327 try 329 { 330 if (currentStatement != null) 331 { 332 currentStatement.cancel(); 333 } 334 335 } 336 catch (SQLException ex) 337 { 338 ; } 340 } 341 342 super.dispose(); 343 } 344 345 public void run() 346 { 347 pendingOperations.put(new Integer (operationId), this); 349 350 int num_executed = 0; 351 try 352 { 353 boolean result_available = false; 354 ArrayList results = new ArrayList(); 355 int num_affected_rows = 0; 356 int num_to_execute = (sqlStatements == null) ? statements.length : sqlStatements.length; 357 358 for (; num_executed < num_to_execute; num_executed++) 359 { 360 currentStatement = (statements.length == 1) ? statements[0] : statements[num_executed]; 361 362 if ((currentStatement instanceof PreparedStatement) == true) 363 { 364 result_available = ((PreparedStatement) currentStatement).execute(); 365 367 } 368 else 369 { 370 result_available = currentStatement.execute(sqlStatements[num_executed]); 371 } 372 373 if (result_available == true) 374 { 375 ResultSet rs = currentStatement.getResultSet(); 376 results.add(rs); 377 } 378 else 379 { 380 num_affected_rows = currentStatement.getUpdateCount(); 381 } 382 } 383 384 ResultSet r = null; 385 ResultSet[] multiple_r = null; 386 if (results.size() > 0) 387 { 388 r = ((ResultSet) (results.get(results.size()-1))); 389 if (statements.length > 1) 390 { 391 multiple_r = new ResultSet[results.size()]; 392 results.toArray(multiple_r); 393 394 } 405 } 406 407 409 if ((quit == false) && (quitThread == false)) 410 { 411 if (parent.sendMessage(new AceSQLMessage(AceSQLMessage.SQL_EXECUTED, 412 operationId, 413 r, 414 multiple_r, 415 num_affected_rows, 416 parent, 417 this, 418 num_executed, 419 userParm)) == false) 420 { 421 AceLogger.Instance().log(AceLogger.ERROR, 422 AceLogger.SYSTEM_LOG, 423 parent.getName() 424 + "- AceSQL.AceSQLThread.run() -- Error sending SQL executed message : " 425 + getErrorMessage()); 426 } 427 } 428 } 429 catch (SQLException ex) 430 { 431 if ((quit == false) && (quitThread == false)) 432 { 433 AceLogger.Instance().log(AceLogger.WARNING, 434 AceLogger.SYSTEM_LOG, 435 parent.getName() 436 + " -- Unexpected database result : " 437 + ex.getMessage()); 438 439 if (parent.sendMessage(new AceSQLMessage(AceSQLMessage.SQL_ERROR, 441 operationId, 442 parent, 443 this, 444 num_executed, 445 userParm)) == false) 446 { 447 AceLogger.Instance().log(AceLogger.ERROR, 448 AceLogger.SYSTEM_LOG, 449 parent.getName() 450 + "- AceSQL.AceSQLThread.run() -- Error sending SQL error message : " 451 + getErrorMessage()); 452 } 453 } 454 } 455 456 currentStatement = null; 457 dispose(); 458 459 pendingOperations.remove(new Integer (operationId)); 461 return; 462 } 463 464 private boolean quitThread = false; 465 private AceThread parent; 466 private String [] sqlStatements = null; 467 private Object userParm; 468 private Statement[] statements = null; 469 private Statement currentStatement = null; 470 private int operationId; 471 } 472 473 public static String formatString(String str) 474 { 475 StringBuffer buffer = new StringBuffer (); 476 char[] c = str.toCharArray(); 477 478 for (int i = 0; i < c.length; i++) 479 { 480 switch(c[i]) 481 { 482 case '\'': 483 buffer.append("\\\'"); 484 break; 485 case '\"': 486 buffer.append("\\\""); 487 break; 488 case '\0': 489 buffer.append("\\0"); 490 break; 491 case '\b': 492 buffer.append("\\b"); 493 break; 494 case '\n': 495 buffer.append("\\n"); 496 break; 497 case '\r': 498 buffer.append("\\n"); 499 break; 500 case '\t': 501 buffer.append("\\t"); 502 break; 503 case '\\': 504 buffer.append("\\\\"); 505 break; 506 case '%': 507 buffer.append("\\%"); 508 break; 509 case '_': 510 buffer.append("\\_"); 511 break; 512 513 default: 514 buffer.append(c[i]); 515 } 516 } 517 518 return buffer.toString(); 519 } 520 521 public static void main(String [] args) 523 { 524 class TestQueryOK extends AceThread 525 { 526 public TestQueryOK(AceSQL database) 527 throws IOException 528 { 529 super(); 530 db = database; 531 } 532 533 public void run() 534 { 535 String testcase = "Test query (OK)"; 536 537 int op_id = db.executeSQL("select name from user_tbl where name = 'ace' and password = password('a1b2c3d4');", 538 testcase); 539 540 AceSQLMessage message = (AceSQLMessage) db.waitSQLResult(op_id); 541 542 if (message.getStatus() == AceSQLMessage.SQL_ERROR) 543 { 544 System.out.println(testcase + 545 " -- failed with SQL_ERROR status"); 546 return; 547 } 548 549 if (message.getOperationId() != op_id) 550 { 551 System.out.println(testcase + 552 " -- failed returning correct operation ID"); 553 return; 554 } 555 556 if (((String )(message.getUserParm())).equals(testcase) == false) 557 { 558 System.out.println(testcase + 559 " -- failed returning correct user parm"); 560 return; 561 } 562 563 if (message.getParent().getName().equals(this.getName()) == false) 564 { 565 System.out.println(testcase + 566 " -- failed returning correct parent"); 567 return; 568 } 569 570 572 if (message.resultAvailable() == false) 573 { 574 System.out.println(testcase + 575 " -- failed no result (user ace not found)"); 576 return; 577 } 578 579 try 580 { 581 ResultSet r = message.getResultSet(); 582 String uname = r.getString(1); 583 if (uname.equals("ace") == false) 584 { 585 System.out.println(testcase + 586 " -- failed wrong result - expecting returned user name = ace, got " 587 + uname); 588 return; 589 } 590 } 591 catch (SQLException ex) 592 { 593 System.out.println(testcase + 594 " -- failed SQLException processing result set, error = " 595 + ex.getMessage()); 596 return; 597 } 598 599 System.out.println(testcase + " -- passed"); 600 return; 601 } 602 603 private AceSQL db; 604 } 605 606 class TestQueryNotOK extends AceThread 607 { 608 public TestQueryNotOK(AceSQL database) 609 throws IOException 610 { 611 super(); 612 db = database; 613 } 614 615 public void run() 616 { 617 String testcase = "Test query (NotOK)"; 618 619 int op_id = db.executeSQL("select name from user_tbl where name = 'ace' and password = password('aaaaaaaa');", 620 testcase); 621 622 AceSQLMessage message = (AceSQLMessage) db.waitSQLResult(op_id); 623 624 if (message.getStatus() == AceSQLMessage.SQL_ERROR) 625 { 626 System.out.println(testcase + 627 " -- failed with SQL_ERROR status"); 628 return; 629 } 630 631 if (message.getOperationId() != op_id) 632 { 633 System.out.println(testcase + 634 " -- failed returning correct operation ID"); 635 return; 636 } 637 638 if (((String )(message.getUserParm())).equals(testcase) == false) 639 { 640 System.out.println(testcase + 641 " -- failed returning correct user parm"); 642 return; 643 } 644 645 if (message.getParent().getName().equals(this.getName()) == false) 646 { 647 System.out.println(testcase + 648 " -- failed returning correct parent"); 649 return; 650 } 651 652 654 if (message.resultAvailable() == true) 655 { 656 System.out.println(testcase + 657 " -- failed with a result"); 658 return; 659 } 660 661 System.out.println(testcase + " -- passed"); 662 return; 663 } 664 665 private AceSQL db; 666 } 667 668 class TestUpdateOK extends AceThread 669 { 670 public TestUpdateOK(AceSQL database) 671 throws IOException 672 { 673 super(); 674 db = database; 675 } 676 677 public void run() 678 { 679 String testcase = "Test update (OK)"; 680 681 int op_id = db.executeSQL("insert into user_tbl values ('" 682 + NEW_USER 683 + "',password('a1b2c3d4'),0,0);", 684 testcase); 685 686 AceSQLMessage message = (AceSQLMessage) db.waitSQLResult(op_id); 687 688 if (message.getStatus() == AceSQLMessage.SQL_ERROR) 689 { 690 System.out.println(testcase + 691 " -- failed with SQL_ERROR status"); 692 return; 693 } 694 695 if (message.getOperationId() != op_id) 696 { 697 System.out.println(testcase + 698 " -- failed returning correct operation ID"); 699 return; 700 } 701 702 if (((String )(message.getUserParm())).equals(testcase) == false) 703 { 704 System.out.println(testcase + 705 " -- failed returning correct user parm"); 706 return; 707 } 708 709 if (message.getParent().getName().equals(this.getName()) == false) 710 { 711 System.out.println(testcase + 712 " -- failed returning correct parent"); 713 return; 714 } 715 716 718 if (message.getAffectedRows() != 1) 719 { 720 System.out.println(testcase + 721 " -- failed - num affected rows = " 722 + message.getAffectedRows() 723 + "; OK if you already have user name = " 724 + NEW_USER 725 + " in your ace.user_tbl"); 726 return; 727 } 728 729 System.out.println(testcase + " -- passed"); 730 return; 731 } 732 733 public static final String NEW_USER = "dummyhead"; 734 735 private AceSQL db; 736 } 737 738 class TestSQLError extends AceThread 739 { 740 public TestSQLError(AceSQL database) 741 throws IOException 742 { 743 super(); 744 db = database; 745 } 746 747 public void run() 748 { 749 String testcase = "Test SQL error"; 750 751 int op_id = db.executeSQL("insert into user_tbl values ('" 752 + TestUpdateOK.NEW_USER 753 + "',password('a1b2c3d4'),0,0);", 754 testcase); 755 756 AceSQLMessage message = (AceSQLMessage) db.waitSQLResult(op_id); 757 758 if (message.getOperationId() != op_id) 759 { 760 System.out.println(testcase + 761 " -- failed returning correct operation ID"); 762 return; 763 } 764 765 if (((String )(message.getUserParm())).equals(testcase) == false) 766 { 767 System.out.println(testcase + 768 " -- failed returning correct user parm"); 769 return; 770 } 771 772 if (message.getParent().getName().equals(this.getName()) == false) 773 { 774 System.out.println(testcase + 775 " -- failed returning correct parent"); 776 return; 777 } 778 779 781 if (message.getStatus() != AceSQLMessage.SQL_ERROR) 782 { 783 System.out.println(testcase + 784 " -- failed with other than SQL_ERROR status, status = " 785 + message.getStatus()); 786 return; 787 } 788 789 System.out.println(testcase + " -- passed"); 790 return; 791 } 792 793 private AceSQL db; 794 } 795 796 class TestUpdateNotOK extends AceThread 797 { 798 public TestUpdateNotOK(AceSQL database) 799 throws IOException 800 { 801 super(); 802 db = database; 803 } 804 805 public void run() 806 { 807 String testcase = "Test update (NotOK)"; 808 809 int op_id = db.executeSQL("update user_tbl set name = 'ACE' where name = 'ace' and password = password('aaaaaaaa');", 810 testcase); 811 812 AceSQLMessage message = (AceSQLMessage) db.waitSQLResult(op_id); 813 814 if (message.getStatus() == AceSQLMessage.SQL_ERROR) 815 { 816 System.out.println(testcase + 817 " -- failed with SQL_ERROR status"); 818 return; 819 } 820 821 if (message.getOperationId() != op_id) 822 { 823 System.out.println(testcase + 824 " -- failed returning correct operation ID"); 825 return; 826 } 827 828 if (((String )(message.getUserParm())).equals(testcase) == false) 829 { 830 System.out.println(testcase + 831 " -- failed returning correct user parm"); 832 return; 833 } 834 835 if (message.getParent().getName().equals(this.getName()) == false) 836 { 837 System.out.println(testcase + 838 " -- failed returning correct parent"); 839 return; 840 } 841 842 844 if (message.getAffectedRows() != 0) 845 { 846 System.out.println(testcase + 847 " -- failed - num affected rows = " 848 + message.getAffectedRows() 849 + ", should be 0"); 850 return; 851 } 852 853 System.out.println(testcase + " -- passed"); 854 return; 855 } 856 857 private AceSQL db; 858 } 859 860 class TestMultipleStatements extends AceThread 861 { 862 public TestMultipleStatements(AceSQL database) 863 throws IOException 864 { 865 super(); 866 db = database; 867 } 868 869 public void run() 870 { 871 String testcase = "Test multiple statements"; 872 873 String [] statements = new String [2]; 874 statements[0] = "delete from user_tbl where name = '" 875 + TestUpdateOK.NEW_USER 876 + "';"; 877 statements[1] = "select name from user_tbl where name = '" 878 + TestUpdateOK.NEW_USER 879 + "';"; 880 881 int op_id = db.executeSQL(statements, 882 testcase, false); 883 884 AceSQLMessage message = (AceSQLMessage) db.waitSQLResult(op_id); 885 886 if (message.getStatus() == AceSQLMessage.SQL_ERROR) 887 { 888 System.out.println(testcase + 889 " -- failed with SQL_ERROR status"); 890 return; 891 } 892 893 if (message.getOperationId() != op_id) 894 { 895 System.out.println(testcase + 896 " -- failed returning correct operation ID"); 897 return; 898 } 899 900 if (((String )(message.getUserParm())).equals(testcase) == false) 901 { 902 System.out.println(testcase + 903 " -- failed returning correct user parm"); 904 return; 905 } 906 907 if (message.getParent().getName().equals(this.getName()) == false) 908 { 909 System.out.println(testcase + 910 " -- failed returning correct parent"); 911 return; 912 } 913 914 916 if (message.resultAvailable() == true) 917 { 918 System.out.println(testcase + 919 " -- failed with a result"); 920 return; 921 } 922 923 System.out.println(testcase + " -- passed"); 924 return; 925 } 926 927 private AceSQL db; 928 } 929 930 931 932 934 Connection connection = null; 935 try 936 { 937 Class.forName("com.mysql.jdbc.Driver").newInstance(); 938 939 String url_str = new String ("jdbc:mysql" + "://" + "localhost" + "/" + "ace"); 940 connection = DriverManager.getConnection(url_str, "ace", "a1b2c3d4"); 941 942 } 943 catch (SQLException ex) 944 { 945 System.out.println("An SQL error occured while trying to connect to the database server - " 946 + ex.getMessage()); 947 948 System.exit(1); 949 } 950 catch (Exception ex) 951 { 952 System.out.println("An error (" 953 + ex.getClass().getName() 954 + ") occured while trying to connect to the database server - " 955 + ex.getMessage()); 956 System.exit(1); 957 } 958 959 AceSQL db = new AceSQL(connection); 960 961 BufferedReader reader = new BufferedReader(new InputStreamReader(System.in)); 962 963 964 965 968 try 969 { 970 String syntax = new String ("Which test do you want to do: \n" 971 + " " 972 + "basic \n" 973 + " " 974 + "cancel (you must uncomment test code in AceSQL first) \n" 975 + " " 976 + "dispose (you must uncomment test code in AceSQL first) \n" 977 + " " 978 + "quit or exit \n"); 979 System.out.println(syntax); 980 981 loop : while (true) 982 { 983 System.out.print("test> "); 984 System.out.flush(); 985 986 String line = reader.readLine().trim(); 987 if (line.length() > 0) 988 { 989 StringTokenizer strtok = new StringTokenizer(line, " "); 990 int num_tokens = strtok.countTokens(); 991 String command = strtok.nextToken(); 992 993 if (command.equals("basic") == true) 994 { 995 new TestQueryOK(db).start(); 996 new TestQueryNotOK(db).start(); 997 new TestUpdateOK(db).start(); 998 new TestSQLError(db).start(); 999 new TestUpdateNotOK(db).start(); 1000 TestMultipleStatements t = new TestMultipleStatements(db); 1001 t.start(); 1002 t.join(); 1003 } 1004 else if (command.equals("cancel") == true) 1005 { 1006 } 1007 else if (command.equals("dispose") == true) 1008 { 1009 1010 } 1011 else if ((command.equals("quit") == true) || (command.equals("exit") == true)) 1012 { 1013 db.dispose(); 1014 System.out.println("bye\n"); 1015 System.exit(0); 1016 } 1017 else 1018 { 1019 System.err.println(syntax); 1020 } 1021 } 1022 } 1023 } 1024 catch (IOException ex) 1025 { 1026 System.out.println("An IOException error occured while creating test AceThreads - " 1027 + ex.getMessage()); 1028 System.exit(1); 1029 } 1030 catch (InterruptedException ex) 1031 { 1032 System.out.println("An InterruptedException error occured while running test AceThreads - " 1033 + ex.getMessage()); 1034 System.exit(1); 1035 } 1036 1037 System.exit(0); 1038 } 1039} 1040 1041 1042 1043 | Popular Tags |