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:

 

Other Spring Boot Tutorials:


About the Author:

is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.



Add comment

   


Comments 

#5Lê Công Huy2023-05-15 22:02
Tôi đã mapping với database nhưng việc sử dụng hàm findAll() của tôi không thể thực hiện được việc lấy dữ liệu trong database
Quote
#4skipa2022-10-24 09:12
How can I do the connection to Hadoop?
Quote
#3Jesus Veliz2021-07-12 16:48
Hi, sorry for the inconvenience I am using Spring java 8. and SQL Server 2019. with Mave dependencies.

And I can't connect the database, that is, I can't connect java with SQL Server.

If you could help me.
Quote
#2Dhrumin shah2021-03-16 05:28
im getting error of: can you help
org.hibernate.boot.registry.selector.spi.StrategySelectionException: Unable to resolve name [org.hibernate.dialect. org.hibernate.dialect.SQLServer2008Dialect] as strategy [org.hibernate.dialect.Dialect]
Quote
#1Manjunath2021-02-04 03:32
Can you please share the complete source code. Why I ask, i'm getting an error while creating Repository instance: Autowired failed.
Quote