Configuration of JTDS for use with HikariCP + Spring + MS SQL Server

Question

I kept googling for configuration of JTDS (1.3.1) for use with HikariCP (2.4.3), Spring (4.1.2), and MS SQL Server (2008), but unable to find a complete and working example.

Here is what I have:

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean> 

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
    <property name="poolName" value="springHikariCP" />
    <property name="connectionTestQuery" value="SELECT 1" />
    <property name="dataSourceClassName" value="${jdbc.dataSourceClassName}" />
    <property name="maximumPoolSize" value="${jdbc.maximumPoolSize}" />
    <property name="minimumIdle" value="${jdbc.minimumIdle}" />
    <property name="idleTimeout" value="${jdbc.idleTimeout}" />
    ....
    <property name="dataSourceProperties">
        <props>
            ....
        </props>
    </property>
</bean>

Can anyone out there share the JTDS configs used in a production environment?

Regards.

UPDATE

I found this SO post:

HikariCP hanging on getConnection

It seems that JTDS has a problem working with HikariCP. Actually, I have this problem too. Here is my complete config for JTDS:

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean> 

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
    <property name="poolName" value="springHikariCP" />
    <property name="connectionTestQuery" value="${jdbc.connectionTestQuery}" />
    <property name="dataSourceClassName" value="${jdbc.dataSourceClassName}" />
    <property name="maximumPoolSize" value="${jdbc.maximumPoolSize}" />
    <property name="minimumIdle" value="${jdbc.minimumIdle}" />
    <property name="idleTimeout" value="${jdbc.idleTimeout}" />
    <property name="connectionTimeout" value="${jdbc.connectionTimeout}" />
    <property name="jdbcUrl" value="${jdbc.url}" />
    <property name="dataSourceProperties">
        <props>
            <prop key="user">${jdbc.username}</prop>
            <prop key="password">${jdbc.password}</prop>
            <prop key="cacheMetaData">${jtds.cacheMetaData}</prop>                              
        </props>
    </property>
</bean>

This is exactly the reason I posted my question, and I hope to see a complete example. However, at HikariCP's page, JTDS is listed as supported. I am confused.

Answer 1

The following works:

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close" depends-on="flyway">
    <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver"/>
    <property name="connectionTestQuery" value="SELECT GETDATE()"/>
    <property name="maximumPoolSize" value="32"/>
    <property name="jdbcUrl" value="${dbUrl}"/>
    <property name="username" value="${dbUsername}"/>
    <property name="password" value="${dbPassword}"/>
</bean>

Notice the connectionTestQuery property which is required so Hikari will not assume the driver is JDBC4 compliant (jTDS is 3.0 compliant).