Spring Boot REST API with SQLServer 2019

In this post I will demonstrate how to change MySQL Spring Boot application in to Microsoft SQL database. For this I will use code base of my previous post "Spring Boot REST API CRUD operations with MySQL with Spring Data". You can access steps to correctly install MSSQL server in following blog post "Installing SQL Server 2017 Express edition in windows".

Prerequisites 

1. You should have install java 1.8 or above.
2. You should have Eclipse installed in your PC.
3. Your PC should setup Maven installed and configured.
4. MS SQL server need to be installed. 

Dependency Configure existing project 

I will use following project https://github.com/NirmalBalasooriya/RestApiSpringBoot for the demonstration of this post. Check out the project in to you IDE. 

First remove the MySQL dependency as show below

<!-- Runtime -->

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

Add the MSSQL dependency 

<dependency>

<groupId>com.microsoft.sqlserver</groupId>

<artifactId>mssql-jdbc</artifactId>

<version>6.1.0.jre8</version>

</dependency>

After update pom file would be as follows.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.nirmal.springbootrest</groupId>

  <artifactId>RestApiSpringBoot</artifactId>

  <version>0.0.1-SNAPSHOT</version>

<parent>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

        <version>2.0.5.RELEASE</version>

    </parent>

<dependencies>

<!-- Compile -->

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-data-jpa</artifactId>

        </dependency>

<dependency>

<groupId>javax.servlet</groupId>

<artifactId>jstl</artifactId>

</dependency>

<!-- Provided -->

  <dependency>

   <groupId>org.apache.tomcat.embed</groupId>

   <artifactId>tomcat-embed-jasper</artifactId>

   <scope>provided</scope>

  </dependency>

<!-- Runtime -->

<dependency>

<groupId>com.microsoft.sqlserver</groupId>

<artifactId>mssql-jdbc</artifactId>

<version>6.1.0.jre8</version>

</dependency>

</dependencies>

<build>

<plugins>

<plugin>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-maven-plugin</artifactId>

</plugin>

</plugins>

</build>

</project>

Modify SpringBoot Configure on application.properties 

To configure MSSQL database we have to modify database URL, username, password, driver class name and database-platform. Update the file based on your configuration for username and password.

#==== connect to mysql ======#

spring.jpa.hibernate.ddl-auto=update

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=TestDB

spring.datasource.username=nirmal

spring.datasource.password=Test123_

spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

spring.jpa.database-platform=org.hibernate.dialect.SQLServer2012Dialect

spring.jpa.properties.hibernate.show_sql=true

spring.jpa.properties.hibernate.use_sql_comments=false

spring.jpa.properties.hibernate.format_sql=true

#==== Logging configurations ======#

logging.level.root=WARN,INFO,ERROR

logging.level.com.baeldung=TRACE

Run the application

In order to run first we have to perform maven install command and then we can run the application

Go to run debug configuration and add following command 

spring-boot:run

Then run the application  by click on Run button

After successful execution you should be able to see similar output as show below.

Also you should be see table has been created on your configured Database and you can access the web service from following URL. 

http://localhost:8080/findBook/1

With following out put on web browser.

Lets Test the application 

Then lets add new Book in to API

curl -H "Accept: application/json" -H "Content-type: application/json" -X POST -d "{ \"isbmNumber\":\"9999\", \"name\":\"How to develop API\", \"description\":\"sample book\", \"auther\":\"Nirmal Balasooriya \" }" http://localhost:8080/saveOrUpdate

  

For this one following output will return

{"code":"1","desc":"Book save successful","t":"9999"}

If you check the database you can see data has been inserted to the Database.

Also we can access data through API as well http://localhost:8080/findBook/9999

You can access the updated code base from following GitHub URL.