带有 SQL Server 2012 的 Java Hibernate 不起作用? [英] Java Hibernate with SQL Server 2012 not working?

问题描述

我有一个适用于 SQL Server 2008 R2 的 Java Hibernate 项目配置，现在使用新的 OS 8.1(来自 7)和 SQL Server 2012(express)，我无法连接到 SQL 服务器.

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.

相关配置是/应该是语法正确，因为它适用于 2008 R2:

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

我尝试了两种方言 org.hibernate.dialect.SQLServerDialect 在 2008 R2 中工作.

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 是使用混合模式身份验证安装的，并且 SQL Server Management Studio 连接没有问题(无论是否有实例名称).

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

我已经为 SQLEXPRESS 更新了 SQL Server 网络配置.

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

SQLEXPRESS 的协议:

TCP/IP 启用以及所有 TCP/IP 属性 - TCP 端口到 1433.

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

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

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

我最终添加了防火墙规则并遵循了这个优秀的配置 SQL Express 2012 以接受远程连接文章.

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

错误信息:

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

推荐答案

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

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)

不过，问题与 SQL Server 版本无关，而与使用的 DBCP (Tomcat) 版本(或项目部署到的 Tomcat 服务器版本)有关.

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

曾经我使用 jTDS 1.3.1 并且该项目在 Tomcat7 下运行良好(并连接到 SQLServer 2012).当我换到Tomcat 8时，出现了那个错误.

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 论坛中暗示，是:

• (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() 来测试连接的有效性.

• (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.

所以，解决方法是设置validationQuery参数，这将使DBCP2不调用.isValid()来测试有效性连接.方法如下:

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:

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

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" />

在春天

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

On Spring

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

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

关于简单的java代码

dataSource.setValidationQuery("select 1"); 

这篇关于带有 SQL Server 2012 的 Java Hibernate 不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！