DEV Community

Cover image for Spring Boot + Microsoft SQL Server: CRUD Operations example
bezkoder
bezkoder

Posted on

Spring Boot + Microsoft SQL Server: CRUD Operations example

In this tutorial, we're gonna build a Spring Boot CRUD Operations example with Maven that use Spring Data JPA to interact with Microsoft SQL Server (MSSQL). You'll know:

  • Way to use SQL Server maven dependency in Spring Boot
  • How to configure Spring Data, JPA, Hibernate to work with Database
  • How to define Data Models and Repository interfaces
  • Way to create Spring Rest Controller to process HTTP requests
  • Way to use Spring Data JPA to interact with MSSQL Database

Full Article: Spring Boot + SQL Server: CRUD Operations example

Overview of Spring Boot + SQL Server example

We will build a Spring Boot CRUD Rest Apis using Spring Data JPA with SQL Server (MSSQL) Database for a Tutorial application in that:

  • Each Tutotial has id, title, description, published status.
  • Apis help to create, retrieve, update, delete Tutorials.
  • Apis also support custom finder methods such as find by published status or by title.

These are APIs that we need to provide:

Methods Urls Actions
POST /api/tutorials create new Tutorial
GET /api/tutorials retrieve all Tutorials
GET /api/tutorials/:id retrieve a Tutorial by :id
PUT /api/tutorials/:id update a Tutorial by :id
DELETE /api/tutorials/:id delete a Tutorial by :id
DELETE /api/tutorials delete all Tutorials
GET /api/tutorials/published find all published Tutorials
GET /api/tutorials?title=[keyword] find all Tutorials which title contains keyword
  • We make CRUD operations & finder methods with Spring Data JPA's JpaRepository.
  • The database will be SQL Server (MSSQL) by configuring project dependency & datasource.

Technology

  • Java 8
  • Spring Boot 2.5 (with Spring Web MVC, Spring Data JPA)
  • Microsoft SQL Server (MSSQL)
  • Maven 3.6.1

Project Structure

Alt Text

Let me explain it briefly.

Tutorial data model class corresponds to entity and table tutorials.
TutorialRepository is an interface that extends JpaRepository for CRUD methods and custom finder methods. It will be autowired in TutorialController.
TutorialController is a RestController which has request mapping methods for RESTful requests such as: getAllTutorials, createTutorial, updateTutorial, deleteTutorial, findByPublished...
– Configuration for Spring Datasource, JPA & Hibernate in application.properties.
pom.xml contains dependencies for Spring Boot and SQL Server.

Run the Spring Boot + Microsoft SQL Server CRUD example

Run Spring Boot application with command: mvn spring-boot:run.

tutorials table will be automatically generated in Microsoft SQL Server Database.

Create some Tutorials:

spring-boot-sql-server-crud-example-mssql-create-tutorial

MSSQL tutorials table after that:

spring-boot-sql-server-crud-example-mssql-create-tutorial-database

Retrieve All Tutorials:

spring-boot-sql-server-crud-example-mssql-retrieve-tutorial

Retrieve a Tutorial by Id:

spring-boot-sql-server-crud-example-mssql-retrieve-one-tutorial

Update some Tutorials:

spring-boot-sql-server-crud-example-mssql-update-tutorial

The table data is changed:

spring-boot-sql-server-crud-example-mssql-update-tutorial-database

Find all Tutorials which title contains string 'ring':

spring-boot-sql-server-crud-example-mssql-search-tutorial

Find all published Tutorials:

spring-boot-sql-server-crud-example-mssql-find-tutorial

Delete a Tutorial:

spring-boot-sql-server-crud-example-mssql-delete-one-tutorial

spring-boot-sql-server-crud-example-mssql-delete-one-tutorial-database

Delete all Tutorials:

spring-boot-sql-server-crud-example-mssql-delete-tutorial

MSSQL database table is clean now:

spring-boot-sql-server-crud-example-mssql-delete-tutorial-database

You can also test this Spring Boot App with Client in one of these posts:

For step by step instruction and Github source code, please visit:
Spring Boot + SQL Server: CRUD Operations example

Further Reading

If you want to add Pagination to this Spring project, you can find the instruction at:
Spring Boot Pagination & Filter example | Spring JPA, Pageable

To sort/order by multiple fields:
Spring Data JPA Sort/Order by multiple Columns | Spring Boot

Handle Exception for this Rest APIs is necessary:

Or way to write Unit Test for the JPA Repository:
Spring Boot Unit Test for JPA Repositiory with @DataJpaTest

Fullstack CRUD App:

More Practice:

Using other databases:

Top comments (0)