Tutorial

Spring JdbcTemplate Example

Published on August 3, 2022
author

Pankaj

Spring JdbcTemplate Example

Spring JdbcTemplate is the most important class in Spring JDBC package.

Spring JdbcTemplate

  • JDBC produces a lot of boiler plate code, such as opening/closing a connection to a database, handling sql exceptions etc. It makes the code extremely cumbersome and difficult to read.
  • Implementing JDBC in the Spring Framework takes care of working with many low-level operations (opening/closing connections, executing SQL queries, etc.).
  • Thanks to this, when working with the database in the Spring Framework, we only need to define the connection parameters from the database and register the SQL query, the rest of the work for us is performed by Spring.
  • JDBC in Spring has several classes (several approaches) for interacting with the database. The most common of these is using the JdbcTemplate class. This is the base class that manages the processing of all events and database connections.
  • The JdbcTemplate class executes SQL queries, iterates over the ResultSet, and retrieves the called values, updates the instructions and procedure calls, “catches” the exceptions, and translates them into the exceptions defined in the org.springframwork.dao package.
  • Instances of the JdbcTemplate class are thread-safe. This means that by configuring a single instance of the JdbcTemplate class, we can then use it for several DAO objects.
  • When using JdbcTemplate, most often, it is configured in the Spring configuration file. After that, it is implemented using bean in DAO classes.

Spring JdbcTemplate Example

Let’s look at Spring JdbcTemplate example program. I am using Postgresql database here, but you can use any other relational database too, such as MySQL and Oracle. All you need is to change the database configurations and it should work. First of all we need some sample data to work on. Below SQL queries will create a table and populate it with some data for us to use.

create table people (
id serial not null primary key,
first_name varchar(20) not null,
last_name varchar(20) not null,
age integer not null
);

insert into people (id, first_name, last_name, age) values
(1, 'Vlad', 'Boyarskiy', 21),
(2,'Oksi', ' Bahatskaya', 30),
(3,'Vadim', ' Vadimich', 32);

Below image shows the final project structure in Eclipse. Spring JdbcTemplate Example

Spring JDBC Maven Dependencies

We need following dependencies - spring-core, spring-context, spring-jdbc and postgresql. If you are using any other relational database such as MySQL, then add it’s corresponding java driver dependencies. Here is our final pom.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.journaldev.spring</groupId>
	<artifactId>JdbcTemplate</artifactId>
	<version>1.0-SNAPSHOT</version>
	<properties>
		<spring.framework>4.3.0.RELEASE</spring.framework>
		<postgres.version>42.1.4</postgres.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<version>${postgres.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.framework}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.framework}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.framework}</version>
		</dependency>
	</dependencies>

</project>

Spring DataSource Configuration

Next step is to create spring configuration class to define DataSource bean. I am using java based configuration, you can also do this using spring bean configuration xml file.

package com.journaldev.spring.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
@ComponentScan("com.journaldev.spring")
@PropertySource("classpath:database.properties")
public class AppConfig {

	@Autowired
	Environment environment;

	private final String URL = "url";
	private final String USER = "dbuser";
	private final String DRIVER = "driver";
	private final String PASSWORD = "dbpassword";

	@Bean
	DataSource dataSource() {
		DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
		driverManagerDataSource.setUrl(environment.getProperty(URL));
		driverManagerDataSource.setUsername(environment.getProperty(USER));
		driverManagerDataSource.setPassword(environment.getProperty(PASSWORD));
		driverManagerDataSource.setDriverClassName(environment.getProperty(DRIVER));
		return driverManagerDataSource;
	}
}
  • @Configuration – says that this class is configuration for Spring context.
  • @ComponentScan(“com.journaldev.spring”)- specifies the package to scan for component classes.
  • @PropertySource(“classpath:database.properties”)- says that properties will be read from database.properties file.

Content of database.properties file is shown below.

driver=org.postgresql.Driver
url=jdbc:postgresql://127.0.0.1:5432/school
dbuser=postgres
dbpassword=postgres

