Efficient way to work with JDBC (template style)

Writing reusable codes has always been a great challange for any developer. We, as java developers always are concerned to write reusable codes. But not always we end up with reusable codes. I hope all java developers who worked with JDBC have discovered such situations when we end up with duplicate codes that are used to releaes resources like ResultSet, Statement and Connection objects. I will try to discuss on the solutions to this problem in this article. Best ways to work with JDBC is beyond the scope this discussion.

Let us first look at the following code snippet that is used to read some information from a table of a database. Here we are first creating a database connection. Obtaining Statement object from the Connection. Then we get a ResultSet object to work with by executing the statement. Care must be taken to release these resources properly.


public void someMethod() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbctutorial";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);
stmt = conn.createStatement();
rs = stmt.executeQuery("Select * from MyTable");
// do some work with this result set
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
}
rs = null;
if (stmt != null) {
stmt.close();
}
stmt = null;
if (conn != null) {
conn.close();
}
conn = null;
}
}

Every time we are to write code to add more functionality to the application, we need to write code to obtain connection,prepare statement, execute, handle result set and at last release resources. Obtaining connection, preparing statements,and releasing resources are the common steps and this is where we end up with duplicate codes.

It is time to apply Abstraction and Encapsulation to make our code more flexible. We will apply abstraction to the codes that are used to create/obtain database connection. Again we will apply encapsulation to the codes that processes the ResultSet obtained by executing the statement. Here is our new codes that uses a different kind of approach to do read/write operation to the database.

Let us now have a look at the following example. This is a DAO implementation which has two methods. First method addCompany(Company) is used to add new Company and the second one List listCompany() is used to get the list of existing companies. Note that we are not dealing with connection and statements here. We dont need to worry about opening and closing the connection and releasing resources like Statements and ResultSets. Everything is being taken care of by JDBCTemplate implementation.The first statement of addCompany is creating a new instance of a sub-class of UpdateStatement. The overriden handleGeneratedKeys(ResultSet) method will be called when executeUpdate of JDBCTemplate gets executed. Similarly in getCompanyList() the overriden method UpdateStatement.handleResultSet(ResultSet) gets executed as a callback method by JDBCTemplate.executeUpdate(). The codes are now much cleaner.

