Main Tutorials

Spring JdbcTemplate Querying Examples

Here are a few examples to show you how to use Spring JdbcTemplate to query or extract data from database.

Technologies used :

  • Spring Boot 2.1.2.RELEASE
  • Spring JDBC 5.1.4.RELEASE
  • Maven 3
  • Java 8

In Short:

  • jdbcTemplate.queryForObject for single row or value
  • jdbcTemplate.query for multiple rows or list
Note
The article is updated from Spring core 2.5.x to Spring Boot 2.1.x

P.S You may also interested in this Spring Boot JDBC Examples

1. Query for Single Row

In Spring, we can use jdbcTemplate.queryForObject() to query a single row record from database, and convert the row into an object via row mapper.

1.1 Custom RowMapper

CustomerRowMapper.java

import org.springframework.jdbc.core.RowMapper;

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

public class CustomerRowMapper implements RowMapper<Customer> {

    @Override
    public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {

        Customer customer = new Customer();
        customer.setID(rs.getLong("ID"));
        customer.setName(rs.getString("NAME"));
        customer.setAge(rs.getInt("AGE"));
        customer.setCreatedDate(rs.getTimestamp("created_date").toLocalDateTime());

        return customer;

    }
}

import org.springframework.jdbc.core.JdbcTemplate;

	@Autowired
    private JdbcTemplate jdbcTemplate;
	
	public Customer findByCustomerId(Long id) {

        String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";

        return jdbcTemplate.queryForObject(sql, new Object[]{id}, new CustomerRowMapper());

    }

1.2 Spring BeanPropertyRowMapper, this class saves you a lot of time for the mapping.


import org.springframework.jdbc.core.BeanPropertyRowMapper;
	
    public Customer findByCustomerId2(Long id) {

        String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";

        return (Customer) jdbcTemplate.queryForObject(
			sql, 
			new Object[]{id}, 
			new BeanPropertyRowMapper(Customer.class));

    }

1.3 In Java 8, we can map it directly:


    public Customer findByCustomerId3(Long id) {

        String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";

        return jdbcTemplate.queryForObject(sql, new Object[]{id}, (rs, rowNum) ->
                new Customer(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getTimestamp("created_date").toLocalDateTime()
                ));

    }

2. Query for Multiple Rows

For multiple rows, we use jdbcTemplate.query()

2.1 Custom RowMapper


	public List<Customer> findAll() {
	
        String sql = "SELECT * FROM CUSTOMER";

        List<Customer> customers = jdbcTemplate.query(
                sql,
                new CustomerRowMapper());

        return customers;
		
    }

2.2 BeanPropertyRowMapper


    public List<Customer> findAll() {

        String sql = "SELECT * FROM CUSTOMER";

        List<Customer> customers = jdbcTemplate.query(
                sql,
                new BeanPropertyRowMapper(Customer.class));

        return customers;
    }

2.3 Java 8


    public List<Customer> findAll() {

        String sql = "SELECT * FROM CUSTOMER";

        return jdbcTemplate.query(
                sql,
                (rs, rowNum) ->
                        new Customer(
                                rs.getLong("id"),
                                rs.getString("name"),
                                rs.getInt("age"),
                                rs.getTimestamp("created_date").toLocalDateTime()
                        )
        );
    }

2.4 jdbcTemplate.queryForList, it works, but not recommend, the mapping in Map may not same as the object, need casting.


	public List<Customer> findAll() {

        String sql = "SELECT * FROM CUSTOMER";

        List<Customer> customers = new ArrayList<>();

        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);

        for (Map row : rows) {
            Customer obj = new Customer();

            obj.setID(((Integer) row.get("ID")).longValue());
            obj.setName((String) row.get("NAME"));
			// Spring returns BigDecimal, need convert
            obj.setAge(((BigDecimal) row.get("AGE")).intValue()); 
            obj.setCreatedDate(((Timestamp) row.get("CREATED_DATE")).toLocalDateTime());
            customers.add(obj);
        }

        return customers;
    }

