updated: Jun 17 2008
*In New York City, one suicide in ten is attributed to a lack of storage space. (Judith Stone)
Table of Contents
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.
Sorry, documentation is not yet complete and does not provide sufficient information. Please refer to test cases, javadoc and sources for more details.
If you need more info on this particular subject, do not hesitate to demand more documentation.
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.
query.closeAllResultSets(); // and/or query.close();
Moreover, various update and count query execution methods (those that do not return
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,
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
Additional methods for setting the parameters
Besides all usual statement methods
DbQuery query = new DbQuery("select * from Foo f where f.ID=:foo.id and f.NAME=:foo.names"); query.setBean("foo", Foo);
Type of the used JDBC statements
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();
DbThreadSession & DbSessionProvider
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.SESSION_PROVIDER = new ThreadDbSessionProvider(); // somewhere in initialization part ... DbQuery query = new DbQuery("select...");