Spring has arrivied -- working with Spring's JDBC Template

Spring. It brings to mind rebirth; the season of regrowth, and new beginnings.  Rarely, if at all, when mentioned in non-technical circles, does the term "Spring" bring to mind an open source application framework for the Java platform.  But, that is exactly what does come to mind, if you happen to have a technical conversation amongst a number of Java developers.   Considering that I am not a Java developer (my day job involves being a member of a Quality Assurance team), I was interested to know what a Java developer might recommend when it came to communicating with a database.

I had dabbled with using JDBC in the past, and had some limited success with it, however, the fact that using a pure implementation of JDBC meant that I would need to manage the connection(s) as well as any errors that might arise, got me to thinking if there wasn't some sort of alternative. After a brief discussion with one of our developers, he informed me of something called JDBC Template.  JDBC Template is a part of the Spring Framework.  What's the point of using JDBC Template, if I can just use the JDBC API, you may ask? Well, I'll defer the answer to that question to a couple of online resources.

The Spring Framework takes care of all the grungy, low-level details that can make JDBC such a tedious API to develop with¹.

The Spring JDBC template allows to clean-up the resources automatically, e.g. release the database connections.

The Spring JDBC template converts the standard JDBC SQLExceptions into RuntimeExceptions. This allows the programmer to react more flexible to the errors. The Spring JDBC template converts also the vendor specific error messages into better understandable error messages².

Essentially, this results in easier to read/maintain code by removing the boiler-plate coding required to manage JDBC resources.  In order to test out this framework, let's delve into an example that I put together to help me understand the use and benefits of JDBC Template.  First off, we'll need to get things setup before moving forward -- this example uses MySQL as the database, and assumes some knowledge of Java and Maven.

Setup
I've set things up using Maven.  The following represents the necessary dependencies required for this project.


  <dependencies>
   <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
   <version>3.0.5.RELEASE</version>
   </dependency>
   <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.15</version>
   </dependency>
  </dependencies>

The first dependency pulls in the necessary Spring JDBC Framework.  The second dependency noted, pulls in the MySQL Java Connector required to communicate with the MySQL database.

Implementation
In order to extract and/or update information within the database, we will need to create an interface to the specified table.  The interface will then be implemented by a class that contains the necessary instance of the JdbcTemplate.  This is the convention normally taken when developing DAO artifacts.

The following code assumes that a local MySQL database exists with a table labeled Account that contains, for the purposes of this example, an account_name field and an account_id field.

The Interface


public interface IAccountDAO {
public String selectAccountNameById(int id);
}

We are going to extract the account's name from the database, using the specified identifier.




The Class that Implements the Interface




import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;




public class AccountJdbcTemplateDAOImpl implements IAccountDAO {

private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {

this.jdbcTemplate = new JdbcTemplate(dataSource);

}




public String selectAccountNameById(int id) {

// the SQL to execute

String sql = "SELECT account_name FROM accounts WHERE account_id = ?";

// using JDBC template, extract the account name

String accountName = (String)jdbcTemplate.queryForObject(sql, new Object[]{new Integer(id)}, String.class);

return accountName;

}




}




Implementing the interfaces method, results in extracting the information we are after by using an SQL statement that is sent to JDBC's queryForObject method.

The Main Class



import com.my.dao.AccountJdbcTemplateDAOImpl;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;


public class ConnectUsingJdbcTemplate {


/**
* @param args
*/
public static void main(String[] args) {
AccountJdbcTemplateDAOImpl accountDAO = new AccountJdbcTemplateDAOImpl();
// create a MySQL data source
MysqlDataSource dataSource = new MysqlDataSource();
// assign properties to the data source 
dataSource.setUser("myuser");
dataSource.setPassword("mypassword");
dataSource.setURL("jdbc:mysql://localhost/mydb");

// assign the data source to the DAO object
accountDAO.setDataSource(dataSource);

// grab the account name and print it out to the console
System.out.println("Account Name = " + accountDAO.selectAccountNameById(1008));
}


}







References
1.http://static.springsource.org/spring/docs/2.0.8/reference/jdbc.html
2.http://www.vogella.de/articles/SpringJDBC/article.html