Java Hibernate with SQL Server 2012 not working?

I have a Java Hibernate project configuration which worked with SQL Server 2008 R2, now with a new OS 8.1 (from 7) and SQL Server 2012 (express), I’m unable to connect to SQL server.

Relevant configuration which is/should be syntactically correct since it worked with 2008 R2:

  • Find out the number of connections to tomcat server
  • Stored procedure to call a different stored procedure based on parameter
  • C#, DataTables, Primary Keys, and Select performance
  • How to strip all non-alphabetic characters from string in SQL Server?
  • SQL Server: Attach incorrect version 661
  • How to persuade ascmd.exe to make tables as output, not a XML file?
  • datasource.properties

    jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
    jdbc.url=jdbc:jtds:sqlserver://localhost:1433/dbname;instance=SQLEXPRESS
    jdbc.username=auser
    jdbc.password=xyz
    

    I’ve tried two dialects org.hibernate.dialect.SQLServerDialect worked in 2008 R2.

    hibernate.hbm2ddl.auto=create-drop
    hibernate.dialect=org.hibernate.dialect.SQLServerDialect
    #hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
    hibernate.show_sql=true
    

    springConfiguration.xml

    <bean id="dataSource" class="org.apache.tomcat.dbcp.dbcp2.BasicDataSource"> 
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>
    

    SQL Server 2012 was installed with mixed mode authentication and SQL Server Management Studio has no problem connecting (with or without the instance name).

    I’ve updated the SQL Server Network Configuration for SQLEXPRESS.

    Protocols for SQLEXPRESS:

    TCP/IP Enabled
    As well as all of the TCP/IP Properties - TCP Port‘s to 1433.

    I’ve tried disabling Windows Firewall just to test if it’s in the way but it results in the same error.

    I ended up adding Firewall rules and following some of the steps in this excellent configure SQL Express 2012 to accept remote connections article.

    The error message:

    Caused by: java.lang.AbstractMethodError
    at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2833)
    

  • Hql query optimization - return indexed id or the whole object
  • what does null entities are not supported by org.hibernate.event.def.EventCache mean?
  • Spring-data-jpa with SQL Server - Geometry Deserialization Exception
  • JPA GenerationType.AUTO not considering column with auto increment
  • Problems with Date Java8 Hibernate5.2
  • Hibernate, Stored Procedures and Invalid Parameter Index Error
  • 2 Solutions collect form web for “Java Hibernate with SQL Server 2012 not working?”

    Your problem is jTDS does not support the way DBCP2 validates a connection by default (I’m assuming you use DBCP2 from <bean id="dataSource" class="org.apache.tomcat.dbcp.dbcp2.BasicDataSource">). See the solution below.

    Usually the error stacktrace is as shown:

    Caused by: java.lang.AbstractMethodError
        at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2833)
        at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.isValid(DelegatingConnection.java:913)
    

    The problem, though, is not related to the SQL Server version, but to the DBCP (Tomcat) version used (or the Tomcat server version the project is deployed to).

    Once I was using jTDS 1.3.1 and the project worked fine (and connected to SQLServer 2012 as well) under Tomcat7. When I changed to Tomcat 8, that error appeared.

    The reason, as hinted in jTDS forums, is:

    • (Tomcat7 uses DBCP 1 and Tomcat 8 uses DBCP 2)
    • Unlike DBCP 1.x, DBCP 2 will call java.sql.Connection.isValid(int) to validate the connection
    • jTDS doesn’t implement .isValid(), so jTDS driver won’t work with DBCP 2, unless…
    • …unless you set the validationQuery parameter, which will make DBCP not call .isValid() to test the validity of the connection.

    Workaround

    So, the workaround is to set the validationQuery parameter, which will make DBCP2 not call .isValid() to test the validity of the connection. Here’s how:

    On Tomcat

    Add validationQuery="select 1" to your Tomcat <Resource> tag for connection pool, which is usually in META-INF/context.xml of your app or conf/server.xml:

    <Resource ... validationQuery="select 1" />
    

    On Spring

    When using DBCP2 through Spring, the solution is something around:

    <bean id="..." ...> 
        ...
        <property name="validationQuery" value="select 1" />
    </bean>
    

    On Simple java Code

    dataSource.setValidationQuery("select 1"); 
    

    It appears that jTDS has some issues with SQL Server 2012 (update 2?) or something has changed in 2012/8.1 which previously worked in 2008 R2/7.

    Using nearly the same configuration as above with a couple minor changes, I downloaded and changed the datasource.properties to use Microsoft JDBC Driver 4.0 for SQL Server.

    jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
    jdbc.url=jdbc:sqlserver://localhost:1433;
    #jdbc.url=jdbc:sqlserver://localhost\dbname:1433;
    

    I just put the sqljdbc4.jar in tomcat\lib\ to verify that the MS JDBC driver 4.0 works with SQL Server 2012 with all updates and it works perfectly. The dialect org.hibernate.dialect.SQLServerDialect worked in 2012 too.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.