Spring Boot RESTful Web Service with JPA and MySQL

By Atul Rai | Last Updated: October 16, 2018 Previous       Next

In this article, you will learn how to build a RESful webs service in Spring Boot using JPA repository and MySQL database. Here we will create a RESTful web service that retrieves the data from the database based on the query and response in JSON format. Spring Boot uses the spring-boot-starter-data-jpa to create the database connection using Hibernate. You can define your database credentials in the application.yml or application.properties.

Here we can only fetch the records from the database. If you want to fetch particular employee records, your end-point URL will be:

http://localhost:8888/employee/7698

you will get the response as given below…

{
    "empNo": 7698,
    "name": "BLAKE",
    "job": "MANAGER"
}

And to fetch all employee records, your end-point URL will be:

http://localhost:8888/employee/all

Technologies Used

Find the list of all technologies used in this application.

• Spring Tool Suite 4
• JDK 8
• Maven 3
• Spring-boot 2.0.5.RELEASE
• MySQL Database

Database Schema

Find the table structure and insert statement of MySQL database used in this example.

/*`emp` table structure */
CREATE TABLE `emp` (
  `empno` decimal(4,0) NOT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` decimal(4,0) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` decimal(2,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `emp` */

insert  into `emp`(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`comm`,`deptno`) values ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20'),('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30'),('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30'),('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20'),('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');

Dependencies Required

Building a RESTful web service in Spring Boot, you need the following dependencies. Add the following code in your pom.xml.

<?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>org.websparrow</groupId>
	<artifactId>sring-boot-jpa-mysql-rest</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<name>sring-boot-jpa-mysql-rest</name>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.5.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</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>
			</plugin>
		</plugins>
	</build>

</project>

Project Structure

Final project structure of our application in STS ide will look like as follows.

Application Configuration

Configure the database credentials like database name, username, password, and Tomcat server port, etc in the application.yml file.

server:
  port: 8888

spring:
  datasource:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/websparrow
    username: root
    password:
  jpa:
    hibernate.ddl-auto: update
    generate-ddl: true
    show-sql: true

Model Class

Create a model class for the Emp table. This class contains the table name and it columns name that you want to fetch from the database.

package org.websparrow.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "emp")
public class Employee {

	// TODO- Generate Getters and Setter of all the fields

	@Id
	@GeneratedValue
	@Column(name = "empno")
	private Integer empNo;
	@Column(name = "ename")
	private String name;
	@Column(name = "job")
	private String job;
	
}

Repository

EmployeeRepository interface extends the JpaRepository. JPA is the standard way of persisting Java objects into relational databases. By extending the JpaRepository interface, our EmployeeRepository has the capabilities to query with the database like listing the all the employees, find the employee by id, etc.

package org.websparrow.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.websparrow.model.Employee;

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

}

Controller Class

In Spring’s approach to building RESTful web services, HTTP requests are handled by a controller. These components are easily identified by the @RestController annotation, and the EmployeeController handles GET requests.

package org.websparrow.controller;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.websparrow.model.Employee;
import org.websparrow.repository.EmployeeRepository;

@RestController
@RequestMapping("/employee")
public class EmployeeController {

	@Autowired
	EmployeeRepository empRepo;

	@GetMapping(value = "/all")
	public List<Employee> getAll() {
		return empRepo.findAll();
	}

	@GetMapping(value = "/{id}")
	public Optional<Employee> getOne(@PathVariable("id") Integer id) {
		return empRepo.findById(id);
	}

}

Execute It

Create an App class and run it.

package org.websparrow;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class App {

	public static void main(String[] args) {
		SpringApplication.run(App.class, args);
	}
}

Test the Service

To fetch the specific employee records, visit http://localhost:8888/employee/7839, where 7839 is employee id and you will get:

{
    "empNo": 7839,
    "name": "KING",
    "job": "PRESIDENT"
}

As Spring uses the Hibernate to query from the database, you can see the HQL in your console log.

Hibernate: select employee0_.empno as empno1_0_0_, employee0_.job as job2_0_0_, employee0_.ename as ename3_0_0_ from emp employee0_ where employee0_.empno=?

And to fetch all employee records, visit http://localhost:8888/employee/all

[
    {
        "empNo": 7369,
        "name": "SMITH",
        "job": "CLERK"
    },
    {
        "empNo": 7499,
        "name": "ALLEN",
        "job": "SALESMAN"
    },
    {
        "empNo": 7521,
        "name": "WARD",
        "job": "SALESMAN"
    }
]

References

1. Spring Boot RESTful Web Service Example
2. JPA Repositories
3. Interface JpaRepository<T,ID>