3. Query for a Single Value

It’s same like query a single row from database, uses jdbcTemplate.queryForObject()

3.1 Single column name


	public String findCustomerNameById(Long id) {

        String sql = "SELECT NAME FROM CUSTOMER WHERE ID = ?";

        return jdbcTemplate.queryForObject(
                sql, new Object[]{id}, String.class);

    }

3.2 Count


    public int count() {

        String sql = "SELECT COUNT(*) FROM CUSTOMER";

        // queryForInt() is Deprecated
        // https://www.mkyong.com/spring/jdbctemplate-queryforint-is-deprecated/
        //int total = jdbcTemplate.queryForInt(sql);

        return jdbcTemplate.queryForObject(sql, Integer.class);

    }

4. Test

Run a Spring Boot CommandLineRunner application, create tables and test the APIs.

pom.xml

	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-jdbc</artifactId>
	</dependency>

	<!-- in-memory database -->
	<dependency>
		<groupId>com.h2database</groupId>
		<artifactId>h2</artifactId>
	</dependency>
StartApplication.java

package com.mkyong;

import com.mkyong.customer.Customer;
import com.mkyong.customer.CustomerRepository;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;

import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;

@SpringBootApplication
public class StartApplication implements CommandLineRunner {

    private static final Logger log = LoggerFactory.getLogger(StartApplication.class);

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    CustomerRepository customerRepository;

    public static void main(String[] args) {
        SpringApplication.run(StartApplication.class, args);
    }

    @Override
    public void run(String... args) {

        log.info("StartApplication...");

        startCustomerApp();

    }

    // Tested with H2 database
    void startCustomerApp() {

        jdbcTemplate.execute("DROP TABLE customer IF EXISTS");
        jdbcTemplate.execute("CREATE TABLE customer(" +
                "id SERIAL, name VARCHAR(255), age NUMERIC(2), created_date timestamp)");

        List<Customer> list = Arrays.asList(
                new Customer("Customer A", 19),
                new Customer("Customer B", 20),
                new Customer("Customer C", 21),
                new Customer("Customer D", 22)
        );

        list.forEach(x -> {
            log.info("Saving...{}", x.getName());
            customerRepository.save(x);
        });

        log.info("[FIND_BY_ID]");
        log.info("{}", customerRepository.findByCustomerId(1L));
        log.info("{}", customerRepository.findByCustomerId2(2L));
        log.info("{}", customerRepository.findByCustomerId3(3L));

        log.info("[FIND_ALL]");
        log.info("{}", customerRepository.findAll());
        log.info("{}", customerRepository.findAll2());
        log.info("{}", customerRepository.findAll3());
        log.info("{}", customerRepository.findAll4());

        log.info("[FIND_NAME_BY_ID]");
        log.info("{}", customerRepository.findCustomerNameById(4L));

        log.info("[COUNT]");
        log.info("{}", customerRepository.count());

    }

}

Output


INFO  com.mkyong.StartApplication - Saving...Customer A
INFO  com.mkyong.StartApplication - Saving...Customer B
INFO  com.mkyong.StartApplication - Saving...Customer C
INFO  com.mkyong.StartApplication - Saving...Customer D
INFO  com.mkyong.StartApplication - [FIND_BY_ID]
INFO  com.mkyong.StartApplication - Customer{ID=1, name='Customer A', age=19, createdDate=2019-08-01T15:48:45.950848}
INFO  com.mkyong.StartApplication - Customer{ID=2, name='Customer B', age=20, createdDate=2019-08-01T15:48:45.961819}
INFO  com.mkyong.StartApplication - Customer{ID=3, name='Customer C', age=21, createdDate=2019-08-01T15:48:45.961819}
INFO  com.mkyong.StartApplication - [FIND_ALL]
INFO  com.mkyong.StartApplication - [
	Customer{ID=1, name='Customer A', age=19, createdDate=2019-08-01T15:48:45.950848}, 
	Customer{ID=2, name='Customer B', age=20, createdDate=2019-08-01T15:48:45.961819}, 
	Customer{ID=3, name='Customer C', age=21, createdDate=2019-08-01T15:48:45.961819}, 
	Customer{ID=4, name='Customer D', age=22, createdDate=2019-08-01T15:48:45.961819}
	]
