Spring Boot – Spring Data JPA – MS SQL Server Example

By Amit Phaltankar

Quickly build a Spring Boot and Spring Data JPA RESTFul Web Service using a Microsoft SQL Server database instance running in Docker container.

Overview

In this tutorial we will build a Spring Boot based RESTful web service using Microsoft SQL Server and Spring Data JPA – Hibernate in the persistence layer.

We will cover steps to build a basic Spring Boot application from scratch and add all the required dependencies. We’ll create an Entity Bean, a Spring Data Repository Interface, and a Spring REST Controller to expose standard GET, POST, PUT, and DELETE endpoints. On top of that, we will also cover how to run MS SQL Server database instance locally using Docker container.

MS SQL Server in Docker Container

This step is optional, if you already have a locally available MS SQL Server instance. Else, you can follow the steps to launch an instance quickly.

Prepare a docker-compose.yml file and add the MS SQL Server image details and a few configurations.

docker-compose.yml

version: "3"

services:
  database:
    restart: always
    image: mcr.microsoft.com/mssql/server
    container_name: ms-sql-server-latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=password
    ports:
      - 14033:1433Code language: YAML (yaml)

Note that, by default we are using the latest image of the SQL Server. MS SQL Server database has a root user ‘sa’ and in the above configuration we are setting a new password for the root user. Also, we are accepting the End User License Agreement. Lastly, we are forwarding the SQL Server default port (1433) to the 14033 port of our local machine. 

~ docker-compose -f path/to/docker-compose.yml up -dCode language: Bash (bash)

Now, we are starting the docker-compose file in a detached mode. 

Creating network "sql-server-docker_default" with the default driver
Pulling database (mcr.microsoft.com/mssql/server:)…
latest: Pulling from mssql/server
345e3491a907: Pull complete
57671312ef6f: Pull complete
5e9250ddb7d0: Pull complete
1f9b20e23ebb: Pull complete
e23afff1f9a0: Pull complete
83c8e0c0584e: Pull complete
17d57cdb8829: Pull complete
Digest: sha256:51965e4e4c17e6fef087550190c2920c7ef91bd449d0eec06a5484b92c437767
Status: Downloaded newer image for mcr.microsoft.com/mssql/server:latest
Creating ms-sql-server-latest … done

From the logs, we can see the MS SQL server image is downloaded and started in a container. We can verify by connecting to the database using the root user and its password.

Spring Boot + Spring Data JPA + SQL Server Application

Now, we have a locally accessible MS SQL Server database instance. Next, we will build our Spring Boot application from scratch. We already have a tutorial (CRUD REST Service With Spring Boot, Hibernate, and JPA tutorial) detailing on how to build a Spring Boot and Spring Data JPA based application. We recommend using the tutorial to get all the pre-requisites done.

Following the steps in the given tutorial, we have

  • Created a Spring Boot web application.
  • Added dependencies like spring-boot-starter-parent, spring-boot-starter-data-jpa, spring-boot-starter-web, and optionally the Lombok dependency.
  • Created an Entity Bean class (Student) with an auto-incrementing primary key.
  • Added a Spring Data Repository interface (StudentRepository).
  • Created a Spring REST Controller (StudentController). Our StudentController has POST /students, PUT/students, DELETE /students, GET /students, and GET /students/{studentId} endpoints.

Next, we will add SQL Server Java Drivers dependency to our pom.xml or build.gradle files.

pom.xml

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
</dependency>Code language: Java (java)

build.gradle

runtimeOnly 'com.microsoft.sqlserver:mssql-jdbc'Code language: Gradle (gradle)

SQL Sever Datasource Configuration

Spring Boot supports application properties based datasource config. As we know Spring Boot Auto-configuration refers to the dependencies and datasource configurations to automatically initialize all the essential components of Hibernate, and Spring Data JPA.

Thus, we only need to provide SQL Server driver name, database url, database username and the password. 

spring:
  datasource:
    username: "sa"
    password: "password" ##Enter your root password
    url: "jdbc:sqlserver://localhost:14033"
    driverClassName: "com.microsoft.sqlserver.jdbc.SQLServerDriver"Code language: YAML (yaml)

Prepare Schema and Initial Data

Additionally, we can also create a table and load initial data into the database. Our student service needs a student table with four columns.

With Spring Boot, we can put all of our schema creation scripts in schema.sql file and our data scripts inside data.sql file.

schema.sql

CREATE TABLE student
(
    student_id INTEGER     NOT NULL IDENTITY PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name  VARCHAR(50),
    year       INTEGER
);Code language: SQL (Structured Query Language) (sql)

Note that, the student_id is a primary key and it is marked as IDENTITY. Thus the field value is auto-incrementing.

data.sql

INSERT INTO student (first_name, last_name, year) VALUES ('Jon', 'S', 2024);
INSERT INTO student (first_name, last_name, year) VALUES ('Khal', 'D', 2025);
INSERT INTO student (first_name, last_name, year) VALUES ('Belwas', 'S', 2029);
INSERT INTO student (first_name, last_name, year) VALUES ('Petyr', 'L', 2023);Code language: SQL (Structured Query Language) (sql)

Here, we are inserting four student records in our newly created table.

Make sure these two files are placed under src/main/resources directory.

Launch the Application

So far, we have covered all the required steps to launch and test our application. Start the application, manually by running the Application.java or use Spring Boot Maven plugin to launch it. 

~ mvn spring-boot:runCode language: Bash (bash)

This command will build and launch the Spring Boot application. 

INFO [main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8084 (http) with context path ''
INFO [main] com.amitph.spring.data.Application       : Started Application in 8.841 seconds (JVM running for 9.407)
INFO [main] o.s.b.a.ApplicationAvailabilityBean      : Application availability state LivenessState changed to CORRECT
INFO [main] o.s.b.a.ApplicationAvailabilityBean      : Application availability state ReadinessState changed to ACCEPTING_TRAFFICCode language: Bash (bash)

The logs show that our application is started and listening on the given port – 8084.

Summary

This was a quick tutorial to build a Spring Boot + Spring Data JPA + MS SQL Server web application from scratch. We created a dummy Students Service that persists the students records in a MS SQL Server instance and manages the persistence using Hibernate and Spring Data JPA. Also, the application exposes standard Create, Read, Update, and Delete actions on the students. Additionally, we covered how to launch a SQL Server instance in a docker container.

For full source of the examples used here, please visit our Github Repo – spring-boot-data-jpa-postgres.