Docker Java Spring Boot

Spring Boot CRUD Rest API with MS SQL Server

Editorial Team

In this post, we’ll implement a sample REST API that uses Spring Boot with Microsoft SQL Server.

· Prerequisites
· Overview
∘ What is Microsoft SQL Server?
∘ SQL Server editions
· Microsoft SQL Server Setup with Docker
· Getting Started
∘ Configure Spring Data SQL Server
∘ CRUD API
· Test the application
· Conclusion
· References

Prerequisites

This is the list of all the prerequisites:

  • Spring Boot 3+
  • Maven 3.6.3
  • Java 21
  • Docker and Docker compose installed (Optional if you already have a SQL Server instance)
  • SQL Server instance

Overview

What is Microsoft SQL Server?

Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is a software product used to administer the database and retrieve information. The data is organized in tables(relations), and the tables are related. Each table has rows and columns(attributes).

T-SQL (Transact-SQL), is a procedural language extension exclusive to Microsoft for SQL Server. T-SQL is central to using Microsoft SQL products and services. All tools and applications that communicate with an SQL Server database do so by sending T-SQL commands.

SQL Server editions

Microsoft provides several editions that users can choose depending on their requirements:

  • Enterprise edition: This version is the premium offering. It delivers comprehensive high-end data center capabilities with blazing-fast performance, unlimited virtualization 1, and end-to-end business intelligence, enabling high service levels for mission-critical workloads and end-user access to data insights.
  • Standard edition: The Standard edition delivers a basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premises and cloud, enabling effective database management with minimal IT resources.
  • Web edition: This version of SQL Server is a low total-cost-of-ownership option for Web hosters (including choosing Web edition on IaaS on Azure) and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
  • Developer edition: This edition enables developers to build and test applications on top of SQL Server.
  • Express edition: This edition is an entry-level, free database that is ideal for learning and building desktop and small server data-driven applications.

Microsoft SQL Server Setup with Docker

This part is optional if you already have an SQL Server instance installed.

We’ll run SQL Server Linux container images with Docker. We will create a docker-compose file containing all the instructions to run an SQL Server DB instance.

version: '3.8'

services:
  mssql:
    container_name: sql-server
    image: mcr.microsoft.com/mssql/server:2017-latest
    restart: always
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Express
      - MSSQL_SA_PASSWORD=HCVbd4_prv@
      - MSSQL_DATABASE=bookdb
      - MSSQL_SLEEP=15
    ports:
      - 1433:1433
    command: /bin/bash -c '/opt/mssql/bin/sqlservr & echo "wait $$MSSQL_SLEEP sec for DB to start "; sleep $$MSSQL_SLEEP; /opt/mssql-tools/bin/sqlcmd -U sa -P $$MSSQL_SA_PASSWORD -d master -q "EXIT(CREATE DATABASE $$MSSQL_DATABASE)"; wait;'
    volumes:
      - mssql_dev_data:/var/opt/mssql/data

volumes:
  mssql_dev_data:

We use SQL Server Express Edition as a Docker Container to run the local development database. More information about the container image is available on its Docker Hub page (this is especially useful if you’re having issues).

  • ACCEPT_EULA accepts the end user license agreement.
  • MSSQL_SA_PASSWORD configures the SA user password. The SA_PASSWORD environment variable is deprecated. Use MSSQL_SA_PASSWORD instead.
  • MSSQL_PID specifies the freely licensed Express Edition of SQL Server for non-production use. (default: Developer)
  • MSSQL_TCP_PORT sets the TCP port that the SQL Server listens on to 1433.

Once we’ve created this yml, open the CLI and run the following command:

docker-compose up -d

This command creates an SQL Server database instance with a database.

Getting Started

We will start by creating a simple Spring Boot project from start.spring.io, with the following dependencies: Spring Web, MS SQL Server Driver, Spring Data JPA, Lombok, and Validation.

Configure Spring Data SQL Server

We need to add an MS SQL Server connection in the application.yml file.

spring:
  jpa:
    open-in-view: false
    database-platform: org.hibernate.dialect.SQLServerDialect
    show-sql: true
    hibernate:
      ddl-auto: update
  datasource:
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    username: sa
    password: <<bd_password>>
    url:  jdbc:sqlserver://localhost:1433;databaseName=bookdb;encrypt=false
    hikari:
      poolName: Hikari
      auto-commit: false

CRUD API

Create Book Model

To start, we need to create a Bookclass to present a table in SQL Server.

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "book")
public class Book implements Serializable {

    @Serial
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Integer id;

    @Column(name = "title", nullable = false, length = 100)
    private String title;

