Spring Boot + Microsoft SQL Server + JPA/Hibernate CRUD Restful API Tutorial


In this article, you’ll learn how to configure Spring Boot to use the Microsoft SQL Server database and build a Restful CRUD API for Employee Management System.

You’ll also learn how Spring Data JPA and Hibernate can be used with a Microsoft SQL database. Before development, make sure that the MS-SQL server is installed on your machine.

Spring makes switching between RDBMS simple. When you’re using Spring Data JPA with an ORM technology such as Hibernate, the persistence layer is nicely well decoupled. As we are using Hibernate so it will support out of the box to work with different database vendors without changing underlying code.

You may like these similar articles with different databases:

MS-SQL Server Dependencies

To connect with SQL Server from Java applications, Microsoft provides a Microsoft JDBC Driver for SQL Server. However, till November 2016, Maven did not directly support the driver as it was not open source. By making it open source, Microsoft finally made the driver available on the Maven Central Repository. More information can be found here.

Follow these quick two steps to configure the Microsoft SQL server in the Spring boot application:

Step 1: Provide MS-SQL driver dependency in your pom.xml file:

<dependency>
 <groupId>com.microsoft.sqlserver</groupId>
 <artifactId>sqljdbc4</artifactId>
 <version>4.0</version>
</dependency>

Step 2: Let’s configure Spring Boot to use the MS-SQL server database as our data source. We are simply adding the Microsoft SQL server URL, username, and password in the src/main/resources/application.properties file -
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://localhost;databaseName=employees
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql = true

## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.SQLServer2012Dialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update
That's all. Now you are good to go.

Let's develop step-by-step a complete CRUD RESTFul APIs for a Simple Employee Management System using Spring Boot 2 JPA and Microsoft SQL database.

Table of Contents

  1. What we’ll build?
  2. Tools and Technologies Used
  3. Creating and Importing a Project
  4. Packaging Structure
  5. The pom.xml File
  6. Configuring MS-SQL Server Database
  7. Create JPA Entity - Employee.java
  8. Create Spring Data Repository - EmployeeRepository.java
  9. Exception(Error) Handling for RESTful Services
  10. Create Spring Rest Controller - EmployeeController.java
  11. Running Application
  12. Integration Testing for REST APIs
  13. Testing REST APIs via Postman Client
  14. Source code on GitHub Repository

1. What we’ll build

We will build CRUD RESTFul APIs for a Simple Employee Management System using Spring Boot, Spring Data JPA (JPA/Hibernate), and MS-SQL server database.

The following are five REST APIs (Controller handler methods) that we are going to create for the Employee resource.

2. Tools and Technologies Used

  • Spring Boot - 2.0.4.RELEASE
  • JDK - 1.8 or later
  • Spring Framework - 5.0.8 RELEASE
  • Hibernate - 5.2.17.Final
  • JPA
  • Maven - 3.2+
  • IDE - Eclipse or Spring Tool Suite (STS)
  • Microsoft SQL server - 4.0

3. Creating and Importing a Project

There are many ways to create a Spring Boot application. The simplest way is to use Spring Initializr, which is an online Spring Boot application generator.
Use the following details while creating a Spring boot project using Spring Initializr:
  • Generate: Maven Project
  • Java Version: 1.8 (Default)
  • Spring Boot: always use the latest version
  • Group: net.guides.springboot2
  • Artifact: springboot2-mssql-jpa-hibernate-crud-example
  • Name: springboot2-mssql-jpa-hibernate-crud-example
  • Description: springboot2-mssql-jpa-hibernate-crud-example
  • Package Name : net.guides.springboot2.crud
  • Packaging: jar (This is the default value)
  • Dependencies: Spring Web, JPA
Once, all the details are entered, click on Generate Project button will generate a spring boot project and downloads it. Next, Unzip the downloaded zip file and import it into your favorite IDE.

4. Packaging Structure

Following is the packing structure of our Employee Management System -

5. The pom.xml File

Note that this pom.xml file contains the MS-SQL database driver:
<?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>net.guides.springboot2</groupId>
    <artifactId>springboot2-mssql-jpa-hibernate-crud-example</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springboot2-mssql-jpa-hibernate-crud-example</name>
    <description>springboot2-mssql-jpa-hibernate-crud-example</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath />
        <!-- lookup parent from reposictory -->
    </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>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </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>
Make sure to add MS-SQL database driver dependency to the pom.xml file:
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>

6. Configuring MS-SQL Server Database

Let’s configure Spring Boot to use the Microsoft SQL server as our data source. You can do that simply by adding the Microsoft SQL database URL, username, and password in the src/main/resources/application.properties file -
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://localhost;databaseName=employees
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql = true

## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.SQLServer2012Dialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update
Don’t forget to change the spring.datasource.username and spring.datasource.password as per your MS-SQL database installation. Also, create a database named employees in the MS-SQL server database before proceeding to the next section.
You don’t need to create any tables. The tables will automatically be created by Hibernate from the Employee entity that we will define in the next step. This is made possible by the property spring.jpa.hibernate.ddl-auto = update.

