Introduction to Spring JDBC Framework

SHARE & COMMENT :

Introduction

In this article, we will see how to interact with the database through JDBC APIs using Spring framework. Spring’s JDBC framework integration simplifies the task of resource management like closing connection, statement, resultset etc, complex exception handling, providing reusable object oriented access towards database operations.

Jdbc Template Usage

also read:

also read:

Jdbc Template is the core API in Spring’s JDBC Framework. This API provides facility for querying data from the database, performing inserts, deletes/updates and can also be used for extracting results. It hides all the low level exception handling and simplifies the access to database. In the following example, we will see how to use the JdbcTemplate class. The following class serves as an utility for the various examples that we will be seeing in the forth coming sections. The utility class provides a method that will return a JDBC Template object. The Jdbc Template class has to be instructed about the data source from which data. We have externalized the data source information in an external configuration file.
JdbcUtils.java

package net.javabeat.spring.jdbc.common;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class JdbcUtils {
	public static JdbcTemplate jdbcTempalte(){
		ApplicationContext context = new ClassPathXmlApplicationContext("main.xml");
		DriverManagerDataSource dataSource = (DriverManagerDataSource)context.getBean("mySqlDataSource");
		System.out.println("Data source is " + dataSource);

		JdbcTemplate template = new JdbcTemplate(dataSource);
		return template;
	}
}

Config File

In the above JdbcUtils class, we have referred the configuration file containing the details of the data source like the database URL, the class name of the driver with the username and the password information. Given below is the listing of the configuration file which points to My Sql database.
spring.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd">

	<beans>

		<bean id="mySqlDataSource" destroy-method="close">
			<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
			<property name="url" value="jdbc:mysql://localhost/test"/>
			<property name="username" value="root"/>
			<property name="password" value="<Password4Root>"/>
		</bean>

</beans>

Usage

Before looking into the Jdbc Template usage, we will have a look at the model objects that we will be using throughout the application. The model object are Book and Author and Book object has a reference to the Author object.The following code listing for the Author class. The author object owns two properties id and the name.
Author.java

package net.javabeat.spring.jdbc.model;

public class Author {

	private Integer id;
	private String name;

	public Author(){
		this(-1, null);
	}

	public Author(Integer id){
		this(id, null);
	}

	public Author(Integer id, String name){
		this.id = id;
		this.name = name;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String toString(){
		StringBuilder result = new StringBuilder();
		result.append("[Id = " + id + ", Name = " + name + "]");
		return result.toString();
	}
}

And here is the definition of the book object. Other than the primitive properties id and name, the book class contains a reference to Author object.
Book.java

package net.javabeat.spring.jdbc.model;

public class Book {

	private Integer id;
	private String name;
	private Integer authorId;
	private Author author;

	public Author getAuthor() {
		return author;
	}

	public void setAuthor(Author author) {
		this.author = author;
	}

	public Integer getAuthorId() {
		return authorId;
	}

