Select Records using queryForObject, queryForList, BeanPropertyRowMapper in Spring JdbcTemplate

Introduction

In this post we will see how to select records using queryForObject, queryForList, BeanPropertyRowMapper in Spring JdbcTemplate. Here we will create annotation based example. If you need XML based application then you can refer to the example here. Spring’s queryForObject is used to fetch single row from the database. Spring’s queryForList and BeanPropertyRowMapper are used to fetch multiple rows from the database. Therefore we will fetch single and multiple rows from the database using Spring JdbcTemplate.

Prerequisites

Eclipse Neon, Java 1.8, Spring 5.1.8, MySQL 8.0.17, Gradle 5.4.1

Creating Project

Create a gradle based project in Eclipse with the project name as spring-jdbctemplate-selection.

Updating Build Script

The default generated build.gradle script doesn’t include required dependencies. So we will add required dependencies in the build script.

buildscript {
	ext {
		springVersion = '5.1.8.RELEASE'
	}
}
apply plugin: 'java'
sourceCompatibility = 1.8
targetCompatibility = 1.8
repositories {
    mavenLocal()
    mavenCentral()
}
dependencies {
	implementation("org.springframework:spring-core:${springVersion}")
	implementation("org.springframework:spring-beans:${springVersion}")
	implementation("org.springframework:spring-context:${springVersion}")
	implementation("org.springframework:spring-jdbc:${springVersion}")
	implementation("mysql:mysql-connector-java:8.0.17")
}

Database Properties

Create a file database.properties under src/main/resources folder with the below content in it.

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/roytuts
spring.datasource.username=root
spring.datasource.password=root

Creating MySQL Table

Let’s say we have a table called cd under database roytuts in MySQL server.

The table structure is given below:

CREATE TABLE `cd` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `artist` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Dumping Data

Dump some data into the cd table.

insert  into `cd`(`id`,`title`,`artist`) values
(1,'Title 1','Artist 1'),
(2,'Title 2','Artist 2'),
(3,'Title 3','Artist 3'),
(4,'Title 4','Artist 4');
(5,'Title 5','Artist 5');
(10,'Single Title','Single Artist');

Database Config Class

Creating DataSource and JdbcTemplate beans will help us perform database operations in DAO layer.

package com.roytuts.spring.jdbctemplate.selection.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.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
@Configuration
@PropertySource("classpath:database.properties")
@ComponentScan(basePackages = "com.roytuts.spring.jdbctemplate.selection")
public class SpringJdbcConfig {
	@Autowired
	private Environment env;
	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
		dataSource.setUrl(env.getProperty("spring.datasource.url"));
		dataSource.setUsername(env.getProperty("spring.datasource.username"));
		dataSource.setPassword(env.getProperty("spring.datasource.password"));
		return dataSource;
	}
	@Bean
	public JdbcTemplate jdbcTemplate() {
		return new JdbcTemplate(dataSource());
	}
}

Creating Model Class

The below model class represents Java class attributes to table columns.

package com.roytuts.spring.jdbctemplate.selection.model;
public class Cd {
	private Long id;
	private String title;
	private String artist;
	//getters and setters
}

Creating RowMapper Class

We will implement Spring’s RowMapper interface to map table column values to Java class fileds.

package com.roytuts.spring.jdbctemplate.selection.rowmapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.roytuts.spring.jdbctemplate.selection.model.Cd;
public class CdRowMapper implements RowMapper<Cd> {
	@Override
	public Cd mapRow(ResultSet rs, int rowNum) throws SQLException {
		Cd cd = new Cd();
		cd.setId(rs.getLong("id"));
		cd.setTitle(rs.getString("title"));
		cd.setArtist(rs.getString("artist"));
		return cd;
	}
}

Creating DAO Class

We will create DAO class to select single or multiple records from database table.

Notice how we are retrieving single row and map to Java object of Cd class using RowMapper.

We are retrieving multiple rows from table using queryForList and BeanPropertyRowMapper.

queryForList gives us list of Map objects and we need to retrieve the actual values from key/value pair of Map object.

Using BeanPropertyRowMapper in a single call we can fetch multiple records from the database table.

When you are using BeanPropertyRowMapper then you need to have the same Java attribute names as table column names.

