17
Dec
09

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>
Advertisements

0 Responses to “Querying a Database using Spring JdbcTemplate”



  1. Leave a Comment

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 77 other followers

December 2009
S M T W T F S
« Nov   Jan »
 12345
6789101112
13141516171819
20212223242526
2728293031  

Blog Stats

  • 830,829 hits

%d bloggers like this: