Concept Overview

In the previous tutorials we have covered the basic concepts of spring as a framework. We now start looking at how spring integrates with the other parts of the application. The main power of spring lies in its ability to talk to various other parts of the application, whatever technology they may be in. When we start talking about other parts of the application, the first thing that pops into the mind is….. yes, you are right.. database. Almost all applications store data into a database and database access code takes a major part of developers time.

The standard way to access a database using java is to use a JDBC driver. The steps involved are – creating or obtaining a connection from a connection pool, creating a statement, executing query or update statements and closing the connection. This looks simple, but you need to do this every time, the same steps over and over again in all methods that talk to the database, and that is not all, you also have to handle the ubiquitous SQLException almost everywhere.

Accessing Data – Your application code talks to a layer called DAO or data access object. The DAO is a layer that sits between your application and the database. The DAO has two main advantages – 1. Its coded to an interface and 2. It abstracts the implementation and hence the application need not be aware of the actual database.

SQLException – You always have to deal with SQLException when working with JDBC. The exception is thrown in almost all methods, but to get to the root of the problem is very difficult since SQLException does not tell us much about the problem (unless you catch the error code and write scores of if-then statements). Even if you manage to get the problem out of SQLException, it will always be specific to a particular persistence mechanism. Spring provides a hierarchy of Exceptions that cover almost all kinds of exceptions that a database can throw. Examples of these are – DataIntegrityViolationException, PermissionDeniedDataAccessException, CannotAcquireLockException etc. The other good thing about this exceptions is that they need not be caught. (unchecked exception) No matter what persistence mechanism you use, spring always throws this exception (how cool is that!)

Templates : Most of the code for database access is repetitive. Opening connection, closing connection, etc. Spring creates templates that handles most of this repetitive code. The developer uses this template which does most of the work and just fills in the logic specific to the application. He does not have to worry about managing connections or exceptions. Spring has template for various persistence frameworks such as plain JDBC, Hibernate, iBatis etc. Examples of Templates are jdbc.core.JdbcTemplate, orm.hibernate.HibernateTemplate etc

DataSource – No matter what technology is used, spring would require the connection info to the persistence mechanism. This connection info is specified in the form of datasource. The datasource needs to be injected in the template. The datasource may also be created by the application server, the developer then uses JNDI to access the datasource.

JdbcTemplate – The JdbcTemplate is the spring answer to plain JDBC access. It handles database connectivity. Users can specify the sql and a callback through its method. The template does the work of creating the connection and executing the query or update. The callback can be used to handle resultset (ResultSetExtractor) or create a prepared statement (PreparedStatementCreator)

RowMapper – RowMapper Interface is used by the JdbcTemplate to map a resultset row. The implementation of this interface maps a resultset row to a result object. The implementation does not have to worry about catching exceptions. Implementations must implement the method

T mapRow(ResultSet rs, int rowNum) throws SQLException; 

This is the JdbcTemplate method that uses it

public  List query(String sql, RowMapper rowMapper) throws DataAccessException

Sample Program Overview

The sample program demonstrates database connectivity using Spring. It uses a Data Access Object to perform database operations. The objective of the program is to use a PersonService to add and retrieve members of the Person class.

We will create the Person class with members as name and email.

We will create the PersonService class with members as personDao.

We will create the PersonDao class with members as jdbcTemplate.

We will create the PersonRowMapper class with members as .

We will create the PersonService class with members as personDao.

We will create the DbUtil class with members as dataSource.

We will also create the spring-config.xml.

Finally, we will test our setup using TestSpringJdbcTemplate class which will load Spring context and get a reference to PersonService class. We will use the personService class to add person and retrieve a list of person.

• cglib.jar
• commons-logging.jar
• hsqldb.jar
• log4j.jar
• org.springframework.aop.jar
• org.springframework.asm.jar
• org.springframework.beans.jar
• org.springframework.context.jar
• org.springframework.context.support.jar
• org.springframework.core.jar
• org.springframework.expression.jar
• org.springframework.jdbc.jar
• org.springframework.transaction.jar

Create the Person (see sample code below). This object will be saved and retrieved from database.

Create members name and email (see lines 5-6 below)