package com.roytuts.spring.jdbctemplate.selection.dao;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import com.roytuts.spring.jdbctemplate.selection.model.Cd;
import com.roytuts.spring.jdbctemplate.selection.rowmapper.CdRowMapper;
@Repository
public class CdDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	private final String SQL_SELECT_TITLE = "select title from cd where id = ?";
	private final String SQL_SELECT_SINGLE = "select * from cd where id = ?";
	private final String SQL_SELECT_MULTIPLE = "select * from cd";
	public String selectTitle(Long id) {
		return jdbcTemplate.queryForObject(SQL_SELECT_TITLE, new Object[] { id }, String.class);
	}
	public Cd selectSingle(Long id) {
		Cd cd = jdbcTemplate.queryForObject(SQL_SELECT_SINGLE, new Object[] { id }, new CdRowMapper());
		return cd;
	}
	public List<Cd> selectMultipleUsingQueryForList() {
		List<Map<String, Object>> results = jdbcTemplate.queryForList(SQL_SELECT_MULTIPLE);
		List<Cd> cds = results.stream().map(m -> {
			Cd cd = new Cd();
			cd.setId(Long.parseLong(String.valueOf(m.get("id"))));
			cd.setTitle(String.valueOf(m.get("title")));
			cd.setArtist(String.valueOf(m.get("artist")));
			return cd;
		}).collect(Collectors.toList());
		return cds;
	}
	public List<Cd> selectMultipleUsingBeanPropertyRowMapper() {
		List<Cd> cds = jdbcTemplate.query(SQL_SELECT_MULTIPLE, new BeanPropertyRowMapper<Cd>(Cd.class));
		return cds;
	}
}

Creating Main Class

Generally DAO class is injected into service layer or business layer code but for simplicity we will call from main class to test the functionality.

package com.roytuts.spring.jdbctemplate.selection;
import java.util.List;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import com.roytuts.spring.jdbctemplate.selection.config.SpringJdbcConfig;
import com.roytuts.spring.jdbctemplate.selection.dao.CdDao;
import com.roytuts.spring.jdbctemplate.selection.model.Cd;
public class SpringJdbcTemplateSelectApp {
	public static void main(String[] args) {
		AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext();
		context.register(SpringJdbcConfig.class);
		context.refresh();
		CdDao cdDao = context.getBean(CdDao.class);
		System.out.println("Select => CD Title: " + cdDao.selectTitle(1l));
		System.out.println();
		Cd cd = cdDao.selectSingle(2l);
		System.out.println(
				"Single selection => Id: " + cd.getId() + ", Title: " + cd.getTitle() + ", Artist: " + cd.getArtist());
		System.out.println();
		System.out.println("Multiple Selection using queryForList");
		List<Cd> cds = cdDao.selectMultipleUsingQueryForList();
		cds.forEach(c -> System.out
				.println("Id: " + c.getId() + ", Title: " + c.getTitle() + ", Artist: " + c.getArtist()));
		System.out.println();
		System.out.println("Multiple Selection using BeanPropertyRowMapper");
		cds = cdDao.selectMultipleUsingBeanPropertyRowMapper();
		cds.forEach(c -> System.out
				.println("Id: " + c.getId() + ", Title: " + c.getTitle() + ", Artist: " + c.getArtist()));
		context.close();
	}
}

Testing the Application

Now execute the above main class and you will see below output in the console:

Select => CD Title: Title 1
Single selection => Id: 2, Title: Title 2, Artist: Artits 2
Multiple Selection using queryForList
Id: 1, Title: Title 1, Artist: Artist 1
Id: 2, Title: Title 2, Artist: Artits 2
Id: 3, Title: Title 3, Artist: Artist 3
Id: 4, Title: Title 4, Artist: Artist 4
Id: 5, Title: Title 5, Artist: Artist 5
Id: 10, Title: Single Title, Artist: Single Artist
Multiple Selection using BeanPropertyRowMapper
Id: 1, Title: Title 1, Artist: Artist 1
Id: 2, Title: Title 2, Artist: Artits 2
Id: 3, Title: Title 3, Artist: Artist 3
Id: 4, Title: Title 4, Artist: Artist 4
Id: 5, Title: Title 5, Artist: Artist 5
Id: 10, Title: Single Title, Artist: Single Artist

Source Code

download source code

Thanks for reading.