Configuring Spring Boot for Microsoft SQL Server

Configuring Spring Boot for Microsoft SQL Server

24 Comments

Out of the box, Spring Boot is very easy to use with the H2 Database. Spring programmers typically prefer writing code against such lightweight in-memory database, rather than on an enterprise database server such as Microsoft SQL Server or Oracle.

In-memory databases come with several restrictions making them useful only in the development stages in local environments. While in-memory databases are great to develop against, data is not persisted to disk, thus is lost when the database is shut down.

As the development progresses, you would most probably require an RDBMS to develop and test your application before deploying it to use a production database server. I have written a series of posts on integrating Spring Boot for Oracle, MySQL, MariaDB, and PostgreSQL.

Spring makes switching between RDBM’s simple. When you’re using Spring Data JPA with an ORM technology such as Hibernate, the persistence layer is nicely well decoupled. Which allows you to run your code against multiple databases. The level of decoupling even allows you to easily switch between an RDBMS and a NoSQL database, such as MongoDB. One of my previous post on Integrating Spring Boot for MongoDB covers that.

In this post, I will discuss Spring Boot configuration for Microsoft SQL Server.

SQL Server Configuration

For this post, I’m using SQL Server 2014 Express installed locally on my laptop. I used SQL Server 2014 Management Studio to connect to the database server using SQL Server Authentication.
Connect To SQL Server
Once you are logged in, create a springbootdb database from the Object Explorer window.
Configure SQL Server database for use with Spring Boot

A common problem that trips up many Java developers trying to connect to SQL Server is this error:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: “Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.”.

I too learned the hard way to resolve it with these steps.

    1. From the Start menu, open SQL Server 2014 Configuration Manager.
    2. Click Protocol for SQLEXPRESS under SQL Server Network Configuration on the left pane. On the right pane, right- click TCP/IP, and select Properties.
    3. On the TCP/IP Properties dialog box that appears, click the IP Addresses tab.
    4. Scroll down to locate the IPALL node. Remove any value, if present for TCP Dynamic Ports and specify 1433 for TCP Port.

TCP/IP Properties for SQL Server

  1. Click OK.
  2. Again right-click TCP/IP on the right pane, and select Enable.
  3. On the SQL Server Services node, right-click SQL Server (SQLEXPRESS), and select Restart.

This sets up SQL Server to be reached from JDBC code.

SQL Server Dependencies

To connect with SQL Server from Java applications, Microsoft provides a Microsoft JDBC Driver for SQL Server. However, till November 2016, Maven did not directly support the driver as it was not open sourced. By making it open source, Microsoft finally made the driver available on the Maven Central Repository. More information can be found here.

The Maven POM file of my Spring Boot application that brings in the database driver is this.

pom.xml


    4.0.0
    guru.springframework
    blogposts
    0.0.1-SNAPSHOT
    jar
    Blog Posts
    Misc Blog Posts
    
        org.springframework.boot
        spring-boot-starter-parent
        1.4.4.RELEASE
         
    
    
        UTF-8
        guru.springframework.blog.BlogPostsApplication
        1.8
    
    
        
            org.springframework.boot
            spring-boot-starter
        
        
            org.springframework.boot
            spring-boot-starter-data-jpa
        
        
            com.microsoft.sqlserver
            sqljdbc4
            4.0
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        
    
    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
            
        
    

Spring Boot Properties

We need to override the H2 database properties being set by default in Spring Boot. The nice part is, Spring Boot sets default database properties only when you don’t. So, when we configure SQL Server for use, Spring Boot won’t setup the H2 database anymore.

The following data source configurations are required to configure SQL Server with Spring Boot.

application.properties

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=springbootdb
spring.datasource.username=sa
spring.datasource.password=Projects@123
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
spring.jpa.hibernate.ddl-auto = create-drop

As we are using JPA, we need to configure Hibernate for SQL Server too. Line 7 tells Hibernate to recreate the database on startup. This is definitely not the behavior we want if this was actually a production database You can set this property to the following values: none, validate, update, create-drop.

For a production database, you probably want to use validate.

Spring Framework 5
Become a Spring Framework 5 Guru!

JPA Entity

