{"id":3390,"date":"2017-02-14T11:14:48","date_gmt":"2017-02-14T16:14:48","guid":{"rendered":"http:\/\/springframework.guru\/?p=3390"},"modified":"2024-10-21T10:23:13","modified_gmt":"2024-10-21T14:23:13","slug":"configuring-spring-boot-for-microsoft-sql-server","status":"publish","type":"post","link":"https:\/\/springframework.guru\/configuring-spring-boot-for-microsoft-sql-server\/","title":{"rendered":"Configuring Spring Boot for Microsoft SQL Server"},"content":{"rendered":"

Out of the box, Spring Boot is very easy to use with the H2 Database<\/a>. 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.<\/p>\n

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.<\/p>\n

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<\/a>, MySQL<\/a>, MariaDB<\/a>, and PostgreSQL<\/a>.<\/p>\n

Spring makes switching between RDBM’s simple. When you\u2019re using Spring Data JPA<\/a> with an ORM technology such as\u00a0Hibernate<\/a>, 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<\/a>, such as MongoDB<\/a>.<\/p>\n

In this post, I will discuss Spring Boot configuration for Microsoft SQL Server<\/a>.<\/p>\n

SQL Server Configuration<\/h2>\n

For this post, I\u2019m using SQL Server 2014 Express<\/a> installed locally on my laptop. I used SQL Server 2014 Management Studio<\/a> to connect to the database server using SQL Server Authentication.
\n\"Connect<\/a>
\nOnce you are logged in, create a springbootdb<\/strong> database from the Object Explorer<\/strong> window.
\n\"Configure<\/a><\/p>\n

A common problem that trips up many Java developers trying to connect to SQL Server is this error:<\/p>\n

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP\/IP connection to the host localhost, port 1433 has failed. Error: \u201cConnection 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.\u201d.<\/pre>\n
I too learned the hard way to resolve it with these steps.<\/p>\n
    \n
  1. \n
      \n
    1. From the Start<\/strong> menu, open SQL Server 2014 Configuration Manager<\/strong>.<\/li>\n
    2. Click Protocol for SQLEXPRESS<\/strong> under SQL Server Network Configuration<\/strong> on the left pane. On the right pane, right- click TCP\/IP<\/strong>, and select Properties<\/strong>.<\/li>\n
    3. On the TCP\/IP Properties<\/strong> dialog box that appears, click the IP Addresses<\/strong> tab.<\/li>\n
    4. Scroll down to locate the IPALL<\/strong> node. Remove any value, if present for TCP Dynamic Ports<\/strong> and specify 1433<\/strong> for TCP Port<\/strong>.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
      \"TCP\/IP<\/a><\/p>\n
        \n
      1. Click OK<\/strong>.<\/li>\n
      2. Again right-click TCP\/IP<\/strong> on the right pane, and select Enable<\/strong>.<\/li>\n
      3. On the SQL Server Services<\/strong> node, right-click SQL Server (SQLEXPRESS)<\/strong>, and select Restart<\/strong>.<\/li>\n<\/ol>\n
        This sets up SQL Server to be reached from JDBC code.<\/p>\n
        SQL Server Dependencies<\/h2>\n
        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<\/a>.<\/p>\n
        The Maven POM file of my Spring Boot application that brings in the database driver is this.<\/p>\n
        pom.xml<\/h4>\n
        \n\n    4.0.0\n    guru.springframework\n    blogposts\n    0.0.1-SNAPSHOT\n    jar\n    Blog Posts\n    Misc Blog Posts\n    \n        org.springframework.boot\n        spring-boot-starter-parent\n        1.4.4.RELEASE\n         \n    \n    \n        UTF-8\n        guru.springframework.blog.BlogPostsApplication\n        1.8\n    \n    \n        \n            org.springframework.boot\n            spring-boot-starter\n        \n        \n            org.springframework.boot\n            spring-boot-starter-data-jpa\n        \n        \n            com.microsoft.sqlserver\n            sqljdbc4\n            4.0\n        \n        \n            org.springframework.boot\n            spring-boot-starter-test\n            test\n        \n    \n    \n        \n            \n                org.springframework.boot\n                spring-boot-maven-plugin\n            \n        \n    \n\n<\/pre>\n
        Spring Boot Properties<\/h2>\n
        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\u2019t. So, when we configure SQL Server for use, Spring Boot won\u2019t setup the H2 database anymore.<\/p>\n
        The following data source configurations are required to configure SQL Server with Spring Boot.<\/p>\n
        application.properties<\/h4>\n
        spring.datasource.url=jdbc:sqlserver:\/\/localhost;databaseName=springbootdb\nspring.datasource.username=sa\nspring.datasource.password=Projects@123\nspring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver\nspring.jpa.show-sql=true\nspring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect\nspring.jpa.hibernate.ddl-auto = create-drop<\/pre>\n
        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<\/code>, validate<\/code>, update<\/code>, create-drop<\/code>.<\/p>\n
        For a production database, you probably want to use validate<\/code>.<\/p>\n
        \"Spring<\/a>
        Become a Spring Framework 5 Guru!<\/figcaption><\/figure>\n
        JPA Entity<\/h2>\n
        In our example application, we will perform CRUD operations on a user. For that, we will write a simple JPA entity, User<\/code> for our application. I have written a post to use Spring Data JPA in a Spring Boot Web application<\/a>, and so won\u2019t go into JPA here.<\/p>\n
        User.java<\/h4>\n
        package guru.springframework.blog.domain;\n\nimport javax.persistence.*;\n\n@Entity\n@Table(name = \"user_tbl\")\npublic class User {\n    @Id\n    @GeneratedValue(strategy = GenerationType.AUTO)\n    private Integer id;\n    private String name;\n    private int age;\n    public User() {\n    }\n\n    public User(String name, int age) {\n        this.name = name;\n        this.age = age;\n    }\n\n    public Integer getId() {\n        return id;\n    }\n    public void setId(Integer id) {\n        this.id = id;\n    }\n\n    public String getName() {\n        return name;\n    }\n    public void setName(String name) {\n        this.name = name;\n    }\n\n    public int getAge() {\n        return age;\n    }\n    public void setAge(int age) {\n        this.age = age;\n    }\n    @Override\n    public String toString() {\n        return \"User{\" +\n                \", name='\" + name + '\\'' +\n                \", Age=\" + age +\n                '}';\n    }\n}<\/pre>\n
        JPA Repository<\/h2>\n
        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<\/code> 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.<\/p>\n
        For our User<\/code> domain class we can define a Spring Data JPA repository as follows.<\/p>\n
        UserRepository.java<\/h4>\n
        package guru.springframework.blog.repositories;\n\nimport guru.springframework.blog.domain.User;\nimport org.springframework.data.repository.CrudRepository;\n\npublic interface UserRepository extends CrudRepository<User, Integer> {\n    User findByName(String name);\n}<\/pre>\n
        That\u2019s all we need to setup in Spring Boot to use SQL Server.<\/p>\n
        Let’s write some test code for this setup.<\/p>\n
        UserRepositoryTest.java<\/h4>\n
        package guru.springframework.blog.repositories;\n\nimport guru.springframework.blog.domain.User;\nimport org.junit.Before;\nimport org.junit.Test;\nimport org.junit.runner.RunWith;\nimport org.springframework.beans.factory.annotation.Autowired;\nimport org.springframework.boot.test.context.SpringBootTest;\nimport org.springframework.test.context.junit4.SpringRunner;\n\nimport static org.junit.Assert.*;\n@RunWith(SpringRunner.class)\n@SpringBootTest\npublic class UserRepositoryTest {\n    @Autowired\n    private UserRepository userRepository;\n    @Before\n    public void setUp() throws Exception {\n        User user1= new User(\"Alice\", 23);\n        User user2= new User(\"Bob\", 38);\n        \/\/save user, verify has ID value after save\n        assertNull(user1.getId());\n        assertNull(user2.getId());\/\/null before save\n        this.userRepository.save(user1);\n        this.userRepository.save(user2);\n        assertNotNull(user1.getId());\n        assertNotNull(user2.getId());\n    }\n\n    @Test\n    public void testFetchData(){\n        \/*Test data retrieval*\/\n        User userA = userRepository.findByName(\"Bob\");\n        assertNotNull(userA);\n        assertEquals(38, userA.getAge());\n        \/*Get all products, list should only have two*\/\n        Iterable users = userRepository.findAll();\n        int count = 0;\n        for(User p : users){\n            count++;\n        }\n        assertEquals(count, 2);\n    }\n}<\/pre>\n
        For the test, I have used JUnit. To know more about JUnit, you can refer my series on JUnit Testing<\/a>.<\/p>\n
        The result of the JUnit test is this.<\/p>\n
        \"JUnit<\/a><\/p>\n
        Conclusion<\/h1>\n
        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.<\/p>\n","protected":false},"excerpt":{"rendered":"
        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 […]Continue reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":4575,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_lmt_disableupdate":"","_lmt_disable":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[1],"tags":[177,178,29],"class_list":["post-3390","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","tag-microsoft","tag-microsoft-sql-server","tag-spring-boot"],"jetpack_publicize_connections":[],"aioseo_notices":[],"modified_by":"jt","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/springframework.guru\/wp-content\/uploads\/2018\/06\/NewBannerBOOTSWeb.jpg","jetpack_shortlink":"https:\/\/wp.me\/p5BZrZ-SG","_links":{"self":[{"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/posts\/3390"}],"collection":[{"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/comments?post=3390"}],"version-history":[{"count":8,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/posts\/3390\/revisions"}],"predecessor-version":[{"id":8298,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/posts\/3390\/revisions\/8298"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/media\/4575"}],"wp:attachment":[{"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/media?parent=3390"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/categories?post=3390"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/tags?post=3390"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}