	public void setAuthorId(Integer authorId) {
		this.authorId = authorId;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String toString(){

		StringBuilder result = new StringBuilder();
		result.append("[Id = " + id + ", Name = " + name + ", Author Id = " + authorId + ", Author = " + author + "]");
		return result.toString();
	}
}

Coming back to the usage of the Jdbc Template usage, we will see how to perform select/insert/delete/update operations to author and book objects. Have a look at the following code. In the main method, we have acquired an instance of JdbcTemplate by calling the JdbcUtils.jdbcTemplate() and this instance is passed on to different methods for performing the various database operations. To perform a select query operation, we have called the method queryForList() defined on JdbcTemplate instance by passing in an Sql select query. Since there is always a possibility that the number of records returned will be more than one, the returned result is stored in a list. For insert/update/delete operations, which all comes under of the category of update operations. So we have used the update() method defined on JdbcTemplate by passing in the appropriate sql queries. Note that the second parameter for update() method would be list of parameters being passed in to the query.
JdbcTemplateTest.java

package net.javabeat.spring.jdbc.template;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcTemplateTest {

	public static void main(String[] args) {

		JdbcTemplate template = net.javabeat.spring.jdbc.common.JdbcUtils.jdbcTempalte();

		select(template);
		insert(template);
		update(template);
		delete(template);
	}

	private static void select(JdbcTemplate template){

		String query = "select * from author";
		List<?> list = template.queryForList(query);
		for (Object object : list){
			System.out.println("Object is " + object);
		}
	}

	private static void insert(JdbcTemplate template){

		String query = "insert into author (id, name) values (?, ?)";
		template.update(query, new Object[]{4, "Test Author"});
	}

	private static void update(JdbcTemplate template){

		String query = "update author set name = ? where id = ?";
		template.update(query, new Object[]{"Test Author 2", 4});
	}

	private static void delete(JdbcTemplate template){

		String query = "delete from author where id = ?";
		template.update(query, new Object[]{4});
	}
}

Row Mappers

In this section, we will see what are Row Mappers and how they are used. The traditional way for dealing with results from a query through JDBC looks like the following,

ResultSet resultSet = ...;
while (resultSet.hasNext()){
	Integer id = resultSet.getInt("id");
	String name = resultSet.getString("name");
}

Say for example, when we are selecting records from an employee table, we will iterate over the result set to get the individual values which won’t be ideal for situations, especially in Java where we want to map records from a database to individual Java objects. Also the question of re-usability comes into the picture as the above code doesn’t represent for getting itself re-used. Spring Row Mapper interfaces come into the rescue for such situations.

Author Row Mapper

We want to map the results from the Author table to individual Author Java objects. Have a look at the following code. We have written the class AuthorRowMapper that implements the RowMapper interface and the method mapRow() is overridden.
AuthorRowMapper.java

package net.javabeat.spring.jdbc.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import net.javabeat.spring.jdbc.model.Author;

import org.springframework.jdbc.core.RowMapper;

public class AuthorRowMapper implements RowMapper{

	public Author mapRow(ResultSet resultSet, int rowNumber) throws SQLException {

		Integer id = resultSet.getInt("id");
		String name = resultSet.getString("name");

		Author author = new Author();
		author.setId(id);
		author.setName(name);
		return author;
	}
}

The method mapRow() will be called as part of sql select opertation done on the Author table for each record. The first parameter representing the ResultSet points to the current row and the second parameter is the row number. Within the implementation of mapRow() method, we create an instance of Author object, populate the Author object with information taken from the ResultSet object object and return object. The client code that makes use of this implementation is shown later.

Book Row Mapper

The below code provides the implementation for mapping Book objects. However some additional work has to be done there because Book has a reference to the Author object and the Author object also needs to be populated during the time of populating the Book object.
BookRowMapper.java

package net.javabeat.spring.jdbc.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import net.javabeat.spring.jdbc.model.Author;
import net.javabeat.spring.jdbc.model.Book;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class BookRowMapper implements RowMapper{

	private JdbcTemplate template;

	public BookRowMapper(JdbcTemplate template){
		this.template = template;
	}

	public Book mapRow(ResultSet resultSet, int rowNumber) throws SQLException {

		Integer id = resultSet.getInt("id");
		String name = resultSet.getString("name");
		Integer authorId = resultSet.getInt("author_id");

		String authorQuery = "select id, name from author where id = ?";
		AuthorRowMapper authorRowMapper = new AuthorRowMapper();
		Author author = (Author)template.queryForObject(
		authorQuery, new Object[]{new Integer(authorId)}, authorRowMapper);

		Book book = new Book();
		book.setId(id);
		book.setName(name);
		book.setAuthorId(authorId);
		book.setAuthor(author);

		return book;
	}
}

And again the method mapRow() will be called for every Book records for the Book table in the database. We have extracted the primitive properties id, name and the author id from the ResultSet object. After that, using the JdbcTemplate we have invoked the queryForObject() which takes the select query for the author for the given author id. The second parameter passed to this method is an instance of AuthorRowMapper which we have already created. Now, when the select query has returned a single result, the ResultSet object along with the row number will be passed on to AuthorRowMapper.mapRow() method which will return an instance of Author object. So, rather than dealing with primitive properties from a ResultSet, now we have got the desired object through the use of RowMapper interface. Finally the Book object is created, populated with the primitive data taken from the ResultSet object along with the Author object that we have just obtained directly from the Author table with the help of AuthorRowMapper interface.

Usage of Row Mapper

We have already seen the usage of Row Mapper interfaces within the implementation of BookRowMapper. The following example shows how to see the usage of RowMapper interfaces that can be used with queryForObject() which returns a single object and query() which returns a list of objects defined on JdbcTemplate.
RowMapperTest.java

package net.javabeat.spring.jdbc.mapper;

import java.util.List;

import net.javabeat.spring.jdbc.model.Author;
import net.javabeat.spring.jdbc.model.Book;

import org.springframework.jdbc.core.JdbcTemplate;

public class RowMapperTest {

