Querying a Database using Spring JdbcTemplate

This page describes the process of querying data from a database using The Spring Framework’s JdbcTemplate and SimpleJdbcTemplate.

One of the first things that catches our eye when we start using the spring framework is the JdbcTemplate.

The following is the DDL for the table we will be using to run Queries. It holds movie Genre’s. Its a simple table with 2 columns.

CREATE TABLE IF NOT EXISTS `GENRE` (
  `GNR_I` int(11) NOT NULL AUTO_INCREMENT,
  `GNR_X` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`GNR_I`)
)

JdbcTemplate

(legacy) This class is considered legacy. If you want to take advantages of the Java 5 features then use SimpleJdbcTemplate in the next section.

The following code queries the Genre table.

package test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component("genreDataManager")
public class GenreDataManagerImpl implements GenreDataManager {
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	@Autowired
	@Required
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Genre> getAllGenre() {
		List genreList = this.jdbcTemplate.query(
			    "select gnr_i, gnr_x from GENRE",
			    new RowMapper() {
			        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			            Genre genre = new Genre();
			            genre.setId(rs.getInt(1));
			            genre.setName(rs.getString(2));
			            return genre;
			        }
			    });
		return genreList;
	}
}

SimpleJdbcTemplate

This class takes advantage of varargs and autoboxing, and exposing only the most commonly required operations in order to simplify JdbcTemplate usage. Use this class instead of the plain old JdbcTemplate.

The following is the same thing but using SimpleJdbcTemplate

package test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

@Component("genreDataManager")
public class GenreDataManagerImpl implements GenreDataManager {
	private SimpleJdbcTemplate jdbcTemplate;

	public SimpleJdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	@Autowired
	@Required
	public void setJdbcTemplate(SimpleJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Genre> getAllGenre() {
		List<Genre> genreList = this.jdbcTemplate.<Genre>query(
			    "select gnr_i, gnr_x from GENRE",
			    new ParameterizedRowMapper<Genre>() {
			        public Genre mapRow(ResultSet rs, int rowNum) throws SQLException {
			            Genre genre = new Genre();
			            genre.setId(rs.getInt(1));
			            genre.setName(rs.getString(2));
			            return genre;
			        }
			    });
		return genreList;
	}
}

ApplicationContext.xml

The following file defines the jdbcTemplate and datasource. With these minimal settings you can successfully query a database.

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-2.5.xsd


http://www.springframework.org/schema/context


http://www.springframework.org/schema/context/spring-context-2.5.xsd">

<!-- The following is used for the @Autowired and @Required, @Resource etc... java.sql.DriverManager -->
	<context:annotation-config/>
	<context:component-scan base-package="test"/>
	<!-- This is a quick and dirty way to setup a datasource -->
	<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="org.gjt.mm.mysql.Driver"/>
		<property name="url" value="dburl"/>
		<property name="username" value="dbusername"/>
		<property name="password" value="password"/>
	</bean>

	<bean name="jdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
		<constructor-arg><ref bean="dataSource"/></constructor-arg>
	</bean>
</beans>

About these ads