1 21 22 package org.opensubsystems.core.persist.db.driver; 23 24 import java.sql.PreparedStatement ; 25 import java.sql.ResultSet ; 26 import java.sql.SQLException ; 27 28 import junit.extensions.TestSetup; 29 import junit.framework.Test; 30 import junit.framework.TestSuite; 31 32 import org.opensubsystems.core.error.OSSException; 33 import org.opensubsystems.core.persist.db.Database; 34 import org.opensubsystems.core.persist.db.DatabaseImpl; 35 import org.opensubsystems.core.persist.db.DatabaseSchemaManager; 36 import org.opensubsystems.core.persist.db.DatabaseTest; 37 import org.opensubsystems.core.persist.db.DatabaseTestSetup; 38 import org.opensubsystems.core.persist.db.DatabaseTestSuite; 39 import org.opensubsystems.core.util.DatabaseUtils; 40 41 49 public final class QueryTest 50 { 51 53 56 private QueryTest( 57 ) 58 { 59 } 61 62 64 70 public static Test suite( 71 ) 72 { 73 TestSuite suite = new DatabaseTestSuite("QueryTest"); 74 suite.addTestSuite(QueryTestInternal.class); 75 TestSetup wrapper = new DatabaseTestSetup(suite); 76 77 return wrapper; 78 } 79 80 85 public static class QueryTestInternal extends DatabaseTest 86 { 87 90 static 91 { 92 Database dbDatabase; 94 95 try 96 { 97 dbDatabase = DatabaseImpl.getInstance(); 98 dbDatabase.add(DatabaseTestSchema.class); 100 } 101 catch (OSSException bfeExc) 102 { 103 throw new RuntimeException ("Unexpected exception.", bfeExc); 104 } 105 } 106 107 112 public QueryTestInternal( 113 String strTestName 114 ) 115 { 116 super(strTestName); 117 } 118 119 125 public void testJoinQuery( 126 ) throws Throwable 127 { 128 final String INSERT_BASE = "insert into GROUP_BASE_TEST values (?,?)"; 129 final String DELETE_BASE = "delete from GROUP_BASE_TEST"; 130 final String INSERT_CHILD = "insert into GROUP_CHILD_TEST values (?,?,?)"; 131 final String DELETE_CHILD = "delete from GROUP_CHILD_TEST"; 132 133 final String SELECT = "select GROUP_BASE_TEST.TEST_BASE_ID, " + 134 "GROUP_CHILD_TEST.TEST_CHILD_VALUE " + 135 "from GROUP_BASE_TEST " + 136 "left join GROUP_CHILD_TEST " + 137 "on GROUP_CHILD_TEST.TEST_BASE_FK_ID=GROUP_BASE_TEST.TEST_BASE_ID " + 138 "and GROUP_CHILD_TEST.TEST_CHILD_VALUE=? " + 139 "where GROUP_BASE_TEST.TEST_BASE_ID=?"; 140 141 PreparedStatement insertStatement = null; 142 PreparedStatement deleteStatement = null; 143 PreparedStatement selectStatement = null; 144 ResultSet rsResults = null; 145 146 try 147 { 148 m_transaction.begin(); 149 try 150 { 151 insertStatement = m_connection.prepareStatement(INSERT_BASE); 152 insertStatement.setInt(1, 1); 153 insertStatement.setInt(2, 1); 154 insertStatement.execute(); 155 156 insertStatement.setInt(1, 2); 157 insertStatement.setInt(2, 2); 158 insertStatement.execute(); 159 160 insertStatement = m_connection.prepareStatement(INSERT_CHILD); 161 insertStatement.setInt(1, 1); 162 insertStatement.setInt(2, 1); 163 insertStatement.setInt(3, 1); 164 insertStatement.execute(); 165 166 insertStatement.setInt(1, 2); 167 insertStatement.setInt(2, 1); 168 insertStatement.setInt(3, 2); 169 insertStatement.execute(); 170 171 insertStatement.setInt(1, 3); 172 insertStatement.setInt(2, 2); 173 insertStatement.setInt(3, 3); 174 insertStatement.execute(); 175 176 m_transaction.commit(); 177 } 178 catch (Throwable throwable) 179 { 180 m_transaction.rollback(); 181 throw throwable; 182 } 183 finally 184 { 185 DatabaseUtils.closeStatement(insertStatement); 186 } 187 188 try 189 { 190 selectStatement = m_connection.prepareStatement(SELECT); 191 selectStatement.setInt(1, 3); 192 selectStatement.setInt(2, 1); 193 rsResults = selectStatement.executeQuery(); 194 195 assertTrue("There have to be rows in result", rsResults.next()); 196 } 197 finally 198 { 199 DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement); 200 } 201 202 } 203 finally 204 { 205 m_transaction.begin(); 206 try 207 { 208 deleteStatement = m_connection.prepareStatement(DELETE_CHILD); 209 deleteStatement.execute(); 210 deleteStatement = m_connection.prepareStatement(DELETE_BASE); 211 deleteStatement.execute(); 212 m_transaction.commit(); 213 } 214 catch (Throwable thr) 215 { 216 m_transaction.rollback(); 217 throw new Exception (thr); 218 } 219 finally 220 { 221 DatabaseUtils.closeStatement(deleteStatement); 222 } 223 } 224 } 225 226 232 public void testGroupQuery( 233 ) throws Throwable 234 { 235 final String INSERT_BASE = "insert into GROUP_BASE_TEST values (?,?)"; 236 final String DELETE_BASE = "delete from GROUP_BASE_TEST"; 237 final String INSERT_CHILD = "insert into GROUP_CHILD_TEST values (?,?,?)"; 238 final String DELETE_CHILD = "delete from GROUP_CHILD_TEST"; 239 240 final String SELECT = "select GROUP_BASE_TEST.TEST_BASE_ID," + 241 "GROUP_BASE_TEST.TEST_BASE_VALUE," + 242 "sum(GROUP_CHILD_TEST.TEST_CHILD_VALUE) " + 243 "from GROUP_BASE_TEST, GROUP_CHILD_TEST " + 244 "where GROUP_CHILD_TEST.TEST_BASE_FK_ID=GROUP_BASE_TEST.TEST_BASE_ID " + 245 "and GROUP_CHILD_TEST.TEST_CHILD_VALUE=? " + 246 "group by GROUP_BASE_TEST.TEST_BASE_ID,GROUP_BASE_TEST.TEST_BASE_VALUE"; 247 248 PreparedStatement insertStatement = null; 249 PreparedStatement deleteStatement = null; 250 PreparedStatement selectStatement = null; 251 ResultSet rsResults = null; 252 253 try 254 { 255 m_transaction.begin(); 256 try 257 { 258 insertStatement = m_connection.prepareStatement(INSERT_BASE); 259 insertStatement.setInt(1, 1); 260 insertStatement.setInt(2, 1); 261 insertStatement.execute(); 262 263 insertStatement = m_connection.prepareStatement(INSERT_CHILD); 264 insertStatement.setInt(1, 1); 265 insertStatement.setInt(2, 1); 266 insertStatement.setInt(3, 1); 267 insertStatement.execute(); 268 269 m_transaction.commit(); 270 } 271 catch (Throwable throwable) 272 { 273 m_transaction.rollback(); 274 throw throwable; 275 } 276 finally 277 { 278 DatabaseUtils.closeStatement(insertStatement); 279 } 280 281 try 282 { 283 selectStatement = m_connection.prepareStatement(SELECT); 284 selectStatement.setInt(1, 3); 285 rsResults = selectStatement.executeQuery(); 286 287 assertFalse("There should be no rows in result", rsResults.next()); 288 } 289 finally 290 { 291 DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement); 292 } 293 294 } 295 finally 296 { 297 m_transaction.begin(); 298 try 299 { 300 deleteStatement = m_connection.prepareStatement(DELETE_CHILD); 301 deleteStatement.execute(); 302 deleteStatement = m_connection.prepareStatement(DELETE_BASE); 303 deleteStatement.execute(); 304 m_transaction.commit(); 305 } 306 catch (Throwable thr) 307 { 308 m_transaction.rollback(); 309 throw new Exception (thr); 310 } 311 finally 312 { 313 DatabaseUtils.closeStatement(deleteStatement); 314 } 315 } 316 } 317 318 323 public void testUnionQuery( 324 ) throws Throwable 325 { 326 final String INSERT = "insert into QUERY_TEST (VALUE_1, VALUE_2) values (?,?)"; 327 final String DELETE = "delete from QUERY_TEST"; 328 329 final String SELECT = "select VALUE_1 from QUERY_TEST where VALUE_2 in " + 330 "(select VALUE_2 from QUERY_TEST union select VALUE_1 from QUERY_TEST)"; 331 332 PreparedStatement insertStatement = null; 333 PreparedStatement deleteStatement = null; 334 PreparedStatement selectStatement = null; 335 ResultSet rsResults = null; 336 337 try 338 { 339 m_transaction.begin(); 340 try 341 { 342 insertStatement = m_connection.prepareStatement(INSERT); 343 insertStatement.setInt(1, 1); 344 insertStatement.setInt(2, 11); 345 insertStatement.execute(); 346 347 insertStatement = m_connection.prepareStatement(INSERT); 348 insertStatement.setInt(1, 2); 349 insertStatement.setInt(2, 12); 350 insertStatement.execute(); 351 352 m_transaction.commit(); 353 } 354 catch (Throwable throwable) 355 { 356 m_transaction.rollback(); 357 throw throwable; 358 } 359 finally 360 { 361 DatabaseUtils.closeStatement(insertStatement); 362 } 363 364 try 365 { 366 selectStatement = m_connection.prepareStatement(SELECT); 367 rsResults = selectStatement.executeQuery(); 368 369 assertTrue("There should be rows in result", rsResults.next()); 370 assertTrue("There should be rows in result", rsResults.next()); 371 assertFalse("There should be no more rows in result", rsResults.next()); 372 } 373 catch (SQLException sqleExc) 374 { 375 assertTrue("It seems like the database doesn't support UNION: " 376 + sqleExc.getMessage(), false); 377 } 378 finally 379 { 380 DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement); 381 } 382 } 383 finally 384 { 385 m_transaction.begin(); 386 try 387 { 388 deleteStatement = m_connection.prepareStatement(DELETE); 389 deleteStatement.execute(); 390 m_transaction.commit(); 391 } 392 catch (Throwable thr) 393 { 394 m_transaction.rollback(); 395 throw new Exception (thr); 396 } 397 finally 398 { 399 DatabaseUtils.closeStatement(deleteStatement); 400 } 401 } 402 } 403 404 409 public void testIntersectQuery( 410 ) throws Throwable 411 { 412 final String INSERT = "insert into QUERY_TEST (VALUE_1, VALUE_2) values (?,?)"; 413 final String DELETE = "delete from QUERY_TEST"; 414 415 final String SELECT = "select VALUE_1 from QUERY_TEST where VALUE_2 in " + 416 "(select VALUE_2 from QUERY_TEST intersect select VALUE_1 from QUERY_TEST)"; 417 418 PreparedStatement insertStatement = null; 419 PreparedStatement deleteStatement = null; 420 PreparedStatement selectStatement = null; 421 ResultSet rsResults = null; 422 423 try 424 { 425 m_transaction.begin(); 426 try 427 { 428 insertStatement = m_connection.prepareStatement(INSERT); 429 insertStatement.setInt(1, 1); 430 insertStatement.setInt(2, 11); 431 insertStatement.execute(); 432 433 insertStatement = m_connection.prepareStatement(INSERT); 434 insertStatement.setInt(1, 11); 435 insertStatement.setInt(2, 12); 436 insertStatement.execute(); 437 438 m_transaction.commit(); 439 } 440 catch (Throwable throwable) 441 { 442 m_transaction.rollback(); 443 throw throwable; 444 } 445 finally 446 { 447 DatabaseUtils.closeStatement(insertStatement); 448 } 449 450 try 451 { 452 selectStatement = m_connection.prepareStatement(SELECT); 453 rsResults = selectStatement.executeQuery(); 454 455 assertTrue("There should be rows in result", rsResults.next()); 456 assertFalse("There should be no more rows in result", rsResults.next()); 457 } 458 catch (SQLException sqleExc) 459 { 460 fail("It seems like the database doen't support INTERSECT: " 462 + sqleExc.getMessage()); 463 } 464 finally 465 { 466 DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement); 467 } 468 } 469 finally 470 { 471 m_transaction.begin(); 472 try 473 { 474 deleteStatement = m_connection.prepareStatement(DELETE); 475 deleteStatement.execute(); 476 m_transaction.commit(); 477 } 478 catch (Throwable thr) 479 { 480 m_transaction.rollback(); 481 throw new Exception (thr); 482 } 483 finally 484 { 485 DatabaseUtils.closeStatement(deleteStatement); 486 } 487 } 488 } 489 490 495 public void testInsertSelectQuery( 496 ) throws Throwable 497 { 498 final String INSERT = "insert into QUERY_TEST (VALUE_1, VALUE_2) values (?,?)"; 499 final String DELETE = "delete from QUERY_TEST"; 500 501 final String INSERT_SELECT = ((DatabaseTestSchema)DatabaseSchemaManager.getInstance( 502 DatabaseTestSchema.class)).getInsertSelectQuery(); 503 504 final String SELECT = "select VALUE_1, VALUE_2 from QUERY_TEST where VALUE_1=?"; 505 506 PreparedStatement insertStatement = null; 507 PreparedStatement deleteStatement = null; 508 PreparedStatement insertSelectStatement = null; 509 PreparedStatement selectStatement = null; 510 ResultSet rsResults = null; 511 512 try 513 { 514 m_transaction.begin(); 515 try 516 { 517 insertStatement = m_connection.prepareStatement(INSERT); 518 insertStatement.setInt(1, 1); 519 insertStatement.setInt(2, 1); 520 insertStatement.execute(); 521 522 insertStatement = m_connection.prepareStatement(INSERT); 523 insertStatement.setInt(1, 2); 524 insertStatement.setInt(2, 2); 525 insertStatement.execute(); 526 527 m_transaction.commit(); 528 } 529 catch (Throwable throwable) 530 { 531 m_transaction.rollback(); 532 throw throwable; 533 } 534 finally 535 { 536 DatabaseUtils.closeStatement(insertStatement); 537 } 538 539 m_transaction.begin(); 540 try 541 { 542 insertSelectStatement = m_connection.prepareStatement(INSERT_SELECT); 543 insertSelectStatement.setInt(1, 3); 544 insertSelectStatement.execute(); 545 546 m_transaction.commit(); 547 } 548 catch (SQLException sqleExc) 549 { 550 m_transaction.rollback(); 551 throw sqleExc; 552 } 553 finally 554 { 555 DatabaseUtils.closeResultSetAndStatement(rsResults, insertSelectStatement); 556 } 557 558 selectStatement = m_connection.prepareStatement(SELECT); 559 selectStatement.setInt(1, 3); 560 rsResults = selectStatement.executeQuery(); 561 562 assertTrue("The inserted data not in DB", rsResults.next()); 563 assertFalse("More data in DB", rsResults.next()); 564 } 565 finally 566 { 567 m_transaction.begin(); 568 try 569 { 570 deleteStatement = m_connection.prepareStatement(DELETE); 571 deleteStatement.execute(); 572 m_transaction.commit(); 573 } 574 catch (Throwable thr) 575 { 576 m_transaction.rollback(); 577 throw new Exception (thr); 578 } 579 finally 580 { 581 DatabaseUtils.closeStatement(deleteStatement); 582 } 583 } 584 } 585 586 591 public void testExceptExcept( 592 ) throws Throwable 593 { 594 final String INSERT = "insert into QUERY_TEST_EXCEPT (VALUE_1) values (?)"; 595 596 final String DELETE = "delete from QUERY_TEST_EXCEPT"; 597 598 final String EXCEPT_EXCEPT_SELECT = ((DatabaseTestSchema)DatabaseSchemaManager.getInstance( 599 DatabaseTestSchema.class)).getSelectExceptQuery(); 600 601 PreparedStatement insertStatement = null; 602 PreparedStatement deleteStatement = null; 603 PreparedStatement selectStatement = null; 604 ResultSet rsResults = null; 605 606 try 607 { 608 m_transaction.begin(); 609 try 610 { 611 insertStatement = m_connection.prepareStatement(INSERT); 612 insertStatement.setInt(1, 1); 613 insertStatement.execute(); 614 615 insertStatement.setInt(1, 2); 616 insertStatement.execute(); 617 618 insertStatement.setInt(1, 3); 619 insertStatement.execute(); 620 621 m_transaction.commit(); 622 } 623 catch (Throwable throwable) 624 { 625 m_transaction.rollback(); 626 throw throwable; 627 } 628 finally 629 { 630 DatabaseUtils.closeStatement(insertStatement); 631 } 632 633 selectStatement = m_connection.prepareStatement(EXCEPT_EXCEPT_SELECT); 638 selectStatement.setInt(1, 1); 639 selectStatement.setInt(2, 2); 640 selectStatement.setInt(3, 3); 641 selectStatement.setInt(4, 1); 642 selectStatement.setInt(5, 2); 643 selectStatement.setInt(6, 2); 644 selectStatement.setInt(7, 3); 645 646 try 647 { 648 rsResults = selectStatement.executeQuery(); 649 } 650 catch (SQLException sqleExc) 651 { 652 fail("It seems like database doesn't support queries with EXCEPT: " + 653 sqleExc.getMessage()); 654 throw sqleExc; 655 } 656 657 assertFalse("Select should not return any data", rsResults.next()); 658 } 659 finally 660 { 661 m_transaction.begin(); 662 try 663 { 664 deleteStatement = m_connection.prepareStatement(DELETE); 665 666 deleteStatement.execute(); 667 m_transaction.commit(); 668 } 669 catch (Throwable thr) 670 { 671 m_transaction.rollback(); 672 throw new Exception (thr); 673 } 674 finally 675 { 676 DatabaseUtils.closeStatement(deleteStatement); 677 } 678 } 679 } 680 681 686 public void testExceptUnion( 687 ) throws Throwable 688 { 689 final String INSERT = "insert into QUERY_TEST_EXCEPT (VALUE_1) values (?)"; 690 691 final String DELETE = "delete from QUERY_TEST_EXCEPT"; 692 693 final String EXCEPT_UNION_SELECT = ((DatabaseTestSchema)DatabaseSchemaManager.getInstance( 694 DatabaseTestSchema.class)).getSelectExceptUnionQuery(); 695 696 PreparedStatement insertStatement = null; 697 PreparedStatement deleteStatement = null; 698 PreparedStatement selectStatement = null; 699 ResultSet rsResults = null; 700 701 try 702 { 703 m_transaction.begin(); 704 try 705 { 706 insertStatement = m_connection.prepareStatement(INSERT); 707 insertStatement.setInt(1, 1); 708 insertStatement.execute(); 709 710 insertStatement.setInt(1, 2); 711 insertStatement.execute(); 712 713 insertStatement.setInt(1, 3); 714 insertStatement.execute(); 715 716 m_transaction.commit(); 717 } 718 catch (Throwable throwable) 719 { 720 m_transaction.rollback(); 721 throw throwable; 722 } 723 finally 724 { 725 DatabaseUtils.closeStatement(insertStatement); 726 } 727 728 selectStatement = m_connection.prepareStatement(EXCEPT_UNION_SELECT); 733 selectStatement.setInt(1, 1); 734 selectStatement.setInt(2, 2); 735 selectStatement.setInt(3, 3); 736 selectStatement.setInt(4, 1); 737 selectStatement.setInt(5, 2); 738 selectStatement.setInt(6, 2); 739 selectStatement.setInt(7, 3); 740 741 try 742 { 743 rsResults = selectStatement.executeQuery(); 744 } 745 catch (SQLException sqleExc) 746 { 747 fail("It seems like database doesn't support queries with EXCEPT" + 748 " or with UNION: " + sqleExc.getMessage()); 749 throw sqleExc; 750 } 751 752 assertTrue("Select should return first value", rsResults.next()); 753 assertEquals("First value should be 2", 2, rsResults.getInt(1)); 754 assertTrue("Select should return second value", rsResults.next()); 755 assertEquals("First value should be 3", 3, rsResults.getInt(1)); 756 } 757 finally 758 { 759 m_transaction.begin(); 760 try 761 { 762 deleteStatement = m_connection.prepareStatement(DELETE); 763 764 deleteStatement.execute(); 765 m_transaction.commit(); 766 } 767 catch (Throwable thr) 768 { 769 m_transaction.rollback(); 770 throw new Exception (thr); 771 } 772 finally 773 { 774 DatabaseUtils.closeStatement(deleteStatement); 775 } 776 } 777 } 778 779 784 public void testExistsQuery( 785 ) throws Throwable 786 { 787 final String INSERT = "insert into QUERY_TEST (VALUE_1, VALUE_2) values (?,?)"; 788 final String DELETE = "delete from QUERY_TEST"; 789 790 final String SELECT = ((DatabaseTestSchema)DatabaseSchemaManager.getInstance( 791 DatabaseTestSchema.class)).getSelectExistQuery(); 792 793 PreparedStatement insertStatement = null; 794 PreparedStatement deleteStatement = null; 795 PreparedStatement selectStatement = null; 796 ResultSet rsResults = null; 797 798 try 799 { 800 m_transaction.begin(); 801 try 802 { 803 insertStatement = m_connection.prepareStatement(INSERT); 804 insertStatement.setInt(1, 1); 805 insertStatement.setInt(2, 11); 806 insertStatement.execute(); 807 808 insertStatement = m_connection.prepareStatement(INSERT); 809 insertStatement.setInt(1, 11); 810 insertStatement.setInt(2, 12); 811 insertStatement.execute(); 812 813 m_transaction.commit(); 814 } 815 catch (Throwable throwable) 816 { 817 m_transaction.rollback(); 818 throw throwable; 819 } 820 finally 821 { 822 DatabaseUtils.closeStatement(insertStatement); 823 } 824 825 try 826 { 827 selectStatement = m_connection.prepareStatement(SELECT); 828 rsResults = selectStatement.executeQuery(); 829 830 assertTrue("There should be rows in result", rsResults.next()); 831 assertFalse("There should be no more rows in result", rsResults.next()); 832 } 833 catch (SQLException sqleExc) 834 { 835 assertTrue(sqleExc.getMessage(), false); 836 } 837 finally 838 { 839 DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement); 840 } 841 } 842 finally 843 { 844 m_transaction.begin(); 845 try 846 { 847 deleteStatement = m_connection.prepareStatement(DELETE); 848 deleteStatement.execute(); 849 m_transaction.commit(); 850 } 851 catch (Throwable thr) 852 { 853 m_transaction.rollback(); 854 throw new Exception (thr); 855 } 856 finally 857 { 858 DatabaseUtils.closeStatement(deleteStatement); 859 } 860 } 861 } 862 } 863 } 864 | Popular Tags |