7. Create JPA Entity - Employee.java

Let's create an Employee model or domain class with the following fields: 
  • id - primary key
  • firstName - user first name
  • lastName - user last name
  • emailId - user email ID
package net.guides.springboot2.crud.model;

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

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

    private long id;
    private String firstName;
    private String lastName;
    private String emailId;
 
    public Employee() {
  
    }
 
    public Employee(String firstName, String lastName, String emailId) {
         this.firstName = firstName;
         this.lastName = lastName;
         this.emailId = emailId;
    }
 
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
        public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
 
    @Column(name = "first_name", nullable = false)
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
 
    @Column(name = "last_name", nullable = false)
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
 
    @Column(name = "email_address", nullable = false)
    public String getEmailId() {
        return emailId;
    }
    public void setEmailId(String emailId) {
        this.emailId = emailId;
    }

    @Override
    public String toString() {
        return "Employee [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", emailId=" + emailId
       + "]";
    }
 
}

8. Create Spring Data Repository - EmployeeRepository.java

Let's create an EmployeeRepository to access Employee data from the database.

Well, Spring Data JPA comes with a JpaRepository interface that defines methods for all the CRUD operations on the entity, and a default implementation of JpaRepository called SimpleJpaRepository.
package net.guides.springboot2.crud.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import net.guides.springboot2.springboot2jpacrudexample.model.Employee;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long>{

}

9. Exception(Error) Handling for RESTful Services

Spring Boot provides a good default implementation for exception handling for RESTful Services. Let’s quickly look at the default Exception Handling features provided by Spring Boot.

Resource Not Present

Here's what happens when you fire a request to not resource found: http://localhost:8080/some-dummy-url
{
  "timestamp": 1512713804164,
  "status": 404,
  "error": "Not Found",
  "message": "No message available",
  "path": "/some-dummy-url"
}
That's a cool error response. It contains all the details that are typically needed.

What happens when we throw an Exception?

Let’s see what Spring Boot does when an exception is thrown from a Resource. we can specify the Response Status for a specific exception along with the definition of the Exception of the ‘@ResponseStatus’ annotation.
Let's create a ResourceNotFoundException.java class.
package net.guides.springboot2.crud.exception;

import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ResponseStatus;

@ResponseStatus(value = HttpStatus.NOT_FOUND)
public class ResourceNotFoundException extends Exception{

    private static final long serialVersionUID = 1L;

    public ResourceNotFoundException(String message){
        super(message);
    }
}

Customizing Error Response Structure

The default error response provided by Spring Boot contains all the details that are typically needed.
However, you might want to create a framework independent response structure for your organization. In that case, you can define a specific error response structure.
Let’s define a simple error response bean.
package net.guides.springboot2.crud.exception;

import java.util.Date;

public class ErrorDetails {
    private Date timestamp;
    private String message;
    private String details;

    public ErrorDetails(Date timestamp, String message, String details) {
         super();
         this.timestamp = timestamp;
         this.message = message;
         this.details = details;
    }

    public Date getTimestamp() {
         return timestamp;
    }

    public String getMessage() {
         return message;
    }

    public String getDetails() {
         return details;
    }
}
To use ErrorDetails to return the error response, let’s create a GlobalExceptionHandler class annotated with @ControllerAdvice annotation. This class handles exception-specific and global exceptions in a single place.
package net.guides.springboot2.crud.exception;

import java.util.Date;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.context.request.WebRequest;

@ControllerAdvice
public class GlobalExceptionHandler {
    @ExceptionHandler(ResourceNotFoundException.class)
    public ResponseEntity<?> resourceNotFoundException(ResourceNotFoundException ex, WebRequest request) {
         ErrorDetails errorDetails = new ErrorDetails(new Date(), ex.getMessage(), request.getDescription(false));
         return new ResponseEntity<>(errorDetails, HttpStatus.NOT_FOUND);
    }

    @ExceptionHandler(Exception.class)
    public ResponseEntity<?> globleExcpetionHandler(Exception ex, WebRequest request) {
        ErrorDetails errorDetails = new ErrorDetails(new Date(), ex.getMessage(), request.getDescription(false));
        return new ResponseEntity<>(errorDetails, HttpStatus.INTERNAL_SERVER_ERROR);
    }
}

10. Create Spring Rest Controller - EmployeeController.java

Let's create the REST APIs for creating, retrieving, updating, and deleting an Employee:
package net.guides.springboot2.crud.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.validation.Valid;

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

import net.guides.springboot2.springboot2jpacrudexample.exception.ResourceNotFoundException;
import net.guides.springboot2.springboot2jpacrudexample.model.Employee;
import net.guides.springboot2.springboot2jpacrudexample.repository.EmployeeRepository;

@RestController
@RequestMapping("/api/v1")
public class EmployeeController {
    @Autowired
    private EmployeeRepository employeeRepository;

    @GetMapping("/employees")
    public List<Employee> getAllEmployees() {
        return employeeRepository.findAll();
    }

