Using JDBC api correctly requires writing the same code snippets over and over again, what easily leads to unmaintainable application. Database support in Java should be simpler. Db provides several layers around plain JDBC, significantly reducing the amount of written code. First, there is a smart statement facade, with some additional options, such as named parameters, user-friendly debug and more methods for execution and for parameters setting. Next layer encapsulates database session and helps with transactions and queries. Finally, DbOrm returns the joy of simple, but efficient objects mapping, simple object relations, templated queries and some query auto-generations.

DbQuery is a wrapper and an enhancement for prepared and regular JDBC statements. It supports (friendly:) named parmeters as well as ordinal parameters. It also has the debug mode that returns query string with 'quick-and-dirty' populated parameters. DbQuery can be used in the same way as prepared statement for setting parameter values. Of course, some more methods has been added, such as: setBean(), setMap() and setObject(). Last but not least, DbQuery takes care about all opened result sets and closes them automatically, if not already closed explicitly.

DbQuery uses plain SQL as the query language. If used database understands it, so does DbQuery. No proprietary query language.

DbQuery can be easily extended for custom usage. One such extension already exist: DbProfiledQuery, that measures execution time of queries.

And finally, DbQuery may be used independently from other parts of this package.

DbQuery supports named parameters as well as ordinal ones. Named parameters are much easier to use. Here is a simple example:

DbQuery query = new DbQuery(connection, "select * from GIRLS where ID = :id");
query.setInteger("id", 2);
ResultSet rs = query.execute();
...

Named and ordinal parameters may mix in one query, although it is not a good practice.

Closing opened ResultSets, statements and connections correctly requires a lot of boilerplate code. DbQuery helps here, too. In above example, developer may close created ResultSet with just one line of code:

query.closeResultSet(rs);

But DbQuery can do more than that! In fact, user is not obligated to close explicitly any created ResultSet: he may close all created result sets or to close the query, what will also close the internal statements.

query.closeAllResultSets();
// and/or
query.close();

Moreover, various update and count query execution methods (those that do not return ResultSet) may close the query immediately.

As it will be shown later, queries may be also closed automatically, so no closing code would be required :)

As it is well known, dumping prepared statement query returns question mark signs (?) for parameter values, i.e. developer can't see the values. This makes things difficult for debugging. Fortunately, DbQuery offers debug mode that will return populated query string! Please note that this debug-view of the query is quick-and-dirty and is not always 100% correct (e.g. strings are not escaped, etc), but it will work in most cases:)

It is possible to set debug mode for single query as well as for all queries. Example:

DbQuery.DEFAULT_QUERY_MODE = new DbQueryMode().debug();
...
DbQuery query = new ...
System.out.println(query.getQueryString());

In above example all created queries will be in debug mode. Here is the difference of the possible ouput between the queries created in regular and in the debug mode:

select * from USER t where t.PASSWORD=? and t.USERNAME=?
select * from USER t where t.PASSWORD='najgor!' and t.USERNAME='najgor'		-- debug mode

Besides all usual statement methods DbQuery offers some more methods that will be appreciated by developers that deal with every-day database work. To mention few: executeCount(), executeCountAndClose(), closeResultSet()... There are some new methods for setting more values at once, such as: setBean(), setMap(), setObject(), setObjects()... Interesting might be the setBean()method where user can have query parameters named as bean properties that will be populated with bean values.

DbQuery query = new DbQuery("select * from Foo f where f.ID=:foo.id and f.NAME=:foo.names[0]");
query.setBean("foo", Foo);

DbQuery encapsulates both regular and prepared JDBC statements. If SQL query contains parameters, DbQuery will create and use PreparedStatement under the hood. Otherwise, regular Statement is created. It has been found that this approach gives the best performances. Of course, if needed, it is possible to force creation of PreparedStatement for all queries, by setting the static flag DbQuery.FORCE_PREPARED_STATEMENTS.

DbSession goes one step further in the encapsulation of existing JDBC api. It represents one database session and, therefore, wraps database connections.

DbSession uses ConnectionProvider instances for getting the actual database connections. There are various ConnectionProvider implementations in Jodd, including the custom very nice connection pool (CoreConnectionPool).

DbSession are used for creating DbQuery-ies. DbSession takes care of created DbQuery instances during its session and closes all resources at the end: all queries and therefore all created result sets; at the end it returns connection back to ConnectionProvider. Here is an example of typical working session with DbSession.

DbSession session = new DbSession(connectionProvider);
...
session.beginTransaction();
DbQuery query = new DbQuery(session, "insert into...");
query.executeUpdate(true);		// query will be closed after execution
...
session.commitTransaction();
session.close();

Finally, DbSession and DbQuery may be used independently from other parts of this package.

DbSession is easy to extend for custom usage. One such extension already exists: DbThreadSession. Upon creation, it assigns created session to current thread. Useful when only one session (i.e. connection) is used per thread, through application layers.

DbSession session = new DbThreadSession(connectionProvider);
...
...// some layers in between
...
DbSession session = DbThreadSession.getThreadSession();
DbQuery query = new DbQuery(session, "select...");
...
session.close();

Still, here we are tight to concrete implementation and this might not be the most user-friendly usage pattern. The goal is to 'free' the DbQuery code from DbSession creation.

DbSessionProvider implementation are responsible for creating new DbSessions. What is more interesting is that DbQuery provides ability to register default DbSessionProvider implementation used by DbQuery. Now, the previous code snippet might looks like:

DbQuery.SESSION_PROVIDER = new ThreadDbSessionProvider();		// somewhere in initialization part
...
DbQuery query = new DbQuery("select...");

Now DbQuery creation doesn't depend on DbSession. Of course, it is still necessary to open and close the session - but now this can be decoupled from the code where DbQuery is used.