How to Configure Spring Boot and MS SQL Server

Hello

Today I am sharing the configuration of integrating Spring Boot With MS SQL Server.Follow the Below Steps To achieve this:

1.Add Dependencies in pom.xml
2.Create application.properties files.
3.Create Main class to start the Spring boot application
4.Add a controller and entity and repository class to do the CRUD operation on db.

Lets Start

Add following dependencies to pom.xml file

 <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.0.RELEASE</version>
    </parent>
  <dependencies>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
    <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
   <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
  </dependencies>
   <properties>
        <java.version>1.8</java.version>
    </properties>

NOTE:  SQLJDBC  jar is not present in maven central repo.You have to install it manually.First download it from https://www.microsoft.com/en-in/download/details.aspx?id=11774 and install it by running the following command from the directory in which you put your jar.

mvn install:install-file -Dfile=sqljdbc4.jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0 -Dpackaging=jar

2.Create a application.properties file and put it in your project resource folder.Spring will automatically search for this file for configuring the application.

spring.datasource.url=jdbc:sqlserver://localhost:1433;database=xyz;

spring.datasource.username=sa
spring.datasource.password=yourpassword
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

3.Create application class

package com.vj;


import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication

public class BootApplication {

public static void main(String[] args) {
        ApplicationContext ctx = SpringApplication.run(BootApplication.class, args);
     
    }


}

Create a Controller Class

package com.vj;

import java.util.Iterator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class HelloController {
@Autowired
UserrhyRepository repo;
 @RequestMapping("/")
   public String index() {

                Userrhy user=new Userrhy();

 user.setName("vj");
repo.save(user);
Userrhy usr=repo.findOne(1);

Iterable<Userrhy> users= repo.findAll();
Iterator userrow=users.iterator();
while(userrow.hasNext()){
Userrhy us=(Userrhy)userrow.next();
System.out.println("User ID"+us.getId());
System.out.println("User Name"+us.getName());

}
       return "Greetings from Spring Boot!"+usr.getName();
   }
 

}

->> Create an Entity class and create a table in your database with same fields and same name in small letters.

package com.vj;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Userrhy {

private String name;
@Id
@Column(name="id")
@GeneratedValue(strategy=GenerationType.AUTO)
private Integer id;

public String getName() {
return name;
}

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

public Integer getId() {
return id;
}

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

->>Create a repository interface for querying DB

package com.eits.sql;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface UserrhyRepository extends CrudRepository<Userrhy, Integer>{
@Query(value = "SELECT * FROM userrhy WHERE id = ?1", nativeQuery = true)
Userrhy findUser(int id);

}

Now Run the application as spring-boot:run .And hit the browser with http://localhost:8080.And check your console which has all the records of userrhy table printed.And also create a record which we add in out controller.

PLEASE LIKE AND SHARE