In our example application, we will perform CRUD operations on a user. For that, we will write a simple JPA entity, User for our application. I have written a post to use Spring Data JPA in a Spring Boot Web application, and so won’t go into JPA here.

User.java

package guru.springframework.blog.domain;

import javax.persistence.*;

@Entity
@Table(name = "user_tbl")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String name;
    private int age;
    public User() {
    }

    public User(String name, int age) {
        this.name = name;
        this.age = age;
    }

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

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

    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "User{" +
                ", name='" + name + '\'' +
                ", Age=" + age +
                '}';
    }
}

JPA Repository

Spring Data JPA CRUD Repository is a feature of Spring Data JPA that I extensively use. Using it, you can just define an interface that extends CrudRepository to manage entities for most common operations, such as saving an entity, updating it, deleting it, or finding it by id. Spring Data JPA uses generics and reflection to generate the concrete implementation of the interface we define.

For our User domain class we can define a Spring Data JPA repository as follows.

UserRepository.java

package guru.springframework.blog.repositories;

import guru.springframework.blog.domain.User;
import org.springframework.data.repository.CrudRepository;

public interface UserRepository extends CrudRepository<User, Integer> {
    User findByName(String name);
}

That’s all we need to setup in Spring Boot to use SQL Server.

Let’s write some test code for this setup.

UserRepositoryTest.java

package guru.springframework.blog.repositories;

import guru.springframework.blog.domain.User;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import static org.junit.Assert.*;
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserRepositoryTest {
    @Autowired
    private UserRepository userRepository;
    @Before
    public void setUp() throws Exception {
        User user1= new User("Alice", 23);
        User user2= new User("Bob", 38);
        //save user, verify has ID value after save
        assertNull(user1.getId());
        assertNull(user2.getId());//null before save
        this.userRepository.save(user1);
        this.userRepository.save(user2);
        assertNotNull(user1.getId());
        assertNotNull(user2.getId());
    }

    @Test
    public void testFetchData(){
        /*Test data retrieval*/
        User userA = userRepository.findByName("Bob");
        assertNotNull(userA);
        assertEquals(38, userA.getAge());
        /*Get all products, list should only have two*/
        Iterable users = userRepository.findAll();
        int count = 0;
        for(User p : users){
            count++;
        }
        assertEquals(count, 2);
    }
}

For the test, I have used JUnit. To know more about JUnit, you can refer my series on JUnit Testing.

The result of the JUnit test is this.

JUnit Test Result for SQL Server

Conclusion

As you can see, it is very easy to configure Spring Boot for SQL Server. As usual, Spring Boot will auto configure sensible defaults for you. And as needed, you can override the default Spring Boot properties for your specific application.

