Problem of build runnable jar file of a Spring boot using SQL Server jdbc

What Spring boot version do you use? Please check these threads: https://github.com/spring-projects/spring-boot/issues/6537 , https://github.com/spring-projects/spring-boot/issues/6314 This SO post also has suggestions to similar problem.

Hi Andrey

Thanks for reply. The version number of Spring Boot is 2.0. I have checked these two threads, seamed there was not clear solution for it. Could you help me with it? 

The dependencies in pom.xml is below:<dependencies>

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

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

   <dependency>
      <groupId>com.googlecode.json-simple</groupId>
      <artifactId>json-simple</artifactId>
      <version>1.1</version>
   </dependency>

   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
   </dependency>
</dependencies>

SQL Server was used as database, and sqljdbc42.jar was included into external library.

The DbConfig.class has two data source showing below:

@Bean(name="primaryDataSource")
@Primary
@ConfigurationProperties(prefix="spring.datasource")
public DataSource primaryDataSource(){
    //return DataSourceBuilder.create().build();

    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(primaryDriverClassName);
    dataSource.setUrl(primaryUrl);
    dataSource.setUsername(primaryUsername);
    dataSource.setPassword(primaryPassword);

    return dataSource;
}

@Bean(name="secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource1")
public DataSource secondaryDataSource(){
   //return DataSourceBuilder.create().build();
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(secondaryDriverClassName);
    dataSource.setUrl(secondaryUrl);
    dataSource.setUsername(secondaryUsername);
    dataSource.setPassword(secondaryPassword);

    return dataSource;

}

@Bean(name = "jdbcPrimaryTemplate")
@Autowired
public JdbcTemplate jdbcPrimaryTemplate(@Qualifier(value = "primaryDataSource") DataSource primaryDataSource) {
    return new JdbcTemplate(primaryDataSource);
}

@Bean(name = "jdbcSecondaryTemplate")
@Autowired
public JdbcTemplate jdbcSecondaryTemplate(@Qualifier(value = "secondaryDataSource") DataSource secondaryDataSource) {
    return new JdbcTemplate(secondaryDataSource);
}

The CzProductDao.class was:

@Repository
public class CzProductDao {
    @Qualifier("jdbcPrimaryTemplate")
    @Autowired
    JdbcTemplate jdbcPrimaryTemplate;

    public int getCountOfPt(){
        return jdbcPrimaryTemplate.queryForObject("select count(*) from pt_mstr", Integer.class);
    }
}

The CzProlinkSQLController was:

@RestController
@RequestMapping(value = "CZ")
public class CzProlinkSQLController {

    @Autowired
    CzProductDao czProductDao;

    @RequestMapping(value = "count_pt", method = RequestMethod.GET)
    public String getCountPt(){
        JSONObject result = new JSONObject();
        int num = czProductDao.getCountOfPt();

        result.put("count", num);
        return result.toJSONString();
    }
}

The full error was:

2018-04-08 09:11:06.230 INFO 11488 --- [ main] o.s.j.d.DriverManagerDataSource : Loaded JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
2018-04-08 09:11:06.239 INFO 11488 --- [ main] o.s.j.d.DriverManagerDataSource : Loaded JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
2018-04-08 09:11:06.841 WARN 11488 --- [ main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: Unable to resolve persistence unit root URL
2018-04-08 09:11:06.845 INFO 11488 --- [ main] o.apache.catalina.core.StandardService : Stopping service [Tomcat]
2018-04-08 09:11:06.860 INFO 11488 --- [ main] ConditionEvaluationReportLoggingListener :

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2018-04-08 09:11:06.869 ERROR 11488 --- [ main] o.s.boot.SpringApplication : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: Unable to resolve persistence unit root URL
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1710) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:583) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1085) ~[spring-context-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:858) ~[spring-context-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549) ~[spring-context-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:140) ~[spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:752) [spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:388) [spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:327) [spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1246) [spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1234) [spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
at com.vtyc.SqlserverApplication.main(SqlserverApplication.java:14) [sqlserver.jar:na]
Caused by: javax.persistence.PersistenceException: Unable to resolve persistence unit root URL
at org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager.determineDefaultPersistenceUnitRootUrl(DefaultPersistenceUnitManager.java:637) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager.preparePersistenceUnitInfos(DefaultPersistenceUnitManager.java:459) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager.afterPropertiesSet(DefaultPersistenceUnitManager.java:440) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:328) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1769) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1706) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
... 16 common frames omitted
Caused by: java.io.FileNotFoundException: class path resource [] cannot be resolved to URL because it does not exist
at org.springframework.core.io.ClassPathResource.getURL(ClassPathResource.java:195) ~[spring-core-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager.determineDefaultPersistenceUnitRootUrl(DefaultPersistenceUnitManager.java:633) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
... 21 common frames omitted

In case someone meets the same problem. I put my solution here to share with you. Two steps to solve it:

1. Maven install sqljdbc42.jar, than sqljdbc42.jar can be included in pom.xml

2. Use maven to do package. It will solve the problem.