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
Thanks for reading.