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:
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)
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:
java.sql.Connection.isValid(int)
to validate the connection.isValid()
, so jTDS driver won't work with DBCP 2, unless...validationQuery
parameter, which will make DBCP not call .isValid()
to test the validity of the connection.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:
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" />
When using DBCP2 through Spring, the solution is something around:
<bean id="..." ...>
...
<property name="validationQuery" value="select 1" />
</bean>
dataSource.setValidationQuery("select 1");