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:

 

Related Spring and Database Tutorials:

 

Other Spring Tutorials:


About the Author:

is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.



Attachments:
Download this file (SpringJDBCTemplateExample.zip)SpringJDBCTemplateExample.zip[Eclipse-Maven project]13 kB

Add comment

   


Comments 

#7Nam2020-08-19 17:22
Hi amol,
Yes, you can read SQL statements from a text file and execute each one. You can read the file line by line. See examples: codejava.net/.../...
Quote
#6amol2020-08-19 12:17
thanks for the tutorial.
how can I refer a query from external file ?
Quote
#5Mahbub Junaidi2020-08-04 08:13
helpful and good project
Quote
#4umesh2019-05-20 13:03
helpful content spring jdbc template
Quote
#3john2017-08-25 05:21
good project, Why we write first extends keyword, why not implements?
please help me in this question.
Quote