Select Single and Multiple Records 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 at least 8, Gradle 6.5.1 or Maven 3.6.3, Spring Boot 2.4.0

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:

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

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.0</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<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.17</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.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>at least 8</source>
					<target>at least 8</target>
				</configuration>
			</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 TABLE `student` (
  `student_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `student_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `student_dob` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `student_email` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `student_address` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB 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,'Gourab','01-01-1982','gourab@email.com','Garia'),
(3,'Debina','01-01-1982','debina@email.com','Salt Lake'),
(4,'Souvik','01-01-1992','souvik@email.com','Alipore'),
(5,'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.

package com.roytuts.spring.jdbctemplate.select.single.multiple.records.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

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

package com.roytuts.spring.jdbctemplate.select.single.multiple.records.model;

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.

package com.roytuts.spring.jdbctemplate.select.single.multiple.records.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.roytuts.spring.jdbctemplate.select.single.multiple.records.model.Student;
import com.roytuts.spring.jdbctemplate.select.single.multiple.records.row.mapper.StudentDaoMapper;

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

package com.roytuts.spring.jdbctemplate.select.single.multiple.records.row.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.roytuts.spring.jdbctemplate.select.single.multiple.records.model.Student;

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.

package com.roytuts.spring.jdbctemplate.select.single.multiple.records;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.roytuts.spring.jdbctemplate.select.single.multiple.records.dao.StudentDao;

@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 the Application

Running the above class will give you the following output:

Sumit

Student [studentId=2, studentName=Gourab, studentDob=01-01-1982, studentEmail=gourab@email.com, studentAddress=Garia]

Student [studentId=1, studentName=Sumit, studentDob=01-01-1980, studentEmail=sumit@email.com, studentAddress=Garifa]
Student [studentId=2, studentName=Gourab, studentDob=01-01-1982, studentEmail=gourab@email.com, studentAddress=Garia]
Student [studentId=3, studentName=Debina, studentDob=01-01-1982, studentEmail=debina@email.com, studentAddress=Salt Lake]
Student [studentId=4, studentName=Souvik, studentDob=01-01-1992, studentEmail=souvik@email.com, studentAddress=Alipore]
Student [studentId=5, 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 Comment