* 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 SqlObjectQuery} 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()}: * *
* SqlObjectQuery req;
*
* // Build the SQL query and result object mapping
* req = new {@link #SqlObjectQuery SqlObjectQuery}(EmployeeInfo.class);
*
* 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 SqlObjectResultSet} 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 SqlObjectResultSet#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 {@link ResultSet}, one row * at a time: * *
* // Retrieve the results of the SQL query
* while (rset.next())
* {
* EmployeeInfo row;
*
* // Get the next query result row as an object
* row = (EmployeeInfo) rset.{@link SqlObjectResultSet#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 SqlObjectResultSet#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 {@link SqlObjectQuery} 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 {@link SqlObjectQuery} * instances within the same JVM. * * *
* 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.19, 2008-09-07
*/
public void cacheInfo()
{
// Ensure that there is a cache
synchronized (SqlObjectQuery.class)
{
if (s_cache == null)
s_cache = new HashMap(20);
}
// Add info about the class type to the cache
if (s_cache.get(m_type) == null)
{
synchronized (s_cache)
{
Object[] info;
info = new Object[2];
info[0] = m_ctor;
info[1] = m_methods;
s_cache.put(m_type, info);
}
}
}
/***************************************************************************
* 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.1, 2008-02-14
*/
public void addSelect(String clause)
throws SQLException
{
// Consistency check
if (m_mappings == null)
throw new SQLException("Query has been closed");
if (m_query != null)
throw new SQLException("Cannot change query after executeQuery()"
+ " has been called");
// Prepend a clause to the SQL query SELECT clause
if (m_select == null)
m_select = new StringBuffer(clause.length() + 10);
if (m_select.length() > 0)
m_select.append(' ');
m_select.append(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.7, 2008-02-29
*/
public String getQueryString()
{
// Consistency check
if (m_mappings == null)
return "";
// Build the complete SQL query
if (m_query == null)
{
StringBuffer query = new StringBuffer(150);
query.append("SELECT");
if (m_select != null)
{
query.append(' ');
query.append(m_select);
}
for (int i = 0; i < m_fields.size(); i++)
{
if (i > 0)
query.append(',');
query.append(' ');
query.append(m_fields.get(i));
}
if (m_clauses != null)
{
query.append(' ');
query.append(m_clauses);
}
m_query = query.toString();
}
return m_query;
}
/***************************************************************************
* Execute the SQL query.
* The results of the query are stored in this object, and can be retrieved
* by {@link SqlObjectResultSet#getResult() SqlObjectResultSet.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.7, 2008-02-29
*/
public SqlObjectResultSet executeQuery(Statement stmt)
throws SQLException
{
ResultSet results = null;
// Sanity check
if (m_mappings == null)
throw new SQLException("Query has been closed");
if (stmt == null)
throw new NullPointerException("Missing JDBC statement");
if (m_clauses == null)
throw new SQLException("Missing SQL query;"
+ " use addSelect(), addField(), and addClause()");
// Build the complete SQL query
if (m_query == null)
getQueryString();
// Perform the SQL query
try
{
// Execute the SQL query
results = stmt.executeQuery(m_query);
// Return an iterator for the query result set
return new SqlObjectResultSet(this, m_type, m_ctor, m_mappings,
results, stmt);
}
catch (SQLException ex)
{
// SQL query failed, clean it up
try
{
if (results != null)
results.close();
}
catch (SQLException ex2)
{ }
try
{
stmt.close();
}
catch (SQLException ex2)
{ }
// Re-throw the exception
throw ex;
}
}
/***************************************************************************
* Shut down this query object.
* Subsequent attempts to modify this object or invoke
* {@link #executeQuery executeQuery()} result in exceptions.
*
* @since 1.12, 2008-09-03
*/
public void close()
{
// Clean up
if (m_mappings != null)
{
m_fields = null;
m_methods = null;
m_mappings = null;
m_select = null;
m_clauses = null;
m_query = null;
}
}
}
// End SqlObjectQuery.java
* 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 SqlObjectQuery
{
static final String REV =
"@(#)tribble/sql/SqlObjectQuery.java $Revision: 1.20 $ $Date: 2008/09/15 17:38:43 $\n";
// ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
// Static variables
/** Cache for storing result type class info. */
private static HashMap/*
*
*
* @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.1, 2008-02-14
*/
public void addField(String field, String member)
throws SQLException
{
// Consistency check
if (m_mappings == null)
throw new SQLException("Query has been closed");
if (m_query != null)
throw new SQLException("Cannot change query after executeQuery()"
+ " has been called");
// Find the named member variable or method for the result field
if (member != null && member.length() > 0)
{
if (member.endsWith("()"))
{
member = member.substring(0, member.length() - 2);
addFieldToSetterMap(field, member);
}
else
addFieldToVariableMap(field, member);
}
else
{
// Add an unmapped field to the list
m_fields.add(field);
m_mappings.add(null);
}
}
/***************************************************************************
* Determine the mapping from an SQL SELECT field to a setter member
* method of the Java result object class.
*
* @param field
* The column name as it appears in the SELECT clause of the SQL
* query (e.g., "e.employee_id".
*
*
* @param member
* The name of the member setter method of the ResultType class that
* the field is to be assigned to when the query result object is retrieved.
*
* @throws SQLException
* Thrown if var does not name a member method of the result type
* class, or if the named method does not take a single argument of one of
* the basic types supported by SQL.
*
* @since 1.1, 2008-02-26
*/
private void addFieldToSetterMap(String field, String member)
throws SQLException
{
try
{
Method meth;
Field fld;
int mods;
Class type;
FieldTypeMap fact;
// Find a suitable setter method for the result field
meth = (Method) m_methods.get(member);
if (meth == null)
throw new SQLException(
"No unique matching setter method found: "
+ member + "()");
// Determine the type of the member variable
type = meth.getParameterTypes()[0];
fact = FieldTypeMap.getInstance(type);
if (fact == null)
throw new SQLException(
"Setter method parameter is not an assignable type: "
+ member + "(): " + type.getName());
// Add the mapped field to the list
m_fields.add(field);
m_mappings.add(fact.create(meth));
}
catch (Exception ex)
{
// Inaccessible or nonexistent member variable
throw new SQLException(
"Inaccessible or nonexistent class method: " + member + "()");
}
}
/***************************************************************************
* Determine the mapping from an SQL SELECT field to a member
* variable of the Java result object class.
*
* @param field
* The column name as it appears in the SELECT clause of the SQL
* query (e.g., "e.employee_id".
*
*
* @param member
* The name of the member variable of the ResultType class that the
* field is to be assigned to when the query result object is retrieved.
*
* @throws SQLException
* Thrown if var does not name a member variable of the result type
* class, or if the named member is not of one of the basic types supported
* by SQL.
*
* @since 1.1, 2008-02-26
*/
private void addFieldToVariableMap(String field, String member)
throws SQLException
{
try
{
Field var;
int mods;
FieldTypeMap fact;
// Find the class variable or setter method for the variable
var = m_type.getDeclaredField(member);
// Verify that the member variable is assignable
mods = var.getModifiers();
if ((mods & Modifier.FINAL) != 0)
throw new SQLException("Class variable is final: "
+ member);
if ((mods & Modifier.STATIC) != 0)
throw new SQLException("Class variable is static: "
+ member);
// Determine the type of the member variable
fact = FieldTypeMap.getInstance(var.getType());
if (fact == null)
throw new SQLException(
"Class variable is not an assignable type: " + member + ": "
+ var.getType().getName());
// Add the mapped field to the list
m_fields.add(field);
m_mappings.add(fact.create(var));
}
catch (Exception ex)
{
// Inaccessible or nonexistent member variable
throw new SQLException(
"Inaccessible or nonexistent class variable: " + 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.1, 2008-02-14
*/
public void addClause(String clause)
throws SQLException
{
// Consistency check
if (m_mappings == null)
throw new SQLException("Query has been closed");
if (m_query != null)
throw new SQLException("Cannot change query after executeQuery()"
+ " has been called");
// Append the clause to the SQL query
if (m_clauses == null)
m_clauses = new StringBuffer(80);
if (m_clauses.length() > 0)
m_clauses.append(' ');
m_clauses.append(clause);
}
/***************************************************************************
* Retrieve the SQL query string to be executed.
*
*