Spring Boot CRUD Example with MS SQL Server: CRUD REST API Step-By-Step Guide

In this tutorial, we will build a Spring Boot CRUD (Create, Read, Update, Delete) application using MS SQL Server. We will build CRUD RESTful web services for creating, reading, updating, and deleting products.

Spring Boot Three-Layer Architecture

+-----------+      +-----------------+
|           |      | Controller Layer|
|  Postman  +----->+ (Handles HTTP   |
| (Client)  |      |  requests and   |
+-----------+      |  responses)     |
                   +--------+--------+
                            |
                            v
                   +--------+--------+
                   | Service Layer   |
                   | (Implements     |
                   |  business logic |
                   |  and data       |
                   |  transformations)|
                   +--------+--------+
                            |
                            v
                   +--------+--------+
                   | Repository Layer|
                   | (Manages data   |
                   |  operations with|
                   |  MS SQL Server) |
                   +--------+--------+
                            |
                            v
                   +--------+--------+
                   |   MS SQL Server |
                   | (Database for   |
                   |  storing data)  |
                   +-----------------+

Description of the diagram

• Postman (Client): Acts as the external client that sends HTTP requests to the Spring Boot application. This is where the interaction with the API takes place, typically used for testing API endpoints.
• Controller Layer: The first point of interaction in the application, which handles incoming HTTP requests, routes them to the appropriate services, and returns the responses. It’s essential for directing the flow of data in and out of the application.
• Service Layer: Contains the business logic of the application. It processes data, applying business rules as well as making calls to the repository layer for data persistence and retrieval.
• Repository Layer: Directly manages the data of the application, interacting with Microsoft SQL Server. This layer’s responsibility is to retrieve data from the database, manipulate it as needed, and update or persist changes back to the database.
• Microsoft SQL Server: The backend database system used for storing all application data persistently. It is responsible for data storage, retrieval, and management, ensuring data integrity and security.

What You’ll Learn:

• Setting up a Spring Boot project with MS SQL Server.
• Configuring Spring Boot to connect to MS SQL Server.
• Implementing CRUD operations with Spring Data JPA.
• Using Java record for DTOs to transfer data.
• Testing REST APIs using Postman.

Prerequisites

Before starting, ensure you have:

• Java Development Kit (JDK) 17 or later
• Apache Maven (for project management)
• MS SQL Server (installed locally or via a cloud service like Azure)
• IDE (e.g., IntelliJ IDEA, Eclipse, or VS Code)
• Postman (to test APIs)

Step 1: Setting Up the Project

1.1 Create a Spring Boot Project

1. Open Spring Initializr.
2. Configure the project metadata:
   • Project: Maven
   • Language: Java
   • Spring Boot Version: Latest (3.x)
   • Group: com.example
   • Artifact: spring-boot-mssql-crud
   • Java Version: 17 or later
3. Add the following dependencies:
   • Spring Web: For building RESTful web services.
   • Spring Data JPA: For interacting with MS SQL Server using JPA (Java Persistence API).
   • MS SQL Server Driver: To connect Spring Boot with MS SQL Server.
   • Spring Boot DevTools: For hot reloading during development.
4. Click Generate to download the project, extract the zip file, and open it in your IDE.

Explanation:

Spring Initializr generates a pre-configured Spring Boot project with the required dependencies for MS SQL Server. We will use the MS SQL Server JDBC Driver to connect to the SQL Server.

Step 2: Configuring MS SQL Server

2.1 Create a Database in MS SQL Server

Use SQL Server Management Studio (SSMS) or the command line to create a new database for your application:

CREATE DATABASE ProductDB;

2.2 Configure application.properties

In the src/main/resources/application.properties file, configure Spring Boot to connect to MS SQL Server:

# MS SQL Server database configuration
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=ProductDB
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

# JPA configurations
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Replace your_username and your_password with your MS SQL Server credentials.

Explanation:

