sqllib.java

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

     *
     * <p>
     * Example output:
     * <code>
     * <pre>
     * SELECT NAME,SALARY FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
     * </pre>
     * </code>
     * </p>
     *
     * @param  selectFieldNames
     *
     *   Must not be null.  Elements must not be null.
     *   Use "*" to select all fields.
     *
     * @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, selectFieldNames, or an element of setFieldNames is
     *   null.
     *********************************************************************/
     public static String  createSelectSql (
       String [ ]  selectFieldNames,
       String      tableName,
       Pair [ ]    wherePairs )
     //////////////////////////////////////////////////////////////////////
     {
       return createSelectSql (
         selectFieldNames, tableName, wherePairs, null );
     }

     /*********************************************************************
     * Creates an SQL SELECT statement.
     *
     * <p>
     * Example output:
     * <code>
     * <pre>
     * SELECT NAME,SALARY FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
     * </pre>
     * </code>
     * </p>
     *
     * @param  selectFieldNames
     *
     *   Must not be null.  Elements must not be null.
     *   Use "*" to select all fields.
     *
     * @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().
     *
     * @param  orderBy
     *
     *   The column name to use for ordering.
     *
     * @throws NullArgumentException
     *
     *   If tableName, selectFieldNames, or an element of setFieldNames is
     *   null.
     *********************************************************************/
     public static String  createSelectSql (
       String [ ]  selectFieldNames,
       String      tableName,
       Pair [ ]    wherePairs,
       String      orderBy )
     //////////////////////////////////////////////////////////////////////
     {
       NullArgumentException.check ( selectFieldNames  );

       NullArgumentException.check ( tableName );

       StringBuffer  stringBuffer = new StringBuffer ( );

       stringBuffer.append ( "SELECT " );

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

         NullArgumentException.check (
           selectFieldNames [ i ], "selectFieldNames[" + i + "] is null" );

         stringBuffer.append ( selectFieldNames [ i ] );
       }

       stringBuffer.append ( " FROM " );

       stringBuffer.append ( tableName );

       appendWhereClause ( stringBuffer, wherePairs );

       if ( orderBy != null )
       {
         stringBuffer.append ( " ORDER BY " );

         stringBuffer.append ( orderBy );
       }

       return stringBuffer.toString ( );
     }

     /*********************************************************************
     * Creates an SQL UPDATE statement.
     *
     * <p>
     * Example output:
     * <code>
     * <pre>
     * UPDATE TABLE_USER SET GENDER='M',BIRTHDAY=NULL WHERE USERNAME='joe'
     * </pre>
     * </code>
     * </p>
     *
     * @param  setPairs
     *
     *   Must not be null.  Array elements must not be null.
     *   An element setPair.value may be null.
     *   A non-null setPair.value will converted by escapeQuotes().
     *
     * @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, setPairs, or an element of setPairs is null.
     *********************************************************************/
     public static String  createUpdateSql (
       String    tableName,
       Pair [ ]  setPairs,
       Pair [ ]  wherePairs )
     //////////////////////////////////////////////////////////////////////
     {
       NullArgumentException.check ( tableName );

       NullArgumentException.check ( setPairs  );

       StringBuffer  stringBuffer = new StringBuffer ( );

       stringBuffer.append ( "UPDATE " );

       stringBuffer.append ( tableName );

       stringBuffer.append ( " SET " );

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

         Pair  setPair = setPairs [ i ];

         NullArgumentException.check (
           setPair, "setPairs[" + i + "] is null" );

         stringBuffer.append ( setPair.name );

         stringBuffer.append ( '=' );

         if ( setPair.value != null )
         {
           stringBuffer.append ( '\'' );

           stringBuffer.append ( escapeQuotes ( setPair.value ) );

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

       appendWhereClause ( stringBuffer, wherePairs );

       return stringBuffer.toString ( );
     }

     /*********************************************************************
     * Removes rows from a table.
     *
     * @param  wherePairs
     *
     *   See method createDeleteSql() for description.
     *
     * @return
     *
     *   The number of rows deleted.
     *********************************************************************/
     public static int  delete (
       Connection  connection,
       String      tableName,
       Pair [ ]    wherePairs )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       String  updateSql = createDeleteSql ( tableName, wherePairs );

       if ( debug )
       {
         System.out.println ( "SqlLib.delete():  " + updateSql );
       }

       return executeUpdate ( connection, updateSql );
     }

     public static int  dropTable (
       Connection  connection,
       String      tableName )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       String  updateSql = "DROP TABLE " + tableName;

       if ( debug )
       {
         System.out.println ( "SqlLib.dropTable():  " + updateSql );
       }

       return executeUpdate ( connection, updateSql );
     }

     /*********************************************************************
     * Doubles all single and double quotes in the original String.
     *
     * <p>
     * Used to prepare a String to be passed as an SQL statement variable.
     * For example, notice how the single quote is doubled inside the
     * the password "abc'123" which contains an apostrophe:
     * <code>
     * <pre>
     * INSERT INTO TABLE_USER VALUES ('croft', 'abc''123')
     * </pre>
     * </code>
     * </p>
     *********************************************************************/
     public static String  escapeQuotes ( String  originalString )
     //////////////////////////////////////////////////////////////////////
     {
       NullArgumentException.check ( originalString );

       StringBuffer  stringBuffer = new StringBuffer ( );

       int  originalStringLength = originalString.length ( );

       for ( int  i = 0; i < originalStringLength; i++ )
       {
         char  c = originalString.charAt ( i );

         stringBuffer.append ( c );

         if ( c == '\'' )
         {
           stringBuffer.append ( '\'' );
         }
         else if ( c == '"' )
         {
           stringBuffer.append ( '"' );
         }
       }

       return stringBuffer.toString ( );
     }

     /*********************************************************************
     * Executes an SQL update statement.
     *
     * <p>
     * This convenience method creates a new Statement instance,
     * executes the update, and then ensures that the Statement is closed
     * before return or abnormal exit.
     * </p>
     *
     * @return
     *
     *   The number of rows created.
     *********************************************************************/
     public static int  executeUpdate (
       Connection  connection,
       String      updateSql )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       Statement  statement = null;
       
       try
       {
         statement = connection.createStatement ( );

         return statement.executeUpdate ( updateSql );
       }
       finally
       {
         if ( statement != null )
         {
           statement.close ( );
         }
       }
     }

     /*********************************************************************
     * Adds a row to a table.
     *
     * @param  values
     *
     *   Each value will be converted by escapeQuotes() before being used.
     * 
     * @return
     *
     *   The number of rows created.
     *********************************************************************/
     public static int  insert (
       Connection  connection,
       String      tableName,
       String [ ]  values )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       String  updateSql = createInsertSql ( tableName, values );

       if ( debug )
       {
         System.out.println ( "SqlLib.insert():  " + updateSql );
       }

       return executeUpdate ( connection, updateSql );
     }

     /*********************************************************************
     * Returns the maximum column value.
     *
     * <p>
     * Example query:
     * <code>
     * <pre>
     * SELECT MAX(SALARY) FROM TABLE_EMPLOYEE WHERE DEPARTMENT_ID='3'
     * </pre>
     * </code>
     * </p>
     *
     * @param  columnName
     *
     *   Must not be null.
     *
     * @param  wherePair
     *
     *   If the wherePair object is null, all rows will be included.
     *   Instance variable wherePair.value may be null.
     *   A non-null wherePair.value will converted by escapeQuotes().
     * 
     * @return
     *
     *   The maximum column value as a BigDecimal or null if there are no
     *   non-null values selected to compare.
     *
     * @throws NullArgumentException
     *
     *   If columnName or tableName is null.
     *********************************************************************/
     public static BigDecimal  max (
       Connection  connection,
       String      columnName,
       String      tableName,
       Pair        wherePair )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       NullArgumentException.check ( columnName );

       NullArgumentException.check ( tableName );

       StringBuffer  stringBuffer = new StringBuffer ( "SELECT MAX(" );

       stringBuffer.append ( columnName );

       stringBuffer.append ( ") FROM " );

       stringBuffer.append ( tableName );

       appendWhereClause ( stringBuffer, wherePair );

       String  querySql = stringBuffer.toString ( );

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

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

         ResultSet  resultSet = statement.executeQuery ( querySql );

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

     /*********************************************************************
     * Selects the first row that meets the where condition.
     *
     * <p>
     * Only returns the values for the first row selected.
     * </p>
     *
     * @param  selectFieldNames
     *
     *   Must not be null.  Elements must not be null.
     *   Use new String[]{"*"} to select all fields.
     *
     * @param  wherePair