tribble.sql
Class SqlTypeQuery<ResultType>

java.lang.Object
  extended by tribble.sql.SqlTypeQuery<ResultType>

public class SqlTypeQuery<ResultType>
extends java.lang.Object

SQL query builder. Uses a JDBC connection to query an SQL database, then stores the results of the query into an object of a class type (ResultType) provided by the client.

Note: This class is intended to replace class SqlObjectQuery, using Java generics instead of the nonspecific Object type. Likewise, the query result class SqlObjectResultSet is replaced by class SqlTypeResultSet.

Usage

Our client has a JDBC connection to a database:

    Connection  conn;
    Statement   stmt;

    // Establish the JDBC connection
    conn = driverManager.getConnection(...);
    stmt = conn.createStatement(...);

The following SQL query is to be performed against the database:

    SELECT TOP 100 DISTINCT
      e.first_name,
      e.last_name,
      e.employee_id,
      w.salary_per_year
    FROM employees e
    JOIN employee_wages w
      ON w.employee_id = e.employee_id
    WHERE e.last_name BETWEEN 'R' AND 'S'
    ORDER BY e.last_name

Each row of the result of this query is to be assigned to an object of type EmployeeInfo:

    public class EmployeeInfo
    {
        public EmployeeInfo() { ... }

        public void setId(int id) { ... }
        public void setFirstName(String name) { ... }
        public void setLastName(String name) { ... }
        public void setSalary(double amt) { ... }
        ...
    }

Note that the EmployeeInfo result type must be public, and have public setter methods and/or public member variables.

We construct a SqlTypeQuery object and provide it with the connection and the query. Note that the SELECT keyword of the query is omitted; we provide any options follwing the implied SELECT keyword by calling addSelect(), and we provide the selected column names by calling addField(). The rest of the query, starting with the FROM clause, is provided by calling addClause():

    SqlTypeQuery<EmployeeInfo>  req = null;

    // Build the SQL query and result object mapping
    req = new SqlTypeQuery<MyObject>();

    req.addSelect("TOP 100");
    req.addSelect("DISTINCT");

    req.addField("e.employee_id", "setId()");
    req.addField("e.first_name", "setFirstName()");
    req.addField("e.last_name", "setLastName()");
    req.addField("w.salary_per_year", "setSalary()");

    req.addClause("FROM employees e");
    req.addClause("JOIN employee_wages w ON w.employee_id = e.employee_id");
    req.addClause("WHERE e.last_name BETWEEN 'R' AND 'S'");
    req.addClause("ORDER BY e.last_name");

    // e.employee_id     will be set by EmployeeInfo.setId()
    // e.first_name      will be set by EmployeeInfo.setFirstName()
    // e.last_name       will be set by EmployeeInfo.setLastName()
    // w.salary_per_year will be set by EmployeeInfo.setSalary()

Each invocation of addField() assigns a field (column) of the SQL query result to either a class setter method or a class member variable in the result object type. The first argument specifies the text of the field to be included in the SQL query string. The second argument specifies the name of either a setter method (by ending with "()") or the name of a member variable. In either case, the named member must be public. Java reflection is used to locate the specified class member. (Note that setting a member variable directly is generally considered bad object-oriented programming style, the use of a setter method being considered safer, but such capability is included here for completeness.)

If a query field is needed for the query string itself but the value is not used in the result object (such as a field used for a GROUP BY clause), null is passed as the second argument. The field will be included in the SQL query string, but the resulting value for it will be ignored.

Note that the order of the invocations to addField() is important; the first call assigns the result of column 1, the second call assigns the result of column 2, and so forth. This means that calls to addSelect() must not specify additional query fields; otherwise, the resulting SQL query result member assignments will not behave as expected.

Once the query has been set up, it is then executed:

    SqlTypeResultSet<EmployeeInfo>    rset = null;

    try
    {
        // Execute the SQL query
        rset = req.executeQuery(stmt);
    }
    catch (SQLException ex)
    {
        // Failure
        System.out.println("SQL query failed");
        throw ex;
    }
    finally
    {
        // Clean up
        if (rset != null)
            rset.close();
        if (req != null)
            req.close();
        if (stmt != null)
            stmt.close();
    }

