001 package com.croftsoft.core.sql; 002 003 import java.math.BigDecimal; 004 import java.sql.*; 005 import java.util.Vector; 006 007 import com.croftsoft.core.lang.NullArgumentException; 008 import com.croftsoft.core.lang.Pair; 009 010 /********************************************************************* 011 * A library of SQL manipulation methods. 012 * 013 * @version 014 * 2002-09-16 015 * @since 016 * 2001-06-06 017 * @author 018 * <a href="https://www.croftsoft.com/">David Wallace Croft</a> 019 *********************************************************************/ 020 021 public final class SqlLib 022 ////////////////////////////////////////////////////////////////////// 023 ////////////////////////////////////////////////////////////////////// 024 { 025 026 /********************************************************************* 027 * If set to true, debugging information will be printed to System.out. 028 *********************************************************************/ 029 public static boolean debug = false; 030 031 /********************************************************************* 032 * "All the major databases support VARCHAR lengths up to 254 033 * characters." -- JDBC API Tutorial and Reference, 2nd Edition, p911. 034 *********************************************************************/ 035 public static int VARCHAR_LENGTH_MAX = 254; 036 037 ////////////////////////////////////////////////////////////////////// 038 ////////////////////////////////////////////////////////////////////// 039 040 /********************************************************************* 041 * Prints the result of the test method. 042 *********************************************************************/ 043 public static void main ( String [ ] args ) 044 ////////////////////////////////////////////////////////////////////// 045 { 046 System.out.println ( test ( args ) ); 047 } 048 049 /********************************************************************* 050 * Test method. 051 *********************************************************************/ 052 public static boolean test ( String [ ] args ) 053 ////////////////////////////////////////////////////////////////////// 054 { 055 String sql = createDeleteSql ( 056 "TABLE_USER", 057 new Pair [ ] { 058 new Pair ( "USERNAME", "croft" ) } ); 059 060 if ( !sql.equals ( 061 "DELETE FROM TABLE_USER WHERE USERNAME='croft'" ) ) 062 { 063 System.out.println ( sql ); 064 065 return false; 066 } 067 068 sql = createInsertSql ( 069 "TABLE_USER", 070 new String [ ] { "croft", "abc'123", null } ); 071 072 if ( !sql.equals ( 073 "INSERT INTO TABLE_USER VALUES('croft','abc''123',NULL)" ) ) 074 { 075 System.out.println ( sql ); 076 077 return false; 078 } 079 080 sql = createSelectSql ( 081 new String [ ] { "FIRST_NAME", "LAST_NAME" }, 082 "TABLE_USER", 083 new Pair ( "USERNAME", "croft" ) ); 084 085 if ( !sql.equals ( "SELECT FIRST_NAME,LAST_NAME FROM TABLE_USER" 086 + " WHERE USERNAME='croft'" ) ) 087 { 088 System.out.println ( sql ); 089 090 return false; 091 } 092 093 sql = createUpdateSql ( 094 "TABLE_USER", 095 new Pair [ ] { 096 new Pair ( "GENDER" , "M" ), 097 new Pair ( "BIRTHDAY", null ) }, 098 new Pair [ ] { 099 new Pair ( "USERNAME", "joe" ) } ); 100 101 if ( !sql.equals ( 102 "UPDATE TABLE_USER SET GENDER='M',BIRTHDAY=NULL" 103 + " WHERE USERNAME='joe'" ) ) 104 { 105 System.out.println ( sql ); 106 107 return false; 108 } 109 110 return true; 111 } 112 113 ////////////////////////////////////////////////////////////////////// 114 ////////////////////////////////////////////////////////////////////// 115 116 /********************************************************************* 117 * Counts the rows in a table that meet the WHERE condition. 118 * 119 * <p> 120 * Example query: 121 * <code> 122 * <pre> 123 * SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M' 124 * </pre> 125 * </code> 126 * </p> 127 * 128 * @param wherePair 129 * 130 * If the wherePair object is null, all rows will be counted. 131 * Instance variable wherePair.value may be null. 132 * A non-null wherePair.value will converted by escapeQuotes(). 133 * 134 * @return 135 * 136 * The count of rows selected. 137 * 138 * @throws NullArgumentException 139 * 140 * If tableName is null. 141 *********************************************************************/ 142 public static int count ( 143 Connection connection, 144 String tableName, 145 Pair wherePair ) 146 throws SQLException 147 ////////////////////////////////////////////////////////////////////// 148 { 149 return count ( connection, tableName, 150 wherePair == null 151 ? ( Pair [ ] ) null : new Pair [ ] { wherePair } ); 152 } 153 154 /********************************************************************* 155 * Counts the rows in a table that meet the WHERE condition. 156 * 157 * <p> 158 * Example query: 159 * <code> 160 * <pre> 161 * SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M' AND AGE='34' 162 * </pre> 163 * </code> 164 * </p> 165 * 166 * @param wherePairs 167 * 168 * If the wherePairs object is null, all rows will be counted. 169 * If the length of wherePairs is greater than one, the where 170 * clause will be the conjuction ("AND") of the individual 171 * where pairs. 172 * A null wherePair.value will be translated as "IS NULL". 173 * A non-null wherePair.value will converted by escapeQuotes(). 174 * 175 * @return 176 * 177 * The count of rows selected. 178 * 179 * @throws NullArgumentException 180 * 181 * If tableName is null. 182 *********************************************************************/ 183 public static int count ( 184 Connection connection, 185 String tableName, 186 Pair [ ] wherePairs ) 187 throws SQLException 188 ////////////////////////////////////////////////////////////////////// 189 { 190 String querySql = createCountSql ( tableName, wherePairs ); 191 192 if ( debug ) 193 { 194 System.out.println ( "SqlLib.count(): " + querySql ); 195 } 196 197 Statement statement = null; 198 199 try 200 { 201 statement = connection.createStatement ( ); 202 203 ResultSet resultSet = statement.executeQuery ( querySql ); 204 205 if ( resultSet.next ( ) ) 206 { 207 return resultSet.getInt ( 1 ); 208 } 209 else 210 { 211 return 0; 212 } 213 } 214 finally 215 { 216 if ( statement != null ) 217 { 218 statement.close ( ); 219 } 220 } 221 } 222 223 /********************************************************************* 224 * Creates an SQL SELECT COUNT(*) statement. 225 * 226 * Used to count the number of rows that meet the criterion. 227 * 228 * <p> 229 * Example output: 230 * <code> 231 * <pre> 232 * SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M' 233 * </pre> 234 * </code> 235 * </p> 236 * 237 * @param wherePair 238 * 239 * If the wherePair object is null, no "where" clause will be 240 * appended, indicating that all rows in the table should be 241 * selected. 242 * Instance variable wherePair.value may be null. 243 * A non-null wherePair.value will converted by escapeQuotes(). 244 * 245 * @throws NullArgumentException 246 * 247 * If tableName is null. 248 *********************************************************************/ 249 public static String createCountSql ( 250 String tableName, 251 Pair wherePair ) 252 ////////////////////////////////////////////////////////////////////// 253 { 254 return createCountSql ( tableName, 255 wherePair == null 256 ? ( Pair [ ] ) null : new Pair [ ] { wherePair } ); 257 } 258 259 /********************************************************************* 260 * Creates an SQL SELECT COUNT(*) statement. 261 * 262 * Used to count the number of rows that meet the criteria. 263 * 264 * <p> 265 * Example output: 266 * <code> 267 * <pre> 268 * SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M' AND AGE='34' 269 * </pre> 270 * </code> 271 * </p> 272 * 273 * @param wherePairs 274 * 275 * If the wherePairs object is null, no "where" clause will be 276 * appended, indicating that all rows in the table should be 277 * selected. 278 * If the length of wherePairs is greater than one, the where 279 * clause will be the conjuction ("AND") of the individual 280 * where pairs. 281 * A null wherePair.value will be translated as "IS NULL". 282 * A non-null wherePair.value will converted by escapeQuotes(). 283 * 284 * @throws NullArgumentException 285 * 286 * If tableName is null. 287 *********************************************************************/ 288 public static String createCountSql ( 289 String tableName, 290 Pair [ ] wherePairs ) 291 ////////////////////////////////////////////////////////////////////// 292 { 293 NullArgumentException.check ( tableName ); 294 295 StringBuffer stringBuffer 296 = new StringBuffer ( "SELECT COUNT(*) FROM " ); 297 298 stringBuffer.append ( tableName ); 299 300 appendWhereClause ( stringBuffer, wherePairs ); 301 302 return stringBuffer.toString ( ); 303 } 304 305 /********************************************************************* 306 * Creates an SQL DELETE statement. 307 * 308 * <p> 309 * Example output: 310 * <code> 311 * <pre> 312 * DELETE FROM TABLE_USER WHERE GENDER='M' AND AGE='34' 313 * </pre> 314 * </code> 315 * </p> 316 * 317 * @param wherePairs 318 * 319 * If the wherePairs object is null, no "where" clause will be 320 * appended, indicating that all rows in the table should be 321 * selected. 322 * If the length of wherePairs is greater than one, the where 323 * clause will be the conjuction ("AND") of the individual 324 * where pairs. 325 * A null wherePair.value will be translated as "IS NULL". 326 * A non-null wherePair.value will converted by escapeQuotes(). 327 * 328 * @throws NullArgumentException 329 * 330 * If tableName is null. 331 *********************************************************************/ 332 public static String createDeleteSql ( 333 String tableName, 334 Pair [ ] wherePairs ) 335 ////////////////////////////////////////////////////////////////////// 336 { 337 NullArgumentException.check ( tableName ); 338 339 StringBuffer stringBuffer = new StringBuffer ( ); 340 341 stringBuffer.append ( "DELETE FROM " ); 342 343 stringBuffer.append ( tableName ); 344 345 appendWhereClause ( stringBuffer, wherePairs ); 346 347 return stringBuffer.toString ( ); 348 } 349 350 /********************************************************************* 351 * Creates an SQL INSERT statement. 352 * 353 * <p> 354 * Example output: 355 * <code> 356 * <pre> 357 * INSERT INTO TABLE_USER VALUES('croft','abc''123',NULL) 358 * </pre> 359 * </code> 360 * </p> 361 * 362 * @param values 363 * 364 * Individual elements of the array may be null. 365 * Non-null values will be converted by escapeQuotes(). 366 * 367 * @throws NullArgumentException 368 * 369 * If tableName or values is null. 370 *********************************************************************/ 371 public static String createInsertSql ( 372 String tableName, 373 String [ ] values ) 374 ////////////////////////////////////////////////////////////////////// 375 { 376 NullArgumentException.check ( tableName ); 377 378 NullArgumentException.check ( values ); 379 380 StringBuffer stringBuffer = new StringBuffer ( ); 381 382 stringBuffer.append ( "INSERT INTO " ); 383 384 stringBuffer.append ( tableName ); 385 386 stringBuffer.append ( " VALUES(" ); 387 388 for ( int i = 0; i < values.length; i++ ) 389 { 390 if ( i > 0 ) 391 { 392 stringBuffer.append ( "," ); 393 } 394 395 if ( values [ i ] != null ) 396 { 397 stringBuffer.append ( "'" ); 398 399 stringBuffer.append ( escapeQuotes ( values [ i ] ) ); 400 401 stringBuffer.append ( "'" ); 402 } 403 else 404 { 405 stringBuffer.append ( "NULL" ); 406 } 407 } 408 409 stringBuffer.append ( ")" ); 410 411 return stringBuffer.toString ( ); 412 } 413 414 /********************************************************************* 415 * Creates an SQL SELECT statement. 416 * 417 * <p> 418 * Example output: 419 * <code> 420 * <pre> 421 * SELECT FIRST_NAME,LAST_NAME FROM TABLE_USER WHERE USERNAME='croft' 422 * </pre> 423 * </code> 424 * </p> 425 * 426 * @param selectFieldNames 427 * 428 * Must not be null. Elements must not be null. 429 * Use "*" to select all fields. 430 * 431 * @param wherePair 432 * 433 * If the wherePair object is null, no "where" clause will be 434 * appended, indicating that all rows in the table should be 435 * selected. 436 * Instance variable wherePair.value may be null. 437 * A non-null wherePair.value will converted by escapeQuotes(). 438 * 439 * @throws NullArgumentException 440 * 441 * If tableName, selectFieldNames, or an element of setFieldNames is 442 * null. 443 *********************************************************************/ 444 public static String createSelectSql ( 445 String [ ] selectFieldNames, 446 String tableName, 447 Pair wherePair ) 448 ////////////////////////////////////////////////////////////////////// 449 { 450 return createSelectSql ( 451 selectFieldNames, tableName, 452 wherePair == null 453 ? ( Pair [ ] ) null : new Pair [ ] { wherePair } ); 454 } 455 456 /********************************************************************* 457 * Creates an SQL SELECT statement. 458 * 459 * <p> 460 * Example output: 461 * <code> 462 * <pre> 463 * SELECT NAME,SALARY FROM TABLE_USER WHERE GENDER='M' AND AGE='34' 464 * </pre> 465 * </code> 466 * </p> 467 * 468 * @param selectFieldNames 469 * 470 * Must not be null. Elements must not be null. 471 * Use "*" to select all fields. 472 * 473 * @param wherePairs 474 * 475 * If the wherePairs object is null, no "where" clause will be 476 * appended, indicating that all rows in the table should be 477 * selected. 478 * If the length of wherePairs is greater than one, the where 479 * clause will be the conjuction ("AND") of the individual 480 * where pairs. 481 * A null wherePair.value will be translated as "IS NULL". 482 * A non-null wherePair.value will converted by escapeQuotes(). 483 * 484 * @throws NullArgumentException 485 * 486 * If tableName, selectFieldNames, or an element of setFieldNames is 487 * null. 488 *********************************************************************/ 489 public static String createSelectSql ( 490 String [ ] selectFieldNames, 491 String tableName, 492 Pair [ ] wherePairs ) 493 ////////////////////////////////////////////////////////////////////// 494 { 495 return createSelectSql ( 496 selectFieldNames, tableName, wherePairs, null ); 497 } 498 499 /********************************************************************* 500 * Creates an SQL SELECT statement. 501 * 502 * <p> 503 * Example output: 504 * <code> 505 * <pre> 506 * SELECT NAME,SALARY FROM TABLE_USER WHERE GENDER='M' AND AGE='34' 507 * </pre> 508 * </code> 509 * </p> 510 * 511 * @param selectFieldNames 512 * 513 * Must not be null. Elements must not be null. 514 * Use "*" to select all fields. 515 * 516 * @param wherePairs 517 * 518 * If the wherePairs object is null, no "where" clause will be 519 * appended, indicating that all rows in the table should be 520 * selected. 521 * If the length of wherePairs is greater than one, the where 522 * clause will be the conjuction ("AND") of the individual 523 * where pairs. 524 * A null wherePair.value will be translated as "IS NULL". 525 * A non-null wherePair.value will converted by escapeQuotes(). 526 * 527 * @param orderBy 528 * 529 * The column name to use for ordering. 530 * 531 * @throws NullArgumentException 532 * 533 * If tableName, selectFieldNames, or an element of setFieldNames is 534 * null. 535 *********************************************************************/ 536 public static String createSelectSql ( 537 String [ ] selectFieldNames, 538 String tableName, 539 Pair [ ] wherePairs, 540 String orderBy ) 541 ////////////////////////////////////////////////////////////////////// 542 { 543 NullArgumentException.check ( selectFieldNames ); 544 545 NullArgumentException.check ( tableName ); 546 547 StringBuffer stringBuffer = new StringBuffer ( ); 548 549 stringBuffer.append ( "SELECT " ); 550 551 for ( int i = 0; i < selectFieldNames.length; i++ ) 552 { 553 if ( i > 0 ) 554 { 555 stringBuffer.append ( ',' ); 556 } 557 558 NullArgumentException.check ( 559 selectFieldNames [ i ], "selectFieldNames[" + i + "] is null" ); 560 561 stringBuffer.append ( selectFieldNames [ i ] ); 562 } 563 564 stringBuffer.append ( " FROM " ); 565 566 stringBuffer.append ( tableName ); 567 568 appendWhereClause ( stringBuffer, wherePairs ); 569 570 if ( orderBy != null ) 571 { 572 stringBuffer.append ( " ORDER BY " ); 573 574 stringBuffer.append ( orderBy ); 575 } 576 577 return stringBuffer.toString ( ); 578 } 579 580 /********************************************************************* 581 * Creates an SQL UPDATE statement. 582 * 583 * <p> 584 * Example output: 585 * <code> 586 * <pre> 587 * UPDATE TABLE_USER SET GENDER='M',BIRTHDAY=NULL WHERE USERNAME='joe' 588 * </pre> 589 * </code> 590 * </p> 591 * 592 * @param setPairs 593 * 594 * Must not be null. Array elements must not be null. 595 * An element setPair.value may be null. 596 * A non-null setPair.value will converted by escapeQuotes(). 597 * 598 * @param wherePairs 599 * 600 * If the wherePairs object is null, no "where" clause will be 601 * appended, indicating that all rows in the table should be 602 * selected. 603 * If the length of wherePairs is greater than one, the where 604 * clause will be the conjuction ("AND") of the individual 605 * where pairs. 606 * A null wherePair.value will be translated as "IS NULL". 607 * A non-null wherePair.value will converted by escapeQuotes(). 608 * 609 * @throws NullArgumentException 610 * 611 * If tableName, setPairs, or an element of setPairs is null. 612 *********************************************************************/ 613 public static String createUpdateSql ( 614 String tableName, 615 Pair [ ] setPairs, 616 Pair [ ] wherePairs ) 617 ////////////////////////////////////////////////////////////////////// 618 { 619 NullArgumentException.check ( tableName ); 620 621 NullArgumentException.check ( setPairs ); 622 623 StringBuffer stringBuffer = new StringBuffer ( ); 624 625 stringBuffer.append ( "UPDATE " ); 626 627 stringBuffer.append ( tableName ); 628 629 stringBuffer.append ( " SET " ); 630 631 for ( int i = 0; i < setPairs.length; i++ ) 632 { 633 if ( i > 0 ) 634 { 635 stringBuffer.append ( ',' ); 636 } 637 638 Pair setPair = setPairs [ i ]; 639 640 NullArgumentException.check ( 641 setPair, "setPairs[" + i + "] is null" ); 642 643 stringBuffer.append ( setPair.name ); 644 645 stringBuffer.append ( '=' ); 646 647 if ( setPair.value != null ) 648 { 649 stringBuffer.append ( '\'' ); 650 651 stringBuffer.append ( escapeQuotes ( setPair.value ) ); 652 653 stringBuffer.append ( '\'' ); 654 } 655 else 656 { 657 stringBuffer.append ( "NULL" ); 658 } 659 } 660 661 appendWhereClause ( stringBuffer, wherePairs ); 662 663 return stringBuffer.toString ( ); 664 } 665 666 /********************************************************************* 667 * Removes rows from a table. 668 * 669 * @param wherePairs 670 * 671 * See method createDeleteSql() for description. 672 * 673 * @return 674 * 675 * The number of rows deleted. 676 *********************************************************************/ 677 public static int delete ( 678 Connection connection, 679 String tableName, 680 Pair [ ] wherePairs ) 681 throws SQLException 682 ////////////////////////////////////////////////////////////////////// 683 { 684 String updateSql = createDeleteSql ( tableName, wherePairs ); 685 686 if ( debug ) 687 { 688 System.out.println ( "SqlLib.delete(): " + updateSql ); 689 } 690 691 return executeUpdate ( connection, updateSql ); 692 } 693 694 public static int dropTable ( 695 Connection connection, 696 String tableName ) 697 throws SQLException 698 ////////////////////////////////////////////////////////////////////// 699 { 700 String updateSql = "DROP TABLE " + tableName; 701 702 if ( debug ) 703 { 704 System.out.println ( "SqlLib.dropTable(): " + updateSql ); 705 } 706 707 return executeUpdate ( connection, updateSql ); 708 } 709 710 /********************************************************************* 711 * Doubles all single and double quotes in the original String. 712 * 713 * <p> 714 * Used to prepare a String to be passed as an SQL statement variable. 715 * For example, notice how the single quote is doubled inside the 716 * the password "abc'123" which contains an apostrophe: 717 * <code> 718 * <pre> 719 * INSERT INTO TABLE_USER VALUES ('croft', 'abc''123') 720 * </pre> 721 * </code> 722 * </p> 723 *********************************************************************/ 724 public static String escapeQuotes ( String originalString ) 725 ////////////////////////////////////////////////////////////////////// 726 { 727 NullArgumentException.check ( originalString ); 728 729 StringBuffer stringBuffer = new StringBuffer ( ); 730 731 int originalStringLength = originalString.length ( ); 732 733 for ( int i = 0; i < originalStringLength; i++ ) 734 { 735 char c = originalString.charAt ( i ); 736 737 stringBuffer.append ( c ); 738 739 if ( c == '\'' ) 740 { 741 stringBuffer.append ( '\'' ); 742 } 743 else if ( c == '"' ) 744 { 745 stringBuffer.append ( '"' ); 746 } 747 } 748 749 return stringBuffer.toString ( ); 750 } 751 752 /********************************************************************* 753 * Executes an SQL update statement. 754 * 755 * <p> 756 * This convenience method creates a new Statement instance, 757 * executes the update, and then ensures that the Statement is closed 758 * before return or abnormal exit. 759 * </p> 760 * 761 * @return 762 * 763 * The number of rows created. 764 *********************************************************************/ 765 public static int executeUpdate ( 766 Connection connection, 767 String updateSql ) 768 throws SQLException 769 ////////////////////////////////////////////////////////////////////// 770 { 771 Statement statement = null; 772 773 try 774 { 775 statement = connection.createStatement ( ); 776 777 return statement.executeUpdate ( updateSql ); 778 } 779 finally 780 { 781 if ( statement != null ) 782 { 783 statement.close ( ); 784 } 785 } 786 } 787 788 /********************************************************************* 789 * Adds a row to a table. 790 * 791 * @param values 792 * 793 * Each value will be converted by escapeQuotes() before being used. 794 * 795 * @return 796 * 797 * The number of rows created. 798 *********************************************************************/ 799 public static int insert ( 800 Connection connection, 801 String tableName, 802 String [ ] values ) 803 throws SQLException 804 ////////////////////////////////////////////////////////////////////// 805 { 806 String updateSql = createInsertSql ( tableName, values ); 807 808 if ( debug ) 809 { 810 System.out.println ( "SqlLib.insert(): " + updateSql ); 811 } 812 813 return executeUpdate ( connection, updateSql ); 814 } 815 816 /********************************************************************* 817 * Returns the maximum column value. 818 * 819 * <p> 820 * Example query: 821 * <code> 822 * <pre> 823 * SELECT MAX(SALARY) FROM TABLE_EMPLOYEE WHERE DEPARTMENT_ID='3' 824 * </pre> 825 * </code> 826 * </p> 827 * 828 * @param columnName 829 * 830 * Must not be null. 831 * 832 * @param wherePair 833 * 834 * If the wherePair object is null, all rows will be included. 835 * Instance variable wherePair.value may be null. 836 * A non-null wherePair.value will converted by escapeQuotes(). 837 * 838 * @return 839 * 840 * The maximum column value as a BigDecimal or null if there are no 841 * non-null values selected to compare. 842 * 843 * @throws NullArgumentException 844 * 845 * If columnName or tableName is null. 846 *********************************************************************/ 847 public static BigDecimal max ( 848 Connection connection, 849 String columnName, 850 String tableName, 851 Pair wherePair ) 852 throws SQLException 853 ////////////////////////////////////////////////////////////////////// 854 { 855 NullArgumentException.check ( columnName ); 856 857 NullArgumentException.check ( tableName ); 858 859 StringBuffer stringBuffer = new StringBuffer ( "SELECT MAX(" ); 860 861 stringBuffer.append ( columnName ); 862 863 stringBuffer.append ( ") FROM " ); 864 865 stringBuffer.append ( tableName ); 866 867 appendWhereClause ( stringBuffer, wherePair ); 868 869 String querySql = stringBuffer.toString ( ); 870 871 if ( debug ) 872 { 873 System.out.println ( "SqlLib.max(): " + querySql ); 874 } 875 876 Statement statement = null; 877 878 try 879 { 880 statement = connection.createStatement ( ); 881 882 ResultSet resultSet = statement.executeQuery ( querySql ); 883 884 if ( resultSet.next ( ) ) 885 { 886 return resultSet.getBigDecimal ( 1 ); 887 } 888 else 889 { 890 return null; 891 } 892 } 893 finally 894 { 895 if ( statement != null ) 896 { 897 statement.close ( ); 898 } 899 } 900 } 901 902 /********************************************************************* 903 * Selects the first row that meets the where condition. 904 * 905 * <p> 906 * Only returns the values for the first row selected. 907 * </p> 908 * 909 * @param selectFieldNames 910 * 911 * Must not be null. Elements must not be null. 912 * Use new String[]{"*"} to select all fields. 913 * 914 * @param wherePair 915 * 916 * See method createSelectSql() for description. 917 * 918 * @throws NullArgumentException 919 * 920 * If tableName, selectFieldNames, or an element of setFieldNames is 921 * null. 922 * 923 * @return 924 * 925 * The field values for the first row selected or null if no row 926 * was selected. 927 *********************************************************************/ 928 public static String [ ] select ( 929 Connection connection, 930 String [ ] selectFieldNames, 931 String tableName, 932 Pair wherePair ) 933 throws SQLException 934 ////////////////////////////////////////////////////////////////////// 935 { 936 return select ( 937 connection, 938 selectFieldNames, 939 tableName, 940 wherePair == null 941 ? ( Pair [ ] ) null : new Pair [ ] { wherePair } ); 942 } 943 944 /********************************************************************* 945 * Selects the first row that meets the where condition. 946 * 947 * <p> 948 * Only returns the values for the first row selected. 949 * </p> 950 * 951 * @param selectFieldNames 952 * 953 * Must not be null. Elements must not be null. 954 * Use new String[]{"*"} to select all fields. 955 * 956 * @param wherePairs 957 * 958 * See method createSelectSql() for description. 959 * 960 * @throws NullArgumentException 961 * 962 * If tableName, selectFieldNames, or an element of setFieldNames is 963 * null. 964 * 965 * @return 966 * 967 * The field values for the first row selected or null if no row 968 * was selected. 969 *********************************************************************/ 970 public static String [ ] select ( 971 Connection connection, 972 String [ ] selectFieldNames, 973 String tableName, 974 Pair [ ] wherePairs ) 975 throws SQLException 976 ////////////////////////////////////////////////////////////////////// 977 { 978 return select ( connection, 979 selectFieldNames, tableName, wherePairs, null, 0 ); 980 } 981 982 /********************************************************************* 983 * Returns the results of a SELECT query as a String array. 984 * 985 * <p> 986 * This method lets you return multiple column values from a single row 987 * or multiple row values from a single column. The results are then 988 * returned as a 1-dimensional String array. If the input variable 989 * <i>maxRows</i> is zero, just the column values from the first row 990 * selected are returned. If <i>maxRows</i> is greater than zero, up 991 * to that number of row values from a single column are returned. 992 * </p> 993 * 994 * @param selectFieldNames 995 * 996 * Must not be null. Elements must not be null. 997 * Use new String[]{"*"} to select all fields. 998 * 999 * @param wherePairs 1000 * 1001 * See method createSelectSql() for description. 1002 * 1003 * @param orderBy 1004 * 1005 * The column name to use for ordering. 1006 * 1007 * @param maxRows 1008 * 1009 * If 0, multiple column values from a single row will be returned. 1010 * If greater than 0, multiple row values from a single column 1011 * will be returned, up to maxRows in length. 1012 * 1013 * @throws NullArgumentException 1014 * 1015 * If tableName, selectFieldNames, or an element of setFieldNames is 1016 * null. 1017 * 1018 * @return 1019 * 1020 * The field values for the first row selected or null if no row 1021 * was selected. 1022 *********************************************************************/ 1023 public static String [ ] select ( 1024 Connection connection, 1025 String [ ] selectFieldNames, 1026 String tableName, 1027 Pair [ ] wherePairs, 1028 String orderBy, 1029 int maxRows ) 1030 throws SQLException 1031 ////////////////////////////////////////////////////////////////////// 1032 { 1033 if ( maxRows < 0 ) 1034 { 1035 throw new IllegalArgumentException ( "maxRows < 0" ); 1036 } 1037 1038 if ( maxRows > 0 && selectFieldNames.length > 1 ) 1039 { 1040 throw new IllegalArgumentException ( 1041 "selectFieldNames.length > 1 when maxRows > 0" ); 1042 } 1043 1044 String querySql = createSelectSql ( 1045 selectFieldNames, tableName, wherePairs, orderBy ); 1046 1047 if ( debug ) 1048 { 1049 System.out.println ( "SqlLib.select(): " + querySql ); 1050 } 1051 1052 Statement statement = null; 1053 1054 try 1055 { 1056 statement = connection.createStatement ( ); 1057 1058 if ( maxRows > 0 ) 1059 { 1060 statement.setFetchSize ( maxRows ); 1061 1062 statement.setMaxRows ( maxRows ); 1063 } 1064 else 1065 { 1066 statement.setFetchSize ( 1 ); 1067 1068 statement.setMaxRows ( 1 ); 1069 } 1070 1071 ResultSet resultSet = statement.executeQuery ( querySql ); 1072 1073 if ( !resultSet.next ( ) ) 1074 { 1075 return null; 1076 } 1077 1078 if ( maxRows > 0 ) 1079 { 1080 Vector stringVector = new Vector ( ); 1081 1082 stringVector.addElement ( resultSet.getString ( 1 ) ); 1083 1084 while ( resultSet.next ( ) ) 1085 { 1086 stringVector.addElement ( resultSet.getString ( 1 ) ); 1087 } 1088 1089 String [ ] values = new String [ stringVector.size ( ) ]; 1090 1091 stringVector.copyInto ( values ); 1092 1093 return values; 1094 } 1095 else 1096 { 1097 ResultSetMetaData resultSetMetaData 1098 = resultSet.getMetaData ( ); 1099 1100 String [ ] values 1101 = new String [ resultSetMetaData.getColumnCount ( ) ]; 1102 1103 for ( int i = 0; i < values.length; i++ ) 1104 { 1105 values [ i ] = resultSet.getString ( i + 1 ); 1106 } 1107 1108 return values; 1109 } 1110 } 1111 finally 1112 { 1113 if ( statement != null ) 1114 { 1115 statement.close ( ); 1116 } 1117 } 1118 } 1119 1120 /********************************************************************* 1121 * Retrieves the String value at a given row and column. 1122 * 1123 * <p> 1124 * Only returns a single value for the first row selected. 1125 * </p> 1126 * 1127 * @param selectFieldName 1128 * 1129 * Must not be null. If selectFieldName is "*", this method will 1130 * return the value associated with the first field name only. 1131 * 1132 * @param wherePair 1133 * 1134 * See method createSelectSql() for description. 1135 * 1136 * @throws NullArgumentException 1137 * 1138 * If tableName or selectFieldName is null. 1139 * 1140 * @return 1141 * 1142 * The column value for the first row selected or null if no 1143 * row was selected. 1144 *********************************************************************/ 1145 public static String select ( 1146 Connection connection, 1147 String selectFieldName, 1148 String tableName, 1149 Pair wherePair ) 1150 throws SQLException 1151 ////////////////////////////////////////////////////////////////////// 1152 { 1153 return select ( 1154 connection, 1155 selectFieldName, 1156 tableName, 1157 wherePair == null 1158 ? ( Pair [ ] ) null : new Pair [ ] { wherePair } ); 1159 } 1160 1161 /********************************************************************* 1162 * Retrieves the String value at a given row and column. 1163 * 1164 * <p> 1165 * Only returns a single value for the first row selected. 1166 * </p> 1167 * 1168 * @param selectFieldName 1169 * 1170 * Must not be null. If selectFieldName is "*", this method will 1171 * return the value associated with the first field name only. 1172 * 1173 * @param wherePairs 1174 * 1175 * See method createSelectSql() for description. 1176 * 1177 * @throws NullArgumentException 1178 * 1179 * If tableName or selectFieldName is null. 1180 * 1181 * @return 1182 * 1183 * The column value for the first row selected or null if no 1184 * row was selected. 1185 *********************************************************************/ 1186 public static String select ( 1187 Connection connection, 1188 String selectFieldName, 1189 String tableName, 1190 Pair [ ] wherePairs ) 1191 throws SQLException 1192 ////////////////////////////////////////////////////////////////////// 1193 { 1194 String [ ] values = select ( connection, 1195 new String [ ] { selectFieldName }, tableName, wherePairs ); 1196 1197 return values == null ? null : values [ 0 ]; 1198 } 1199 1200 public static boolean tableExists ( 1201 Connection connection, 1202 String tableName ) 1203 throws SQLException 1204 ////////////////////////////////////////////////////////////////////// 1205 { 1206 DatabaseMetaData databaseMetaData = connection.getMetaData ( ); 1207 1208 ResultSet resultSet = databaseMetaData.getTables ( 1209 ( String ) null, // catalog 1210 ( String ) null, // schemaPattern 1211 tableName, // tableNamePattern 1212 ( String [ ] ) null ); // types 1213 1214 return resultSet.next ( ); 1215 } 1216 1217 public static int truncateTable ( 1218 Connection connection, 1219 String tableName ) 1220 throws SQLException 1221 ////////////////////////////////////////////////////////////////////// 1222 { 1223 String updateSql = "TRUNCATE TABLE " + tableName; 1224 1225 if ( debug ) 1226 { 1227 System.out.println ( "SqlLib.truncateTable(): " + updateSql ); 1228 } 1229 1230 return executeUpdate ( connection, updateSql ); 1231 } 1232 1233 /********************************************************************* 1234 * Updates rows in a table. 1235 * 1236 * @param setPairs 1237 * 1238 * Must not be null. Array elements must not be null. 1239 * An element setPair.value may be null. 1240 * A non-null setPair.value will converted by escapeQuotes(). 1241 * 1242 * @param wherePair 1243 * 1244 * See method createUpdateSql() for description. 1245 * 1246 * @throws NullArgumentException 1247 * 1248 * If tableName, setPairs, or an element of setPairs is null. 1249 * 1250 * @return 1251 * 1252 * The number of rows updated. 1253 *********************************************************************/ 1254 public static int update ( 1255 Connection connection, 1256 String tableName, 1257 Pair [ ] setPairs, 1258 Pair wherePair ) 1259 throws SQLException 1260 ////////////////////////////////////////////////////////////////////// 1261 { 1262 return update ( 1263 connection, tableName, setPairs, new Pair [ ] { wherePair } ); 1264 } 1265 1266 /********************************************************************* 1267 * Updates rows in a table. 1268 * 1269 * @param setPairs 1270 * 1271 * Must not be null. Array elements must not be null. 1272 * An element setPair.value may be null. 1273 * A non-null setPair.value will converted by escapeQuotes(). 1274 * 1275 * @param wherePairs 1276 * 1277 * See method createUpdateSql() for description. 1278 * 1279 * @throws NullArgumentException 1280 * 1281 * If tableName, setPairs, or an element of setPairs is null. 1282 * 1283 * @return 1284 * 1285 * The number of rows updated. 1286 *********************************************************************/ 1287 public static int update ( 1288 Connection connection, 1289 String tableName, 1290 Pair [ ] setPairs, 1291 Pair [ ] wherePairs ) 1292 throws SQLException 1293 ////////////////////////////////////////////////////////////////////// 1294 { 1295 String updateSql 1296 = createUpdateSql ( tableName, setPairs, wherePairs ); 1297 1298 if ( debug ) 1299 { 1300 System.out.println ( "SqlLib.update(): " + updateSql ); 1301 } 1302 1303 return executeUpdate ( connection, updateSql ); 1304 } 1305 1306 ////////////////////////////////////////////////////////////////////// 1307 // private methods 1308 ////////////////////////////////////////////////////////////////////// 1309 1310 private static void appendWhereClause ( 1311 StringBuffer stringBuffer, 1312 Pair wherePair ) 1313 ////////////////////////////////////////////////////////////////////// 1314 { 1315 appendWhereClause ( stringBuffer, 1316 wherePair == null 1317 ? ( Pair [ ] ) null : new Pair [ ] { wherePair } ); 1318 } 1319 1320 private static void appendWhereClause ( 1321 StringBuffer stringBuffer, 1322 Pair [ ] wherePairs ) 1323 ////////////////////////////////////////////////////////////////////// 1324 { 1325 if ( wherePairs != null ) 1326 { 1327 stringBuffer.append ( " WHERE " ); 1328 1329 for ( int i = 0; i < wherePairs.length; i++ ) 1330 { 1331 Pair wherePair = wherePairs [ i ]; 1332 1333 stringBuffer.append ( wherePair.name ); 1334 1335 if ( wherePair.value != null ) 1336 { 1337 stringBuffer.append ( '=' ); 1338 1339 stringBuffer.append ( '\'' ); 1340 1341 stringBuffer.append ( escapeQuotes ( wherePair.value ) ); 1342 1343 stringBuffer.append ( '\'' ); 1344 } 1345 else 1346 { 1347 stringBuffer.append ( " IS NULL" ); 1348 } 1349 1350 if ( i < wherePairs.length - 1 ) 1351 { 1352 stringBuffer.append ( " AND " ); 1353 } 1354 } 1355 } 1356 } 1357 1358 ////////////////////////////////////////////////////////////////////// 1359 ////////////////////////////////////////////////////////////////////// 1360 1361 /********************************************************************* 1362 * Static method library classes do not require instantiation. 1363 *********************************************************************/ 1364 private SqlLib ( ) { } 1365 1366 ////////////////////////////////////////////////////////////////////// 1367 ////////////////////////////////////////////////////////////////////// 1368 }