Spring Boot with MS SQL Server Driver and Tomcat

Question

I am creating a web application with Spring Boot and MS SQL Server 2012. For that I added the Driver (sqljdbc4.jar) to the Maven local Repository and added the dependency. I can find and use the driver in the IDE: SQLServerDriver in Netbeans

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>my.service</groupId>
    <artifactId>my-app</artifactId>
    <version>0.1.0</version>
    <packaging>war</packaging>

    <name>my-app</name>
    <description>My App</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.1.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <endorsed.dir>${project.build.directory}/endorsed</endorsed.dir>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </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>
    </dependencies>

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

</project>

After building and deploying the application to a Tomcat server, the library is in the WEB-INF\lib folder:

C:\my-app\target\my-app-0.1.0\WEB-INF\lib>dir
03.11.2016  14:17    <DIR>          .
03.11.2016  14:17    <DIR>          ..
27.10.2016  11:48           445.288 antlr-2.7.7.jar
28.10.2016  14:15         1.864.922 aspectjweaver-1.8.9.jar
03.11.2016  14:02            33.218 avalon-framework-api-4.3.1.jar
03.11.2016  14:02            61.021 avalon-framework-impl-4.3.1.jar
03.11.2016  14:02           377.042 batik-anim-1.8.jar
03.11.2016  14:02           301.028 batik-awt-util-1.8.jar
03.11.2016  14:02           529.674 batik-bridge-1.8.jar
03.11.2016  14:02           256.539 batik-css-1.8.jar
03.11.2016  14:02           140.057 batik-dom-1.8.jar
03.11.2016  14:02            10.347 batik-ext-1.8.jar
03.11.2016  14:02            54.735 batik-extension-1.8.jar
03.11.2016  14:02           144.227 batik-gvt-1.8.jar
03.11.2016  14:02            55.269 batik-parser-1.8.jar
03.11.2016  14:02            17.156 batik-script-1.8.jar
03.11.2016  14:02           217.196 batik-svg-dom-1.8.jar
03.11.2016  14:02           177.875 batik-svggen-1.8.jar
03.11.2016  14:02            92.514 batik-transcoder-1.8.jar
03.11.2016  14:02           107.737 batik-util-1.8.jar
03.11.2016  14:02            26.777 batik-xml-1.8.jar
28.10.2016  14:15            64.804 classmate-1.3.1.jar
03.11.2016  14:02            83.613 commons-io-1.3.1.jar
27.10.2016  13:21            38.015 commons-logging-1.0.4.jar
27.10.2016  11:48           313.898 dom4j-1.6.1.jar
03.11.2016  14:02         4.127.117 fop-2.1.jar
28.10.2016  14:15            75.288 hibernate-commons-annotations-5.0.1.Final.jar
28.10.2016  14:15         5.619.712 hibernate-core-5.0.11.Final.jar
28.10.2016  14:15           612.550 hibernate-entitymanager-5.0.11.Final.jar
28.10.2016  14:15           113.371 hibernate-jpa-2.1-api-1.0.0.Final.jar
28.10.2016  14:15           704.465 hibernate-validator-5.2.4.Final.jar
28.10.2016  14:15            55.650 jackson-annotations-2.8.3.jar
28.10.2016  14:15           281.078 jackson-core-2.8.3.jar
28.10.2016  14:15         1.233.703 jackson-databind-2.8.3.jar
28.10.2016  14:15           187.752 jandex-2.0.0.Final.jar
27.10.2016  14:49           750.581 javassist-3.20.0-GA.jar
28.10.2016  14:15            30.724 javax.transaction-api-1.2.jar
28.10.2016  14:15            66.802 jboss-logging-3.3.0.Final.jar
28.10.2016  14:15            16.431 jcl-over-slf4j-1.7.21.jar
28.10.2016  14:15             4.597 jul-to-slf4j-1.7.21.jar
28.10.2016  14:15            23.646 log4j-over-slf4j-1.7.21.jar
28.10.2016  14:15           304.075 logback-classic-1.1.7.jar
28.10.2016  14:15           470.782 logback-core-1.1.7.jar
28.10.2016  14:15            41.071 slf4j-api-1.7.21.jar
28.10.2016  14:15           273.599 snakeyaml-1.17.jar
28.10.2016  14:15           379.939 spring-aop-4.3.3.RELEASE.jar
28.10.2016  14:15            58.720 spring-aspects-4.3.3.RELEASE.jar
28.10.2016  14:15           760.602 spring-beans-4.3.3.RELEASE.jar
28.10.2016  14:15           657.087 spring-boot-1.4.1.RELEASE.jar
28.10.2016  14:15           967.727 spring-boot-autoconfigure-1.4.1.RELEASE.jar
28.10.2016  14:15             2.289 spring-boot-starter-1.4.1.RELEASE.jar
28.10.2016  14:15             2.249 spring-boot-starter-aop-1.4.1.RELEASE.jar
28.10.2016  14:15             2.598 spring-boot-starter-data-jpa-1.4.1.RELEASE.jar
28.10.2016  14:15             2.256 spring-boot-starter-jdbc-1.4.1.RELEASE.jar
28.10.2016  14:15             2.308 spring-boot-starter-logging-1.4.1.RELEASE.jar
28.10.2016  14:15             2.346 spring-boot-starter-web-1.4.1.RELEASE.jar
28.10.2016  14:15         1.134.792 spring-context-4.3.3.RELEASE.jar
28.10.2016  14:15         1.110.374 spring-core-4.3.3.RELEASE.jar
28.10.2016  14:15           693.036 spring-data-commons-1.12.3.RELEASE.jar
28.10.2016  14:15           280.582 spring-data-jpa-1.10.3.RELEASE.jar
28.10.2016  14:15           263.744 spring-expression-4.3.3.RELEASE.jar
28.10.2016  14:15           426.453 spring-jdbc-4.3.3.RELEASE.jar
28.10.2016  14:15           476.863 spring-orm-4.3.3.RELEASE.jar
28.10.2016  14:15           266.965 spring-tx-4.3.3.RELEASE.jar
28.10.2016  14:15           813.281 spring-web-4.3.3.RELEASE.jar
28.10.2016  14:15           913.557 spring-webmvc-4.3.3.RELEASE.jar
30.09.2016  13:51           585.020 sqljdbc4-4.0.jar
28.10.2016  14:15           137.738 tomcat-jdbc-8.5.5.jar
28.10.2016  14:15            41.152 tomcat-juli-8.5.5.jar
27.10.2016  14:19            63.777 validation-api-1.1.0.Final.jar
03.11.2016  14:02         2.730.442 xalan-2.7.0.jar
28.10.2016  14:15           220.536 xml-apis-1.4.01.jar
03.11.2016  14:02            85.686 xml-apis-ext-1.3.04.jar
03.11.2016  14:02           645.023 xmlgraphics-commons-2.1.jar

