The DbQueryManager class has become a convenience class that makes it very easy to control the queries that handle the retrieval, storage, update and removal of data in a database. All queries are executed in a connection of the Datasource that is provided to the constructor of the DbQueryManager.
Convenience methods
A collection of convenience methods have been provided to quickly execute queries in a variety of manners without having to worry about the logic behind them and without having to remember to close the queries at the appropriate moment. These methods optionally interact with the DbPreparedStatementHandler and DbResultSetHandler classes to make it possible to fully customize the executed queries. The following categories of worry-free methods now exist:
- execute an update query directly, eg.:
executeUpdate(Query),
- execute a customizable update query, eg.:
executeUpdate(Query, DbPreparedStatementHandler),
- execute a customizable select query, eg.:
executeQuery(Select, DbPreparedStatementHandler),
- check the result rows of a customizable select query, eg.:
executeHasResultRows(Select, DbPreparedStatementHandler),
- obtain the first value of a customizable select query, eg.:
executeGetFirstString(Select, DbPreparedStatementHandler),
- fetch the first row of a customizable select query, eg.:
executeFetchFirst(Select, DbRowProcessor, DbPreparedStatementHandler),
- fetch the first bean of a customizable select query, eg.:
executeFetchFirstBean(Select, Class, DbPreparedStatementHandler),
- fetch all rows of a customizable select query, eg.:
executeFetchAll(Select, DbRowProcessor, DbPreparedStatementHandler),
- fetch all beans of a customizable select query, eg.:
{{executeFetchAllBeans(Select, Class, DbPreparedStatementHandler),
Lower-level methods are also available for the sake of repetitive code-reduction. To obtain a prepared statement that corresponds to a specific SQL command, use the getPreparedStatement(Query) methods and to execute regular statements directly, use the executeQuery(Query) method.
Finally, since DbStatement and DbPreparedStatement instances preserve a reference to their resultset, it is easy to iterate over the rows of a resultset with the methods fetch(ResultSet, DbRowProcessor) or fetchAll(ResultSet, DbRowProcessor).
You can look at the javadocs of the DbQueryManager
class to get detailed information about all the available methods and their usage.
[top]
Customizing prepared statements
With a child class of DbPreparedStatementHandler, you are able to set the parameters of a DbPreparedStatement before the actual execution of any logic by overriding the setParameters(DbPreparedStatement) method.
For example:
DbQueryManager manager = new DbQueryManager(datasource);
Insert insert = new Insert(datasource);
insert.into("person").fieldParameter("name");
final String name = "me";
int count = manager.executeUpdate(insert, new DbPreparedStatementHandler() {
public void setParameters(DbPreparedStatement statement)
{
statement
.setString("name", name);
}
});
If you need to customize the entire query execution, you can override the performUpdate(DbPreparedStatement) and performQuery(DbPreparedStatement) methods. Note that these methods are actually responsible for calling the setParameters(DbPreparedStatement) method, so if you override them you either have to call this method yourself or include the code in the overridden method.
The DbPreparedStatementHandler class has both a default constructor and one that can take a data object. This can be handy when using it as an anonymous inner class, when you need to use variables inside the inner class that are cumbersome to change to final in the enclosing class.
[top]
Customizing results
With a child class of DbResultSetHandler, you are able to perform custom logic with the resultset of a query by overriding the concludeResults(DbResultSet) method and returning an object.
For example:
DbQueryManager manager = new DbQueryManager(datasource);
Select select = new Select(datasource);
select
.field("first")
.field("last")
.from("person");
String result = (String)manager.executeQuery(select, new DbResultSetHandler() {
public Object concludeResults(DbResultSet resultset)
throws SQLException
{
return resultset.getString("first")+" "+resultset.getString("last");
}
});
The result string will contain the full name of the person that was returned by the SQL query.
[top]