If you are using MySQL or some other relational database, change above configurations accordingly.

Spring JDBC Model Classes

Next step is to create model classes to map our database table.

package com.journaldev.model;

public class Person {
	private Long id;
	private Integer age;
	private String firstName;
	private String lastName;

	public Person() {
	}

	public Person(Long id, Integer age, String firstName, String lastName) {
		this.id = id;
		this.age = age;
		this.firstName = firstName;
		this.lastName = lastName;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	@Override
	public String toString() {
		return "Person{" + "id=" + id + ", age=" + age + ", firstName='" + firstName + '\'' + ", lastName='" + lastName
				+ '\'' + '}';
	}
}

For fetching data from database we need to implement interface RowMapper. This interface has only one method mapRow(ResultSet resultSet, int i), which will return one instance of our model class (i.e. Person).

package com.journaldev.model;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class PersonMapper implements RowMapper<Person> {

	public Person mapRow(ResultSet resultSet, int i) throws SQLException {

		Person person = new Person();
		person.setId(resultSet.getLong("id"));
		person.setFirstName(resultSet.getString("first_name"));
		person.setLastName(resultSet.getString("last_name"));
		person.setAge(resultSet.getInt("age"));
		return person;
	}
}

Spring JDBC DAO Classes

Final step is to create DAO classes to map our model class to database table using sql queries. We will also configure DataSource using @Autowired annotation and expose some APIs.

package com.journaldev.spring.dao;

import java.util.List;

import com.journaldev.model.Person;

public interface PersonDAO {
	Person getPersonById(Long id);

	List<Person> getAllPersons();

	boolean deletePerson(Person person);

	boolean updatePerson(Person person);

	boolean createPerson(Person person);
}
package com.journaldev.spring.dao;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.journaldev.model.Person;
import com.journaldev.model.PersonMapper;

@Component
public class PersonDAOImpl implements PersonDAO {

	JdbcTemplate jdbcTemplate;

	private final String SQL_FIND_PERSON = "select * from people where id = ?";
	private final String SQL_DELETE_PERSON = "delete from people where id = ?";
	private final String SQL_UPDATE_PERSON = "update people set first_name = ?, last_name = ?, age  = ? where id = ?";
	private final String SQL_GET_ALL = "select * from people";
	private final String SQL_INSERT_PERSON = "insert into people(id, first_name, last_name, age) values(?,?,?,?)";

	@Autowired
	public PersonDAOImpl(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public Person getPersonById(Long id) {
		return jdbcTemplate.queryForObject(SQL_FIND_PERSON, new Object[] { id }, new PersonMapper());
	}

	public List<Person> getAllPersons() {
		return jdbcTemplate.query(SQL_GET_ALL, new PersonMapper());
	}

	public boolean deletePerson(Person person) {
		return jdbcTemplate.update(SQL_DELETE_PERSON, person.getId()) > 0;
	}

	public boolean updatePerson(Person person) {
		return jdbcTemplate.update(SQL_UPDATE_PERSON, person.getFirstName(), person.getLastName(), person.getAge(),
				person.getId()) > 0;
	}

	public boolean createPerson(Person person) {
		return jdbcTemplate.update(SQL_INSERT_PERSON, person.getId(), person.getFirstName(), person.getLastName(),
				person.getAge()) > 0;
	}
}

PersonDAOImpl class is annotated with @Component annotation and in this class we have field with type JdbcTemplate. When constructor of this class will be invoked, an instance of DataSource will be injected into it and we can create an instance of JdbcTemplate. After that we can use in in our methods.

Spring JdbcTemplate Test Program

Our Spring JdbcTemplate example project is ready, let’s test this with a test class.

package com.journaldev;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.journaldev.model.Person;
import com.journaldev.spring.config.AppConfig;
import com.journaldev.spring.dao.PersonDAO;

public class Main {
	public static void main(String[] args) {
		AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);

		PersonDAO personDAO = context.getBean(PersonDAO.class);

		System.out.println("List of person is:");

