Configuring Spring MVC JdbcTemplate with JNDI Data Source in Tomcat

In enterprise applications, data source is usually configured as JNDI resource at container level in order to take advantages of database connection pooling services provided by the container, e.g. Tomcat.

In addition, the data source is independent from the application itself and can be shared among other applications in the container. This tutorial shows you how to configure a JNDI data source in Tomcat and look up it in a Spring MVC application, by developing a sample application that displays a list of users from a MySQL database.

1. Database Setup

Execute the following MySQL script to create a database called usersdb and a table called users:

create database usersdb;

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `email` varchar(45) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

Insert some dummy data into the table users, and then we are going to code a Spring MVC application that displays a list of users from this table.

2. Configuring JNDI Data Source in Tomcat

To create a JNDI data source configuration in Tomcat, add a <Resource> entry to the context.xml file under Tomcat’s conf directory as follows:

<Resource 
	name="jdbc/UsersDB" 
	auth="Container" 
	type="javax.sql.DataSource"
	maxActive="100" 
	maxIdle="30" 
	maxWait="10000" 
	driverClassName="com.mysql.jdbc.Driver"
	url="jdbc:mysql://localhost:3306/usersDB" 
	username="root"
	password="secret" 
	/>

NOTES:

• Change the username and password according to yours.
• The attribute name=”jdbc/UsersDB” will be looked up by the Spring MVC application.
• For details about JNDI data source configuration in Tomcat, see the tutorial: Configuring JNDI DataSource for Database Connection Pooling in Tomcat

3. Writing Model Class

Create the User.java class to represent the table users with the following code:

package net.codejava.spring.model;

public class User {
	private int id;
	private String username;
	private String password;
	private String email;

	// getters and setters

}

Note that setter and getter methods are removed for brevity, so you must include them yourself.

4. Writing DAO Classes

Create the simple UserDAO interface as follows:

package net.codejava.spring.dao;

import java.util.List;

import net.codejava.spring.model.User;

public interface UserDAO {
	public List<User> list();
}

And code its implementation (UserDAOImpl.java):

package net.codejava.spring.dao;

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

import javax.sql.DataSource;

import net.codejava.spring.model.User;

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

public class UserDAOImpl implements UserDAO {
	private DataSource dataSource;
	
	public UserDAOImpl(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	@Override
	public List<User> list() {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "SELECT * from users";
		List<User> listUser = jdbcTemplate.query(sql, new RowMapper<User>() {

			@Override
			public User mapRow(ResultSet rs, int rowNumber) throws SQLException {
				User user = new User();
				user.setId(rs.getInt("user_id"));
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				return user;
			}
			
		});
		return listUser;
	}

}

NOTES:

• A DataSource instance is injected via constructor of the UserDAOImpl class.
• The list() method uses JdbcTemplate to query a list of users from database. For more details, see the tutorial: Spring MVC with JdbcTemplate Example

5. Referencing JNDI Data Source in Spring MVC application

Now, it’s time to see how to look up a JNDI Data Source in our Spring MVC application. For your convenience, we describe the lookup mechanism for both configuration approaches:

Configure JNDI Data Source using Java-based configuration:

Write the following method in your Java-based configuration class:

@Bean
public UserDAO getUserDao() throws NamingException {
	JndiTemplate jndiTemplate = new JndiTemplate();
	DataSource dataSource
		 	= (DataSource) jndiTemplate.lookup("java:comp/env/jdbc/UsersDB");
	return new UserDAOImpl(dataSource);
}

In this approach, a JndiTemplate is used to look up the JNDI data source specified by the given name. The prefix java:comp/env/ is required for looking up a JNDI name.

Configure JNDI Data Source using XML configuration:

Add the following beans declaration into your Spring context configuration file:

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="java:comp/env/jdbc/UsersDB"/>
</bean>

<bean id="userDao" class="net.codejava.spring.dao.UserDAOImpl">
    <constructor-arg>
        <ref bean="dataSource" />
    </constructor-arg>
</bean>

In this approach, a JndiObjectFactoryBean is declared and its attribute jndiName is set to the JNDI name of the data source. This FactoryBean returns a DataSource object which is injected into the userDao bean.

6. Writing Spring MVC Controller Class

Here’s source code of the Spring MVC controller class that handles access to the home page of the application:

package net.codejava.spring.controller;

import java.io.IOException;
import java.util.List;

import net.codejava.spring.dao.UserDAO;
import net.codejava.spring.model.User;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class HomeController {

	@Autowired
	private UserDAO userDao;
	
	@RequestMapping(value="/")
	public ModelAndView home() throws IOException{
		List<User> listUsers = userDao.list();
		ModelAndView model = new ModelAndView("home");
		model.addObject("userList", listUsers);
		return model;
	}
}

Spring will automatically inject an instance of the UserDAO interface into this controller. The implementation of UserDAO is declared either in Java-based configuration or XML configuration as described above.

The handler method retrieves a list of users through the userDao and then passes this list to the model in the view named “home” - whose actual view file (JSP) is described below. 

7. Writing View Page

The following JSP page (home.jsp) uses JSTL to iterate over the user list passed from the controller:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Home</title>
    </head>
    <body>
        <div align="center">
	        <h1>Contact List</h1>
        	<table border="1">
	        	<th>No</th>
	        	<th>Username</th>
	        	<th>Email</th>
	        	
