SourceForge.net Logo
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



Top 25%

BSD
 

SqlUtil

Writing JDBC java code may be really boring: you have to obtain a Connection, 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:

SqlUtil example 1
 
SqlUtil dbsql = new SqlUtil(pool);
try {
	dbsql.setSql("select * from FOOTABLE where ID=? and NAME=?");
	dbsql.setInt(1, 173);
	dbsql.setString(2, "user");
	ResultSet rs = dbsql.executeQuery();
	while (rs.next()) {
		...		// do something with a ResultSet
	}
	dbsql.close();		// not necessary, but nice
} catch (SQLException sex) {
	...			// do something with an exception
} finally {
	dbsql.closeAll();
}

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:

SqlUtil example 2
 
SqlUtil dbsql = new SqlUtil(pool);
try {
	dbsql.setStaticSql("update FOOTABLE set NAME='test' where ID=173");
	int r = dbsql.executeUpdate();
	dbsql.close();		// not necessary, but nice
} catch (SQLException sex) {
	...			// do something with an exception
} finally {
	dbsql.closeAll();
}

Debugging mode

SqlUtil 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:

SqlUtil debug example
 
SqlUtil.setDebug(true);		// set global flag to true
...
String s = dbsql.toString();	// read the debug string

Transactions

SqlUtil supports transactions. Usage is quite straightforward:

SqlUtil transaction example
 
SqlUtil dbsql = new SqlUtil(pool);
try {
	dbsql.setAutoCommit(false);
	dbsql.setStaticSql("update FOOTABLE set NAME='test' where ID=173");
	int r = dbsql.executeUpdate();
	dbsql.close();		// not necessary, but nice
	dbsql.commit();
} catch (SQLException sex) {
	dbsql.rollback();
} finally {
	dbsql.closeAll();
}

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:

SqlUtil default commit mode
 
SqlUtil.setDefaultAutoCommit(true);

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 statement

This is just a tip how to reuse existing statement over the same connection:

SqlUtil tip: reusing statement
 

	dbsql.setStaticSql("select count(*) from FOOTABLE");
	ResultSet rs = dbsql.executeQuery();
	if (rs.next()) {
		System.out.println(rs.getInt(1));
	}

	//dbsql.close();					// tip!
	//dbsql.setStaticSql("select count(*) from FOOTABLE");	// tip!

	rs = dbsql.executeQuery();
	if (rs.next()) {
		System.out.println(rs.getInt(1));
	}

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 Connections

It is not necessary to use SqlUtil 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.


http://jodd.sourceforge.net
najgor at users.sourceforge.net