ALL Rob's del.ic.ious bookmarks
Avoiding Connection Leakage and Boiler Plate Code with Spring JdbcTemplate
We use Firestorm DAO for our DAO Code Generation. All of the data access in Firestorm properly handles closing JDBC ResultSets, Statements and Connections.
However there are times when we want to write straight JDBC , and making custom DAOs inside of Firestorm can be time consuming.
So we are starting to write more straight JDBC more and more in the services/manager layer, especially for SUMs and other aggreate functions, however if we don’t properly shut down the resources used by JDBC this will result in weird SQLExceptions because the connections and resources haven’t been shutdown properly.
try{
//call sql
}
catch(Exception e)
{
…
}
finally{
try{
//close resources
if(resultSet != null) resultSet.close(); //can't access result set after its closed
if(statement != null) statement.close();
if(connection != null) DataSourceUtils.releaseConnection(connection);
}
Catch(SQLException e)
{
…
}
}
In any case that is all pretty wordy and its easy to screw up and forget to close some resources and this will lead to problems. Problems like weird , non reproducable connection pool exceptions.
Spring has a solution for this called JdbcTemplate.
The full documentation is at:
http://static.springframework.org/spring/docs/2.5.x/reference/jdbc.html#jdbc-JdbcTemplate
Suffice it to say that everytime we use straight jdbc we should be using this jdbcTemplate class or something else that wraps it (such as DBUtility).
I have created a couple of methods in com.cms.db.DBUtility in stdcore which wrap the jdbcTemplate you can see how much cleaner the code is with the use of the jdbcTemplate as opposed to coding the jdbc by hand
/**
* Used to get back one column from a query that returns one row assuming your hitting the mainDataSource
* can only return a String (you can then use Wrapper classes to make it to wahtever you want)
* @param sql
* @param columnName
* @return
* @throws CMSDBException
*/
public static String getSqlResultsSingleColumn(String sql, String columnName)
{
//this one line of code is alot cleaner than all the code below to do the same thing
//use of JdbcTemplate will close all the necessary jdbc resources properly
return (String) jdbcTemplate.queryForObject(sql, String.class);
/*
Connection conn = null;
StringBuilder results = new StringBuilder();
ResultSet resultSet = null;
Statement statement = null;
try{
conn = FirestormUtil.getCurrentConnection();
statement = conn.createStatement();
Boolean rsIsAvaiable = statement.execute(sql);
resultSet = null;
if(rsIsAvaiable)
{
resultSet = statement.getResultSet();
resultSet.next();
return resultSet.getString(columnName);
}
else{
throw new CMSDBException("result set is not available for "+sql);
}
}
catch(Exception e)
{
results = new StringBuilder();
results.append("Exception Thrown in getSqlResults "+e.getMessage());
results.append(Constants.HTML_NEW_LINE+ "SQL:"+sql);
results.append(Constants.HTML_NEW_LINE+ "Stack Trace:"+Utility.getStackTrace(e));
throw new CMSDBException(results.toString());
}
finally {
try{
if(resultSet != null) resultSet.close(); //can't access result set after its closed
if(statement != null) statement.close();
//if(conn != null) DataSourceUtils.releaseConnection(conn, dataSource); }
catch(Exception e)
{
results = new StringBuilder();
results.append("Exception Thrown in getSqlResults "+e.getMessage());
results.append(Constants.HTML_NEW_LINE+ "SQL:"+sql);
results.append(Constants.HTML_NEW_LINE+ "Stack Trace:"+Utility.getStackTrace(e));
throw new CMSDBException(results.toString());
}
}
*/
}
/**
* Used to get multiple columns back from a query
* Returns an array list of maps
* can return more than one row
* @param sql
* @param columnNames
* @return
* @throws CMSDBException
* returns as strings , strings have to be converted into other types if necessary
*/
public static ArrayList<Map<String, String>> getSqlResultsMutlipleColumns(String sql, final String[] columnNames)
{
RowMapper rowMapper = new RowMapper()
{
public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException
{
Map<String,String> map = new HashMap<String, String>();
for (String columnName : columnNames) {
map.put(columnName, resultSet.getString(columnName));
}
return map;
}
};
return (ArrayList<Map<String, String>>)jdbcTemplate.query(sql,rowMapper);
/*
Connection conn = null;
StringBuilder results = new StringBuilder();
ResultSet resultSet = null;
Statement statement = null;
try{
conn = FirestormUtil.getCurrentConnection();
statement = conn.createStatement();
Boolean rsIsAvaiable = statement.execute(sql);
resultSet = null;
if(rsIsAvaiable)
{
resultSet = statement.getResultSet();
resultSet.next();
HashMap map = new HashMap();
for (String columnName : columnNames) {
map.put(columnName, resultSet.getString(columnName));
}
return map;
}
else{
throw new CMSDBException("result set is not available for "+sql);
}
}
catch(Exception e)
{
results = new StringBuilder();
results.append("Exception Thrown in getSqlResults "+e.getMessage());
results.append(Constants.HTML_NEW_LINE+ "SQL:"+sql);
results.append(Constants.HTML_NEW_LINE+ "Stack Trace:"+Utility.getStackTrace(e));
throw new CMSDBException(results.toString());
}
finally {
try{
if(resultSet != null) resultSet.close(); //can't access result set after its closed
if(statement != null) statement.close();
//if(conn != null) DataSourceUtils.releaseConnection(conn, dataSource);
}
catch(Exception e)
{
results = new StringBuilder();
results.append("Exception Thrown in getSqlResults "+e.getMessage());
results.append(Constants.HTML_NEW_LINE+ "SQL:"+sql);
results.append(Constants.HTML_NEW_LINE+ "Stack Trace:"+Utility.getStackTrace(e));
throw new CMSDBException(results.toString());
}
}
*/
}
And here are the test cases. With DBUtility (which makes use of Spring JdbcTemplate) you can do straight sql without having to have a custom dto or having to rewrite the JDBC BoilerPlate code all the time.
public void testGetSqlResultsSingleColumn()
{
String sql = "SELECT SUM(TRUNITS) AS SUM FROM GLOPEN WHERE TRPART = '001' " +
"AND TRCOMP = '20' AND TRLOC = 'LAS' AND TRIDX = 'I' " +
"AND TRADATE >= '20080101' AND TRADATE < '20080201' " +
"AND (TRCAT='PI' OR TRCAT='PR') ORDER BY TRCAT ASC";
try{
String sum = DBUtility.getSqlResultsSingleColumn(sql, "SUM");
log.info(sum);
}
catch (Exception e) {
e.printStackTrace();
fail(e.getMessage());
}
}
public void testGetSqlResultsMultipleColumns()
{
String companyId = "30";
String locationId = "SFB";
String sql = "SELECT SUM(TRUNITS) AS SUM1, SUM(TRGROSS) AS SUM2 FROM FUEL_TXN" +
" WHERE TRCOMP = '"+companyId+"' AND TRORG = '"+locationId+"'" +
" AND TRTYPE = 'ADJ' ORDER BY TRTDATE DESC LIMIT 1";
try{
ArrayList<Map<String, String>> arrayList = DBUtility.getSqlResultsMutlipleColumns(sql, new String[]{"SUM1", "SUM2"});
for (Map<String, String> map : arrayList) {
for(String key : map.keySet()){
String val = map.get(key);
log.info(key + ": " + val);
}
}
}
catch (Exception e) {
e.printStackTrace();
fail(e.getMessage());
}
}