![]() |
|
Version: 0.27 - Last update: 2004-05-30 | Home Page - SourceForge Project Page - Contact |
Jodd Overview News Download License References Development Using Jodd Javadoc JUnit report Community Contribute Report a bug More reading Milestone 0.30 ![]() ![]() |
SqlUtilWriting JDBC java code may be really boring: you have to obtain aConnection ,
create some Statement object to get a ResultSet ;
at the end you have to close all objects inside try /catch
blocks... a lot of similar code is constantly repeated.SqlUtil facade class does many of this common things in the
background, significantly reducing the amount of code needed for the JDBC,
which increases readability and manageability of the code. All that is done
without any performance or feature loss.Here is an example how SqlUtil may be used to fetch data from the
database:
As it is shown in the example, SqlUtil totally substitutes
PreparedStatement class. But SqlUtil can also be
used as a simple Statement class (when needed), as in example below:
Debugging modeSqlUtil has another nice feature: the debugging mode. When
prepared statements are created, query values are set with
setXXX() methods and real query is builded internally.
There is no way to preview the builded query that is actually
executed on database.When SqlUtil is in the debug mode, it will internally manage
a SQL-like string created from given prepared statement string, with
all '?' characters substituted with adequate values. Resulting string is
usually not fully SQL compatible, but it is quite sufficient for debugging
purposes.Debugging mode can be easily switched on or off. When it is off, debug string will not be managed at all and will be exactly equals as given prepared statement string (with the '?' characters). Here is code snippet:
TransactionsSqlUtil supports transactions. Usage is quite straightforward:
SqlUtil handles connections with care: before returning it to
the connection pool, it sets the original auto-commit mode of the connection.
Therefore, it is possible to rely on the database default auto-commit mode.However, for serious systems this still might not be enough. Theoretically, setting the auto-commit mode in finally block
may still fail, and connection may be returned to the pool without
resetting the commit mode. Therefore, it is recommended to set global
default commit mode equal to database initial auto-commit mode. This is
usually done during initialization, when connection pool is created and
connectivity is checked:
By setting default auto commit mode it is 100% guaranteed that statement will be executed in required auto-commit mode, or SQLException will be thrown.Reusing statementThis is just a tip how to reuse existing statement over the same connection:
Why this works? It is because executeQuery() first closes previously result set
and then creates the new one.Another tip, regarding close() : when it is followed by setSql()
or setStaticSql() , close() is not needed since both setting
sql methods previously close all opened result sets and statements.close() is even not necessary in general, but it is the matter of
good etiquette to use it, and therefore its usage is recommended.
Using ConnectionsIt is not necessary to useSqlUtil with ConnectionPool database pools.
It may be used with a java.sql.Connection , as well, by passing connection object to
SqlUtil constructor. The only difference that user must be aware of is the
SqlUtil.closeAll() method. This method will actually physically close the database
connection, so it will be no available afterwards anymore. If this is the desired behaviour, than everything is ok.
However, if there is a need to have open connection after closing (for reusing it, for example) than
squalid.close() should be used instead, which leaves connection intact.
|