Advertisements
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 theconfiguration 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" class="org.springframework.jdbc.datasource.DriverManagerDataSource" 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/deleteoperations, 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<Author> authors = (List<Author>)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);
}
}
