Record Selection Using Spring JdbcTemplate
This tutorial will show you how you can select or fetch data from a database using Spring JdbcTemplate. I am going to use Spring Boot framework to select single record, multiple records and only single field or column value from the database.
simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the org.springframework.dao package.
Prerequisites
Java 1.8/19, Gradle 6.5.1 or Maven 3.6.3/3.8.5, Spring Boot 2.4.0/3.0.5, MySQL 8.0.31
Project Setup
Create either gradle or maven based project in your favorite IDE or tool. For gradle based project use the following build.gradle script:
buildscript {
ext {
springBootVersion = '2.4.0'
}
repositories {
maven {
url 'https://plugins.gradle.org/m2/'
}
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
plugins {
id 'java-library'
id 'org.springframework.boot' version "${springBootVersion}"
}
sourceCompatibility = 12
targetCompatibility = 12
repositories {
mavenCentral()
jcenter()
}
dependencies {
implementation("org.springframework.boot:spring-boot-starter:${springBootVersion}")
implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}")
implementation('mysql:mysql-connector-java:8.0.21')
//required for JDK 9 or above
implementation 'javax.xml.bind:jaxb-api:2.4.0-b180830.0359'
}For maven based project use the following pom.xml file:
<?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-select-single-multiple-records</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.5</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>
<version>8.0.31</version>
</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>
MySQL Table
I am creating one table student under roytuts database with some sample data so that I can test the application right away.
CREATE DATABASE IF NOT EXISTS `roytuts`;
USE `roytuts`;
CREATE TABLE IF NOT EXISTS `student` (
`student_id` int unsigned NOT NULL AUTO_INCREMENT,
`student_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`student_dob` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`student_email` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`student_address` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `student` (`student_id`, `student_name`, `student_dob`, `student_email`, `student_address`) VALUES
(1, 'Sumit', '01-01-1980', 'sumit@email.com', 'Garifa'),
(2, 'Sumit', '01-01-1980', 'sumit@email.com', 'Garifa'),
(3, 'Anjisnu', '01-01-1982', 'gourab@email.com', 'Garia'),
(4, 'Debina', '01-01-1982', 'debina@email.com', 'Salt Lake'),
(5, 'Souvik', '01-01-1992', 'souvik@email.com', 'Alipore'),
(6, 'Liton', '01-01-1990', 'liton@email.com', 'Salt Lake');Datasource Configuration
Create an application.properties under class path folder src/main/resources for declaring the database details.
#datasource
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/roytuts
jdbc.username=root
jdbc.password=rootNow I am creating the Java based configuration to create DataSource and JdbcTemplate.
@Configuration
public class DbConfig {
@Autowired
private Environment environment;
@Bean
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(environment.getProperty("jdbc.driverClassName"));
dataSource.setUrl(environment.getProperty("jdbc.url"));
dataSource.setUsername(environment.getProperty("jdbc.username"));
dataSource.setPassword(environment.getProperty("jdbc.password"));
return dataSource;
}
@Bean
public JdbcTemplate getJdbcTemplate() throws ClassNotFoundException {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource());
return jdbcTemplate;
}
}Model
The below model class maps the database table student. This model class actually maps Java attributes to database table columns.
public class Student {
private Integer studentId;
private String studentName;
private String studentDob;
private String studentEmail;
private String studentAddress;
public Student() {
}
public Student(Integer studentId, String studentName, String studentDob, String studentEmail,
String studentAddress) {
this.studentId = studentId;
this.studentName = studentName;
this.studentDob = studentDob;
this.studentEmail = studentEmail;
this.studentAddress = studentAddress;
}
// getters and setters
@Override
public String toString() {
return "Student [studentId=" + studentId + ", studentName=" + studentName + ", studentDob=" + studentDob
+ ", studentEmail=" + studentEmail + ", studentAddress=" + studentAddress + "]";
}
}
Repository
This is not a Spring Data JPA repository but it’s a DAO layer code for interacting with database for performing the required operations for fetching records from student table.
@Repository
public class StudentDao {
@Autowired
private JdbcTemplate jdbcTemplate;
private final String SQL_STUDENT_SELECT_NAME = "select student_name from student where student_id = ?";
private final String SQL_STUDENT_SELECT_SINGLE = "select * from student where student_id = ?";
private final String SQL_STUDENT_SELECT_MULTIPLE = "select * from student";
public String selectStudentName(Integer studentId) {
String name = jdbcTemplate.queryForObject(SQL_STUDENT_SELECT_NAME, String.class, new Object[] { studentId });
return name;
}
public Student selectSingleStudent(Integer studentId) {
Student student = jdbcTemplate.queryForObject(SQL_STUDENT_SELECT_SINGLE, new StudentDaoMapper(),
new Object[] { studentId });
return student;
}
public List<Student> selectMultipleStudents() {
List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL_STUDENT_SELECT_MULTIPLE);
List<Student> students = new ArrayList<Student>();
for (Map<String, Object> row : rows) {
Student student = new Student();
student.setStudentId(Integer.parseInt(String.valueOf(row.get("student_id"))));
student.setStudentName(String.valueOf(row.get("student_name")));
student.setStudentDob(String.valueOf(row.get("student_dob")));
student.setStudentEmail(String.valueOf(row.get("student_email")));
student.setStudentAddress(String.valueOf(row.get("student_address")));
students.add(student);
}
// or using BeanPropertyRowMapper in a single call
// List<Student> students = jdbcTemplate.query(SQL_STUDENT_SELECT_MULTIPLE, new
// BeanPropertyRowMapper(Student.class));
return students;
}
}Mapper
To actually map table columns to Java fields in the Student class.
public class StudentDaoMapper implements RowMapper<Student> {
@Override
public Student mapRow(ResultSet rs, int arg1) throws SQLException {
Student student = new Student();
student.setStudentId(rs.getInt("student_id"));
student.setStudentName(rs.getString("student_name"));
student.setStudentDob(rs.getString("student_dob"));
student.setStudentEmail(rs.getString("student_email"));
student.setStudentAddress(rs.getString("student_address"));
return student;
}
}Main Class
A class with main method and @SpringBootApplication is sufficient to start the application. I am running the application in CLI by implementing the interface CommandLineRunner.
@SpringBootApplication
public class SpringJdbcTemplateSelectApp implements CommandLineRunner {
@Autowired
private StudentDao studentDao;
public static void main(String[] args) {
SpringApplication.run(SpringJdbcTemplateSelectApp.class, args);
}
@Override
public void run(String... args) throws Exception {
System.out.println(studentDao.selectStudentName(1));
System.out.println(studentDao.selectSingleStudent(2));
studentDao.selectMultipleStudents().stream().forEach(s -> System.out.println(s));
}
}Testing Single And Multiple Records Selection
Running the above class will give you the following output:
Sumit
Student [studentId=2, studentName=Sumit, studentDob=01-01-1980, studentEmail=sumit@email.com, studentAddress=Garifa]
Student [studentId=1, studentName=Sumit, studentDob=01-01-1980, studentEmail=sumit@email.com, studentAddress=Garifa]
Student [studentId=2, studentName=Sumit, studentDob=01-01-1980, studentEmail=sumit@email.com, studentAddress=Garifa]
Student [studentId=3, studentName=Anjisnu, studentDob=01-01-1982, studentEmail=gourab@email.com, studentAddress=Garia]
Student [studentId=4, studentName=Debina, studentDob=01-01-1982, studentEmail=debina@email.com, studentAddress=Salt Lake]
Student [studentId=5, studentName=Souvik, studentDob=01-01-1992, studentEmail=souvik@email.com, studentAddress=Alipore]
Student [studentId=6, studentName=Liton, studentDob=01-01-1990, studentEmail=liton@email.com, studentAddress=Salt Lake]That’s all about single, multiple records fetching from database table using JdbcTemplate in Spring framework.