Spring Boot Connect to Microsoft SQL Server Examples

In this Spring Boot article, you will learn how to connect to Microsoft SQL Server from a Spring Boot application in the following two common scenarios:

• A Spring Boot console application with Spring JDBC and JdbcTemplate
• A Spring Boot web application with Spring Data JPA and Hibernate framework

Basically, here are the steps you need to do in order to make a connection to a database on Microsoft SQL server:

• Declare a dependency for SQL Server JDBC driver that allows Java application to connect to Microsoft SQL Server.
• Declare a dependency for Spring JDBC or Spring Data JPA
• Specify data source properties for the database connection information
• For simple cases, you can use Spring JDBC with JdbcTemplate for executing plain SQL statements against the database
• For more advanced usage, you can use Spring Data JPA with an entity class and a repository interface.

Below are the details for connecting to Microsoft SQL Server in a Spring Boot application.

1. Declare dependency for SQL Server JDBC Driver

Put the following dependency declaration in the Maven project file of your Spring Boot project:

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

Note that the scope is runtime, which means the driver JAR file is only needed at runtime – not development (compile time). And you don’t need to specify the version as Spring Boot uses the default version specified in the parent POM.

2. Specify Data Source Properties

Open the Spring Boot application configuration file (application.properties) and specify the following properties:

spring.datasource.url=jdbc:sqlserver://sqlsrv\\sqlexpress;databaseName=customer
spring.datasource.username=username
spring.datasource.password=password

Here, the JDBC URL points to a named instance of a remote SQL server and SQL authentication mode is used (recommended).

3. Connect to SQL Server with Spring JDBC

Use Spring JDBC if you just want to connect and execute simple SQL statements. Add the following dependency to your project:

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

For example, below is code of a Spring Boot console program that uses Spring JDBC with JdbcTemplate API:

package net.codejava;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

@SpringBootApplication
public class SpringBootJdbcTemplateSqlServerApplication implements CommandLineRunner {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public static void main(String[] args) {
		SpringApplication.run(SpringBootJdbcTemplateSqlServerApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		String sql = "SELECT * FROM customers";
		List<Customer> customers = jdbcTemplate.query(sql, 
				BeanPropertyRowMapper.newInstance(Customer.class));
		
		customers.forEach(System.out :: println);
	}

}

As you can see, this program connects to SQL Server and execute a SQL Select statement for retrieving rows from the customers table. You don’t need to explicitly make a connection or disconnection as the JdbcTemplate does it behind the scene.

Follow this this article to learn more about using Spring JdbcTemplate.

4. Connect to SQL Server with Spring Data JPA

In case you need to use Spring Data JPA, declare the following dependency in the Maven project file:

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Note that by default, Spring Data JPA uses Hibernate as implementation of Java Persistence API (JPA).

Besides the required data source properties (JDBC URL, username and password), you can also specify some additional properties as follows:

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect. org.hibernate.dialect.SQLServer2008Dialect

And then, you need to code a Java class (entity) that maps to a table in the database, for example:

package net.codejava;

import javax.persistence.*;

@Entity
@Table(name = "customers")
public class Customer {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;
	
	private String name;
	private String email;
	private int age;
	
	// getters and setters...
}

This entity class maps to the corresponding customers table in SQL server database. And then you need to create the corresponding repository interface as below:

package net.codejava;

import org.springframework.data.jpa.repository.JpaRepository;

public interface CustomerRepository extends JpaRepository<Customer, Long> {

}

And then you can use the repository in a Spring MVC controller or business class like this:

@Controller
public class CustomerController {
	@Autowired
	private CustomerRepository customerRepo;
	
	@GetMapping("/customers")
	public String listAll(Model model) {
		List<Customer> listCustomers = customerRepo.findAll();
		model.addAttribute("listCustomers", listCustomers);
		
		return "customers";
	}
	
}

Those are some code examples about connecting to Microsoft SQL Server in a Spring Boot application. You can use Spring JDBC for simple cases and Spring Data JPA for more advanced usage.

To learn more about Spring Data JPA, I recommend you to follow this article: Understand Spring Data JPA with Simple Example.

To see the coding in action, I recommend you to watch the following video:

Related Articles:

• Understand Spring Data JPA with Simple Example
• Spring MVC with JdbcTemplate Example
• Spring Boot - Spring Data JPA - MySQL Example

Other Spring Boot Tutorials:

• How to create a Spring Boot Web Application (Spring MVC with JSP/ThymeLeaf)
• Spring Boot CRUD Example with Spring MVC – Spring Data JPA – ThymeLeaf - Hibernate - MySQL
• Spring Boot Hello World RESTful Web Services Tutorial
• Spring Boot Thymeleaf Form Handling Tutorial
• Spring Data JPA Paging and Sorting Examples
• Spring Boot Error Handling Guide
• Spring Boot Logging Basics
• Spring Security Role-based Authorization Tutorial
• Spring Security Customize Login and Logout
• How to Get Logged-in User's Details with Spring Security
• Spring Security: Prevent User from Going Back to Login Page if Already logged in

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.