Select Single and Multiple Records using Spring JdbcTemplate

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=root

Now 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.

Source Code

Download

Leave a Reply

Your email address will not be published. Required fields are marked *