		for (Person p : personDAO.getAllPersons()) {
			System.out.println(p);
		}

		System.out.println("\nGet person with ID 2");

		Person personById = personDAO.getPersonById(2L);
		System.out.println(personById);

		System.out.println("\nCreating person: ");
		Person person = new Person(4L, 36, "Sergey", "Emets");
		System.out.println(person);
		personDAO.createPerson(person);
		System.out.println("\nList of person is:");

		for (Person p : personDAO.getAllPersons()) {
			System.out.println(p);
		}

		System.out.println("\nDeleting person with ID 2");
		personDAO.deletePerson(personById);

		System.out.println("\nUpdate person with ID 4");

		Person pperson = personDAO.getPersonById(4L);
		pperson.setLastName("CHANGED");
		personDAO.updatePerson(pperson);

		System.out.println("\nList of person is:");
		for (Person p : personDAO.getAllPersons()) {
			System.out.println(p);
		}

		context.close();
	}
}

Below image shows the output produced when we execute above program. Output will vary based on sample data and on multiple executions, idea is to learn here how to use Spring JdbcTemplate through example program. Spring JDBC, Spring JdbcTemplate Example That’s all about Spring JdbcTemplate, you can download the final project from below link.

Download Spring JdbcTemplate Example Project

Reference: API Doc

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the authors
Default avatar
Pankaj

author

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
JournalDev
DigitalOcean Employee
DigitalOcean Employee badge
January 25, 2021

Here Environment environment; means ? iam not not understanding that line?

- vamshi

    JournalDev
    DigitalOcean Employee
    DigitalOcean Employee badge
    June 5, 2020

    Great guide! Could you explain how I can setup another Embed Datasource which is used for my Test classes? Greetings from Germany Christian

    - Christian

      JournalDev
      DigitalOcean Employee
      DigitalOcean Employee badge
      September 16, 2019

      Getting ERROR: malformed array literal: “” Detail: Array value must start with “{” or dimension information. for queryForObject.

      - Jayash

        JournalDev
        DigitalOcean Employee
        DigitalOcean Employee badge
        September 11, 2019

        Great Example, very well explained

        - Juan

          JournalDev
          DigitalOcean Employee
          DigitalOcean Employee badge
          November 26, 2018

          Hello. If I have two classes, Person and Auto, for example. And I need to make two separate classes, two separate dataSources in each class DAO implementation, like PersonDAO and AutoDAO. And then I need to make a Transaction between these two classes ( some method in Person binder with some method in Auto, and if one method failed, another method shouldn’t update any information in database - rollback). In final, we have two separate dataSources and transactions inside aren’t binded in one transaction. What can I do to make one dataSource for all DAO implementations?

          - Art

            JournalDev
            DigitalOcean Employee
            DigitalOcean Employee badge
            September 21, 2018

            Hi Pankaj, Your posts always gives clear picture about any topic. Thanks for the that. One quick question, it is possible to fetch two result set in spring jdbc template in same query? for Ex: First result will give count of total record and second result will give me record Any suggestion will be appreciated…

            - Suraj Bhandari

              JournalDev
              DigitalOcean Employee
              DigitalOcean Employee badge
              August 30, 2018

              Hi Pankaj, thank you, I read many of your posts and I always found them very interesting and well written. I have a question: when using JdbcTemplate and its subclasses, for example NamedParameterJdbcTemplate, is Spring that handle the connection pooling? Is spring that opens, closes and reuses a connection right? I don’t have to take care of opening, closing connection, resultset etc. Is it correct? Thank you Greeting from Rome (Italy) Matteo

              - Matteo

                Try DigitalOcean for free

                Click below to sign up and get $200 of credit to try our products over 60 days!

                Sign up

                Join the Tech Talk
                Success! Thank you! Please check your email for further details.

                Please complete your information!

                Featured on Community

                Get our biweekly newsletter

                Sign up for Infrastructure as a Newsletter.

                Hollie's Hub for Good

                Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

                Become a contributor

                Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

                Welcome to the developer cloud

                DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

                Learn more