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

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:

MSSQL tutorials table after that:

Retrieve All Tutorials:

Retrieve a Tutorial by Id:

Update some Tutorials:

The table data is changed:

Find all Tutorials which title contains string 'ring':

Find all published Tutorials:

Delete a Tutorial:

Delete all Tutorials:

MSSQL database table is clean now:

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

• Simple HTTP Client using Axios
• Angular 8 CRUD Application example with Web API
• Angular 10 CRUD Application example with Web API
• Angular 11 CRUD Application example with Web API
• Angular 12 CRUD Application example with Web API
• Vue 2 CRUD Application with Vue Router & Axios
• Vue 3 CRUD Application with Axios & Vue Router
• React CRUD example to consume Web API
• React Redux CRUD example with API calls

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:

• Spring Boot @ControllerAdvice & @ExceptionHandler example
• @RestControllerAdvice example in Spring Boot

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

Fullstack CRUD App:

• Spring Boot + Vue.js example
• Angular 8 + Spring Boot example
• Angular 10 + Spring Boot example
• Angular 11 + Spring Boot example
• Angular 12 + Spring Boot example
• React + Spring Boot example

More Practice:

• Secure Spring Boot App with Spring Security & JWT Authentication
• Spring Boot Rest XML example – Web service with XML Response
• Spring Boot Multipart File upload example
• Spring Boot Pagination and Sorting example

Using other databases:

• Spring JPA + H2
• Spring JPA + PostgreSQL
• Spring JPA + MySQL
• Spring Data + MongoDB