QueryForObject, QueryForList, BeanPropertyRowMapper

In this post you will see how to select records using queryForObject(), queryForList(), BeanPropertyRowMapper in Spring Boot JdbcTemplate. 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. So, I will fetch single and multiple rows from the database using Spring JdbcTemplate.

Prerequisites

Java 17+ (19), Spring Boot 3.0.2, MySQL 8.0.31, Maven 3.8.5

Project Setup

Create a maven based project in your favorite IDE or tool with the project name as spring-jdbctemplate-queryfor. I will add required dependencies in the build file pom.xml.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.roytuts</groupId>
	<artifactId>spring-jdbctemplate-queryfor</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>19</maven.compiler.source>
		<maven.compiler.target>19</maven.compiler.target>
	</properties>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.0.2</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<!--required only if jdk 9 or higher version is used -->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

Application Properties

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

spring.datasource.url=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

MySQL Table Data

Let’s say you 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;

Dump some data into the cd table for testing the application right away.

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'),
(6,'Single Title','Single Artist');

Model Class

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

public class Cd {
	private Long id;
	private String title;
	private String artist;
	//getters and setters
}

RowMapper Class

I will implement Spring’s RowMapper interface to map table column values to Java class fields.

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;
	}

}

DAO Class

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

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

I am retrieving multiple rows from table using queryForList() and BeanPropertyRowMapper.

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

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

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

@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, String.class, new Object[] { id });
	}

	public Cd selectSingle(Long id) {
		Cd cd = jdbcTemplate.queryForObject(SQL_SELECT_SINGLE, new CdRowMapper(), new Object[] { id });
		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;
	}

}

Spring Boot Main Class

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

@SpringBootApplication
public class App implements CommandLineRunner {

	@Autowired
	private CdDao cdDao;

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

	@Override
	public void run(String... args) throws Exception {
		System.out.println();
		System.out.println("Select => CD Title: " + cdDao.selectTitle(1l));
		System.out.println();

		Cd cd = cdDao.selectSingle(2l);
		System.out.println(
				"Single Record selection => Id: " + cd.getId() + ", Title: " + cd.getTitle() + ", Artist: " + cd.getArtist());
		System.out.println();

		System.out.println("Multiple Records Selection using queryForList");
		System.out.println("---------------------------------------------");
		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 Records Selection using BeanPropertyRowMapper");
		System.out.println("------------------------------------------------------");
		cds = cdDao.selectMultipleUsingBeanPropertyRowMapper();
		cds.forEach(c -> System.out
				.println("Id: " + c.getId() + ", Title: " + c.getTitle() + ", Artist: " + c.getArtist()));
		System.out.println();
	}
}

Testing the Spring Boot JdbcTemplate Application

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

Select => CD Title: Title 1

Single Record selection => Id: 2, Title: Title 2, Artist: Artist 2

Multiple Records Selection using queryForList
---------------------------------------------
Id: 1, Title: Title 1, Artist: Artist 1
Id: 2, Title: Title 2, Artist: Artist 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: 6, Title: Single Title, Artist: Single Artist

Multiple Records Selection using BeanPropertyRowMapper
------------------------------------------------------
Id: 1, Title: Title 1, Artist: Artist 1
Id: 2, Title: Title 2, Artist: Artist 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: 6, Title: Single Title, Artist: Single Artist

Source Code

Download