public class CompanyDaoImpl implements CompanyDao {
public void saveCompany(Company company) {
try {
UpdateStatement stmt = new UpdateStatement() {
public void handleGeneratedKeys(ResultSet rs) {
// Add codes to retrieve and use the generated id....
}
};
String sql = "Insert into company_list(name, code, type) values(?,?,?)";
Object[] parameters = new Object[] { company.getName(),
company.getCode(), company.getType() };
int[] sqlTypes = new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR };
new JDBCTemplate()
.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS,
parameters, sqlTypes, stmt);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public List getCompanyList(String username) {
final List companyList = new ArrayList();
try {
SelectStatement stmt = new SelectStatement() {
public void handleResultSet(ResultSet rs) throws SQLException {
while (rs.next()) {
Company company = new Company();
company.setId(rs.getLong(1));
company.setName(rs.getString(2));
company.setCode(rs.getString(3));
company.setType(rs.getString(4));
companyList.add(company);
}
}
};
new JDBCTemplate()
.executeQuery(
"Select C.id, C.name, C.code, C.type"
+ " from company_list C
+ " where C.username=?)",
new Object[] { username }, new int[] {Types.VARCHAR}, stmt);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return companyList;
}
}

The description of other helper classes used in the above example are given below.

1. ConnectionContext
This class is an abstraction to the codes that are used to connect to the database.

package com.jaywalkable.dsepop.dao.impl;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class ConnectionContext {
public static Connection getConnection() throws SQLException {
Connection conn = null;
try {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("jdbc/TestDB");
conn = ds.getConnection();
} catch (NamingException e) {
// notify the error here....
}
return conn;
}
}

2. SelectStatement
The implementation of this interface is used to handle/process ResultSet object that we get after executing any statement that returns a ResultSet object.

package com.jaywalkable.dsepop.dao.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface SelectStatement {
public void handleResultSet(ResultSet rs) throws SQLException;
}

3. UpdateStatement
The sub-classes of this class are responsible to handle/process any keys that are generated because of any insert statement. This class can be used to send any update statements to the database.

package com.jaywalkable.dsepop.dao.jdbc;
import java.sql.ResultSet;
public class UpdateStatement {
public void handleGeneratedKeys(ResultSet rs) {
}
}

4. This is the core interface of this API. Implementation of this interface is mainly responsible to minimize or help us to write reusable codes.

package com.jaywalkable.dsepop.dao.jdbc;
import java.sql.SQLException;
public interface SQLTemplate {
public void addBatch(String sql, Object[] parameters, int[] sqlTypes) throws SQLException;
public void beginTransaction() throws SQLException;
public void commit() throws SQLException;
public int[] executeBatch() throws SQLException;
public void executeQuery(String sql, Object[] parameters, int[] sqlTypes,
SelectStatement command) throws SQLException;
public int executeUpdate(String sql, int autoGeneratedKeys,
Object[] parameters, int[] sqlTypes, UpdateStatement command) throws SQLException;
public void rollback() throws SQLException;
}

5.JDBCTemplate
This is the implementation of the above interface.

package com.jaywalkable.dsepop.dao.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.jaywalkable.dsepop.dao.impl.ConnectionContext;
public class JDBCTemplate implements SQLTemplate {
boolean isAutoCommit = true;
Connection conn = null;
PreparedStatement prepStmt = null;
public JDBCTemplate() {
super();
}
public void addBatch(String sql, Object[] parameters, int[] sqlTypes)
throws SQLException {
setParameters(prepStmt, parameters, sqlTypes);
}
public void beginTransaction() throws SQLException {
this.isAutoCommit = false;
getConnection().setAutoCommit(this.isAutoCommit);
}
public void commit() throws SQLException {
try {
this.conn.commit();
this.isAutoCommit = true;
} catch (SQLException e) {
this.conn.rollback();
throw e;
} finally {
closeConnection();
}
}
public int[] executeBatch() throws SQLException {
if (prepStmt != null) {
int[] updateCounts = prepStmt.executeBatch();
closeResources(null, prepStmt);
prepStmt = null;
return updateCounts;
}
closeConnection();
return new int[0];
}
public void executeQuery(String sql, Object[] parameters, int[] sqlTypes,
SelectStatement command) throws SQLException {
PreparedStatement stmt = getConnection().prepareStatement(sql);
setParameters(stmt, parameters, sqlTypes);
ResultSet rs = stmt.executeQuery();
command.handleResultSet(rs);
closeResources(rs, stmt);
rs = null;
stmt = null;
}
public int executeUpdate(String sql, int autoGeneratedKeys,
Object[] parameters, int[] sqlTypes, UpdateStatement command)
throws SQLException {
PreparedStatement stmt = getConnection().prepareStatement(sql);
setParameters(stmt, parameters, sqlTypes);
int count = stmt.executeUpdate(sql, autoGeneratedKeys);
ResultSet keys = null;
if (Statement.RETURN_GENERATED_KEYS == autoGeneratedKeys) {
keys = stmt.getGeneratedKeys();
command.handleGeneratedKeys(keys);
}
closeResources(keys, stmt);
keys = null;
stmt = null;
return count;
}
public void rollback() throws SQLException {
try {
this.conn.rollback();
this.isAutoCommit = true;
} catch (SQLException e) {
throw e;
} finally {
closeConnection();
}
}
private void closeConnection() {
if (this.conn != null) {
try {
this.conn.close();
} catch (SQLException e) {
;
}
this.conn = null;
}
}
private void closeResources(ResultSet rs, Statement stmt) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
;
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
;
}
}
if (this.isAutoCommit) {
closeConnection();
}
}
private Connection getConnection() throws SQLException {
if (this.conn != null) {
this.conn = ConnectionContext.getConnection();
}
return this.conn;
}
private void setParameters(PreparedStatement stmt, Object[] parameters,
int[] sqlTypes) throws SQLException {
for (int i = 0; i < parameters.length; i++) {
Object value = parameters[i];
int sqlType = sqlTypes[i];
int position = i + 1;
stmt.setObject(position, value, sqlType);
}
}
}

1 Comment »

  1. Leonardo Pinto said

    Very good implementation, congratulations! But I get the following error:

    SQLException: Use of the executeUpdate(String, int) method is not supported on this type of statement.
    SQLState: HYC00

    The only change I made in the code was have added another field ID…

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: