Quickly build a Spring Boot and Spring Data JPA RESTFul Web Service using a Microsoft SQL Server database instance running in Docker container.
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.
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
GET /students, and
Next, we will add SQL Server Java Drivers dependency to our pom.xml or build.gradle files.
<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> </dependency>Code language: Java (java)
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.
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.
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
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.
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.