tribble.sql.SqlTypeQuery<ResultType>
|
|||||||||
| PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
| SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD | ||||||||
java.lang.Object
public class SqlTypeQuery<ResultType>
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.
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 |
|---|
static final java.lang.String REV
| Constructor Detail |
|---|
public SqlTypeQuery(java.lang.Class resultType)
throws java.sql.SQLException
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.
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.| Method Detail |
|---|
public static boolean enableCaching(boolean flag)
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.
cacheInfo()public void cacheInfo()
Note that this method operates regardless of any previous settings made by
calling enableCaching().
enableCaching()
public void addField(java.lang.String field,
java.lang.String member)
throws java.sql.SQLException
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:
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.)
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.
public void addSelect(java.lang.String clause)
throws java.sql.SQLException
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.
java.sql.SQLException - Thrown if executeQuery(),
getQueryString(), or close()
has been invoked.
public void addClause(java.lang.String clause)
throws java.sql.SQLException
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 ] ]
java.sql.SQLException - Thrown if executeQuery(),
getQueryString(), or close()
has been invoked.public java.lang.String getQueryString()
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.
public SqlTypeResultSet<ResultType> executeQuery(java.sql.Statement stmt)
throws java.sql.SQLException
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.
stmt - A JDBC statement with which to execute the SQL query.
java.sql.SQLException - Thrown if an error occurs while executing the SQL query.public void close()
executeQuery() result in exceptions.
|
|||||||||
| PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
| SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD | ||||||||