Back to Spring Boot Course

Spring Boot Course

Spring JDBC

Spring Boot provides a very good support to create a DataSource for Database.

We need not write any extra code to create a DataSource in Spring Boot.

Just adding the dependencies and doing the configuration details is enough to create a DataSource and connect the Database.

Spring JDBC 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. It does this magic by providing JDBC templates which developers can use inside their applications.

Features

  • Simplified JDBC Reduces boilerplate code for managing database resources.
  • Flexibility Supports a wide range of database operations.
  • Error Handling Comprehensive exception translation into Spring's DataAccessException hierarchy.

Example

Below is the maven dependency that we need to any SpringBoot projects in order to use Spring JDBC provider templates.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

Connect to PostgreSQL database

Let's add the PostgreSQL dependency in pom.xml

<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
</dependency>

Setup Table in Database

CREATE TABLE IF NOT EXISTS EMPLOYEE (
  ID BIGINT AUTO_INCREMENT PRIMARY KEY,
  NAME VARCHAR(50) NOT NULL,
  DESIGNATION VARCHAR(50) NOT NULL,
  SALARY BIGINT NOT NULL,
  LOCATION VARCHAR(50) NOT NULL
);

Configurations

We need to include the below properties in the application.yml

spring:
  application:
    name: emp-mgmt-service
  datasource:
    url: jdbc:postgresql://localhost:5432/praveenoruganti
    username: postgres
    password: praveen
    hikari:
      minimum-idle: 5
      maximum-pool-size: 10

Let's see the code now.

Controller

package com.praveen.employee.controller;

import java.util.List;

import org.springframework.http.MediaType;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.praveen.employee.aop.LoggingAspect;
import com.praveen.employee.aop.TrackExecutionTime;
import com.praveen.employee.bo.EmployeeRequest;
import com.praveen.employee.bo.EmployeeResponse;
import com.praveen.employee.entity.EmployeeJdbc;
import com.praveen.employee.exception.EmployeeNotFoundException;
import com.praveen.employee.service.EmployeeManagementServiceJdbc;

import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.validation.Valid;
import jakarta.validation.constraints.Min;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.Size;

@RestController
@Validated
@RequestMapping("/api/v1/jdbc")
@Tag(description = "Employee Management JDBC Operations", name = "Employee Management JDBC Operations")
public class EmployeeManagementControllerJdbc {

	private final EmployeeManagementServiceJdbc employeeManagementService;

	public EmployeeManagementControllerJdbc(EmployeeManagementServiceJdbc employeeManagementService) {
		this.employeeManagementService = employeeManagementService;
	}

	@GetMapping(value = "/employees", produces = MediaType.APPLICATION_JSON_VALUE)
	@LoggingAspect
	@TrackExecutionTime
	@Operation(description = "Fetch All Employees", summary = "Fetch All Employees")
	public List<EmployeeJdbc> fetchAllEmployees() {
		return employeeManagementService.fetchAllEmployees();

	}

	@GetMapping(value = "/employees/search", produces = MediaType.APPLICATION_JSON_VALUE)
	@LoggingAspect
	@TrackExecutionTime
	@Operation(description = "Fetch Employee by Employee Name", summary = "Fetch Employee by Employee Name")
	public List<EmployeeJdbc> getEmployeeByName(@RequestParam @NotBlank @Size(min = 5) String name) {
		return employeeManagementService.getEmployeeByName(name);

	}

	@GetMapping(value = "/employees/{id}", produces = MediaType.APPLICATION_JSON_VALUE)
	@LoggingAspect
	@TrackExecutionTime
	@Operation(description = "Fetch Employee By Employee Id", summary = "Fetch Employee By Employee Id")
	public EmployeeResponse getEmployeeById(@PathVariable @Min(1) Long id) throws EmployeeNotFoundException {
		return employeeManagementService.getEmployeeById(id);
	}

	@PostMapping(value = "/employees", produces = MediaType.APPLICATION_JSON_VALUE, consumes = MediaType.APPLICATION_JSON_VALUE)
	@LoggingAspect
	@TrackExecutionTime
	@Operation(description = "Create Employee", summary = "Create Employee")
	public EmployeeResponse createEmployee(@RequestBody @Valid EmployeeRequest emp) {
		return employeeManagementService.createEmployee(emp);
	}

	@PutMapping(value = "/employees/{id}", produces = MediaType.APPLICATION_JSON_VALUE, consumes = MediaType.APPLICATION_JSON_VALUE)
	@LoggingAspect
	@TrackExecutionTime
	@Operation(description = "Create Employee", summary = "Create Employee")
	public EmployeeResponse updateEmployeeById(@RequestBody @Valid EmployeeRequest emp, @PathVariable @Min(1) Long id)
			throws EmployeeNotFoundException {
		return employeeManagementService.updateEmployeeById(emp, id);
	}

