带有 SQL Server 2012 的 Java Hibernate 不起作用？

Java Hibernate with SQL Server 2012 not working?

提问by lko

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.

我有一个与 SQL Server 2008 R2 一起使用的 Java Hibernate 项目配置，现在使用新的 OS 8.1（来自 7）和 SQL Server 2012（express），我无法连接到 SQL 服务器。

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

相关配置是/应该是语法正确的，因为它与 2008 R2 一起工作：

datasource.properties

数据源.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.SQLServerDialectworked in 2008 R2.

我尝试过两种org.hibernate.dialect.SQLServerDialect在 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 2012was installed with mixed mode authentication and SQL Server Management Studio has no problem connecting (with or without the instance name).

SQL Server 2012是使用混合模式身份验证安装的，并且 SQL Server Management Studio 连接没有问题（有或没有实例名称）。

I've updated the SQL Server Network Configurationfor SQLEXPRESS.

我已经更新了SQL Server Network Configurationfor SQLEXPRESS。

Protocols for SQLEXPRESS:

SQLEXPRESS 的协议：

TCP/IP EnabledAs well as all of the TCP/IP Properties - TCP Port's to 1433.

TCP/IP Enabled以及所有TCP/IP Properties - TCP Port's 到 1433。

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

我试过禁用 Windows 防火墙只是为了测试它是否妨碍，但它导致相同的错误。

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

我最终添加了防火墙规则并遵循了这篇优秀的配置 SQL Express 2012 以接受远程连接文章中的一些步骤。

The error message:

错误信息：

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

回答by acdcjunior

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.

您的问题是 jTDS 不支持 DBCP2 默认情况下验证连接的方式（我假设您使用 DBCP2 from <bean id="dataSource" class="org.apache.tomcat.dbcp.dbcp2.BasicDataSource">）。请参阅下面的解决方案。

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).

但是，问题与 SQL Server 版本无关，而与使用的 DBCP (Tomcat) 版本（或项目部署到的 Tomcat 服务器版本）有关。

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.

一旦我使用 jTDS 1.3.1 并且该项目在 Tomcat7 下运行良好（并且也连接到 SQLServer 2012）。当我更改为Tomcat 8时，出现该错误。

The reason, as hinted in jTDS forums, is:

正如jTDS 论坛中所暗示的，原因是：

• (Tomcat7 uses DBCP 1 and Tomcat 8 uses DBCP 2)
• Unlike DBCP 1.x, DBCP 2will 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 validationQueryparameter, which will make DBCP not call .isValid()to test the validity of the connection.

• （Tomcat7 使用 DBCP 1，Tomcat 8 使用 DBCP 2）
• 与DBCP 1.x不同，DBCP 2将调用java.sql.Connection.isValid(int)以验证连接
• jTDS 没有实现.isValid()，所以 jTDS 驱动程序不能与 DBCP 2 一起工作，除非......
• ...除非你设置了validationQuery参数，否则DBCP不会调用.isValid()来测试连接的有效性。

Workaround

解决方法

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

因此，解决方法是设置validationQuery参数，这将使 DBCP2 不调用.isValid()以测试连接的有效性。就是这样：

On Tomcat

在 Tomcat 上

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

添加validationQuery="select 1"到<Resource>连接池的Tomcat标记，通常在META-INF/context.xml您的应用程序中或conf/server.xml：

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

On Spring

在春天

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

通过 Spring 使用 DBCP2 时，解决方案是：

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

On Simple java Code

简单的java代码

dataSource.setValidationQuery("select 1"); 

回答by lko

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

jTDS似乎在 SQL Server 2012（更新 2？）上存在一些问题，或者2012/8.1以前在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.

使用与上面几乎相同的配置并进行了一些小的更改，我下载并更改了 datasource.properties 以使用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.jarin 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.SQLServerDialectworked in 2012 too.

我只是把sqljdbc4.jar在tomcat\lib\验证MS JDBC驱动程序4.0用于SQL Server 2012的所有更新和它完美的作品。方言org.hibernate.dialect.SQLServerDialect在 2012 年也有效。