	public static void main(String[] args) {

		JdbcTemplate template = net.javabeat.spring.jdbc.common.JdbcUtils.jdbcTempalte();
		forAuthor(template);
		forBook(template);
	}

	@SuppressWarnings("unchecked")
	private static void forAuthor(JdbcTemplate template){

		AuthorRowMapper authorRowMapper = new AuthorRowMapper();

		String singleResultQuery = "select id, name from author where id = ?";
		Author author = (Author)template.queryForObject(singleResultQuery, new Object[]{new Integer(1)}, authorRowMapper);
		System.out.println("Author is " + author);

		String multiResultQuery = "select * from author";
		List authors = (List)template.query(multiResultQuery, authorRowMapper);
		for (Author anAuthor : authors){
			System.out.println("Author is " + anAuthor);
		}
	}

	private static void forBook(JdbcTemplate template){

		BookRowMapper bookRowMapper = new BookRowMapper(template);

		String query = "select id, name, author_id from book where id = ?";
		Book book = (Book)template.queryForObject(query, new Object[]{new Integer(1)}, bookRowMapper);
		System.out.println("Book is " + book);
	}
}

Callbacks for Prepared Statement

In this section, we will see how to reuse and to provide an object oriented access for operations that will involve Prepared Statements. Even though most of the low level details related to JDBC API access such as Connections, Statements, ResultSets etc are hidden, there may be still a situation where the client code want to intercept the calling of the underlying APIs. For such situations Spring provides hook in the form of callbacks.

Usage of Prepared Statement Creator

The interface PreparedStatementCreator allows the client code to create Prepared Statement instances which represent the pre-compiled version of the sql query passed. Have a look at the following code for the usage of Prepared Statement Creator. Let us assume that we want to select the author object based on author id. Because for every select operation, the author id will be changing, we will pre-compile this query using PreparedStatement as follows.
AuthorPreparedStatementCreator.java

package net.javabeat.spring.jdbc.callback;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import net.javabeat.spring.jdbc.model.Author;

import org.springframework.jdbc.core.PreparedStatementCreator;

public class AuthorPreparedStatementCreator implements PreparedStatementCreator{

	private static String SELECT_QUERY = "SELECT * FROM AUTHOR WHERE ID = ?";

	private Author author;

	public AuthorPreparedStatementCreator(Author author){
		this.author = author;
	}

	public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

		PreparedStatement ps = connection.prepareStatement(SELECT_QUERY);
		ps.setInt(1, author.getId());
		return ps;
	}
}

The above class implements the PreparedStatementCreator interfaces overriding the createPreparedStatement() method. Note that this method returns a PreparedStatement object with the Sql select query representing the selection of the author that we have already declared. Because the query will include the author id parameter, as part of construction, an author object is sent containing the author id. We will see the usage of this class in the later section.

Usage of Prepared Statement Setter

From the name itself, one can tell that this interface is useful for setting the values on the PreparedStatement object and the object can be reused across multiple method invocations.
AuthorPreparedStatementSetter.java

package net.javabeat.spring.jdbc.callback;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import net.javabeat.spring.jdbc.model.Author;

import org.springframework.jdbc.core.PreparedStatementSetter;

public class AuthorPreparedStatementSetter implements PreparedStatementSetter{

	private Author author;

	public AuthorPreparedStatementSetter(Author author){
		this.author = author;
	}

	public void setValues(PreparedStatement ps) throws SQLException {
		ps.setInt(1, author.getId());
		ps.setString(2, author.getName());
	}
}

Note that in the above class, the method setValues() is overridden for setting the values on the PreparedStatement object with the values taken from the Author object.

Usage

Now its time to see how to use the above custom class that we have already written, which creates prepared statements and sets values on the prepared statement.
CallbackTest.java

package net.javabeat.spring.jdbc.callback;

import java.util.List;

import net.javabeat.spring.jdbc.common.JdbcUtils;
import net.javabeat.spring.jdbc.mapper.AuthorRowMapper;
import net.javabeat.spring.jdbc.model.Author;

import org.springframework.jdbc.core.JdbcTemplate;

public class CallbackTest {

