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 }