	@DeleteMapping(value = "/employees/{id}", produces = MediaType.APPLICATION_JSON_VALUE)
	@LoggingAspect
	@TrackExecutionTime
	@Operation(description = "Delete Employee By Employee Id", summary = "Delete Employee By Employee Id")
	public EmployeeResponse deleteEmployeeById(@PathVariable @Min(1) Long id) {
		return employeeManagementService.deleteEmployeeById(id);
	}

}

EmployeeJdbc


package com.praveen.employee.entity;

public class EmployeeJdbc {
	private Long id;
	private String name;
	private String designation;
	private Long salary;
	private String location;

	public EmployeeJdbc() {
	}

	public EmployeeJdbc(Long id, String name, String designation, Long salary, String location) {
		this.id = id;
		this.name = name;
		this.designation = designation;
		this.salary = salary;
		this.location = location;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getDesignation() {
		return designation;
	}

	public void setDesignation(String designation) {
		this.designation = designation;
	}

	public Long getSalary() {
		return salary;
	}

	public void setSalary(Long salary) {
		this.salary = salary;
	}

	public String getLocation() {
		return location;
	}

	public void setLocation(String location) {
		this.location = location;
	}

	@Override
	public String toString() {
		return "Employee{" + "id=" + id + ", name='" + name + '\'' + ", designation='" + designation + '\''
				+ ", salary=" + salary + ", location='" + location + '\'' + '}';
	}
}

EmployeeMapper

package com.praveen.employee.mapper;

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

import org.springframework.jdbc.core.RowMapper;

import com.praveen.employee.entity.EmployeeJdbc;

public class EmployeeMapper implements RowMapper<EmployeeJdbc> {
	public EmployeeJdbc mapRow(ResultSet rs, int rowNum) throws SQLException {
		return new EmployeeJdbc(rs.getLong("id"), rs.getString("name"), rs.getString("designation"),
				rs.getLong("salary"), rs.getString("location"));
	}
}

Note

If the column names in a table and field names inside a POJO or Bean are matching then we can use BeanPropertyRowMapper which is provided by Spring framework.

Spring BeanPropertyRowMapper class saves you lot of time since we dont have to define the mappings like we do inside a RowMapper implementation.

For example,

EmployeeDao

package com.praveen.employee.dao;

import java.util.List;
import java.util.Optional;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.praveen.employee.entity.EmployeeJdbc;
import com.praveen.employee.mapper.EmployeeMapper;

@Repository
public class EmployeeDao {

	private final JdbcTemplate jdbcTemplate;

	public EmployeeDao(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<EmployeeJdbc> findAll() {
		String query = "SELECT * FROM employee";
		return jdbcTemplate.query(query, new EmployeeMapper());
	}

	public List<EmployeeJdbc> findByNameContaining(String name) {
		String query = "SELECT * FROM employee WHERE name LIKE ?";
		return jdbcTemplate.query(query, new EmployeeMapper(), "%" + name + "%");
	}

	public Optional<EmployeeJdbc> findById(Long id) {
		String query = "SELECT * FROM employee WHERE id = ?";
		try {
			EmployeeJdbc employee = jdbcTemplate.queryForObject(query, new EmployeeMapper(), id);
			return Optional.ofNullable(employee);
		} catch (EmptyResultDataAccessException e) {
			return Optional.empty();
		}
	}

	public int save(EmployeeJdbc employee) {
		String query = "INSERT INTO employee (name, designation, salary, location) VALUES (?, ?, ?, ?)";
		return jdbcTemplate.update(query, employee.getName(), employee.getDesignation(), employee.getSalary(),
				employee.getLocation());
	}

	public int update(EmployeeJdbc employee) {
		String query = "UPDATE employee SET name = ?, designation = ?, salary = ?, location = ? WHERE id = ?";
		return jdbcTemplate.update(query, employee.getName(), employee.getDesignation(), employee.getSalary(),
				employee.getLocation(), employee.getId());
	}

	public int deleteById(Long id) {
		String query = "DELETE FROM employee WHERE id = ?";
		return jdbcTemplate.update(query, id);
	}
}

Pros

  • High performance.
  • Full control over SQL.
  • Comprehensive exception handling.

Cons

  • Requires manual mapping of rows to objects.
  • More boilerplate code compared to JPA.

Use Cases

  • Applications requiring complex and high-performance queries.
  • Situations where direct SQL access is preferred.