Spring:Connect Sql server from spring boot service

Question

I have rest crud service which will help me make post and get requests and receive respopnses from sql server , inside my application.properties i use similar data:

server.port=9004
spring.datasource.url=jdbc:sqlserver://localhost/1433;databaseName=test1
spring.datasource.username=sa
spring.datasource.password=*****
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2014Dialect
spring.jpa.hibernate.ddl-auto =ddl-auto

AND HERE IS MY POM:

<maven.compiler.source>1.8</maven.compiler.source>
  <maven.compiler.target>1.8</maven.compiler.target>
</properties>

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.3.6.RELEASE</version>
    </parent>
 <dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>
<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</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-tomcat</artifactId>
        <scope>provided</scope>
      </dependency>    

        </dependencies>

<build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

when i run this as a java project i alway got this error:

Caused by: org.springframework.beans.factory.BeanCreationException: Could not autowire field: private org.springframework.boot.autoconfigure.web.HttpMessageConverters org.springframework.boot.autoconfigure.web.WebMvcAutoConfiguration$WebMvcAutoConfigurationAdapter.messageConverters; nested exception is

and:

with name 'mappingJackson2HttpMessageConverter' defined in class path resource [org/springframework/boot/autoconfigure/web/JacksonHttpMessageConvertersConfiguration$MappingJackson2HttpMessageConverterConfiguration.class]: Unsatisfied dependency expressed through constructor argument with index 0 of type [com.fasterxml.jackson.databind.ObjectMapper]: Error creating bean with name 'objectMapper' defined in class path resource [org/springframework/boot/autoconfigure/data/rest/SpringBootRepositoryRestMvcConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.fasterxml.jackson.databind.ObjectMapper]: Factory method 'objectMapper' threw exception; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'config' defined in class path resource [org/springframework/boot/autoconfigure/data/rest/SpringBootRepositoryRestMvcConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.data.rest.core.config.RepositoryRestConfiguration]: Factory method 'config' threw exception; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'resourceMappings' defined in class path resource [org/springframework/boot/autoconfigure/data/rest/SpringBootRepositoryRestMvcConfiguration.class]: Bean instantiation via factory method failed; nested exception is

what should i change inside my pom or properties file to make my program work?

Hight

Answer 1

SQL Server dependency is deprecated, use following to interact with SQL Server

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>6.1.0.jre8</version>
</dependency> 

follow links for more information

Configuring Spring Boot for Microsoft SQL Server

Open sourcing the Microsoft JDBC Driver and Maven support

Zinciferous

Answer 2

Since it is a REST app, need to add the spring-boot-starter-web dependency in pom.xml

    <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>  

Add also connection properties in config (application.properties by default):

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=springbootdb
spring.datasource.username=sa
spring.datasource.password=replace_value
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

Try that one and see if you can build/run the project.

Spang

first of all thank you for your reply but unfortanetly it doesn't work i mean i can't start my service now i have this error : org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set – Hight

will i need to add persistence xml or another configuration file to meca database connection more convenient and accaptable for spring boot application? – Hight

Yes, need to add the persistence config. See updated answer. Also see this link for reference: springframework.guru/… – Spang

persistence.xml insiede src/main/resource/META-INF – Hight

and now i have this errors :Error creating bean with name 'config' defined in class path resource [org/springframework/boot/autoconfigure/data/rest/SpringBootRepositoryRestMvcConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate – Hight

when i try to connect remote sql server , perheps it is mistake that i use spring dependecy for embeded database? – Hight

Can you start from scratch by create a new project in start.spring.io with web and jpa as dependencies. Then add the sqljdbc4 dependency in the pom file? Updated code above to include this dependency. – Spang

Answer 3

Change your url parameter to:

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=test1

Port number must be preceded by a colon(":"), not slash("/").

Nyberg

Answer 4

I tried the same recently, setting up the local MS server and connecting to it from my spring boot project, Here is my working configuration.

# SQL Server configuration
spring.datasource.url=jdbc:sqlserver://127.0.0.1;databaseName=master;integratedSecurity=false;TrustServerCertificate=true
spring.datasource.username=demo
spring.datasource.password=demo
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

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

    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

For creating a new user on your local MS server, if needed:

USE [master]
    GO
    CREATE LOGIN [demo] WITH PASSWORD=N'demo', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    
    USE [master]
    GO
    CREATE USER [demo] FOR LOGIN [demo] WITH DEFAULT_SCHEMA=[dbo]
    GO
    
    USE [master]
    GO
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [demo]
    GO

For TCP/IP issue: JDBC connection failed, error: TCP/IP connection to host failed

Need to restart the MS server for above two changes

Goodnatured