sqllib.java

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

     *
     *   See method createSelectSql() for description.
     *
     * @throws NullArgumentException
     *
     *   If tableName, selectFieldNames, or an element of setFieldNames is
     *   null.
     * 
     * @return
     *
     *   The field values for the first row selected or null if no row
     *   was selected.
     *********************************************************************/
     public static String [ ]  select (
       Connection  connection,
       String [ ]  selectFieldNames,
       String      tableName,
       Pair        wherePair )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       return select (
         connection,
         selectFieldNames,
         tableName,
         wherePair == null
           ? ( Pair [ ] ) null : new Pair [ ] { wherePair } );
     }

     /*********************************************************************
     * 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  wherePairs
     *
     *   See method createSelectSql() for description.
     *
     * @throws NullArgumentException
     *
     *   If tableName, selectFieldNames, or an element of setFieldNames is
     *   null.
     * 
     * @return
     *
     *   The field values for the first row selected or null if no row
     *   was selected.
     *********************************************************************/
     public static String [ ]  select (
       Connection  connection,
       String [ ]  selectFieldNames,
       String      tableName,
       Pair [ ]    wherePairs )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       return select ( connection,
         selectFieldNames, tableName, wherePairs, null, 0 );
     }

     /*********************************************************************
     * Returns the results of a SELECT query as a String array.
     *
     * <p>
     * This method lets you return multiple column values from a single row
     * or multiple row values from a single column.  The results are then
     * returned as a 1-dimensional String array.  If the input variable
     * <i>maxRows</i> is zero, just the column values from the first row
     * selected are returned.  If <i>maxRows</i> is greater than zero, up
     * to that number of row values from a single column are returned.
     * </p>
     *
     * @param  selectFieldNames
     *
     *   Must not be null.  Elements must not be null.
     *   Use new String[]{"*"} to select all fields.
     *
     * @param  wherePairs
     *
     *   See method createSelectSql() for description.
     *
     * @param  orderBy
     *
     *   The column name to use for ordering.
     *
     * @param  maxRows
     *
     *   If 0, multiple column values from a single row will be returned.
     *   If greater than 0, multiple row values from a single column
     *   will be returned, up to maxRows in length.
     *
     * @throws NullArgumentException
     *
     *   If tableName, selectFieldNames, or an element of setFieldNames is
     *   null.
     * 
     * @return
     *
     *   The field values for the first row selected or null if no row
     *   was selected.
     *********************************************************************/
     public static String [ ]  select (
       Connection  connection,
       String [ ]  selectFieldNames,
       String      tableName,
       Pair [ ]    wherePairs,
       String      orderBy,
       int         maxRows )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       if ( maxRows < 0 )
       {
         throw new IllegalArgumentException ( "maxRows < 0" );
       }

       if ( maxRows > 0 && selectFieldNames.length > 1 )
       {
         throw new IllegalArgumentException (
           "selectFieldNames.length > 1 when maxRows > 0" );
       }

       String  querySql = createSelectSql (
         selectFieldNames, tableName, wherePairs, orderBy );

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

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

         if ( maxRows > 0 )
         {
           statement.setFetchSize ( maxRows );

           statement.setMaxRows   ( maxRows );
         }
         else
         {
           statement.setFetchSize ( 1 );

           statement.setMaxRows   ( 1 );
         }

         ResultSet  resultSet = statement.executeQuery ( querySql );

         if ( !resultSet.next ( ) )
         {
           return null;
         }

         if ( maxRows > 0 )
         {
           Vector  stringVector = new Vector ( );

           stringVector.addElement ( resultSet.getString ( 1 ) );

           while ( resultSet.next ( ) )
           {
             stringVector.addElement ( resultSet.getString ( 1 ) );
           }

           String [ ]  values = new String [ stringVector.size ( ) ];

           stringVector.copyInto ( values );

           return values;
         }
         else
         {
           ResultSetMetaData  resultSetMetaData
             = resultSet.getMetaData ( );

           String [ ]  values
             = new String [ resultSetMetaData.getColumnCount ( ) ];

           for ( int  i = 0; i < values.length; i++ )
           {
             values [ i ] = resultSet.getString ( i + 1 );
           }

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

     /*********************************************************************
     * Retrieves the String value at a given row and column.
     *
     * <p>
     * Only returns a single value for the first row selected.
     * </p>
     *
     * @param  selectFieldName
     *
     *   Must not be null.  If selectFieldName is "*", this method will
     *   return the value associated with the first field name only.
     *
     * @param  wherePair
     *
     *   See method createSelectSql() for description.
     *
     * @throws NullArgumentException
     *
     *   If tableName or selectFieldName is null.
     * 
     * @return
     *
     *   The column value for the first row selected or null if no
     *   row was selected.
     *********************************************************************/
     public static String  select (
       Connection  connection,
       String      selectFieldName,
       String      tableName,
       Pair        wherePair )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       return select (
         connection,
         selectFieldName,
         tableName,
         wherePair == null
           ? ( Pair [ ] ) null : new Pair [ ] { wherePair } );
     }

     /*********************************************************************
     * Retrieves the String value at a given row and column.
     *
     * <p>
     * Only returns a single value for the first row selected.
     * </p>
     *
     * @param  selectFieldName
     *
     *   Must not be null.  If selectFieldName is "*", this method will
     *   return the value associated with the first field name only.
     *
     * @param  wherePairs
     *
     *   See method createSelectSql() for description.
     *
     * @throws NullArgumentException
     *
     *   If tableName or selectFieldName is null.
     * 
     * @return
     *
     *   The column value for the first row selected or null if no
     *   row was selected.
     *********************************************************************/
     public static String  select (
       Connection  connection,
       String      selectFieldName,
       String      tableName,
       Pair [ ]    wherePairs )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       String [ ]  values = select ( connection,
         new String [ ] { selectFieldName }, tableName, wherePairs );

       return values == null ? null : values [ 0 ];
     }

     public static boolean  tableExists (
       Connection  connection,
       String      tableName )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       DatabaseMetaData  databaseMetaData = connection.getMetaData ( );

       ResultSet  resultSet = databaseMetaData.getTables (
         ( String ) null,       // catalog
         ( String ) null,       // schemaPattern
         tableName,             // tableNamePattern
         ( String [ ] ) null ); // types

       return resultSet.next ( );
     }

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

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

       return executeUpdate ( connection, updateSql );
     }

     /*********************************************************************
     * Updates rows in a table.
     *
     * @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  wherePair
     *
     *   See method createUpdateSql() for description.
     *
     * @throws NullArgumentException
     *
     *   If tableName, setPairs, or an element of setPairs is null.
     *
     * @return
     *
     *   The number of rows updated.
     *********************************************************************/
     public static int  update (
       Connection  connection,
       String      tableName,
       Pair [ ]    setPairs,
       Pair        wherePair )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       return update (
         connection, tableName, setPairs, new Pair [ ] { wherePair } );
     }

     /*********************************************************************
     * Updates rows in a table.
     *
     * @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
     *
     *   See method createUpdateSql() for description.
     *
     * @throws NullArgumentException
     *
     *   If tableName, setPairs, or an element of setPairs is null.
     *
     * @return
     *
     *   The number of rows updated.
     *********************************************************************/
     public static int  update (
       Connection  connection,
       String      tableName,
       Pair [ ]    setPairs,
       Pair [ ]    wherePairs )
       throws SQLException
     //////////////////////////////////////////////////////////////////////
     {
       String  updateSql
         = createUpdateSql ( tableName, setPairs, wherePairs );

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

       return executeUpdate ( connection, updateSql );
     }

     //////////////////////////////////////////////////////////////////////
     // private methods
     //////////////////////////////////////////////////////////////////////

     private static void  appendWhereClause (
       StringBuffer  stringBuffer,
       Pair          wherePair )
     //////////////////////////////////////////////////////////////////////
     {
       appendWhereClause ( stringBuffer,
         wherePair == null
           ? ( Pair [ ] ) null : new Pair [ ] { wherePair } );
     }

     private static void  appendWhereClause (
       StringBuffer  stringBuffer,
       Pair [ ]      wherePairs )
     //////////////////////////////////////////////////////////////////////
     {
       if ( wherePairs != null )
       {
         stringBuffer.append ( " WHERE " );

         for ( int  i = 0; i < wherePairs.length; i++ )
         {
           Pair  wherePair = wherePairs [ i ];

           stringBuffer.append ( wherePair.name );

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

             stringBuffer.append ( '\'' );

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

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

           if ( i < wherePairs.length - 1 )
           {
             stringBuffer.append ( " AND " );
           }
         }
       }
     }

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

     /*********************************************************************
     * Static method library classes do not require instantiation.
     *********************************************************************/
     private  SqlLib ( ) { }

     //////////////////////////////////////////////////////////////////////
     //////////////////////////////////////////////////////////////////////
     }