Spring JdbcTemplate is the most important class in Spring JDBC package.
JdbcTemplate
class. This is the base class that manages the processing of all events and database connections.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.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.
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>
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;
}
}
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.
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;
}
}
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.
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. 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.
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.
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
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
Thanks for the article. https://medium.com/@7anac/beginners-guide-to-jdbctemplate-2020-8a744a64f101
- Janbo
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