sqllib.java

CroftSoft Code Library是一个开源的可移植的纯Java游戏库

     package com.croftsoft.core.sql;

     import java.math.BigDecimal;
     import java.sql.*;
     import java.util.Vector;

     import com.croftsoft.core.lang.NullArgumentException;
     import com.croftsoft.core.lang.Pair;

     /*********************************************************************
     * A library of SQL manipulation methods.
     *
     * @version
     *   2002-09-16
     * @since
     *   2001-06-06
     * @author
     *   <a href="http://alumni.caltech.edu/~croft">David Wallace Croft</a>
     *********************************************************************/

     public final class  SqlLib
     //////////////////////////////////////////////////////////////////////
     //////////////////////////////////////////////////////////////////////
     {

     /*********************************************************************
     * If set to true, debugging information will be printed to System.out.
     *********************************************************************/
     public static boolean  debug = false;

     /*********************************************************************
     * "All the major databases support VARCHAR lengths up to 254
     * characters." -- JDBC API Tutorial and Reference, 2nd Edition, p911.
     *********************************************************************/
     public static int  VARCHAR_LENGTH_MAX = 254;

     //////////////////////////////////////////////////////////////////////
     //////////////////////////////////////////////////////////////////////

     /*********************************************************************
     * Prints the result of the test method.
     *********************************************************************/
     public static void  main ( String [ ]  args )
     //////////////////////////////////////////////////////////////////////
     {
       System.out.println ( test ( args ) );
     }

     /*********************************************************************
     * Test method.
     *********************************************************************/
     public static boolean  test ( String [ ]  args )
     //////////////////////////////////////////////////////////////////////
     {
       String  sql = createDeleteSql (
         "TABLE_USER",
         new Pair [ ] {
           new Pair ( "USERNAME", "croft" ) } );

       if ( !sql.equals (
         "DELETE FROM TABLE_USER WHERE USERNAME='croft'" ) )
       {
         System.out.println ( sql );

         return false;
       }

       sql = createInsertSql (
         "TABLE_USER",
         new String [ ] { "croft", "abc'123", null } );

       if ( !sql.equals (
         "INSERT INTO TABLE_USER VALUES('croft','abc''123',NULL)" ) )
       {
         System.out.println ( sql );

         return false;
       }

       sql = createSelectSql (
         new String [ ] { "FIRST_NAME", "LAST_NAME" },
         "TABLE_USER",
         new Pair ( "USERNAME", "croft" ) );

       if ( !sql.equals ( "SELECT FIRST_NAME,LAST_NAME FROM TABLE_USER"
         + " WHERE USERNAME='croft'" ) )
       {
         System.out.println ( sql );

         return false;
       }

       sql = createUpdateSql (
         "TABLE_USER",
         new Pair [ ] {
           new Pair ( "GENDER"  , "M"  ),
           new Pair ( "BIRTHDAY", null ) },
         new Pair [ ] {
           new Pair ( "USERNAME", "joe" ) } );

       if ( !sql.equals (
         "UPDATE TABLE_USER SET GENDER='M',BIRTHDAY=NULL"
         + " WHERE USERNAME='joe'" ) )
       {
         System.out.println ( sql );

         return false;
       }

       return true;
     }

     //////////////////////////////////////////////////////////////////////
     //////////////////////////////////////////////////////////////////////

     /*********************************************************************
     * Counts the rows in a table that meet the WHERE condition.
     *
     * <p>
     * Example query:
     * <code>
     * <pre>
     * SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M'
     * </pre>
     * </code>
     * </p>
     *
     * @param  wherePair
     *
     *   If the wherePair object is null, all rows will be counted.
     *   Instance variable wherePair.value may be null.
     *   A non-null wherePair.value will converted by escapeQuotes().
     * 
     * @return
     *
     *   The count of rows selected.
     *
     * @throws NullArgumentException
     *
     *   If tableName is null.
     *********************************************************************/
     public static int  count (
       Connection  connection,
       String      tableName,
       Pair        wherePair )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       return count ( connection, tableName,
         wherePair == null
           ? ( Pair [ ] ) null : new Pair [ ] { wherePair } );
     }

     /*********************************************************************
     * Counts the rows in a table that meet the WHERE condition.
     *
     * <p>
     * Example query:
     * <code>
     * <pre>
     * SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
     * </pre>
     * </code>
     * </p>
     *
     * @param  wherePairs
     *
     *   If the wherePairs object is null, all rows will be counted.
     *   If the length of wherePairs is greater than one, the where
     *     clause will be the conjuction ("AND") of the individual
     *     where pairs.
     *   A null wherePair.value will be translated as "IS NULL".
     *   A non-null wherePair.value will converted by escapeQuotes().
     * 
     * @return
     *
     *   The count of rows selected.
     *
     * @throws NullArgumentException
     *
     *   If tableName is null.
     *********************************************************************/
     public static int  count (
       Connection  connection,
       String      tableName,
       Pair [ ]    wherePairs )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       String  querySql = createCountSql ( tableName, wherePairs );

       if ( debug )
       {
         System.out.println ( "SqlLib.count():  " + querySql );
       }

       Statement  statement = null;
       
       try
       {
         statement = connection.createStatement ( );

         ResultSet  resultSet = statement.executeQuery ( querySql );

         if ( resultSet.next ( ) )
         {
           return resultSet.getInt ( 1 );
         }
         else
         {
           return 0;
         }
       }
       finally
       {
         if ( statement != null )
         {
           statement.close ( );
         }
       }
     }

     /*********************************************************************
     * Creates an SQL SELECT COUNT(*) statement.
     *
     * Used to count the number of rows that meet the criterion.
     *
     * <p>
     * Example output:
     * <code>
     * <pre>
     * SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M'
     * </pre>
     * </code>
     * </p>
     *
     * @param  wherePair
     *
     *   If the wherePair object is null, no "where" clause will be
     *   appended, indicating that all rows in the table should be
     *   selected.
     *   Instance variable wherePair.value may be null.
     *   A non-null wherePair.value will converted by escapeQuotes().
     *
     * @throws NullArgumentException
     *
     *   If tableName is null.
     *********************************************************************/
     public static String  createCountSql (
       String      tableName,
       Pair        wherePair )
     //////////////////////////////////////////////////////////////////////
     {
       return createCountSql ( tableName,
         wherePair == null
           ? ( Pair [ ] ) null : new Pair [ ] { wherePair } );
     }

     /*********************************************************************
     * Creates an SQL SELECT COUNT(*) statement.
     *
     * Used to count the number of rows that meet the criteria.
     *
     * <p>
     * Example output:
     * <code>
     * <pre>
     * SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
     * </pre>
     * </code>
     * </p>
     *
     * @param  wherePairs
     *
     *   If the wherePairs object is null, no "where" clause will be
     *     appended, indicating that all rows in the table should be
     *     selected.
     *   If the length of wherePairs is greater than one, the where
     *     clause will be the conjuction ("AND") of the individual
     *     where pairs.
     *   A null wherePair.value will be translated as "IS NULL".
     *   A non-null wherePair.value will converted by escapeQuotes().
     *
     * @throws NullArgumentException
     *
     *   If tableName is null.
     *********************************************************************/
     public static String  createCountSql (
       String      tableName,
       Pair [ ]    wherePairs )
     //////////////////////////////////////////////////////////////////////
     {
       NullArgumentException.check ( tableName );

       StringBuffer  stringBuffer
         = new StringBuffer ( "SELECT COUNT(*) FROM " );

       stringBuffer.append ( tableName );

       appendWhereClause ( stringBuffer, wherePairs );

       return stringBuffer.toString ( );
     }

     /*********************************************************************
     * Creates an SQL DELETE statement.
     *
     * <p>
     * Example output:
     * <code>
     * <pre>
     * DELETE FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
     * </pre>
     * </code>
     * </p>
     *
     * @param  wherePairs
     *
     *   If the wherePairs object is null, no "where" clause will be
     *     appended, indicating that all rows in the table should be
     *     selected.
     *   If the length of wherePairs is greater than one, the where
     *     clause will be the conjuction ("AND") of the individual
     *     where pairs.
     *   A null wherePair.value will be translated as "IS NULL".
     *   A non-null wherePair.value will converted by escapeQuotes().
     *
     * @throws NullArgumentException
     *
     *   If tableName is null.
     *********************************************************************/
     public static String  createDeleteSql (
       String    tableName,
       Pair [ ]  wherePairs )
     //////////////////////////////////////////////////////////////////////
     {
       NullArgumentException.check ( tableName );

       StringBuffer  stringBuffer = new StringBuffer ( );

       stringBuffer.append ( "DELETE FROM " );

       stringBuffer.append ( tableName );

       appendWhereClause ( stringBuffer, wherePairs );

       return stringBuffer.toString ( );
     }

     /*********************************************************************
     * Creates an SQL INSERT statement.
     *
     * <p>
     * Example output:
     * <code>
     * <pre>
     * INSERT INTO TABLE_USER VALUES('croft','abc''123',NULL)
     * </pre>
     * </code>
     * </p>
     *
     * @param  values
     *
     *   Individual elements of the array may be null.
     *   Non-null values will be converted by escapeQuotes().
     *
     * @throws NullArgumentException
     *
     *   If tableName or values is null.
     *********************************************************************/
     public static String  createInsertSql (
       String      tableName,
       String [ ]  values )
     //////////////////////////////////////////////////////////////////////
     {
       NullArgumentException.check ( tableName );

       NullArgumentException.check ( values    );

       StringBuffer  stringBuffer = new StringBuffer ( );

       stringBuffer.append ( "INSERT INTO " );

       stringBuffer.append ( tableName );

       stringBuffer.append ( " VALUES(" );

       for ( int  i = 0; i < values.length; i++ )
       {
         if ( i > 0 )
         {
           stringBuffer.append ( "," );           
         }

         if ( values [ i ] != null )
         {
           stringBuffer.append ( "'" );

           stringBuffer.append ( escapeQuotes ( values [ i ] ) );

           stringBuffer.append ( "'" );
         }
         else
         {
           stringBuffer.append ( "NULL" );
         }
       }

       stringBuffer.append ( ")" );

       return stringBuffer.toString ( );
     }

     /*********************************************************************
     * Creates an SQL SELECT statement.
     *
     * <p>
     * Example output:
     * <code>
     * <pre>
     * SELECT FIRST_NAME,LAST_NAME FROM TABLE_USER WHERE USERNAME='croft'
     * </pre>
     * </code>
     * </p>
     *
     * @param  selectFieldNames
     *
     *   Must not be null.  Elements must not be null.
     *   Use "*" to select all fields.
     *
     * @param  wherePair
     *
     *   If the wherePair object is null, no "where" clause will be
     *   appended, indicating that all rows in the table should be
     *   selected.
     *   Instance variable wherePair.value may be null.
     *   A non-null wherePair.value will converted by escapeQuotes().
     *
     * @throws NullArgumentException
     *
     *   If tableName, selectFieldNames, or an element of setFieldNames is
     *   null.
     *********************************************************************/
     public static String  createSelectSql (
       String [ ]  selectFieldNames,
       String      tableName,
       Pair        wherePair )
     //////////////////////////////////////////////////////////////////////
     {
       return createSelectSql (
         selectFieldNames, tableName,
         wherePair == null
           ? ( Pair [ ] ) null : new Pair [ ] { wherePair } );
     }

     /*********************************************************************
     * Creates an SQL SELECT statement.