Spring Boot Hibernate with Microsoft SQL Server

Generated by Adobe Firefly

Introduction

This article explores the journey of harnessing the power of Spring Boot, a versatile and lightweight framework, alongside Hibernate, a renowned Object-Relational Mapping (ORM) tool, to seamlessly interact with a Microsoft SQL Server (MSSQL) database.

In this article, there is a step by step guide to create a simple Spring Boot application with Hibernate as the ORM and MSSQL as the database. We’ll be implementing a rest API to populate and retrieve information form the MSSQL database.

Full code is available at — https://github.com/chameerar/spring-hibernate-mssql-project

Prerequisites

1. Java (preferably 17)
2. Maven (preferably 3.9)
3. Java friendly IDE (preferably IntellijIDEA)

Additionally, Docker will be needed if you wish to create the database server in your local machine.

Creating a New Spring Boot Project

First, let’s create a new Spring Boot project with required dependencies. For this let’s use Spring Initializer.

Creating new Spring Boot application in https://start.spring.io/

In here, Maven is selected as the Project type, Java as the Language, Jar as the Packaging, and 17 as the Java version. After filling Project Metadata, let’s add Spring Web and Spring Data JPA dependencies.

Next, download the project using the Generate button.

Project Structure

Let’s open the project in IDEA to observe the project structure.

Project structure

Let’s remove .mvn directory, mvnw file, and mvnw.cmd file as we do not need them.

Adding MSSQL Dependency

To connect with MSSQL database, we need the mssql-jdbc dependency. Let’s add the below line to the pom.xml file.

  <dependency>
     <groupId>com.microsoft.sqlserver</groupId>
     <artifactId>mssql-jdbc</artifactId>
     <scope>runtime</scope>
  </dependency>

We do not have to specify the version since the supported version is inherited from a parent pom file ( spring-boot-dependencies.pom).

Configuring the Database

Let’s add the database configurations to the project. First of all we need access to a database. Below section will be about starting a MSSQL database using Docker. If you already have access to an up and running database, you can skip to the Configuring the Database Connection section.

Starting a MSSQL Server using Docker

Let’s start a MSSQL Sever locally using Docker. On Macbook computers with an ARM64 chip (M1, M2, etc.) we’ll have to use Azure SQL Edge image for this. Otherwise we can use the standard Microsoft SQL Server image.

To start the server using Azure SQL Edge image, use the below command in the terminal.

docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=yourStrong(!)Password' \ 
-p 1433:1433 --name mssql-container \
-d mcr.microsoft.com/azure-sql-edge

To start the server using Microsoft SQL Server image, use the below command in the terminal.

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" \
   -p 1433:1433 --name mssql-container --hostname sql1 \
   -d \
   mcr.microsoft.com/mssql/server:2022-latest

Run only one of the above according to your system.

Make sure to replace yourStrong(!)Password with a desired password.

This command will start the MSSQL server in localhost:1433 address.

Now we have to create a

Now we are all set to configure the database connection.

Configuring the Database Connection

First, let’s create a database named booksdb in the database server. To create the database we can use a database client like DBever.

Next, let’s navigate to the project and open the application.properties file in src/main/resources. Add the below content to configure the database connection.

spring.datasource.url=jdbc:sqlserver://;serverName=localhost;databaseName=booksdb;encrypt=true;trustServerCertificate=true;
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.username=SA
spring.datasource.password=yourStrong(!)Password
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServerDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

Make sure to replace yourStrong(!)Password with your database password.

The application.properties file configures theSpring Boot application to connect to a SQL Server database, specifying the connection details, JDBC driver, credentials, Hibernate dialect, and whether to log SQL statements and automatically update the database schema.

To explain the content of the application.properties file further,

spring.datasource.url: This property specifies the URL used to connect to the SQL Server database. In this case, it's a SQL Server connection URL with the following options:

• serverName=localhost: It indicates that the database server is running on the localhost.
• databaseName=booksdb: The name of the database to connect to is "booksdb."
• encrypt=true: It indicates that the communication with the database server should be encrypted.
• trustServerCertificate=true: This means that the application trusts the server's certificate when encrypting communication.

spring.datasource.driverClassName: This property specifies the fully qualified class name of the JDBC driver to use for connecting to the database. In this case, it's using the Microsoft SQL Server JDBC driver.

spring.datasource.username: This property sets the username (in this case, "SA") to be used when connecting to the database.

spring.datasource.password: This property sets the password (in this case, "yourStrong(!)Password") to be used when connecting to the database.

spring.jpa.hibernate.dialect: This property specifies the Hibernate dialect to be used. In this case, it's configured to use the SQL Server dialect, which helps Hibernate generate SQL statements compatible with SQL Server.

spring.jpa.show-sql: When set to "true," this property makes Hibernate log SQL statements to the console, which can be useful for debugging and understanding how Hibernate interacts with the database.

spring.jpa.hibernate.ddl-auto: This property controls how Hibernate will update the database schema. Setting it to "update" means that Hibernate will automatically create/update database tables based on the entity classes defined in your application. This is often used during development but should be used with caution in production to avoid unintended schema changes.

Creating Entity Classes

Let’s create the Book entity class under a new package models .

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;

@Entity
public class Book {

    @Id
    @GeneratedValue
    private Long id;
    private String name;

    public Book() {
    }

    public Book(String name) {
        this.name = name;
    }

    public Long getId() {

        return id;
    }

    public String getName() {
        return name;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }
}