About jt

    You May Also Like

    24 comments on “Configuring Spring Boot for Microsoft SQL Server

    1. May 24, 2017 at 6:05 pm

      I’ve hit an issue with this where I get the following message when starting up:

      org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘requestMappingHandlerAdapter’ defined in class path resource [org/springframework/boot/autoconfigure/web/WebMvcAutoConfiguration$EnableWebMvcConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter]: Factory method ‘requestMappingHandlerAdapter’ threw exception; nested exception is java.lang.NoSuchFieldError: defaultReader

      Also, the property spring.jpa.hibernate.dialect is an unknown property for me. I cannot use it.

      I’ve tested with both 6.1.0.jre7 and 6.1.0.jre8 and get the same error. Any ideas?

      Reply
      • May 24, 2017 at 6:44 pm

        No, nothing is jumping out at me. Looks like a possible dependency conflict – like maybe you have mixed versions

        Reply
      • August 29, 2017 at 6:19 am

        Hi, I’m experiencing the same issue, did you figure this one out? Thank you very much

        Reply
    2. June 17, 2017 at 4:12 pm

      its working for me
      but can you please make a complete example on how to manipulate the data and view it ??
      thanks already
      and there is something worth mentioning here which is if the the datatype in the DB is NVARCHAR exception will occur we need to add “columnDefinition” like this:
      @Column(name = “Name”, columnDefinition = “NVARCHAR”)
      thanks 🙂

      Reply
    3. August 16, 2017 at 6:32 am

      Do you have an example with Windows Authentication? Thank you

      Reply
      • August 16, 2017 at 9:15 am

        No, I do not

        Reply
    4. September 1, 2017 at 12:32 pm

      Question about the pom.xml. Why is the SQL Server JDBC Driver commented out?

      Reply
      • September 1, 2017 at 12:40 pm

        That was a mistake – fixed now.

        Reply
        • October 11, 2017 at 5:28 am

          what was the solution to it?

          Reply
          • October 11, 2017 at 8:53 am

            Sorry, solution was very simple, few days before I’ve installed java9 to test it and forgot about, in the IDE was seted up java 1.8, in windows defualt was java 1.9… so when I changed to default java 1.8 it will be ok =)

            Reply
    5. September 12, 2017 at 5:10 am

      Can I auto generate hibernate pojos from ddl db schema?

      Reply
    6. September 30, 2017 at 6:03 am

      Hi, it perfect works, when I run it from IntelejIdea, but when I trying to build (-mvm clean package) and run java -jar [myapp].jar,
      I’ve got the following issue, like
      //–
      Error creating bean with name ‘entityManagerFactory’ defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Unsatisfied dependency expressed through method ‘entityManagerFactory’ parameter 0;
      //–
      AS I’ve read, I need to make some configuration class, please can you help me with it ?

      Reply
    7. November 2, 2017 at 3:19 pm

      Hi All, I am Facing Below Exception while Saving, o.h.engine.jdbc.spi.SqlExceptionHelper : A result set was generated for update.
      Exception ::: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement

      Reply
    8. November 29, 2017 at 10:49 am

      Any update ?

      Reply
    9. December 4, 2017 at 5:35 pm

      I get the following error on the use of the sql-server jdbc – any Idea’s the pom and application.properties match ?
      Exception encountered during context initialization – cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ‘org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration’: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘dataSource’ defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Tomcat.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.apache.tomcat.jdbc.pool.DataSource]: Factory method ‘dataSource’ threw exception; nested exception is java.lang.IllegalStateException: Cannot load driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver

      Reply
    10. May 1, 2018 at 5:03 am

      I want to autheticate using kerberos instead of username and password. Any leads how to do that?

      spring.datasource.username=sa
      spring.datasource.password=Projects@123

      Reply
    11. June 10, 2018 at 9:44 pm

      spring.jpa.hibernate.dialect is an unknown property , i don’t know why, can you help me?

      Reply
      • September 18, 2018 at 4:34 pm

        The correct entry is the following: spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

        Example above:
        spring.jpa.hibernate.dialect
        Correct value:
        spring.jpa.properties.hibernate.dialect

        Reply
    12. August 15, 2018 at 12:25 pm

      Hi
      In my pow.xml file i have this: Missing artifact com.microsoft.sqlserver:sqljdbc4:jar:4.0, any sugestion?

      Reply
      • August 29, 2018 at 8:10 am

        Serguey, I put this in my pom.xml:

        com.microsoft.sqlserver
        mssql-jdbc
        7.0.0.jre8

        instead of “4.0”

        Reply
    13. September 14, 2018 at 8:23 am

      think you, that’s so helpful

      Reply
    14. June 4, 2019 at 3:12 pm

      I am getting following error when executing the test:
      [ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.426 s <<< FAILURE! – in de.lim.tap.indikatorenbogen.repositories.BewohnerRepositoryTest
      [ERROR] initializationError(de.lim.tap.indikatorenbogen.repositories.BewohnerRepositoryTest) Time elapsed: 0.016 s <<< ERROR!
      java.lang.IllegalStateException: Unable to find a @SpringBootConfiguration, you need to use @ContextConfiguration or @SpringBootTest(classes=…) with your test
      I only changed packages and class names because my app does not manage users but other persons. I don't understand the error, because I have the @SpringBootTest annotation like in your example code:

      @RunWith(SpringRunner.class)
      @SpringBootTest
      public class BewohnerRepositoryTest {
      @Autowired
      private BewohnerRepository bewohnerRepository;

      Reply
    15. October 29, 2020 at 3:37 am

      How to use mssql as embeded database for unit test purpose where it calls stored procedures?

      Reply

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.