* Note:
* This class is intended to replace class {@link SqlObjectQuery}, using Java
* generics instead of the nonspecific Object type. Likewise, the
* query result class {@link SqlObjectResultSet} is replaced by class
* {@link 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 {@link 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 {@link #addSelect addSelect()}, and we provide the selected * column names by calling {@link #addField addField()}. The rest of the query, * starting with the FROM clause, is provided by calling * {@link #addClause addClause()}: * *
* SqlTypeQuery<EmployeeInfo> req = null;
*
* // Build the SQL query and result object mapping
* req = new {@link #SqlTypeQuery SqlTypeQuery}<MyObject>();
*
* req.{@link #addSelect addSelect}("TOP 100");
* req.addSelect("DISTINCT");
*
* req.{@link #addField 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.{@link #addClause 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: *
* {@link SqlTypeResultSet}<EmployeeInfo> rset = null;
*
* try
* {
* // Execute the SQL query
* rset = req.{@link #executeQuery executeQuery}(stmt);
* }
* catch (SQLException ex)
* {
* // Failure
* System.out.println("SQL query failed");
* throw ex;
* }
* finally
* {
* // Clean up
* if (rset != null)
* rset.{@link SqlTypeResultSet#close close}();
* if (req != null)
* req.{@link #close 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.{@link SqlTypeResultSet#getResult 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.{@link SqlTypeResultSet#close close}();
* req.{@link #close 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 * {@link #enableCaching enableCaching()}. Information about a specific result * class can also be cached explicitly by calling {@link #cacheInfo 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. * *
* Copyright ©2008 by David R. Tribble, all rights reserved.
* Note that this method operates regardless of any previous settings made by
* calling {@link #enableCaching enableCaching()}.
*
* @see #enableCaching enableCaching()
*
* @since 1.3, 2008-09-15
*/
public void cacheInfo()
{
m_req.cacheInfo();
}
/***************************************************************************
* 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 {@link #executeQuery 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:
*
* For reference, a standard SQL query looks like this:
*
* Note that this method should only be called once on a given query object.
*
* @throws SQLException
* Thrown if {@link #executeQuery executeQuery()},
* {@link #getQueryString getQueryString()}, or {@link #close close()}
* has been invoked.
*
* @since 1.3, 2008-09-15
*/
public void addSelect(String clause)
throws SQLException
{
m_req.addSelect(clause);
}
/***************************************************************************
* 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:
*
* Note that once this method has been called, the query cannot be modified
* any further by calling {@link #addSelect addSelect()},
* {@link #addField addField()}, or {@link #addClause addClause()}; doing so
* will cause an exception to be thrown.
*
* @return
* The fully constructed SQL query that will be executed against the
* database.
*
* @since 1.3, 2008-09-15
*/
public String getQueryString()
{
return m_req.getQueryString();
}
/***************************************************************************
* Execute the SQL query.
* The results of the query are stored in this object, and can be retrieved
* by {@link SqlTypeResultSet#getResult() SqlTypeResultSet.getResult()}.
*
*
* Note that once this method has been called, the query cannot be modified
* any further by calling {@link #addSelect addSelect()},
* {@link #addField addField()}, or {@link #addClause addClause()}; doing so
* will cause an exception to be thrown.
*
*
* Note also that the query object can be reused, calling its
* {@link #executeQuery executeQuery()} method multiple times to get multiple
* result sets.
*
* @param stmt
* A JDBC statement with which to execute the SQL query.
*
* @return
* The result set of the query if it was successful, otherwise an exception
* is thrown.
*
* @throws SQLException
* Thrown if an error occurs while executing the SQL query.
*
* @since 1.1, 2008-02-26
*/
public SqlTypeResultSet
* 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.
*/
public class SqlTypeQuery
*
*
* @param 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.
*
*
* @param 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 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
*/
public void addField(String field, String member)
throws SQLException
{
m_req.addField(field, member);
}
/***************************************************************************
* 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.
*
*
* SELECT [option...] column [alias] [, ...]
* FROM table [alias]
* [ JOIN table [alias] ON expr ]
* [ WHERE expr ]
* [ ORDER BY expr [, ...] ]
* [ GROUP BY expr [, ...] [ HAVING expr ] ]
*
*
* SELECT [clause...] column [[AS] alias] [, ...]
* FROM table [alias]
* [ JOIN table [alias] ON expr ]
* [ WHERE expr ]
* [ ORDER BY expr [, ...] ]
* [ GROUP BY expr [, ...] [ HAVING expr ] ]
*
* @throws SQLException
* Thrown if {@link #executeQuery executeQuery()},
* {@link #getQueryString getQueryString()}, or {@link #close close()}
* has been invoked.
*
* @since 1.3, 2008-09-15
*/
public void addClause(String clause)
throws SQLException
{
m_req.addClause(clause);
}
/***************************************************************************
* Retrieve the SQL query string to be executed.
*
*