But I always got java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver exception:

java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerDriver
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:253) ~[tomcat-jdbc.jar:na]
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:181) ~[tomcat-jdbc.jar:na]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:699) ~[tomcat-jdbc.jar:na]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:633) ~[tomcat-jdbc.jar:na]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:484) ~[tomcat-jdbc.jar:na]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:142) ~[tomcat-jdbc.jar:na]
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115) ~[tomcat-jdbc.jar:na]
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102) ~[tomcat-jdbc.jar:na]
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:126) ~[tomcat-jdbc.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[DatasourceConnectionProviderImpl.class:5.0.11.Final]
    at org.hibernate.tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.prepare(SuppliedConnectionProviderConnectionHelper.java:33) ~[SuppliedConnectionProviderConnectionHelper.class:5.0.11.Final]
    at org.hibernate.tool.hbm2ddl.DatabaseExporter.<init>(DatabaseExporter.java:35) ~[DatabaseExporter.class:5.0.11.Final]
    at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:425) ~[SchemaExport.class:5.0.11.Final]
    at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:361) ~[SchemaExport.class:5.0.11.Final]
    at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:350) ~[SchemaExport.class:5.0.11.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:469) ~[SessionFactoryImpl.class:5.0.11.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:444) ~[SessionFactoryBuilderImpl.class:5.0.11.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:879) [EntityManagerFactoryBuilderImpl.class:5.0.11.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) [SpringHibernateJpaPersistenceProvider.class:4.3.3.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353) [LocalContainerEntityManagerFactoryBean.class:4.3.3.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:373) [AbstractEntityManagerFactoryBean.class:4.3.3.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:362) [AbstractEntityManagerFactoryBean.class:4.3.3.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1642) [AbstractAutowireCapableBeanFactory.class:4.3.3.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1579) [AbstractAutowireCapableBeanFactory.class:4.3.3.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545) [AbstractAutowireCapableBeanFactory.class:4.3.3.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482) [AbstractAutowireCapableBeanFactory.class:4.3.3.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) [AbstractBeanFactory$1.class:4.3.3.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) [DefaultSingletonBeanRegistry.class:4.3.3.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) [AbstractBeanFactory.class:4.3.3.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) [AbstractBeanFactory.class:4.3.3.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1076) [AbstractApplicationContext.class:4.3.3.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:851) [AbstractApplicationContext.class:4.3.3.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:541) [AbstractApplicationContext.class:4.3.3.RELEASE]
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122) [EmbeddedWebApplicationContext.class:1.4.1.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:761) [SpringApplication.class:1.4.1.RELEASE]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:371) [SpringApplication.class:1.4.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [SpringApplication.class:1.4.1.RELEASE]
    at org.springframework.boot.web.support.SpringBootServletInitializer.run(SpringBootServletInitializer.java:151) [SpringBootServletInitializer.class:1.4.1.RELEASE]
    at org.springframework.boot.web.support.SpringBootServletInitializer.createRootApplicationContext(SpringBootServletInitializer.java:131) [SpringBootServletInitializer.class:1.4.1.RELEASE]
    at org.springframework.boot.web.support.SpringBootServletInitializer.onStartup(SpringBootServletInitializer.java:86) [SpringBootServletInitializer.class:1.4.1.RELEASE]
    at org.springframework.web.SpringServletContainerInitializer.onStartup(SpringServletContainerInitializer.java:169) [SpringServletContainerInitializer.class:4.3.3.RELEASE]
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5147) [catalina.jar:8.0.3]
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150) [catalina.jar:8.0.3]
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:726) [catalina.jar:8.0.3]
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:702) [catalina.jar:8.0.3]
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:697) [catalina.jar:8.0.3]
    at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:579) [catalina.jar:8.0.3]
    at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1744) [catalina.jar:8.0.3]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_60]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_60]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_60]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_60]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_60]
Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381) ~[na:1.8.0_60]
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424) ~[na:1.8.0_60]
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357) ~[na:1.8.0_60]
    at java.lang.Class.forName0(Native Method) ~[na:1.8.0_60]
    at java.lang.Class.forName(Class.java:348) ~[na:1.8.0_60]
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:245) ~[tomcat-jdbc.jar:na]
    ... 52 common frames omitted

