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:
Top comments (0)