Spring Boot CRUD Example with MS SQL Server

In this tutorial, we will create a Spring Boot CRUD (Create, Read, Update, Delete) application with an MS SQL Server database. We will cover setting up the project, configuring MS SQL Server, creating the necessary entities and repositories, implementing the service layer, and creating RESTful controllers to handle CRUD operations using a Product entity.

Prerequisites

Before we start, ensure you have the following:

• Java Development Kit (JDK) installed
• Apache Maven installed
• MS SQL Server installed and running
• An IDE (such as IntelliJ IDEA, Eclipse, or VS Code) installed

Step 1: Setting Up the Project

1.1 Create a Spring Boot Project

1. Open Spring Initializr:

   • Go to Spring Initializr in your web browser.

2. Configure Project Metadata:

   • Project: Maven Project
   • Language: Java
   • Spring Boot: Select the latest version of Spring Boot
   • Group: com.example
   • Artifact: spring-boot-mssql-crud
   • Name: spring-boot-mssql-crud
   • Description: Spring Boot CRUD example with MS SQL Server
   • Package Name: com.example.springbootmssqlcrud
   • Packaging: Jar
   • Java Version: 17 (or your preferred version)
   • Click Next.

3. Select Dependencies:

   • On the Dependencies screen, select the dependencies you need. For a basic Spring Boot CRUD application, you can start with:
     • Spring Web
     • Spring Data JPA
     • SQL Server Driver
   • Click Next.

4. Generate the Project:

   • Click Generate to download the project zip file.
   • Extract the zip file to your desired location.

5. Open the Project in Your IDE:

   • Open your IDE and import the project as a Maven project.

1.2 Project Structure

After importing the project, you will see the following structure in your IDE:

spring-boot-mssql-crud
├── src
│   ├── main
│   │   ├── java
│   │   │   └── com
│   │   │       └── example
│   │   │           └── springbootmssqlcrud
│   │   │               ├── SpringBootMssqlCrudApplication.java
│   │   │               ├── controller
│   │   │               ├── model
│   │   │               ├── repository
│   │   │               └── service
│   ├── main
│   │   └── resources
│   │       ├── application.properties
│   └── test
│       └── java
│           └── com
│               └── example
│                   └── springbootmssqlcrud
│                       └── SpringBootMssqlCrudApplicationTests.java
└── pom.xml

Step 2: Configuring MS SQL Server

2.1 Create a MS SQL Server Database

1. Open your SQL Server Management Studio (SSMS) or any other SQL client.
2. Create a new database named springbootdb:

   CREATE DATABASE springbootdb;
   

2.2 Configure Spring Boot to Use MS SQL Server

Open the application.properties file located in the src/main/resources directory and add the following configuration:

# MS SQL Server Database configuration
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=springbootdb
spring.datasource.username=yourusername
spring.datasource.password=yourpassword
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.database-platform=org.hibernate.dialect.SQLServerDialect

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

Replace yourusername and yourpassword with your MS SQL Server credentials.

Step 3: Creating the Entity

3.1 Create the Product Entity

In the model package, create a new Java class named Product:

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;
    }
}

Step 4: Creating the Repository

4.1 Create the ProductRepository Interface

In the repository package, create a new Java interface named ProductRepository:

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> {
}

Step 5: Creating the Service

5.1 Create the ProductService Interface

In the service package, create a new Java interface named ProductService:

package com.example.springbootmssqlcrud.service;

import com.example.springbootmssqlcrud.model.Product;

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

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

5.2 Implement the ProductService Interface

In the service package, create a new Java class named ProductServiceImpl:

package com.example.springbootmssqlcrud.service;

import com.example.springbootmssqlcrud.model.Product;
import com.example.springbootmssqlcrud.repository.ProductRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class ProductServiceImpl implements ProductService {

    @Autowired
    private ProductRepository productRepository;

    @Override
    public List<Product> getAllProducts() {
        return productRepository.findAll();
    }

    @Override
    public Optional<Product> getProductById(Long id) {
        return productRepository.findById(id);
    }

    @Override
    public Product saveProduct(Product product) {
        return productRepository.save(product);
    }

    @Override
    public Product updateProduct(Product product) {
        return productRepository.save(product);
    }

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

Step 6: Creating the Controller

6.1 Create the ProductController Class

In the controller package, create a new Java class named ProductController:

package com.example.springbootmssqlcrud.controller;

import com.example.springbootmssqlcrud.model.Product;
import com.example.springbootmssqlcrud.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
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 {

    @Autowired
    private ProductService productService;

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

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

    @PostMapping
    public Product createProduct(@RequestBody Product product) {
        return productService.saveProduct(product);
    }

    @PutMapping("/{id}")
    public ResponseEntity<Product> updateProduct(@PathVariable Long id, @RequestBody Product productDetails) {
        Optional<Product> productOptional = productService.getProductById(id);
        if (productOptional.isPresent()) {
            Product product = productOptional.get();
            product.setName(productDetails.getName());
            product.setDescription(productDetails.getDescription());
            product.setPrice(productDetails.getPrice());
            return ResponseEntity.ok(productService.updateProduct(product));
        } else {
            return ResponseEntity.notFound().build();
        }
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteProduct(@PathVariable Long id) {
        Optional<Product> product = productService.getProductById(id);
        if (product.isPresent()) {
            productService.deleteProduct(id);
            return ResponseEntity.noContent().build();
        } else {
            return ResponseEntity.notFound().build();
        }
    }
}

Step 7: Running the Application

1. Run the Application:
   • Open the `SpringBoot

MssqlCrudApplicationclass in thesrc/main/java/com/example/springbootmssqlcrud` directory.

• Click the green Run button in your IDE or use the terminal to run the application:

  ./mvnw spring-boot:run
  

2. Verify the Application:
   • Open your web browser or a tool like Postman and navigate to http://localhost:8080/api/products.

You can now perform CRUD operations on the Product entity using the following endpoints:

• GET /api/products: Get all products
• GET /api/products/{id}: Get product by ID
• POST /api/products: Create a new product
• PUT /api/products/{id}: Update an existing product
• DELETE /api/products/{id}: Delete a product

Conclusion

In this tutorial, we have walked through the process of creating a Spring Boot CRUD application with MS SQL Server. We configured the project, set up MS SQL Server, created the necessary entities, repositories, services, and controllers, and tested the CRUD operations. This setup provides a solid foundation for developing more complex Spring Boot applications with MS SQL Server.