DbQuery
DbQuery is a wrapper and enhancement for prepared and regular JDBC statements. In the base scenario, it can be used anywhere where statement would be used. Nevertheless, DbQuery provides some more convenient additional features.
Basic usage
Basic way how DbQuery can be created is by providing database connection. Once created, it can be used simillary statement is used:
DbQuery query = new DbQuery(connection, "create table ..."); query.executeUpdate(); query.close(); // alternativly: query.executeUpdateAndClose(); ... query = new DbQuery(connection, "select * from ...."); query.setString(1, "param1"); ResultSet rs = query.execute(); ... query.closeResultSet(rs); // not needed, but still nice to have query.close();
This example already shows few nice new methods offered by DbQuery. Method executeUpdateAndClose() performs everything in just one call. Similarly, there are methods executeCount() and executeCountAndClose() that are made for executing "select count" database queries, where result is a long number in first result row and column.
Closing query is important. Fortunately, Db allows user to invoke close() method, and all the dirty work is done in the behind. When a query created some ResultSet, it is possible to explicitly close it using closeResultSet() method. However, this is not necessary any more! User may just simly close a query, and the DbQuery will close all results set that were created by it! In above example, line #9 may be simple deleted and everything will still work absolutly correctly. As will be show later, it is even possible to have automatic query closing:)
Named parameters
Prepared statement has only ordinal parameters. When having long and dynamic sql query, setting ordinal parameters may be tricky, or just user has to be unnecessary careful. Besides ordinal parameters, DbQuery offers named parameters as well.
DbQuery query = new DbQuery(connection, "select * from FOO where id=:id and name=:name");
query.setLong("id", id);
query.setString("name", "john");
ResultSet rs = query.execute();
...
query.close();
Named and ordinal parameters may mix in one query, although it is not a good practice.
Debug mode
When printing prepared statements, all parameters are signed with a question mark, i.e. it is not possible to see the real set values. This makes things difficult for debugging. DbQuery offers debug mode that will return the same query string but populated with real values. This query debug-view is just quick-and-dirty preview and it is not always 100% syntaxly correct (e.g. strings are not escaped, etc), but the result will be sufficient for debugging purposes.
DbQuery query = new DbQuery(connection, "select * from FOO where id=:id and name=:name");
query.setDebugMode(); // must be called before setting parameters
query.setLong("id", id);
query.setLong("name", "jodd");
System.out.println(query);
Here is the difference that debug mode makes:
select * from FOO where id=? and name=? select * from FOO where id=173 and name='jodd' -- debug mode
Configuration & Lazy initialization
DbQuery initializes lazy. Creating an object still doesn't do anything with the database, therefore it can be configured as needed. DbQuery intilizes on first concrete database-related method. Therefore, setting the debug mode (and other config) must be done immedietly after the DbQuery object creation.
Parameters setters
All prepared statement setting methods are implemented in DbQuery. As said, each method now has two versions: one that works with ordinal parameters and one for named parameters. Moreover, during setting of a parameter, value will be checked, and if it is null, the setNull() method will be invoked instead.
There are some new methods for setting parameter values, such as: setBean(), setMap(), setObject(), setObjects()...
With setBean() it is possible to populate query string where parameters are named as bean properties:
DbQuery query = new DbQuery(connection, "select * from FOO f where f.ID=:foo.id and f.NAME=:foo.names[0]");
query.setBean("foo", Foo);
SqlTypeManager and setObject()
DbQuery provides new method setObject() for setting objects of unknown type as parameters. For that purpose, DbQuery must resolve the way how to handle provided type and to invoke correct setter method.
Db has one central point for resolving sql types from object types: SqlTypeManager, manager for all kind of different SqlTypes. Each SqlType defines how a type is set and get from the database. There is a large amount of already defined types, however, it is easy to add new and more complex ones.
Using factories
It is a good practice to use factories (i.e. factory pattern) for creating various flavors of DbQuery objects, instead of direct instantiation. Moreover, when using factories it is possible to wrap the process of DbQuery creation, either manual or with aspects.
Auto-generated columns
DbQuery supports auto-generated columns. Usage is plain and simple:
DbOrmQuery q = new DbOrmQuery(connection, "insert into FOO(Data) values('data')");
q.setGeneratedColumns(); // indicate some auto-generated columns
q.executeUpdate();
long key = q.getGeneratedKey(); // get the first auto-genereted column, i.e. usually ID
q.close();
Example #2:
DbOrmQuery q = new DbOrmQuery(connection, "insert into FOO(Data) values('data')");
q.setGeneratedColumns("ID"); // indicate auto-generated column
q.executeUpdate();
ResultSet rs = q.getGeneratedColumns();
...
q.closeResultSet(rs);
q.close();
Contents
Db
Documentation
Jodd
Jodd WOT
Reports