This is a simple entity class with only two attributes id and name. The value for id is auto generated when persisting a new book record.

Creating a Repository Interface

Let’s create a repository interface BookRepository for the Book entity under a new package repositories.

import io.github.chameerar.springjpaproject.models.Book;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {

}

This repository interface created by extending JpaRepository interface. This handles the basic CRUD operations for Book entity.

Implementing Service

Next we need a service to handle the business logic. Let’s create BookService class in a new services package.

import io.github.chameerar.springhibernatemssql.models.Book;
import io.github.chameerar.springhibernatemssql.repositories.BookRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.server.ResponseStatusException;

import java.util.List;
import java.util.Optional;

@Service
public class BookService {

    @Autowired
    private BookRepository bookRepository;

    public List<Book> list() {
        return bookRepository.findAll();
    }

    public Book save(Book book) {
        return bookRepository.save(book);
    }

    public Book get(Long id) {

        Optional<Book> bookOptional = bookRepository.findById(id);
        if (bookOptional.isEmpty()) {
            throw new ResponseStatusException(
                    org.springframework.http.HttpStatus.NOT_FOUND, "Book not found");
        }
        return bookOptional.get();
    }

    public Book update(Long id, Book book) {
        Optional<Book> bookOptional = bookRepository.findById(id);
        if (bookOptional.isEmpty()) {
            throw new ResponseStatusException(
                    org.springframework.http.HttpStatus.NOT_FOUND, "Book not found");
        }
        Book existingBook = bookOptional.get();
        existingBook.setName(book.getName());
        return bookRepository.save(existingBook);
    }

    public void delete(Long id) {
        bookRepository.deleteById(id);
    }
}

BooksService contains logic for creating, updating, retreiving, and deleting books.

Implementing Controller Class

Let’s create a controller class BookController in a new package controllers .

import io.github.chameerar.springhibernatemssql.models.Book;
import io.github.chameerar.springhibernatemssql.services.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("books")
public class BookController {
    @Autowired
    BookService bookService;

    @GetMapping(value = "/", produces = "application/json")
    public List<Book> getBooks() {
        return bookService.list();
    }

    @PostMapping(value = "/new", consumes = "application/json", produces = "application/json")
    public Book save(@RequestBody Book book) {
        return bookService.save(book);
    }

    @GetMapping(value = "/{id}", produces = "application/json")
    public Book get(@PathVariable Long id) {
        return bookService.get(id);
    }

    @PutMapping(value = "/{id}", consumes = "application/json", produces = "application/json")
    public Book update(@PathVariable Long id, @RequestBody Book book) {
        return bookService.update(id, book);
    }

    @DeleteMapping(value = "/{id}", produces = "application/json")
    public void delete(@PathVariable Long id) {
        bookService.delete(id);
    }
}

The BooksController class consists of four methods to list books, create book, retrieve book by id, update book, and delete book. With this we can send HTTP requests the app to save, retrieve, update, and delete books form the MSSQL database.

Now the implementation is completed. Let’s run the application.

Running and Testing the Application

Let’s start the server to test the application.

Running Using IDEA

If you are using IntelliJ IDEA you can run directly from the IDEA UI by clicking the play button in the @SpringBootApplication .

@SpringBootApplication opened in IDEA

Running Using Springboot Maven Plugin

The project already has spring-boot-maven-plugin included. We can start the server using below command.

mvn spring-boot:run 

Running as a JAR

Or you can build the project using Maven and run the .jar file created in the /target directory.

To build the project using Maven, execute the below terminal command from the project root directory.

mvn clean package

This packages the project to a jar file and this jar is placed in the /target directory.

Next, navigate to /target directory and execute the terminal command below to start the Spring Boot Application.

java -jar name-of-the-jar.jar

Make sure to replace “name-of-the-jar” with the actual name of the jar.

Once the application is up, it will be serving through localhost:8080 by default. Now let’s test the application by sending HTTP requests.

Testing using Postman

Since sending HTTP requests using Postman is easy let’s use it to test the application. This also can be tested using simple curl commands too.

Let’s create a book by sending a POST request to localhost:8080/books/new address. The json payload should contain name of the book as shown below.

{
    "name": "Atomic Habbits"
}

Sending POST request to create a book via Postman

This will create a book with an id. Similarly we can send a GET request to the address localhost:8080/books/{id} format with id as the id of the created book.

We can do the same to test the update and delete operations as well by sending PUT and DELTE request respectively to the localhost:8080/books/{id} format.

Conclusion

Microsoft SQL Server (MSSQL) has proven itself as a reliable and scalable database management system, offering the foundation for application data storage and retrieval needs. Its compatibility with a wide range of applications and robustness make it a suitable choice for projects of all sizes.

Hibernate, the renowned Object-Relational Mapping (ORM) tool, has allowed to interact with the MSSQL database in an object-oriented manner. By defining entity classes and repositories, we can abstract away the complexities of SQL queries and database connections.

Spring Boot has proven to be a game-changer in the world of web development. Its convention-over-configuration approach, coupled with a wide range of pre-built components, simplifies the development process and allows you to focus on building your application’s unique features. You’ve seen how quickly you can set up a Spring Boot project and start building RESTful APIs.

Combining of all the above three in such an easy way is remarkable and saves time and effort of the developers. This not only enhances the development efficiency but also ensures cleaner, more maintainable code.

References

Docker: Install containers for SQL Server on Linux - SQL Server

Azure SQL Edge

Overview (Hibernate Javadocs)

Common Application Properties

Spring Boot Maven Plugin Documentation