The Solution was to add the driver library to Tomcats lib folder.

The question is: Why do Tomcat need this library and is it possible to run the Application without adding the library to Tomcat?

score 0 · Answer 1 · answered Nov 03 '16 at 16:35

0

Just add this dependency to your pom.xml (in case of maven) or build.gradle.
Artifact link - https://mvnrepository.com/artifact/com.microsoft/sqljdbc4/3.0
Spring Boot plug-in will automatically package this dependency into your jar/war file.

answered Nov 03 '16 at 16:35

PawelN

848
7
17

Like I said. It IS in my war file and it IS in my Maven repository and it IS in the pom.xml. If it weren't, I couldn't access the driver through the source code. But I still get the ´ClassNotFoundException´. That's why I had to add it to Tomcats libs too. But I don't want that. – deve Nov 03 '16 at 18:32
so show your pom.xml and content of WEB-INF/lib directory from war file – PawelN Nov 03 '16 at 21:41
I have created war file with SQLServer config and for me it is deploying on my local tomcat instance. If you can pls share your project. You can check my configuration https://github.com/paweln1986/SQLServerSpringBoot – PawelN Nov 04 '16 at 09:20

score 0 · Accepted Answer · edited May 23 '17 at 12:06

I found the answers to my questions:

JDBC drivers register themselves in the JVM-wide singleton DriverManager which is shared by all web apps. If you have the same (as in class name) JDBC driver register twice from two different web apps, this might cause your problem. This is even more problematic if your web apps use different versions of the same JDBC driver.

Also, putting JDBC drivers into Tomcat's lib folder will help prevent memory leaks when you redeploy your web app without restarting Tomcat, e.g. if you just put a new WAR file into Tomcat's webapps folder:

The class DriverManager gets loaded by the bootstrap classloader and thereby "lives" globally in the JVM, while Tomcat loads all web apps in their own classloaders. So if a JDBC driver from a web app's WEB-INF/lib folder registers itself in DriverManager, it pins that web app's classloader in memory (and thereby all the classes of that web app), preventing its garbage collection.

If instead both DriverManager and JDBC drivers come from non-web app classloaders, you can freely redeploy your web apps without any web app classes pinning themselves in classes loaded from other classloaders.

So the depenency in the pom.xml is not necessary. In fact it should be removed, if the application is run by an application server (war file instead of jar).

Spring Boot with MS SQL Server Driver and Tomcat

2 Answers2