	public static void main(String[] args) {

		JdbcTemplate template = JdbcUtils.jdbcTempalte();
		testPSSetter(template);
		testPSCreator(template);
	}

	private static void testPSSetter(JdbcTemplate template){

		Author author = new Author(5, "New Author");
		AuthorPreparedStatementSetter setter = new AuthorPreparedStatementSetter(author);
		template.update("INSERT INTO AUTHOR (ID, NAME) VALUES (?, ?)", setter);
	}

	@SuppressWarnings("unchecked")
	private static void testPSCreator(JdbcTemplate template){

		Author author = new Author(2);
		AuthorPreparedStatementCreator creator = new AuthorPreparedStatementCreator(author);

		List authors = template.query(creator, new AuthorRowMapper());
		for (Author authorObject : authors){
			System.out.println("Author is " + authorObject);
		}
	}
}

The method testPSSetter() makes use of the customized form of Prepared Statement Setter. In the client code, we have called the update() method defined on the JdbcTemplate by passing in the insert query as well as the Prepared Statement Setter object. Note that while constructing the Prepared Statement Setter object, the author object whose values has to be inserted is passed to the constructor. So the insert operation on the Author table will happen with the placeholders getting replaced with the values from the Author object.
Similarly for the method testPSCreator(), we have called the method query() defined on JdbcTemplate by passing the Prepared Statement Creator as well as the author row mapper object. Note that the Prepared Statement Creator object that we have written selects information from the Author table based on Author id. The author id is passed as part of Author object while constructing the Prepared Statement Creator object. Because we have also passed an instance of AuthorRowMapper object, we can retrieve the results as a list of Author objects.

Modeling query operations as objects

In the final section of this article, we will see how to model sql operations as objects.

Select Query

There are numerous ways for fetching data from the database and this is one way of doing it. For select query operations, the interface MappingSqlQuery is available, which represents a reusable query operation object and that can be also used to map the return result from the database into a Java object. Have a look at the Java code.
BookNameSelectQuery.java

package net.javabeat.spring.jdbc.operation;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import net.javabeat.spring.jdbc.mapper.BookRowMapper;
import net.javabeat.spring.jdbc.model.Book;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;

	public class BookNameSelectQuery extends MappingSqlQuery{

	private static final String SELECT_QUERY = "SELECT * FROM BOOK where name = ?";

	private JdbcTemplate template;

	public BookNameSelectQuery(JdbcTemplate template){

		super(template.getDataSource(), SELECT_QUERY);
		this.template = template;
		declareParameter(new SqlParameter("name", Types.VARCHAR));
		compile();
	}

	@Override
	protected Book mapRow(ResultSet rs, int row) throws SQLException {
		BookRowMapper mapper = new BookRowMapper(template);
		return mapper.mapRow(rs, row);
	}
}

In the above code, we have modeled the query operation that selects data from the Book table based on the Book id as a reusable object. The above class which extends the MappingSqlQuery class passes the reusable query and the database in the constructor. Since the query includes a dynamic parameter in the form of a placeholder it declares the parameter by calling the declareParameter() method by passing in the appropriate Sql parameter object. Later on, once the declarations are done, the sql query is compiled by calling the compile() method. Again, we have reused the BookRowMapper object in the mapRow() method by simply delegating the call to BookRowMapper.mapRow() method.

Insert Query

The following example shows how to write a reusable insert query for inserting an author object. Since the insert operation is a flavour of update operation, the below class extends the SqlUpdate class.
AuthorInsertQuery.java

package net.javabeat.spring.jdbc.operation;

import java.sql.Types;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

public class AuthorInsertQuery extends SqlUpdate{

	private static final String INSERT_QUERY = "INSERT INTO AUTHOR (ID, NAME) VALUES (?, ?)";