• spring.datasource.url: JDBC URL to connect to MS SQL Server. The format is jdbc:sqlserver://[host]:[port];databaseName=[DB name].
• spring.datasource.username and password: Your MS SQL Server login credentials.
• spring.jpa.hibernate.ddl-auto=update: Automatically updates the database schema based on the entity mappings.

Step 3: Creating the Product Entity

3.1 Create the Product Entity

In the model package, create a Java class named Product to represent the product entity in the database.

package com.example.springbootmssqlcrud.model;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String description;
    private double price;

    // Getters and Setters
    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 getDescription() { return description; }
    public void setDescription(String description) { this.description = description; }

    public double getPrice() { return price; }
    public void setPrice(double price) { this.price = price; }
}

Explanation:

• @Entity: Marks this class as a JPA entity, meaning it will map to a table in the MS SQL Server database.
• @Id and @GeneratedValue: Defines the primary key and specifies the IDENTITY strategy for auto-generating values.
• Fields: The Product entity has fields for name, description, and price.

Step 4: Creating the Repository

4.1 Create ProductRepository

In the repository package, create an interface ProductRepository that extends JpaRepository:

package com.example.springbootmssqlcrud.repository;

import com.example.springbootmssqlcrud.model.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
}

Explanation:

• JpaRepository: Provides all the necessary CRUD methods such as save(), findAll(), findById(), and deleteById() without the need to write them manually.
• @Repository: Marks this interface as a Spring repository, enabling automatic CRUD operations on the Product entity.

Step 5: Using Java record for DTO

5.1 Create the ProductDTO Class

In the model package, create a Java record called ProductDTO:

package com.example.springbootmssqlcrud.model;

public record ProductDTO(Long id, String name, String description, double price) {}

Explanation:

• Java record: A special kind of class in Java that reduces boilerplate code by automatically generating constructors, getters, toString(), equals(), and hashCode() methods. It’s ideal for use as a DTO to transfer data between the server and client.

Step 6: Creating the Service Layer

6.1 Create ProductService Interface

In the service package, define an interface ProductService:

package com.example.springbootmssqlcrud.service;

import com.example.springbootmssqlcrud.model.ProductDTO;

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

public interface ProductService {
    List<ProductDTO> getAllProducts();
    Optional<ProductDTO> getProductById(Long id);
    ProductDTO saveProduct(ProductDTO productDTO);
    ProductDTO updateProduct(Long id, ProductDTO productDTO);
    void deleteProduct(Long id);
}

6.2 Implement ProductService in ProductServiceImpl

In the service implementation, add the conversion logic between Product (entity) and ProductDTO (DTO):

package com.example.springbootmssqlcrud.service;

import com.example.springbootmssqlcrud.model.Product;
import com.example.springbootmssqlcrud.model.ProductDTO;
import com.example.springbootmssqlcrud.repository.ProductRepository;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

@Service
public class ProductServiceImpl implements ProductService {

    private final ProductRepository productRepository;

    public ProductServiceImpl(ProductRepository productRepository) {
        this.productRepository = productRepository;
    }

    @Override
    public List<ProductDTO> getAllProducts() {
        return productRepository.findAll().stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
    }

    @Override
    public Optional<ProductDTO> getProductById(Long id) {
        return productRepository.findById(id).map(this::convertToDTO);
    }

    @Override
    public ProductDTO saveProduct(ProductDTO productDTO) {
        Product product = convertToEntity(productDTO);
        Product savedProduct = productRepository.save(product);
        return convertToDTO(savedProduct);
    }

    @Override
    public ProductDTO updateProduct(Long id, ProductDTO productDTO) {
        Product product = productRepository.findById(id).orElseThrow();
        product.setName(productDTO.name());
        product.setDescription(productDTO.description());
        product.setPrice(productDTO.price());
        Product updatedProduct = productRepository.save(product);
        return convertToDTO(updatedProduct);
    }

