Concept Overview
The power of spring lies in its ability to converse freely with various other parts of the application. Spring provides classes and methods to perform functions on the database. It provides template classes that perform common database functions such as opening a connection, closing connection and managing exceptions. The only thing the user has to do then is provide the actual query and a callback to handle results. Read this tutorial for more details on this.
User can also use a prepared statement to specify a query. However SimpleJbdcTemplate allows specifying the parameters using ‘?’ and accessing the parameters using their order of occurrence, i.e. getString(1). NamedParameterJdbcTemplate class on the other hand allows use of named parameters.
Sample Program Overview
The sample program demonstrates the used of NamedParameterJdbcTemplate for retrieving a list of ‘Persons’ using a ‘PersonService’.
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 TestSpringJdbcTemplateNamedParams class which will load Spring context and get a reference to PersonService class. We will retrieve a person based on its name.
- 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 is the entity that 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 PersonService (see sample code below). This represents the service class which contains methods to add and fetch a Person from the database.
Create members personDao (see line 6 below)
Create accessor methods for personDao (see lines 6-12 below).
Create addPerson() and fetchPerson() methods to add a person to database and fetch the person from database based on his name (see lines 14-20 below). These methods delegate the calls to the PersonDao class.
Create the PersonDao (see sample code below). This is class that interacts with the data layer to perform insert and select operations.
It has a reference to
SimpleJdbcTemplate
using which it performs insert and update operations (see lines 11-19 below).
In particular, note the select() method: A map of parameters is created for ‘name’ (see lines 31-32 below).
In the select query, the named parameter ‘:name’ is used (note the colon ‘:’ symbol in the query to signify that named parameter is used (see line 34 below).
Finally the SimplJdbcTemplate.query() method is used to execute the query by passing the Map of parameters.
This demonstrates the mechanism of passing ‘Named Parameters’ using SimpleJdbcTemplate.
Create the PersonRowMapper (see sample code below). This class overrides the mapRow() method to convert JDBC
ResultSet
to Person entity (see lines 11-16 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 TestSpringJdbcTemplateNamedParams (see source code below).
We need to tell Spring framework to use the ‘spring-config.xml’ to load our beans (see line 11 below). We get the reference to PersonService class through Spring using the bean name ‘personService’ (see line 12 below).
Create the Person object and set the name and email properties (see lines 14-18 below).
Use the PersonService.addPerson() method to add the Person object (see line 19 below).
Use the PersonService.fetchPerson() method by passing the ‘name’ to query the database and display the result (see lines 22-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.
(Alternatively you can go the folder containing the springjdbctemplatenamedparams-installer.jar and execute the jar using
java -jar springjdbctemplatenamedparams-installer.jar
command)
This source code for this program is downloaded in the folder specified by you (say, C:\Temp) as an eclipse project called
springjdbctemplatenamedparams
. 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.