	public AuthorInsertQuery(JdbcTemplate template){

		super(template.getDataSource(), INSERT_QUERY);
		declareParameter(new SqlParameter("ID", Types.INTEGER));
		declareParameter(new SqlParameter("NAME", Types.VARCHAR));
		compile();
	}
}

The implementation of the above class looks somewhat similar to the above class, here the insert query contains the place holders for two parameters and correspondingly two calls are made to declareParameter() by passing in the appropriate Sql Parameter objects.

Update Query

The below class which represents a reusable update query for updating the author name resembles very similar to the above classes that we have written.
AuthorNameUpdateQuery.java

package net.javabeat.spring.jdbc.operation;

import java.sql.Types;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

public class AuthorNameUpdateQuery extends SqlUpdate{

	private static final String UPDATE_QUERY = "UPDATE AUTHOR SET NAME = ? WHERE ID = ?";

	public AuthorNameUpdateQuery(JdbcTemplate template){

		super(template.getDataSource(), UPDATE_QUERY);
		declareParameter(new SqlParameter("NAME", Types.VARCHAR));
		declareParameter(new SqlParameter("ID", Types.INTEGER));
		compile();
	}
}

Delete Query

The below class which represents a reusable delete query for deleting the author object based on the author id follows the similar standards of the classes that we have written previously.
AuthorDeleteQuery.java

package net.javabeat.spring.jdbc.operation;

import java.sql.Types;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

public class AuthorDeleteQuery extends SqlUpdate{

	private static final String DELETE_QUERY = "DELETE FROM AUTHOR WHERE ID = ?";

	public AuthorDeleteQuery(JdbcTemplate template){

		super(template.getDataSource(), DELETE_QUERY);
		declareParameter(new SqlParameter("ID", Types.INTEGER));
		compile();
	}

}

Testing Modeling Sql operations as objects

Now that we have written the above class representing the reusable select/insert/update and delete operations, its time to see the usage of those classes. In the following class, we have used the BookNameSelectQuery object as part of query() method by passing the book name to be selected. For AuthorInsertQuery, AuthorDeleteQuery and AuthorNameUpdateQuery operations, the respective objects are passed to the update() method defined on JdbcTemplate object along with the desired parameters.
OperationTest.java

package net.javabeat.spring.jdbc.operation;

import java.util.List;
import net.javabeat.spring.jdbc.common.JdbcUtils;
import net.javabeat.spring.jdbc.model.Book;

import org.springframework.jdbc.core.JdbcTemplate;

public class OperationTest {

	public static void main(String[] args) {

		JdbcTemplate template = JdbcUtils.jdbcTempalte();

		queryOperation(template);
		updateOperation(template);
		insertOperation(template);
		deleteOperation(template);
	}

	@SuppressWarnings("unchecked")
	private static void queryOperation(JdbcTemplate template){

		BookNameSelectQuery query = new BookNameSelectQuery(template);

		List books = query.execute(new Object[]{"Angry Letters"});
		for (Book book : books){
			System.out.println("Book is " + book);
		}
	}

	private static void updateOperation(JdbcTemplate template){

		AuthorNameUpdateQuery query = new AuthorNameUpdateQuery(template);
		query.update(new Object[]{"Bill Clinton1", 3});
	}

	private static void insertOperation(JdbcTemplate template){

		AuthorInsertQuery query = new AuthorInsertQuery(template);
		query.update(new Object[]{6, "David"});
	}

	private static void deleteOperation(JdbcTemplate template){

		AuthorDeleteQuery query = new AuthorDeleteQuery(template);
		query.update(new Object[]{6});
	}

}

Conclusion

In this article, we have shown how to use the core classes for performing common database operations as well as complicated operations that comes as part of Spring’s JDBC framework. Starting from JdbcTemplate class, which is the core, we have seen have to use Row Mapper objects that can directly map a row in a table to a Java object. We have also seen Spring’s capability of intercepting underlying JDBC API’s through PreparedStatementCreator and PreparedStatementSetter interfaces. Finally this article covered representing sql operations as objects. Hope the readers found this article useful in have understood that they now know how to handle data-access operations and strategies in real-time applications.

also read:

Comments

comments

About Krishna Srinivasan

He is Founder and Chief Editor of JavaBeat. He has more than 8+ years of experience on developing Web applications. He writes about Spring, DOJO, JSF, Hibernate and many other emerging technologies in this blog.

Speak Your Mind

*

Close
Please support the site
By clicking any of these buttons you help our site to get better