Tip

Database Access with Spring 3.0 and the JdbcTemplate

Learning Spring can be intimidating, because there are so many different aspects to the framework. At its core, Spring is an Inversion of Control (IoC) and Dependency Injection (DI) container. It's also a powerful platform for doing some aspect oriented programming as well. Layer on Spring MVC, Spring Web Flow, Security, Roo, AMQP, Blaze Integration, and whatever new project the people at VMWare have added to the list, the whole thing can get pretty intimidating.

Learning Spring: Once Concept at a Time

With so many different aspects to the framework, learning Spring can be intimidating. At its core, Spring is an Inversion of Control (IoC) and Dependency Injection (DI) container. It's also a powerful platform for doing some aspect oriented programming as well. Layer on Spring MVC, Spring Web Flow, Security, Roo, AMQP, Blaze Integration, and whatever new project the clever sausages at VMWare have added to the list, the whole thing can get pretty intimidating.

Updated Spring IoC tutorials Jakarta EE development examples

TheServerSide has been updating its resources in terms of the Spring framework, Java EE contexts and various different approaches to inversion of control (IoC) and dependency injection (DI). Check out these newer resources on the topic:

The Data Access Philosophy of the Spring Framework

But the nice thing about Spring is that you can pick and choose which projects, and for that matter, which parts of these various projects, you want to learn. For example, inside the core Spring download, there are a few fantastic libraries for helping you perform JDBC operations, and subsequently build some very easy to write and easy to manage Data Access Objects (DAOs). Now, tutorials on the topic often complicate matters by convoluting the Spring data access philosophy with all sorts of DI and IoC concepts as well, but the fact is, you can learn Spring's approach to data access without doing any sort of IoC or Aspect Oriented Programming at all.

Let's say we wanted to add a new record to the following database table, named gamesummary:

CREATE  TABLE  `rps`.`gamesummary`  ( 
  `id`  INT  NOT  NULL  AUTO_INCREMENT, 
  `clientChoice`  VARCHAR(45), 
  `serverChoice`  VARCHAR(45), 
  `result`  VARCHAR(45), 
  `date`  DATE, 
  PRIMARY  KEY  (`id`) 

 
 

Standard JDBC Coding

If you've ever done any JDBC coding in your life, the following AddGameSummary class will probably look pretty familiar to you. It basically just does all of the stuff that's required to connect to the database and use a PreparedStatement to insert a record:


package  com.mcnz.spring; 
import  java.sql.*; 
 
import  javax.sql.*; 
import  org.springframework.jdbc.datasource.*; 
 
public  class  AddGameSummary  { 
  public  static  void  main(String[]  args)  { 
 
  String  dburl  =  "jdbc:mysql://localhost:3306/rps"; 
  String  name  =  "root"; 
  String  pass  =  "password"; 
  Connection  conn  =  null; 
  DataSource  ds  =  null; 
 
  try  { 
    Driver  driver  =  new  com.mysql.jdbc.Driver(); 
    ds  =  new  SimpleDriverDataSource(driver,  dburl,  name,  pass); 
    conn  =  ds.getConnection(); 
  }  catch  (SQLException  e1)  {e1.printStackTrace();} 
 
 
  try{ 
    String  INSERT_SQL  = 
    "INSERT  INTO  GAMESUMMARY  "  + 
   "  (clientchoice,  serverchoice,  result,  date)  " 
    +  "VALUES  (?,  ?,  ?)"; 
   PreparedStatement  ps  =  conn.prepareStatement(INSERT_SQL); 
    ps.setString(1,  "rock");  ps.setString(2,  "paper"); 
    ps.setString(3,  "win"); 
    ps.executeUpdate(); 
    ps.close(); 
  } 
  catch  (SQLException  e)  {e.printStackTrace();} 
  finally  { 
    try  {conn.close();} 
    catch  (SQLException  e)  {e.printStackTrace();} 
    catch  (NullPointerException  e)  {e.printStackTrace();} 
  } 

  } 

 

Now that's a lot of convoluted code, from the catch blocks that handle problems you really can't do much about, to the routine task of setting and properly parameterizing a PreparedStatement. So, what does Spring do about it?

Using the Spring 3.0 JdbcTemplate Class

Well, Spring provides a super-helpful class called the JdbcTemplate. The thinking is that whenever you perform a JDBC operation, there's always bunch of lines that are pretty much always the same. So, why not just create a simple template out of those lines of code, and then just call the template, rather than typing them in all the time? And that's exactly what Spring has done with their JdbcTemplate class. All you need to do is initialize it by passing a real JDBC connection to the constructor, and then you can issue and execute an SQL command with a single method call.

The JdbcTemplate class is the central class in the JDBC core package. It simplifies the use of JDBC since it handles the creation and release of resources. This helps to avoid common errors such as forgetting to always close the connection. It executes the core JDBC workflow like statement creation and execution, leaving application code to provide SQL and extract results. This class executes SQL queries, update statements or stored procedure calls, imitating iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package. - Spring Documentation

So, we can turn the  guts of our AddGameSummary class from this:


try{
      String  INSERT_SQL  = "INSERT  INTO  GAMESUMMARY  "  +      
         "  (clientchoice,  serverchoice,  result,  date)  " 
             +  "VALUES  (?,  ?,  ?)";
     PreparedStatement  ps  =  conn.prepareStatement(INSERT_SQL);
     ps.setString(1,  "rock");
     ps.setString(2,  "paper");
     ps.setString(3,  "win");
     ps.executeUpdate();
     ps.close();

catch  (SQLException  e)  {e.printStackTrace();} 
finally  {
    try  {conn.close();}
   catch  (SQLException  e)  {e.printStackTrace();}
   catch  (NullPointerException  e)  {e.printStackTrace();}

To this:


String INSERT_SQL = "INSERT INTO GAMESUMMARY "
    + " (clientchoice, serverchoice, result, date) "
          + "VALUES (?, ?, ?)";
JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
jdbcTemplate.update(INSERT_SQL, "rock", "paper", "win");

/*Look ma! No catch block!!! */

And the whole class, when we look at it from top to bottom, looks much, much cleaner!


package  com.mcnz.spring; 
 
import  java.sql.*; 
import  javax.sql.*; 
import  org.springframework.jdbc.core.*; 
import  org.springframework.jdbc.datasource.*; 
 
public  class  AddGameSummary  { 
  public  static  void  main(String[]  args)  { 
 
  String  dburl  =  "jdbc:mysql://localhost:3306/rps"; 
  String  name  =  "root"; 
  String  pass  =  "password"; 
  DataSource  ds  =  null; 
 
  try  { 
  Driver  driver  =  new  com.mysql.jdbc.Driver(); 
  ds  =  new  SimpleDriverDataSource(driver,  dburl,  name,  pass); 
  conn  =  ds.getConnection(); 
  }  catch  (SQLException  e1)  {e1.printStackTrace();} 
 
 
  String  INSERT_SQL  =  "INSERT  INTO  GAMESUMMARY  " 
  +  "  (clientchoice,  serverchoice,  result)  " 
  +  "VALUES  (?,  ?,  ?)"; 
 
  JdbcTemplate  jdbcTemplate  =  new  JdbcTemplate(ds); 
  jdbcTemplate.update(INSERT_SQL,  "rock",  "paper",  "win"); 
 
  } 

 

When the class runs, a single record with the values of "rock" "paper" and "win" will be written to the corresponding columns for the clientchoice, serverchoice and result in the gamesummary database table. The result of running the iteration of the AddGameSummary class that uses the JdbcTemplate is exactly the same as the original iteration that goes through all of the steps of creating and initializing a PreparedStatement, however, using the Spring class greatly simplifies task of interacting with the database. What's more, you can leverage the benefits of the JdbcTemplate on its own, without having to throw in Spring's DI, IoC or aspect oriented programming capabilities.



 

Next Steps

Here are some other helpful tips, tricks, tutorials and examples by Cameron McKenzie (@cameronmcnz):

Getting started with Git: The 5 basic Git commands everyone needs to know

A RESTful APIs tutorial: The principles of effective web service design

Microservices deployment? How to embed Tomcat in an executable JAR

Continuous integration quickstart: A step-by-step Jenkins tutorial for beginners

Undo bad commits with this git reset hard example tutorial

Dig Deeper on Front-end, back-end and middle-tier frameworks

App Architecture
Software Quality
Cloud Computing
Security
SearchAWS
Close