//...omitted, duplicate code
INFO  com.mkyong.StartApplication - [FIND_NAME_BY_ID]
INFO  com.mkyong.StartApplication - Customer D
INFO  com.mkyong.StartApplication - [COUNT]
INFO  com.mkyong.StartApplication - 4

Download Source Code

$ git clone https://github.com/mkyong/spring-boot.git
$ cd spring-jdbc
$ find com.mkyong.customer

References

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
40 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Rob
9 years ago

Does any body know how to map 1 to Any using JDBCTeamplate?.. Need to map something like person with multiple cars and produce Json.

Silvya
3 years ago

Can you explain JDBC template with DTO?

viraj
9 years ago

Hey I have a problem with retrieving Timestamp values from databse using jdbc template. Please put an example of that here

Anubhab
11 years ago

Hey do you know which form of jdbctemplate.query will not throw any exception when no rows are fetched from DB??

Roger Pc
12 years ago

Spring MVC DataSource

Data base

CREATE TABLE VEHICLE ( 
    VEHICLE_NO    VARCHAR(10)    NOT NULL, 
    COLOR         VARCHAR(10), 
    WHEEL         INT, 
    SEAT          INT, 
    PRIMARY KEY (VEHICLE_NO) 
); 

Model

public class Vehicle {
    private String vehicleNo;
    private String color; 
    private int wheel; 
    private int seat;

    public Vehicle(String vehicleNo, String color, int wheel, int seat) {
        this.vehicleNo = vehicleNo;
        this.color = color;
        this.wheel = wheel;
        this.seat = seat;
    }
    // get and set ...
}

DAO

public interface VehicleDao {
    
    public void setDataSource(DataSource dataSource);    
    public void insert(Vehicle vehicle); 
    public void update(Vehicle vehicle); 
    public void delete(Vehicle vehicle); 
    public Vehicle findByVehicleNo(String vehicleNo); 
    
}

Dao Imp

@Repository
public class JdbcVehicleDao implements VehicleDao {

    private JdbcTemplate jdbcTemplate;
    
    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    public void insert(Vehicle vehicle) { 
        String sql = "INSERT INTO VEHICLE (VEHICLE_NO, COLOR, WHEEL, SEAT) " 
                + "VALUES (?, ?, ?, ?)"; 
        jdbcTemplate.update(sql, new Object[] { vehicle.getVehicleNo(), 
                             vehicle.getColor(), vehicle.getWheel(), vehicle.getSeat() });
    }
    // update, delate, findByVehicleNo....

Controller

@Controller
public class VehicleController {
    @Autowired    
    VehicleDao vehicleDao;

