Concept Overview

Spring provides convenient classes to perform functions on the database. It handles creating a connection to a database, performing clean up and handling exceptions. The user creates a datasource and injects it into a jdbctemplate. The jdbctemplate is then injected into the spring Dao. The user can also inject a datasource directly into the Dao. The Dao is an abstract class and the user extends this class to create his own Dao. The advantage of this class is that the user does not have to inject the JdbcTemplate into all of his DAO classes. The user creates a common Dao class that can be extended by all the DAO classes. Spring provides two DAO classes JdbcDaoSupport and NamedParameterJdbcDaoSupport. There is a third class called SimpleJdbcDaoSupport but this is now deprecated in favor of JdbcDaoSupport and NamedParameterJdbcDaoSupport

Sample Program Overview

The sample program demonstrates the use of Spring JDBC Data Access Object.

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

We will create the PersonDao class with members as .

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 TestSpringJdbcDaoSupport class which will load Spring context and get a reference to PersonService class.

• 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.

Note that PersonDao extends from JdbcDaoSupport (see line 7 below). JdbcDaoSupport internally holds a reference to JdbcTemplate.

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

Use the JdbcTemplate.update() method to execute the insert query while passing the name and email as parameters (see line 15 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 19 – 23 below).

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

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

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). Note that PersonDao extends from JdbcDaoSupport which holds reference to JdbcTemplate.

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 TestSpringJdbcDaoSupport (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
JdbcDaoSupport
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 springjdbcdaosupport-installer.jar on your machine

Execute/Run the jar using Java Runtime Environment

(Alternatively you can go the folder containing the springjdbcdaosupport-installer.jar and execute the jar using
java -jar springjdbcdaosupport-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
springjdbcdaosupport
. 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.