The results of the query (zero or more rows) can then be retrieved in a way similar to the way results are obtained from a JDBC ResultSet, one row at a time:

    // Retrieve the results of the SQL query
    while (rset.next())
    {
        EmployeeInfo    row;

        // Get the next result row as an object
        row = rset.getResult();

        // e.employee_id     is set by row.setId()
        // e.first_name      is set by row.setFirstName()
        // e.last_name       is set by row.setLastName()
        // w.salary_per_year is set by row.setSalary()

        System.out.println("Employee: " + row.getFirstName() + " "
            + row.getLastName());
        System.out.println("  ID #" + row.getId() + ":  $" + row.getSalary());
    }

    // Clean up
    rset.close();
    req.close();
    stmt.close();

The call to getResult() instantiates a new result object, in this case of type EmployeeInfo, and populates its members from the query result according to the order in which the addField() calls were made above. The result is a single object containing members whose values are retrieved from columns of a row of the query result.

For increased efficiency, caching of the result class information can be enabled. This causes information about the result class type to be stored in a local cache each time an SqlTypeQuery object is instantiated. This can speed up processing if the same result class types are used multiple times. Caching is disabled by default, but can be enabled by calling enableCaching(). Information about a specific result class can also be cached explicitly by calling cacheInfo(). Note that the cache is static, so it is shared by all SqlTypeQuery instances within the same JVM.

Note: This requires Java 1.5 or later.

Source code:
Available at: http://david.tribble.com/src/java/tribble/sql/SqlTypeQuery.java
Documentation:
Available at: http://david.tribble.com/docs/tribble/sql/SqlTypeQuery.html