    @RequestMapping(value="/jdbc.htm", method = RequestMethod.GET)
    public String jdbc(ModelMap model) {        
        Vehicle vehicle = new Vehicle("TEM0001", "Red", 4, 4); 
        vehicleDao.insert(vehicle);
        model.addAttribute("vehicle", vehicle);
        return "crud";
    }
}
<bean id="propertyConfigurer" 
   class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
   p:location="/WEB-INF/jdbc.properties" />

<bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource"
          p:driverClassName="${jdbc.driverClassName}"
          p:url="${jdbc.url}"
          p:username="${jdbc.username}"
          p:password="${jdbc.password}" />
<context:component-scan base-package="x.D" />
<mvc:annotation-driven />

fuente: Spring Recipes A Problem-Solution Approach

Shiva Krishna K
10 years ago
Reply to  Roger Pc

Awesome one but it would be gud if you could provide a link for what is tag and what is @RequestMapping annotation mean and what it does.

Because all these tutorials are related to Spring Core but not to MVC but the tags that you have mentioned are related to MVC here.

Shiva Krishna K
10 years ago

Tag that i am refering to is <mvc:annotation-driven /> in my above comment

Mathias Dürrenberger
3 years ago

First: thanks, very useful material!

When using “RowCallbackHandler”, I found that first record is already “consumed”, I had to use a “do { .. }while (resultSet.next());” loop in order to iterate over all data – funny thing

Purva
4 years ago

I am trying to use org.springframework.jdbc.core.JdbcTemplate . Stored procedure returns 2 different result sets. How to retrieve those? For 1 result set I am using jdbcTemplate.query(). When I tried to create 2 different methods based on 2 different result sets, I am still getting 1st result set.

Thanks in advance!!

Vishwas Tyagi
4 years ago

Hi,
I have millions of records in database, and I need to write the in csv file. Now when I do select * from , I get exception Exception in thread “main” java.lang.OutOfMemoryError: Java heap space. Could you please suggest some way to read this data in chunk and write to the file.

sandeep prajapat
5 years ago

can you tall me where you declare getJdbcTemplate() mathode in class and how to create JdbcTemplateObject

Wilmar
5 years ago

Hi. Thank you for the post. I have a question. If the resultset is too huge, how deal with it? A can’t retrieve the list without get a “Handler dispatch failed; nested exception is java.lang.OutOfMemoryError: Java heap space” Thank you in advance.

vivek godrej
6 years ago

Do you have any experience using mybatis-spring framework for persistence ? I am struggling to get null pointer eception resolved due to dependency injection not working

Khan
6 years ago

I have a table just with one colomn n one row.. if i want to update that record.. u use update method n set query.. now i want to fetch that single record. I dont have any value to pass as an input. I write sql query as select * from mytable.. n it should give me that single record. Please help me in choosing suitable method.. it will be helpful

jawahar
6 years ago
Reply to  Khan

You can use ‘queryForList’ and do list.get(0) to get the record.

Vikash
6 years ago

We have a environment where we have a web server and a application server. We can connect to DB only via application server where as web server is exposed to the users for using the system.
We have deployed the application on both the server but the problem we are facing is that, How we can execute spring JDBC template from the application server via web server ?
Ex: If user has filled the information in the from and now trying to save the data on web server (but we don’t have connectivity from web server) data should be saved via application server.

I hope I am able to explain my problem.

Gaurav Kumar
6 years ago

I must say very simple and concise Explanation.
We understand the contrast of using RowMapper and another way of doing the same thing.

Anant
6 years ago

Hello,

I am looking for something which can written
Map<Long, List
In my requirement I want to all the list of employee as per the department id from the Employee table.
Thanks in advance.

Jikku
6 years ago

Which approach to follow if i have to select million records from database and write to a flat file

radha r
7 years ago

how can i write the Junit for the CustomerRowMapper class which is mentioned above

guest
9 years ago

Thank you for good information.

Shivaji
10 years ago

You made mistake in JdbcTemplateApp.java file.

Here is the code snippet:

List customerAs = customerDAO.findAll();
for(Customer cust: customerAs){
System.out.println(“Customer As : ” + customerAs);
}

List customerBs = customerDAO.findAll2();
for(Customer cust: customerBs){
System.out.println(“Customer Bs : ” + customerBs);
}

Problem:
List of customers will be printed multiple times(eqaul to total number of customers).

Solution:
There should be “cust” to printed instead of “customerAs” or “customerBs”.
OR
Simply remove the for loops.

Darin
11 years ago

I am trying to set the Parent List in a ParameterizedRowMapper how is this written or approached. I have two Objects one for parent and one for children however children contains a ListThe parents for each child are stored in a separate table in the database and the mapping is 1 – many.

The select for the records for the parents will be done in a separate ResultSet. Will the mapping have to be done separately (separate ParameterizedRowMapper), if so how will i have to write the ParameterizedRowMapper this is the major concern how ParameterizedRowMapper is written to accommodate a list items.

public static class ChildrenMapper implements ParameterizedRowMapper{

public Children mapRow(ResultSet rs, int rowNum) throws SQLException {
Children child = new Children();
child.setFirstName(rs.getString(“firstName”));
child.setLastName(rs.getString(“lastName”));
//a child can have many Parents or gaurdians
child.setParent(List);

return child;
}
}

Mike
11 years ago

Interesting…it keeps removing the generics from the declaration from what is being posted.

Mike
11 years ago

List<Map > rows = jdbcTemplate.queryForList(sql);

Mike
11 years ago

I think there is an error in the findAll (Manually) method. Here is how I think ‘rows’ should be declared.

List<Map > rows = jdbcTemplate.queryForList(sql);

Najum
11 years ago

Nice thx

Me
11 years ago

To map multiple rows you should take a look at the

ResultSetExtractor

interface.

Dipak C
11 years ago

Hi, I am getting problem while reading the Resultset of queryForList

jdbcQuery=”SELECT sp.rollNo rollno, sa.studentName stname, c.shortCode AS shortcode,CONCAT(SUM(sa.present) , ‘/’,COUNT(sa.present),’ (‘, ROUND(SUM(sa.present)*100/COUNT(sa.present),2),’%)’) AS percentage FROM studentattendance sa,studentprofile sp,course c,lecture l WHERE sa.studentId=sp.id AND c.type=’Theory’ AND sp.division_id=1 AND l.startDate >= ‘2012-01-01’ AND l.endDate <= '2012-02-16' AND sa.lectureId=l.id AND l.course_id=c.id GROUP BY sa.studentName,c.name"

geting object address
List<Map> resultSetList = jdbcTemplate.queryForList(jdbcQuery);

Iterator<Map> qdataMapItr = resultSetList.iterator();
String checkString=””;
Map courseAttendMap=null;
while(qdataItr.hasNext())
{
Map mapobj = qdataItr.next();
System.out.println(” shortCode:”+mapobj.get(“shortcode”)+ ” percentage:”+mapobj.get(“percentage”));

}

OUTPUT:
shortCode:APM(TH) percentage:[B@3435

Why it is so? i dont understand. I am able read shortcode,rollno,stname but while getting percentage its giving Object address…

please help!!!!!!!!!!!!!!!!!!

Dipak C
11 years ago
Reply to  Dipak C

on executing the above query on query browser giving following output. I am using mysql.

rollno stname shortcode percentage
21A01 ADHIKARI NILESH MADHUKAR APM(TH) 8/14 (57.14%)
21A01 ADHIKARI NILESH MADHUKAR CT(TH) 13/16 (81.25%)
21A01 ADHIKARI NILESH MADHUKAR FM-1(TH) 20/23 (86.96%)
21A01 ADHIKARI NILESH MADHUKAR SA-1(TH) 23/27 (85.19%)
21A01 ADHIKARI NILESH MADHUKAR SURVEYING(TH) 12/12 (100.00%)
21A02 AHER AMIT ANNASAHEB BP(TH) 9/14 (64.29%)

please help!!!

Priyank
12 years ago

If my query is like :

String sql = “SELECT * FROM CUSTOMER where name in (‘abc’,’xyz’…)”;

what should be the code ?

jawahar
6 years ago
Reply to  Priyank

You can use NamedParameterJdbc template to achieve this.
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue(“names”, Arrays.asList(“abc”, “xyz”));

List objList = namedParameterJdbcTemplate.query(“select * from foo where name in (:names)”,
parameters,
new urobjRowMapper()
);

Anil
12 years ago

2.1 Map it manually shows a nice manual way

Alternative could be like this below and get a list of customers. Although we should use query instead of queryForObject

List getJdbcTemplate().query(
sql, new Object[] { custId }, new CustomerRowMapper());

Adeel
12 years ago

Awesome tut mate…well done …saved my life eh …ta

mario
13 years ago

Nice one. Thanks:)

me
10 years ago
Reply to  mario

you welcom