Setting up jTDS with Wildfly

Should you ever want to use the jTDS open source JDBC driver for Microsoft SQL Server with the Wildfly J2EE Application Server (and you want to use it as a module), then you will have to jump through a few hoops to get it working; and the Wildfly documentation can be lacking in places (or just plain incorrect).

So first things first, download the jTDS driver from the downloads page. At the time of writing the current version was 1.3.1.

I assume you already have Wildfly downloaded and extracted into a folder of your choice. In my case I'm using Wildfly 8.1.0.Final, but these should work identically in 8.2.0.

So navigate to your Wildfly folder and in the modules folder you need to create a new set of directories, one inside of the other. They are as follows: net > sourceforge > jtds > main.

When finished the folder structure should look like this:

Wildfly Folder Structure

Next copy the jTDS jar into the main folder, and then create a new XML file called module.xml. Open up the file in your favourite text editor and enter the following:

<module xmlns="urn:jboss:module:1.0" name="net.sourceforge.jtds">  
    <resources>
        <resource-root path="jtds-1.3.1.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
    </dependencies>
</module>  

Save it and close the file down, then navigate to Wildfly's configuration file for the type of system you are running; in my case this is standalone > configuration > standalone.xml. Then locate the datasources subsection that looks like the following:

<subsystem xmlns="urn:jboss:domain:datasources:2.0">  
    <datasources>
        <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
            <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
            <driver>h2</driver>
            <security>
                <user-name>sa</user-name>
                <password>sa</password>
            </security>
        </datasource>
        <drivers>
            <driver name="h2" module="com.h2database.h2">
                <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
            </driver>
        </drivers>
    </datasources>
</subsystem>  

You then want to replace that section with the following:

<subsystem xmlns="urn:jboss:domain:datasources:2.0">  
    <datasources>
        <datasource jta="true" jndi-name="java:jboss/datasources/<DATASOURCE NAME>" pool-name="<DATASOURCE NAME>" enabled="true" use-java-context="true" use-ccm="true">
            <connection-url>jdbc:jtds:sqlserver://<IP ADDRESS>:<PORT (probably 1433)>/<DATABASE NAME></connection-url>
            <driver>jTDS</driver>
            <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
            <pool>
                <min-pool-size>100</min-pool-size>
                <max-pool-size>300</max-pool-size>
                <prefill>true</prefill>
                <use-strict-min>false</use-strict-min>
                <flush-strategy>FailingConnectionOnly</flush-strategy>
            </pool>
            <security>
                <user-name>USERNAME</user-name>
                <password>PASSWORD</password>
            </security>
            <statement>
                <prepared-statement-cache-size>100</prepared-statement-cache-size>
                <share-prepared-statements>true</share-prepared-statements>
            </statement>
        </datasource>
        <drivers>
            <driver name="jTDS" module="net.sourceforge.jtds">
                <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
                <xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>
            </driver>
        </drivers>
    </datasources>
</subsystem>  

Now if you load up Wildfly you shouldn't see any errors and will have the Datasource available under whatever name you chose.

And that's it. Happy coding.