In this Spring tutorial, we discuss how to access relational database using JDBC with Spring framework. The example code is tested with Spring JDBC 4.0.3 and MySQL database server 5.5.23 using MySQL Connector Java 5.1.30 library. The sample project is built using Eclipse Kepler and Maven 3.0. Of course the code example will work with newer versions of Spring, JDBC and MySQL database.

 

1. Working with JDBC in Spring (JdbcTemplate)

Spring framework simplifies JDBC development through the use of JdbcTemplate class which helps programmers to avoid common errors, reduce boilerplate code to focus on writing SQL queries and extracting result set. To use JdbcTemplate, we must inject a DataSource reference either via constructor or setter when instantiating a new JdbcTemplate object. For example:

SimpleDriverDataSource dataSource = new SimpleDriverDataSource();

// code to set driver class name, database URL, username and password

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

Or setting the DataSource reference via setter:

JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource);

Then use variants of the JdbcTemplate’supdate() and query() methods for execute updates and queries. For example:

jdbcTemplate.update("INSERT INTO...");

List<T> results = jdbcTemplate.query("SELECT * FROM...", new RowMapper<T>() {...});

For querying for results, an implementation (typically an anonymous class) of the RowMapper interface must be provided in order to map a row from the result set to an object in the returned list. Let’s see the details by coding a sample project.

 

2. Setting up Database

Execute the following MySQL script to create a database called ‘contactdb’ with 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 DEFAULT CHARSET=utf8

 

3. Configuring Dependencies in Maven

Update your pom.xml file with two dependencies:

• Spring JDBC dependency:

  <dependency>
  	<groupId>org.springframework</groupId>
  	<artifactId>spring-jdbc</artifactId>
  	<version>4.0.3.RELEASE</version>
  </dependency>

• MySQL Connector Java dependency:

  <dependency>
  	<groupId>mysql</groupId>
  	<artifactId>mysql-connector-java</artifactId>
  	<version>5.1.30</version>
  </dependency>

 

4. Writing Model Class

Code a JavaBean class as follows:

package net.codejava.spring;

/**
 *
 * @author www.codejava.net
 *
 */
public class Contact {
	private String name;
	private String email;
	private String address;
	private String phone;

	// getters and setters are removed for brevity.

	public String toString() {
		return String.format("[%s - %s - %s - %s]", name, email, address, phone);
	}
}

This class simply maps a row in the contact table to a Java object.

 

5. CRUD Examples with JdbcTemplate

Here’s code of a sample program that demonstrates performing CRUD operations using JDBC with Spring JdbcTemplate:

package net.codejava.spring;

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

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

/**
 * This console application demonstrates how to do CRUD operations using JDBC
 * with Spring framework.
 * @author www.codejava.net
 *
 */
public class SpringJDBCTemplateExample {

	public static void main(String[] args) throws SQLException {
		SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
		dataSource.setDriver(new com.mysql.jdbc.Driver());
		dataSource.setUrl("jdbc:mysql://localhost/contactdb");
		dataSource.setUsername("root");
		dataSource.setPassword("P@ssw0rd");
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		String sqlInsert = "INSERT INTO contact (name, email, address, telephone)"
				+ " VALUES (?, ?, ?, ?)";
		jdbcTemplate.update(sqlInsert, "Tom", "tomea@mail.com", "USA", "12345");
		
		String sqlUpdate = "UPDATE contact set email=? where name=?";
		jdbcTemplate.update(sqlUpdate, "tomee@mail.com", "Tom");
		
		String sqlSelect = "SELECT * FROM contact";
		List<Contact> listContact = jdbcTemplate.query(sqlSelect, new RowMapper<Contact>() {

			public Contact mapRow(ResultSet result, int rowNum) throws SQLException {
				Contact contact = new Contact();
				contact.setName(result.getString("name"));
				contact.setEmail(result.getString("email"));
				contact.setAddress(result.getString("address"));
				contact.setPhone(result.getString("telephone"));
				
				return contact;
			}
			
		});
		
		for (Contact aContact : listContact) {
			System.out.println(aContact);
		}
		
		String sqlDelete = "DELETE FROM contact1 where name=?";
		jdbcTemplate.update(sqlDelete, "Tom");
	}

}

For executing a query that select all rows from the tabble and returns a list of domain model class objects, you can use the BeanPropertyRowMapper class greatly simplifies the code, for example:

String sql = "SELECT * FROM Contact";

List<Contact> listContacts = jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Contact.class));

As long as the Contact class has the fields exactly match the columns in the database table.

You can also use the BeanPropertyRowMapper class for a query that returns a single object, for example:

String sql = "SELECT * FROM Contact WHERE id=" + id;
Contact contact = jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(Contact.class));

 

NOTES:

• You should change the database URL, username and password according to settings on your environment.
• For executing SQL Insert statement, you can use the SimpleJdbcInsert class that greatly simplifies the code.
• The JdbcTemplate’s methods throw runtime DataAccessException, so here’s an example if you want to catch this exception explicitly:

  try {
  
  	String sqlDelete = "DELETE FROM contact where name=?";
  	jdbcTemplate.update(sqlDelete, "Tom");
  
  } catch (DataAccessException ex) {
  	ex.printStackTrace();
  }

For more code readability, you can use Spring NamedParameterJdbcTemplate, instead of using question marks (?) as placeholders.

And you can download the sample project in the Attachments section below.

You can also watch the video version of this tutorial:

 

References:

• Accessing Relational Data using JDBC with Spring
• JdbcTemplate Javadoc
• SimpleDriverDataSource Javadoc
• RowMapper interface Javadoc

 

Related Spring and Database Tutorials:

• Spring MVC with JdbcTemplate Example
• How to configure Spring MVC JdbcTemplate with JNDI Data Source in Tomcat
• Spring and Hibernate Integration Tutorial (XML Configuration)
• Understand Spring Data JPA with Simple Example
  
• Spring MVC + Spring Data JPA + Hibernate - CRUD Example

 

Other Spring Tutorials:

• Understand the core of Spring framework
• Understand Spring MVC
• Understand Spring AOP
• Spring MVC beginner tutorial with Spring Tool Suite IDE
• Spring MVC Form Handling Tutorial
• Spring MVC Form Validation Tutorial
• 14 Tips for Writing Spring MVC Controller
• Spring Web MVC Security Basic Example (XML Configuration)