    @Override
    public void deleteProduct(Long id) {
        productRepository.deleteById(id);
    }

    // Convert Product Entity to ProductDTO
    private ProductDTO convertToDTO(Product product) {
        return new ProductDTO(product.getId(), product.getName(), product.getDescription(), product.getPrice());
    }

    // Convert ProductDTO to Product Entity
    private Product convertToEntity(ProductDTO productDTO) {
        Product

 product = new Product();
        product.setName(productDTO.name());
        product.setDescription(productDTO.description());
        product.setPrice(productDTO.price());
        return product;
    }
}

Explanation:

• Service layer: Responsible for handling all business logic, including converting between Product and ProductDTO.
• convertToDTO: Converts a Product entity into a ProductDTO.
• convertToEntity: Converts a ProductDTO into a Product entity.

Step 7: Creating the REST Controller

In the controller package, create the ProductController to expose REST APIs:

package com.example.springbootmssqlcrud.controller;

import com.example.springbootmssqlcrud.model.ProductDTO;
import com.example.springbootmssqlcrud.service.ProductService;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

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

@RestController
@RequestMapping("/api/products")
public class ProductController {

    private final ProductService productService;

    public ProductController(ProductService productService) {
        this.productService = productService;
    }

    @GetMapping
    public List<ProductDTO> getAllProducts() {
        return productService.getAllProducts();
    }

    @GetMapping("/{id}")
    public ResponseEntity<ProductDTO> getProductById(@PathVariable Long id) {
        Optional<ProductDTO> product = productService.getProductById(id);
        return product.map(ResponseEntity::ok).orElseGet(() -> ResponseEntity.notFound().build());
    }

    @PostMapping
    public ProductDTO createProduct(@RequestBody ProductDTO productDTO) {
        return productService.saveProduct(productDTO);
    }

    @PutMapping("/{id}")
    public ResponseEntity<ProductDTO> updateProduct(@PathVariable Long id, @RequestBody ProductDTO productDTO) {
        try {
            ProductDTO updatedProduct = productService.updateProduct(id, productDTO);
            return ResponseEntity.ok(updatedProduct);
        } catch (Exception e) {
            return ResponseEntity.notFound().build();
        }
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteProduct(@PathVariable Long id) {
        productService.deleteProduct(id);
        return ResponseEntity.noContent().build();
    }
}

Explanation:

• The controller exposes CRUD endpoints for managing products.
• All the business logic is handled by the service layer, ensuring the controller focuses on handling HTTP requests.

Step 8: Running and Testing the Application

8.1 Running the Application

To run the application, open SpringBootMssqlCrudApplication.java and click the Run button in your IDE, or run the following command in the terminal:

./mvnw spring-boot:run

8.2 Testing with Postman

You can test the REST APIs using Postman:

• GET all products:
  • URL: http://localhost:8080/api/products
  • Response: A list of product DTOs.
• GET product by ID:
  • URL: http://localhost:8080/api/products/{id}
  • Response: The product data for the given ID.
• POST create a new product:
  • URL: http://localhost:8080/api/products
  • Body:

    {
      "name": "Laptop",
      "description": "A high-performance laptop.",
      "price": 1200.00
    }
    

• PUT update an existing product:
  • URL: http://localhost:8080/api/products/{id}
  • Body:

    {
      "name": "Updated Laptop",
      "description": "An updated high-performance laptop.",
      "price": 1300.00
    }
    

• DELETE a product:
  • URL: http://localhost:8080/api/products/{id}
  • Response: No content (HTTP 204).

Conclusion

In this tutorial, we built a Spring Boot CRUD REST API with MS SQL Server. We followed best practices by using Java record as the DTO for transferring data and kept the conversion logic between the entity and DTO in the service layer. We also learned how to test the APIs using Postman.

This guide provides a clean and well-structured approach to building a Spring Boot CRUD application with MS SQL Server, ensuring maintainable and easy-to-extend code.