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
Table of Contents
Overview
JDBCTemplate
is class which will help us to query the databaseJDBCTemplate
implementsJdbcOperations
which provides methods such asquery()
,execute()
,update()
etc.,BeanPropertyRowMapper
is aRowMapper
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 byJdbcTemplate
, which is used to fetch the single record from the databasequeryForObject()
takes 3 parameters,sql_query
,row_mapper
andparameter
update()
is a method provided byJdbcTemplate
, which is used to delete, update and insert a single record from the databaseupdate()
takes 2 parameters,sql_query
andparameter
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.