Since:
2008-02-26
Version:
@(#)$Revision: 1.3 $ $Date: 2008/09/15 18:18:51 $
Author:
David R. Tribble (david@tribble.com)

Copyright ©2008 by David R. Tribble, all rights reserved.
Permission is granted to any person or entity except those designated by by the United States Department of State as a terrorist, or terrorist government or agency, to use and distribute this source code provided that the original copyright notice remains present and unaltered.


Field Summary
(package private) static java.lang.String REV
           
 
Constructor Summary
SqlTypeQuery(java.lang.Class resultType)
          Constructor.
 
Method Summary
 void addClause(java.lang.String clause)
          Add a trailing clause to the SQL query.
 void addField(java.lang.String field, java.lang.String member)
          Add a field expression to the SELECT clause of the SQL query.
 void addSelect(java.lang.String clause)
          Add a leading clause to the SELECT clause of the SQL query.
 void cacheInfo()
          Save information about the result type class in a local cache.
 void close()
          Shut down this query object.
static boolean enableCaching(boolean flag)
          Enable or disable caching of result type class information.
 SqlTypeResultSet<ResultType> executeQuery(java.sql.Statement stmt)
          Execute the SQL query.
 java.lang.String getQueryString()
          Retrieve the SQL query string to be executed.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

REV

static final java.lang.String REV
See Also:
Constant Field Values
Constructor Detail

SqlTypeQuery

public SqlTypeQuery(java.lang.Class resultType)
             throws java.sql.SQLException
Constructor.

Parameters:
resultType - Class type of the result of the SQL query. Each row of the result set will be converted into an object of this type.
Throws:
java.sql.SQLException - Thrown if the result class type is not public or is abstract, or does not have a public default (no-args) constructor.
Since:
1.1, 2008-02-26
Method Detail

enableCaching

public static boolean enableCaching(boolean flag)
Enable or disable caching of result type class information. This can speed up processing if the same result type classes are re-used multiple times.

Parameters:
flag - If true, information about result type classes is cached, otherwise caching is disabled. The cache is static, so it is shared by all SqlTypeQuery instances within the same JVM.
Returns:
The previous cache setting.
Since:
1.3, 2008-09-15
See Also:
cacheInfo()

cacheInfo

public void cacheInfo()
Save information about the result type class in a local cache. This can speed up processing if the same result type classes are re-used multiple times.

Note that this method operates regardless of any previous settings made by calling enableCaching().

Since:
1.3, 2008-09-15
See Also:
enableCaching()

addField

public void addField(java.lang.String field,
                     java.lang.String member)
              throws java.sql.SQLException
Add a field expression to the SELECT clause of the SQL query.

A specified SQL field expression (e.g., "employees.employee_id as eid") is included as part of the SQL query to be executed by executeQuery(). It is paired with a specific member of the ResultType class. The specified member is either the name of a member variable (e.g., "employeeID") or the name of a setter method (e.g., "setEmployeeID()"). In either case, the type of the SQL field must be assignable to the member variable or be passable as the single argument of the setter method.

The Java types supported are:

Parameters:
field - The column name as it appears in the SELECT clause of the SQL query (e.g., "e.employee_id"). The name can also include an alias clause (e.g., "e.employee_id id") to be used in the rest of the SQL query.

member - The name of the member variable or setter method of the ResultType class that the field is to be assigned to when the query result object is retrieved. If the name ends with "()", it specifies a setter method to be called to assign the result value; otherwise it specifies a member variable to be assigned. The name can be null, in which case the column name will not be assigned to any member of class ResultType (presumably this specifies a column or expression that is used in a GROUP BY clause.)
Throws:
java.sql.SQLException - Thrown if var does not name a member variable or method of the result type class, or if the named member is not of one of the supported types.
Since:
1.3, 2008-09-15

addSelect

public void addSelect(java.lang.String clause)
               throws java.sql.SQLException
Add a leading clause to the SELECT clause of the SQL query. This applies to the operands of the SELECT clause immediately preceding the selected field (column) names.

For reference, a standard SQL query looks like this:

    SELECT [option...] column [alias] [, ...]
    FROM table [alias]
    [ JOIN table [alias] ON expr ]
    [ WHERE expr ]
    [ ORDER BY expr [, ...] ]
    [ GROUP BY expr [, ...] [ HAVING expr ] ]

Note that this method should only be called once on a given query object.

Throws:
java.sql.SQLException - Thrown if executeQuery(), getQueryString(), or close() has been invoked.
Since:
1.3, 2008-09-15

addClause

public void addClause(java.lang.String clause)
               throws java.sql.SQLException
Add a trailing clause to the SQL query. This applies to all clauses starting with the FROM clause. Note that the clauses must be added in the correct order by the caller in order to construct a syntactically correct query.

For reference, a standard SQL query looks like this:

    SELECT [clause...] column [[AS] alias] [, ...]
    FROM table [alias]
    [ JOIN table [alias] ON expr ]
    [ WHERE expr ]
    [ ORDER BY expr [, ...] ]
    [ GROUP BY expr [, ...] [ HAVING expr ] ]

Throws:
java.sql.SQLException - Thrown if executeQuery(), getQueryString(), or close() has been invoked.
Since:
1.3, 2008-09-15

getQueryString

public java.lang.String getQueryString()
Retrieve the SQL query string to be executed.

Note that once this method has been called, the query cannot be modified any further by calling addSelect(), addField(), or addClause(); doing so will cause an exception to be thrown.

Returns:
The fully constructed SQL query that will be executed against the database.
Since:
1.3, 2008-09-15

executeQuery

public SqlTypeResultSet<ResultType> executeQuery(java.sql.Statement stmt)
                                          throws java.sql.SQLException
Execute the SQL query. The results of the query are stored in this object, and can be retrieved by SqlTypeResultSet.getResult().

Note that once this method has been called, the query cannot be modified any further by calling addSelect(), addField(), or addClause(); doing so will cause an exception to be thrown.

Note also that the query object can be reused, calling its executeQuery() method multiple times to get multiple result sets.

Parameters:
stmt - A JDBC statement with which to execute the SQL query.
Returns:
The result set of the query if it was successful, otherwise an exception is thrown.
Throws:
java.sql.SQLException - Thrown if an error occurs while executing the SQL query.
Since:
1.1, 2008-02-26

close

public void close()
Shut down this query object. Subsequent attempts to modify this object or invoke executeQuery() result in exceptions.

Since:
1.3, 2008-09-15