Spring Boot + Microsoft SQL Server: CRUD Operations example
Tags: springboot, java, todayilearned, webdev
Published: 2021-08-16 09:35:11

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:
<ul>
	<li>Way to use SQL Server maven dependency in Spring Boot</li>
    	<li>How to configure Spring Data, JPA, Hibernate to work with Database</li>
    	<li>How to define Data Models and Repository interfaces</li>
	<li>Way to create Spring Rest Controller to process HTTP requests</li>
   	<li>Way to use Spring Data JPA to interact with MSSQL Database</li>
</ul>

Full Article: [Spring Boot + SQL Server: CRUD Operations example](https://www.bezkoder.com/spring-boot-sql-server/)

<h2><span id="Overview">Overview of Spring Boot + SQL Server example</span></h2>
We will build a Spring Boot CRUD Rest Apis using Spring Data JPA with SQL Server (MSSQL) Database for a Tutorial application in that:
<ul>
	<li>Each Tutotial has id, title, description, published status.</li>
	<li>Apis help to create, retrieve, update, delete Tutorials.</li>
	<li>Apis also support custom finder methods such as find by published status or by title.</li>
</ul>

These are APIs that we need to provide:
<div class="table-responsive">
<table class="table table-hover  table table-hover" width="100%">
<thead>
<tr>
<th>Methods</th>
<th>Urls</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<tr>
<td>POST</td>
<td>/api/tutorials</td>
<td>create new Tutorial</td>
</tr>
<tr>
<td>GET</td>
<td>/api/tutorials</td>
<td>retrieve all Tutorials</td>
</tr>
<tr>
<td>GET</td>
<td>/api/tutorials/:id</td>
<td>retrieve a Tutorial by <code>:id</code></td>
</tr>
<tr>
<td>PUT</td>
<td>/api/tutorials/:id</td>
<td>update a Tutorial by <code>:id</code></td>
</tr>
<tr>
<td>DELETE</td>
<td>/api/tutorials/:id</td>
<td>delete a Tutorial by <code>:id</code></td>
</tr>
<tr>
<td>DELETE</td>
<td>/api/tutorials</td>
<td>delete all Tutorials</td>
</tr>
<tr>
<td>GET</td>
<td>/api/tutorials/published</td>
<td>find all published Tutorials</td>
</tr>
<tr>
<td>GET</td>
<td>/api/tutorials?title=[keyword]</td>
<td>find all Tutorials which title contains <code>keyword</code></td>
</tr>
</tbody>
</table>
</div>
- We make CRUD operations & finder methods with Spring Data JPA's <code>JpaRepository</code>.
- The database will be SQL Server (MSSQL) by configuring project dependency & datasource.

<h2><span id="Technology">Technology</span></h2>
<ul>
    	<li>Java 8</li>
    	<li>Spring Boot 2.5 (with Spring Web MVC, Spring Data JPA)</li>
    	<li>Microsoft SQL Server (MSSQL)</li>
    	<li>Maven 3.6.1</li>
</ul>

<h2><span id="Project_Structure">Project Structure</span></h2>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0b4h8jxb18fjr6ekqxp2.png)

Let me explain it briefly.

– <code>Tutorial</code> data model class corresponds to entity and table <em>tutorials</em>.
– <code>TutorialRepository</code> is an interface that extends <a href="https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html">JpaRepository</a> for CRUD methods and custom finder methods. It will be autowired in <code>TutorialController</code>.
– <code>TutorialController</code> is a <a href="https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/web/bind/annotation/RestController.html">RestController</a> which has request mapping methods for RESTful requests such as: <em>getAllTutorials</em>, <em>createTutorial</em>, <em>updateTutorial</em>, <em>deleteTutorial</em>, <em>findByPublished</em>...
– Configuration for Spring Datasource, JPA & Hibernate in <strong>application.properties</strong>.
– <strong>pom.xml</strong> contains dependencies for Spring Boot and SQL Server.

<h2><span id="Run_Test">Run the Spring Boot + Microsoft SQL Server CRUD example</span></h2>
Run Spring Boot application with command: <code>mvn spring-boot:run</code>.

<strong><em>tutorials</em></strong> table will be automatically generated in Microsoft SQL Server Database.

Create some Tutorials:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-create-tutorial.png" alt="spring-boot-sql-server-crud-example-mssql-create-tutorial" width="510" height="390" class="alignnone size-full wp-image-8955" />

MSSQL <code>tutorials</code> table after that:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-create-tutorial-database.png" alt="spring-boot-sql-server-crud-example-mssql-create-tutorial-database" width="400" height="120" class="alignnone size-full wp-image-8956" />

Retrieve All Tutorials:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-retrieve-tutorial.png" alt="spring-boot-sql-server-crud-example-mssql-retrieve-tutorial" width="470" height="680" class="alignnone size-full wp-image-8957" />

Retrieve a Tutorial by Id:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-retrieve-one-tutorial.png" alt="spring-boot-sql-server-crud-example-mssql-retrieve-one-tutorial" width="440" height="260" class="alignnone size-full wp-image-8960" />

Update some Tutorials:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-update-tutorial.png" alt="spring-boot-sql-server-crud-example-mssql-update-tutorial" width="440" height="400" class="alignnone size-full wp-image-8958" />

The table data is changed:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-update-tutorial-database.png" alt="spring-boot-sql-server-crud-example-mssql-update-tutorial-database" width="400" height="120" class="alignnone size-full wp-image-8959" />