				<c:forEach var="user" items="${userList}" varStatus="status">
	        	<tr>
	        		<td>${status.index + 1}</td>
					<td>${user.username}</td>
					<td>${user.email}</td>
							
	        	</tr>
				</c:forEach>	        	
        	</table>
        </div>
    </body>
</html>

8. Maven Dependencies Configuration

The following dependencies are used in the Maven project file (pom.xml):

<properties>
	<java.version>1.7</java.version>
	<spring.version>4.0.3.RELEASE</spring.version>
	<cglib.version>2.2.2</cglib.version>
</properties>

<dependencies>
	<!-- Spring core & mvc -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>${spring.version}</version>
	</dependency>

	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-webmvc</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-orm</artifactId>
		<version>${spring.version}</version>
		<type>jar</type>
		<scope>compile</scope>
	</dependency>

	<!-- CGLib for @Configuration -->
	<dependency>
		<groupId>cglib</groupId>
		<artifactId>cglib-nodep</artifactId>
		<version>${cglib.version}</version>
		<scope>runtime</scope>
	</dependency>


	<!-- Servlet Spec -->
	<dependency>
		<groupId>javax.servlet</groupId>
		<artifactId>javax.servlet-api</artifactId>
		<version>3.1.0</version>
		<scope>provided</scope>
	</dependency>
	<dependency>
		<groupId>javax.servlet.jsp</groupId>
		<artifactId>javax.servlet.jsp-api</artifactId>
		<version>2.3.1</version>
		<scope>provided</scope>
	</dependency>
	<dependency>
		<groupId>jstl</groupId>
		<artifactId>jstl</artifactId>
		<version>1.2</version>		
	</dependency>		
	
	<!-- MySQL JDBC Driver -->
	<dependency>
    	<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.30</version>		
	</dependency>
</dependencies>

9. Testing the Application

Here’s the screenshot captured when running the application under Tomcat on localhost:

For your convenience, we provide the sample application as Eclipse-Maven project with two versions: one for Java-based configuration and another for XML configuration.

Related Spring Tutorials:

• Spring JDBC Template Simple Example
• Spring MVC with JdbcTemplate Example
• Understand Spring Data JPA with Simple Example
• Understand the core of Spring framework
• Understand Spring MVC

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:

SpringMvcJndiDataSourceExample.zip	[Java-based configuration project]	22 kB
SpringMvcJndiDataSourceXML.zip	[XML-based configuration project]	20 kB