    @GetMapping("/employees/{id}")
    public ResponseEntity<Employee> getEmployeeById(@PathVariable(value = "id") Long employeeId)
        throws ResourceNotFoundException {
        Employee employee = employeeRepository.findById(employeeId)
          .orElseThrow(() -> new ResourceNotFoundException("Employee not found for this id :: " + employeeId));
        return ResponseEntity.ok().body(employee);
    }
    
    @PostMapping("/employees")
    public Employee createEmployee(@Valid @RequestBody Employee employee) {
        return employeeRepository.save(employee);
    }

    @PutMapping("/employees/{id}")
    public ResponseEntity<Employee> updateEmployee(@PathVariable(value = "id") Long employeeId,
         @Valid @RequestBody Employee employeeDetails) throws ResourceNotFoundException {
        Employee employee = employeeRepository.findById(employeeId)
        .orElseThrow(() -> new ResourceNotFoundException("Employee not found for this id :: " + employeeId));

        employee.setEmailId(employeeDetails.getEmailId());
        employee.setLastName(employeeDetails.getLastName());
        employee.setFirstName(employeeDetails.getFirstName());
        final Employee updatedEmployee = employeeRepository.save(employee);
        return ResponseEntity.ok(updatedEmployee);
    }

    @DeleteMapping("/employees/{id}")
    public Map<String, Boolean> deleteEmployee(@PathVariable(value = "id") Long employeeId)
         throws ResourceNotFoundException {
        Employee employee = employeeRepository.findById(employeeId)
       .orElseThrow(() -> new ResourceNotFoundException("Employee not found for this id :: " + employeeId));

        employeeRepository.delete(employee);
        Map<String, Boolean> response = new HashMap<>();
        response.put("deleted", Boolean.TRUE);
        return response;
    }
}

11. Running Application

This spring boot application has an entry point Java class called SpringBootCrudRestApplication.java with the public static void main(String[] args) method, which you can run to start the application.
import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}
@SpringBootApplication is a convenience annotation that adds all of the following:
  • @Configuration tags the class as a source of bean definitions for the application context.
  • @EnableAutoConfiguration tells Spring Boot to start adding beans based on classpath settings, other beans, and various property settings.
  • Normally you would add @EnableWebMvc for a Spring MVC app, but Spring Boot adds it automatically when it sees spring-webmvc on the classpath. This flags the application as a web application and activates key behaviors such as setting up a DispatcherServlet.
  • @ComponentScan tells Spring to look for other components, configurations, and services in the hello package, allowing it to find the controllers.
The main() method uses Spring Boot’s SpringApplication.run() method to launch an application.

12. Integration Testing for REST APIs

There is a separate beautiful article for integration testing for REST APIs on:
Spring Boot 2 REST APIs Integration Testing

13. Testing CRUD REST APIs via Postman Client

1. Create Employee REST API


HTTP Method: POST 
Note that request and response JSON in the above diagram, the response contains database auto-generated id.

2. Get Employee by ID REST API

HTTP Method: GET 

3. Get all Employees REST API

HTTP Method: GET 

4. Update Employee REST API

HTTP Method: PUT

5. Delete Employee REST API

HTTP Method: DELETE 


14. Source code on GitHub

The source code of this tutorial is available on my GitHub Repository

Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours


Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course

Comments

  1. Thanks for sharing informative information

    ReplyDelete
  2. Thank you very much for this concise example. It resolved many questions I had and appears to just work. I especially appreciate your exception handling examples but the thing that really saved my day was seeing the repository and application class examples. I had very much over engineered my initial attempt! :-D

    ReplyDelete
  3. Thank you for the example. You misses a column declaration in Employee.java:
    @Column(name="id")

    ReplyDelete
    Replies
    1. Hi, this @Column(name="id") annotation is optional so if we don't use @Column(name="id") then by default column name should be same as property name. Hence in this case column name is id.

      Delete
  4. Very good article.

    Just a notice: Please correct below http method to PUT:
    4. Update Employee REST API
    HTTP Method: GET

    ReplyDelete
  5. I cannot find the src/main/resources/application.properties file.

    is this from the https://github.com/Microsoft/mssql-jdbc downloads?

    ReplyDelete
  6. Excellent! Just what I was looking for! Works perfect :)

    ReplyDelete
  7. The source code works great!
    but when i tried to re-create the same code on my own
    im facing errors like such
    org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'employeeRepository' defined in com.example.demo.repository.

    anyone have any clues on how to solve this error?

    ReplyDelete
    Replies
    1. Double check the following: your annotations are all in place, and you haven't instantiated your bean with the New operator anywhere (except when you register the bean in the application class). If that doesn't help, post your code, or take a look on StackOverflow.

      Delete
    2. thank you for the reply
      i just found out that its ms sql driver dependency thats causing the problem
      you have to fix the dependency manually if using maven build,
      after fixing the dependency/ or simply changed to gradle helps me solve the problem i had!

      Delete
    3. @Michael2060 - I'm running into the same issue as you. What do you mean by "you have to fix the dependency manually if using maven build"? Don't want to switch to Gradle, would rather "fix the dependency" but not sure what that means.

      Delete

Post a Comment