Blog

Hey guys in this post, we will discuss creating Spring boot REST API that connects to the relational database MySQL and perform the database operations such as Create, Read, Update and Delete (CRUD) using Spring JdbcTemplate with full code example

Overview

• JDBCTemplate is class which will help us to query the database
• JDBCTemplate implements JdbcOperations which provides methods such as query(), execute(), update() etc.,
• BeanPropertyRowMapper is a RowMapper implementation that converts a table row into a new instance of the specified mapped target class.
• The mapped target class must be a top-level class and it must have a default or no-arg constructor.
• queryForObject()is a method provided by JdbcTemplate, which is used to fetch the single record from the database
• queryForObject() takes 3 parameters, sql_query, row_mapper and parameter
• update()is a method provided by JdbcTemplate, which is used to delete, update and insert a single record from the database
• update() takes 2 parameters, sql_query and parameter

Complete Example

We will create this example step by step, follow this tutorial till the end

Read More:

• Check the Complete JavaServer Faces (JSF) Tutorial
• Check the Complete Spring Data JPA Tutorial
• Check the Complete Spring Security Tutorial
• Check the Javascript Projects for Beginners

Create a Database

Open the mysql console or workbench and execute the following queries

CREATE DATABASE springbootjdbc;

USE springbootjdbc;

CREATE TABLE tbl_employees
(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    location VARCHAR(255) NOT NULL,
    department VARCHAR(255) NOT NULL
);

INSERT INTO tbl_employees
(name, location, department)
VALUES
("Bushan", "India", "IT");

INSERT INTO tbl_employees
(name, location, department)
VALUES
("Bharath", "India", "Sales");

Create spring boot project

There are many different ways to create a spring boot application, you can follow the below articles to create one –

>> Create spring boot application using Spring initializer
>> Create spring boot application in Spring tool suite [STS]
>> Create spring boot application in IntelliJ IDEA

Add maven dependencies

Open pom.xml and add the following dependencies –

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.3</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>in.bushansirgur</groupId>
	<artifactId>springbootjdbc</artifactId>
	<version>1.0.0</version>
	<name>springbootjdbc</name>
	<description>Spring boot jdbc project</description>
	<properties>
		<java.version>16</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

spring-boot-starter-web dependency for building web applications using Spring MVC. It uses the tomcat as the default embedded container.

spring-boot-devtools dependency for automatic reloads or live reload of applications. spring-boot-starter-jdbc is a starter dependency for communicating with the database. lombok dependency is a java library that will reduce the boilerplate code that we usually write inside every entity class like setters, getters, and toString(). mysql-connector-java dependency is to store the data into the database.

Configure the datasource

Open application.properties file and add the following contents

spring.datasource.url=jdbc:mysql://localhost:3306/springbootjdbc
spring.datasource.username=root
spring.datasource.password=scbushan05

Create an entity class

Create Employee.java inside the in.bushansirgur.springboot.entity package and add the following content

package in.bushansirgur.springbootjdbc.entity;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Setter
@Getter 
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
	
	private Long id;
	
	private String name;
	
	private String location;
	
	private String department;
	
	public Employee(String name, String location, String department) {
		this.name = name;
		this.location = location;
		this.department = department;
	}
}

• @Setter annotation generates the setter methods
• @Getter annotation generates the getter methods
• @AllArgsConstructor generates parameterised constructor with all the fields
• @NoArgsConstructor generates no-arg constructor

Create a DAO

Create an interface EmployeeDAO.java inside the in.bushansirgur.springboot.dao package and add the following content

package in.bushansirgur.springbootjdbc.DAO;

import java.util.List;

import in.bushansirgur.springbootjdbc.entity.Employee;

public interface EmployeeDAO {
	
	public List<Employee> findAll();
	
	public Employee findById(int id);
	
	public int deleteById(int id);
	
	public int save(Employee e);
	
	public int update(Employee e, int id);
}

Create EmployeeDAOImpl.java inside the in.bushansirgur.springboot.dao package and add the following content

package in.bushansirgur.springbootjdbc.DAO;

import java.util.List;

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 in.bushansirgur.springbootjdbc.entity.Employee;

@Repository
public class EmployeeDAOImpl implements EmployeeDAO{

	@Autowired
	JdbcTemplate jdbcTemplate;
	
	@Override
	public List<Employee> findAll() {
		return jdbcTemplate.query("SELECT * FROM tbl_employees", new BeanPropertyRowMapper<Employee>(Employee.class));
	}

	@Override
	public Employee findById(int id) {
		return jdbcTemplate.queryForObject("SELECT * FROM tbl_employees WHERE id=?", new BeanPropertyRowMapper<Employee>(Employee.class), id);
	}

	@Override
	public int deleteById(int id) {
		return jdbcTemplate.update("DELETE FROM tbl_employees WHERE id=?", id);
	}

	@Override
	public int save(Employee e) {
		return jdbcTemplate.update("INSERT INTO tbl_employees (name, location, department) VALUES (?, ?, ?)", new Object[] {e.getName(), e.getLocation(), e.getDepartment()});
	}

	@Override
	public int update(Employee e, int id) {
		return jdbcTemplate.update("UPDATE tbl_employees SET name = ?, location = ?, department = ? WHERE id = ?", new Object[] {e.getName(), e.getLocation(), e.getDepartment(), id});
	}
		
}

Create a Controller

Create EmployeeController.java inside the in.bushansirgur.springbootjdbc.controller package and add the following content

package in.bushansirgur.springbootjdbc.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
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.RestController;

import in.bushansirgur.springbootjdbc.DAO.EmployeeDAO;
import in.bushansirgur.springbootjdbc.entity.Employee;

@RestController
public class EmployeeController {
	
	@Autowired 
	private EmployeeDAO eDAO;
	
	@GetMapping("/employees")
	public List<Employee> findAll() {
		return eDAO.findAll();
	}
	
	@GetMapping("/employees/{id}")
	public Employee findById(@PathVariable int id) {
		return eDAO.findById(id);
	}
	
	@DeleteMapping("/employees/{id}")
	public String deleteById(@PathVariable int id) {
		return eDAO.deleteById(id)+" Employee(s) delete from the database";
	}
	
	@PostMapping("/employees")
	public String save(@RequestBody Employee e) {
		return eDAO.save(e)+" Employee(s) saved successfully";
	}
	
	@PutMapping("/employees/{id}")
	public String update(@RequestBody Employee e, @PathVariable int id) {
		return eDAO.update(e, id)+" Employee(s) updated successfully";
	}
	
}

Run the app

Run the application using the below maven command –

mvn spring-boot:run

Open the Browser or Postman and enter the following URL –

• localhost:8080/employees

—

• localhost:8080/employees/2

—

• localhost:8080/employees/3

—

• localhost:8080/employees

—

• localhost:8080/employees/3

—
That’s it for this post. I hope you enjoyed this post, if you did then please share this with your friends and colleagues. Also, you can share this post with your social media profiles. Thank you I will see you in the next post.