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:
- Inversion of control explained fast with a Spring IoC example
- Spring MVC Java web development with Spring Boot
- Drawbacks to IoC and how to solve inversion of control problems
- Spring IoC vs. Google Guice's inversion of control approach
- Getting started with Google Guice
- Getting started with the Spring IoC container
- The beauty of Spring without XML: Annotation based IoC
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.