Create accessor methods for name and email (see lines 8-19 below).

Create the PersonDao (see sample code below). This represents the
Data Access Layer
through which data will be stored and retrieved from the database.

Create members jdbcTemplate (see line 9 below)

Create accessor methods for jdbcTemplate (see lines 11-17 below).

Create the insert() method to insert the Person class into the database (see lines 19 – 26 below).

Use the JdbcTemplate.update() method to execute the insert query while passing the name and email as parameters (see line 24 below).

Create the selectAll() method to fetch all the Persons in the database and use the PersonRowMapper to create the Person object from
ResultSet
(see lines 28 – 32 below).

Use the JdbcTemplate.query() method to execute the select query (see line 31 below).

This demonstrates the usage of
JdbcTemplate
from within the
Data Access Layer
.

Note: JdbcTemplate provides the templating mechanism to execute the query and the PersonDao does not need to open/close the database connections or directly deal with
Connection
object.


Create the PersonRowMapper which implements
RowMapper
interface(see sample code below). RowMapper is used to convert the
ResultSet
into domain specific object (in this case Person class).

Override the mapRow() method (see lines 11-16 below).

Get name and email data from
ResultSet
and use it to create Person object (see lines 12-15 below).

Create the PersonService (see sample code below).

Create members personDao (see line 7 below)

Create accessor methods for personDao (see lines 9-15 below).

Create addPerson() method and delegate the call to PersonDao.insert() to add the person in database (see lines 17-19 below).

Create fetchAllPersons() method and delegate the call to PersonDao.selectAll() to list all the persons in database (see lines 21-23 below).

Create the DbUtil (see sample code below). This class used only to create the necessary PERSON table in the database.

Create members dataSource (see line 11 below)

Create accessor methods for dataSource (see lines 13-19 below).

Create the initialize method and execute the ‘CREATE TABLE’ statement to create the PERSON table (see lines 21-32)

Create the spring-config.xml file (see below).

Declare personService bean which depends on personDao (see lines 15-17 below).

Declare personDao bean which depends on jdbcTemplate (see lines 19-21 below).

Declare jdbcTemplate bean which depends on dataSource (see lines 23-25 below).

Declare dataSource bean (see lines 27-32 below). We use Spring’s
DriverManagerDataSource
(see line 27 below) and provide database connection parameters like driverClassName, url, username and password (see lines 28-31 below).

These database parameters correspond to the HyperSQL in-memory database.

We declare the dbUtil bean which initializes the database by creating the PERSON table with NAME and EMAIL columns.

Finally, we need a java program to test our setup.This is done by TestSpringJdbcTemplate (see source code below).

We need to tell Spring framework to use the ‘spring-config.xml’ to load our beans (see line 13 below).

We get the reference to PersonService class through Spring using the bean name ‘personService’ (see line 14 below).

Create the Person object and set the name and email properties (see lines 16-18 below).

Use the PersonService.addPerson() method to add the Person object (see line 19 below).

Use the PersonService.fetchAllPersons() method to list the Persons added to the database. We access the list of Persons and print the output to verify that database operations of inserting and selecting using
JdbcTemplate
have occured successfully (see line 23 below).

This sample program has been packaged as a jar installer which will copy the source code (along with all necessary dependencies) on your machine and automatically run the program for you as shown in the steps below. To run the sample program, you only need Java Runtime Environment (JRE) on your machine and nothing else.

Save the springjdbctemplate-installer.jar on your machine

Execute/Run the jar using Java Runtime Environment

(Alternatively you can go the folder containing the springjdbctemplate-installer.jar and execute the jar using
java -jar springjdbctemplate-installer.jar
command)

You will see a wizard as shown below

Enter the location of the directory where you want the program to install and run (say, C:\Temp)

The installer will copy the program on your machine and automatically execute it. The expected output indicating that the program has run successfully on your machine is shown in the image below.

This source code for this program is downloaded in the folder specified by you (say, C:\Temp) as an eclipse project called
springjdbctemplate
. All the required libraries have also been downloaded and placed in the same location. You can open this project from Eclipe IDE and directly browse the source code. See below for details of the project structure.