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