// Tutorial //

Spring JdbcTemplate Example

Published on August 3, 2022
Default avatar
By Pankaj
Developer and author at DigitalOcean.
Spring JdbcTemplate Example

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.

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">



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;

public class AppConfig {

	Environment environment;

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

	DataSource dataSource() {
		DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
		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.


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;

	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();
		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;

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(?,?,?,?)";

	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("\nGet person with ID 2");

		Person personById = personDAO.getPersonById(2L);

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

		for (Person p : personDAO.getAllPersons()) {

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

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

		Person pperson = personDAO.getPersonById(4L);

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


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

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Sign up
About the authors
Default avatar


Developer and author at DigitalOcean.

Still looking for an answer?

Was this helpful?

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

- vamshi

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

    - Christian

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

      - Jayash

        Great Example, very well explained

        - Juan

          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

            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

              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