    @Column(name = "isbn")
    private String isbn;

    @Column(name = "description")
    private String description;

    @Column(name = "page")
    private Integer page;

    @Column(name = "price")
    private BigDecimal price;

    @ManyToOne
    @JsonIgnoreProperties("books")
    private Author author;

}
  • @Entity annotation indicates that the class is a persistent Java class.
  • @Table annotation provides the table that maps this entity.
  • @Id annotation is for the primary key.

Create Book Repository

Let’s create a repository interface BookRepository for the Book entity.

In repositories package, create BookRepository interface that extends JpaRepository

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {

}

It has the fundamental CRUD tasks for the Book entity.

Service Layer

The service layer implementation class (BookServiceImpl) will be injected by the BookRepository mapper.

@Service
@Transactional
public class BookServiceImpl implements BookService {

    private final BookRepository repository;

    public BookServiceImpl(BookRepository repo) {
        this.repository = repo;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Book create(Book d) {
        return repository.save(d);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Book update(Book d) {
        return repository.saveAndFlush(d);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Book getOne(Long id) {
        return repository.findById(id).orElse(null);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public List<Book> getAll() {
        return repository.findAll();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void delete(Long id) {
        repository.deleteById(id);
    }
}

Create Book Controller

Finally, we create a controller that provides APIs for CRUD operations.

@Slf4j
@RestController
@RequestMapping("/api/book")
public class BookController {

    private final BookService entityService;

    public BookController(BookService entityService) {
        this.entityService = entityService;
    }

    /**
     * {@code POST  /book} : Create a new book.
     *
     * @param book the book to create.
     * @return the {@link ResponseEntity} with status {@code 201 (Created)} and with body the new book.
     */
    @PostMapping()
    public ResponseEntity<Book> createBook(@RequestBody @Valid Book book) {
        log.debug("REST request to save Book : {}", book);
        return new ResponseEntity<>(entityService.create(book), HttpStatus.CREATED);
    }

    /**
     * {@code PUT  /book} : Updates an existing book.
     *
     * @param book the book to update.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the updated book,
     * or with status {@code 400 (Bad Request)} if the book is not valid,
     * or with status {@code 500 (Internal Server Error)} if the book couldn't be updated.
     */
    @PutMapping()
    public ResponseEntity<Book> updateBook(@Valid @RequestBody Book book) {
        log.debug("REST request to update Book : {}", book);
        Book result = entityService.update(book);
        return ResponseEntity.ok().body(result);
    }

    /**
     * {@code GET  /book} : get all the books.
     *
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and the list of book in body.
     */

    @GetMapping()
    public ResponseEntity<List<Book>> getAllBook() {
        log.debug("REST request to get all books");
        List<Book> lst = entityService.getAll();

        return new ResponseEntity<>(lst, HttpStatus.OK);
    }

    /**
     * {@code GET  /book/:id} : get the "id" book.
     *
     * @param id the id of the book to retrieve.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the book, or with status {@code 404 (Not Found)}.
     */
    @GetMapping(value = "/{id}")
    public ResponseEntity<Book> getOneBook(@PathVariable("id") Long id) {
        log.debug("REST request to get Book : {}", id);
        Book e = entityService.getOne(id);

        return new ResponseEntity<>(e, HttpStatus.OK);
    }

    /**
     * {@code DELETE  /book/:id} : delete the "id" book.
     *
     * @param id the id of the book to delete.
     * @return the {@link ResponseEntity} with status {@code 204 (NO_CONTENT)}.
     */
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteBook(@PathVariable("id") Long id) {
        log.debug("REST request to delete Book : {}", id);
        entityService.delete(id);
        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
    }

}

Test the application

Now we can run our application and test it.

  • Create Author
POST http://localhost:8080/api/author
  • Create Book
POST http://localhost:8080/api/book
  • Get All Books
GET http://localhost:8080/api/book
  • Get Book by id
GET http://localhost:8080/api/book/1

Conclusion

Well done !!. In this post, we implemented a REST API application that uses Spring Boot with MS SQL Server.

The complete source code is available on GitHub.

You can reach out to me and follow me on Medium, Twitter, GitHub, Linkedln

Support me through GitHub Sponsors.

Thank you for Reading !! See you in the next story.

References

👉 Link to Medium blog

, , , , ,

Related Posts

Docker Jenkins Keycloak

Jenkins Single Sign-On (SSO) with Keycloak using OpenID Connect

Editorial Team
Java Spring Boot

Building Robust API Clients with RestClient in Spring Boot 3.X

Editorial Team