Spring makes it easy to work with JDBC through the use of JdbcTemplate and related classes in the org.springframework.jdbc.core and related packages. For an introductory tutorial for the basics of JdbcTemplate, see: Spring JDBC Template Simple Example. This tutorial goes further by demonstrating how to integrate JdbcTemplate in a Spring MVC application. The sample application in this tutorial manages a contact list that looks like this: The sample application is developed using the following pieces of software/technologies (of course you can use newer versions):
Java 7
Eclipse Kepler
Spring framework 4.0
JSTL 1.2
MySQL Database 5.5
Maven 3
1. Creating MySQL database
Execute the following MySQL script to create a database named contactdb and a table named contact:
create database contactdb;
CREATE TABLE `contact` (
`contact_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`email` varchar(45) NOT NULL,
`address` varchar(45) NOT NULL,
`telephone` varchar(45) NOT NULL,
PRIMARY KEY (`contact_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8
The model class - Contact.java - is pretty simple:
package net.codejava.spring.model;
public class Contact {
private int id;
private String name;
private String email;
private String address;
private String telephone;
public Contact() {
}
public Contact(String name, String email, String address, String telephone) {
this.name = name;
this.email = email;
this.address = address;
this.telephone = telephone;
}
// getters and setters
}
This class simply maps a row in the table contact to a plain old Java object (POJO) - Contact.
4. Coding DAO Classes
The ContactDAO interface defines methods for performing CRUD operations on the contact table:
package net.codejava.spring.dao;
import java.util.List;
import net.codejava.spring.model.Contact;
/**
* Defines DAO operations for the contact model.
* @author www.codejava.net
*
*/
public interface ContactDAO {
public void saveOrUpdate(Contact contact);
public void delete(int contactId);
public Contact get(int contactId);
public List<Contact> list();
}
And here is an implementation - ContactDAOImpl.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.Contact;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
/**
* An implementation of the ContactDAO interface.
* @author www.codejava.net
*
*/
public class ContactDAOImpl implements ContactDAO {
private JdbcTemplate jdbcTemplate;
public ContactDAOImpl(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void saveOrUpdate(Contact contact) {
// implementation details goes here...
}
@Override
public void delete(int contactId) {
// implementation details goes here...
}
@Override
public List<Contact> list() {
// implementation details goes here...
}
@Override
public Contact get(int contactId) {
// implementation details goes here...
}
}
Pay attention to the beginning section that declares a JdbcTemplate and a DataSource object is injected via the constructor:
private JdbcTemplate jdbcTemplate;
public ContactDAOImpl(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
Now, let’s look at implementation details of each method.Insert or update a new contact:
Note that if the contact object having ID greater than zero, update it; otherwise that is an insert.NOTE: For more code readability, you can use NamedParameterJdbcTemplate, instead of using question marks (?) as placeholders. Also you can use SimpleJdbcInsert class which is more convenient to use. Delete a contact:
public void delete(int contactId) {
String sql = "DELETE FROM contact WHERE contact_id=?";
jdbcTemplate.update(sql, contactId);
}
List all contact:
public List<Contact> list() {
String sql = "SELECT * FROM contact";
List<Contact> listContact = jdbcTemplate.query(sql, new RowMapper<Contact>() {
@Override
public Contact mapRow(ResultSet rs, int rowNum) throws SQLException {
Contact aContact = new Contact();
aContact.setId(rs.getInt("contact_id"));
aContact.setName(rs.getString("name"));
aContact.setEmail(rs.getString("email"));
aContact.setAddress(rs.getString("address"));
aContact.setTelephone(rs.getString("telephone"));
return aContact;
}
});
return listContact;
}
Notice the use of RowMapper to map a row in the result set to a POJO object. For more convenient, you can use the BeanPropertyRowMapper class like this:
public List<Contact> list() {
String sql = "SELECT * FROM Contact";
return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Contact.class));
}
Make sure that the Contact class declare the field names exactly match the column names in the database table. Using BeanPropertyRowMapper is more convenient, but performance is slower than using RowMapper. Get a particular contact:
public Contact get(int contactId) {
String sql = "SELECT * FROM contact WHERE contact_id=" + contactId;
return jdbcTemplate.query(sql, new ResultSetExtractor<Contact>() {
@Override
public Contact extractData(ResultSet rs) throws SQLException,
DataAccessException {
if (rs.next()) {
Contact contact = new Contact();
contact.setId(rs.getInt("contact_id"));
contact.setName(rs.getString("name"));
contact.setEmail(rs.getString("email"));
contact.setAddress(rs.getString("address"));
contact.setTelephone(rs.getString("telephone"));
return contact;
}
return null;
}
});
}
Notice the use of ResultSetExtractor to extract a single row as a POJO. You can also use the BeanPropertyRowMapper class like this:
public Contact get(int contactId) {
String sql = "SELECT * FROM Contact WHERE id=" + contactId;
return jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(Contact.class));
}
It greatly simplifies the code, but in return of slower performance.
5. Coding Spring MVC Configuration
Java-based classes and annotations are used to configure this Spring MVC application. Here’s code of the MvcConfiguration class:
package net.codejava.spring.config;
import javax.sql.DataSource;
import net.codejava.spring.dao.ContactDAO;
import net.codejava.spring.dao.ContactDAOImpl;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.web.servlet.ViewResolver;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
import org.springframework.web.servlet.view.InternalResourceViewResolver;
@Configuration
@ComponentScan(basePackages="net.codejava.spring")
@EnableWebMvc
public class MvcConfiguration extends WebMvcConfigurerAdapter{
@Bean
public ViewResolver getViewResolver(){
InternalResourceViewResolver resolver = new InternalResourceViewResolver();
resolver.setPrefix("/WEB-INF/views/");
resolver.setSuffix(".jsp");
return resolver;
}
@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
registry.addResourceHandler("/resources/**").addResourceLocations("/resources/");
}
@Bean
public DataSource getDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/contactdb");
dataSource.setUsername("root");
dataSource.setPassword("P@ssw0rd");
return dataSource;
}
@Bean
public ContactDAO getContactDAO() {
return new ContactDAOImpl(getDataSource());
}
}
Notice the getDataSource() method returns a configured DataSource bean. You may have to change the database URL, username and password according to your environments.The getContactDAO() method returns an implementation of the ContactDAO interface, which is the ContactDAOImpl class. This bean will be injected to the controller class, which is described below.
6. Configuring Spring MVC Dispatcher Servlet
And to enable Spring MVC for our Java web application, update the web deployment descriptor file (web.xml) as below:
public class HomeController {
@Autowired
private ContactDAO contactDAO;
// handler methods go here...
}
Notice we use the @Autowired annotation to let Spring inject an instance of the ContactDAO implementation into this controller automatically. Each handler method uses this contactDAO object to perform necessary CRUD operations. Let’s see implementation details of each method.Handler method for listing all contacts (also served as home page):
Notice that this JSP page uses Spring form tags to bind the values of the form to a model object.To test out the application, you can download the Eclipse project or deploy the attached WAR file at your convenience.You can also watch the video version of this tutorial below:
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.
The sample application is developed using the following pieces of software/technologies (of course you can use newer versions):
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.
SpringMvcJdbcTemplate.war
SpringMvcJdbcTemplate.zip
Comments