Find all Tutorials which title contains string 'ring':

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-search-tutorial.png" alt="spring-boot-sql-server-crud-example-mssql-search-tutorial" width="430" height="490" class="alignnone size-full wp-image-8962" />

Find all <em><strong>published</strong></em> Tutorials:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-find-tutorial.png" alt="spring-boot-sql-server-crud-example-mssql-find-tutorial" width="450" height="380" class="alignnone size-full wp-image-8961" />

Delete a Tutorial:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-delete-one-tutorial.png" alt="spring-boot-sql-server-crud-example-mssql-delete-one-tutorial" width="440" height="180" class="alignnone size-full wp-image-8963" />

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-delete-one-tutorial-database.png" alt="spring-boot-sql-server-crud-example-mssql-delete-one-tutorial-database" width="390" height="100" class="alignnone size-full wp-image-8964" />

Delete all Tutorials:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-delete-tutorial.png" alt="spring-boot-sql-server-crud-example-mssql-delete-tutorial" width="450" height="170" class="alignnone size-full wp-image-8965" />

MSSQL database table is clean now:

<img src="https://www.bezkoder.com/wp-content/uploads/2021/08/spring-boot-sql-server-crud-example-mssql-delete-tutorial-database.png" alt="spring-boot-sql-server-crud-example-mssql-delete-tutorial-database" width="330" height="100" class="alignnone size-full wp-image-8966" />

You can also test this Spring Boot App with Client in one of these posts:
<ul>
	<li><a href="https://www.bezkoder.com/axios-request/">Simple HTTP Client using Axios</a></li>
	<li><a href="https://bezkoder.com/angular-crud-app/">Angular 8 CRUD Application example with Web API</a></li>
	<li><a href="https://bezkoder.com/angular-10-crud-app/">Angular 10 CRUD Application example with Web API</a></li>
	<li><a href="https://bezkoder.com/angular-11-crud-app/">Angular 11 CRUD Application example with Web API</a></li>
	<li><a href="https://bezkoder.com/angular-12-crud-app/">Angular 12 CRUD Application example with Web API</a></li>
	<li><a href="https://bezkoder.com/vue-js-crud-app/">Vue 2 CRUD Application with Vue Router & Axios</a></li>
	<li><a href="https://bezkoder.com/vue-3-crud/">Vue 3 CRUD Application with Axios & Vue Router</a></li>
	<li><a href="https://bezkoder.com/react-crud-web-api/">React CRUD example to consume Web API</a></li>
	<li><a href="https://bezkoder.com/react-redux-crud-example/">React Redux CRUD example with API calls</a></li>
</ul>

For step by step instruction and Github source code, please visit:
[Spring Boot + SQL Server: CRUD Operations example](https://www.bezkoder.com/spring-boot-sql-server/)

<h2><span id="Further_Reading">Further Reading</span></h2>
If you want to add Pagination to this Spring project, you can find the instruction at:
<a href="https://bezkoder.com/spring-boot-pagination-filter-jpa-pageable/">Spring Boot Pagination & Filter example | Spring JPA, Pageable</a>

To sort/order by multiple fields:
<a href="https://bezkoder.com/spring-data-sort-multiple-columns/">Spring Data JPA Sort/Order by multiple Columns | Spring Boot</a>

Handle Exception for this Rest APIs is necessary:
- <a href="https://bezkoder.com/spring-boot-controlleradvice-exceptionhandler/">Spring Boot @ControllerAdvice & @ExceptionHandler example</a>
- <a href="https://bezkoder.com/spring-boot-restcontrolleradvice/">@RestControllerAdvice example in Spring Boot</a>

Or way to write Unit Test for the JPA Repository:
<a href="https://bezkoder.com/spring-boot-unit-test-jpa-repo-datajpatest/">Spring Boot Unit Test for JPA Repositiory with @DataJpaTest</a>

Fullstack CRUD App:
- <a href="https://bezkoder.com/spring-boot-vue-js-crud-example/">Spring Boot + Vue.js example</a>
- <a href="https://bezkoder.com/angular-spring-boot-crud/">Angular 8 + Spring Boot example</a>
- <a href="https://bezkoder.com/angular-10-spring-boot-crud/">Angular 10 + Spring Boot example</a>
- <a href="https://bezkoder.com/angular-11-spring-boot-crud/">Angular 11 + Spring Boot example</a>
- <a href="https://bezkoder.com/angular-12-spring-boot-crud/">Angular 12 + Spring Boot example</a>
- <a href="https://bezkoder.com/react-spring-boot-crud/">React + Spring Boot example</a>

More Practice:
- <a href="https://bezkoder.com/spring-boot-jwt-authentication/">Secure Spring Boot App with Spring Security & JWT Authentication</a>
- <a href="https://bezkoder.com/spring-boot-rest-xml/">Spring Boot Rest XML example – Web service with XML Response</a>
- <a href="https://bezkoder.com/spring-boot-file-upload/">Spring Boot Multipart File upload example</a>
- <a href="https://bezkoder.com/spring-boot-pagination-sorting-example/">Spring Boot Pagination and Sorting example</a>

Using other databases:
- <a href="https://www.bezkoder.com/spring-boot-jpa-h2-example/">Spring JPA + H2</a>
- <a href="https://bezkoder.com/spring-boot-postgresql-example/">Spring JPA + PostgreSQL</a>
- <a href="https://bezkoder.com/spring-boot-jpa-crud-rest-api/">Spring JPA + MySQL</a>
- <a href="https://bezkoder.com/spring-boot-mongodb-crud/">Spring Data + MongoDB</a>