Java Hibernate与SQL Server 2012无法正常工作？ [英] Java Hibernate with SQL Server 2012 not working?

问题描述

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

相关配置自语法正确，因为它与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工作。

  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 =dataSourceclass = org.apache.tomcat.dbcp.dbcp2.BasicDataSource> 
< property name =driverClassNamevalue =$ {jdbc.driverClassName}/> 
< property name =urlvalue =$ {jdbc.url}/> 
< property name =usernamevalue =$ {jdbc.username}/> 
< property name =passwordvalue =$ {jdbc.password}/> 
< / bean> 
  

SQL Server 2012 安装了混合模式身份验证和SQL Server Management Studio没有问题连接（有或没有实例名称）。

我更新了 SQL Server网络配置对于 SQLEXPRESS 。

SQLEXPRESS的协议：

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

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

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

错误消息：

 引起：java.la ng.AbstractMethodError 
 at net.sourceforge.jtds.jdbc.JtdsConnection.isValid（JtdsConnection.java:2833）
  

< bean id = dataSourceclass =org.apache.tomcat.dbcp.dbcp2.BasicDataSource> ）。

通常错误堆栈跟踪如下所示：

 <$ c $引发：java.lang.AbstractMethodError $ b $在net.sourceforge.jtds.jdbc.JtdsConnection.isValid（JtdsConnection.java:2833）
在org.apache.tomcat.dbcp.dbcp2.DelegatingConnection。 isValid（DelegatingConnection.java:913）
  

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

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

原因如暗示在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（）来测试连接的有效性。






解决方法





因此，解决方法是设置 validationQuery parameter ，这将使DBCP2不会调用 .isValid（）来测试连接的有效性。在Tomcat上



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

 < Resource ... validationQuery =select 1/> 
  





在Spring上





当使用DBCP2 Spring，解决方案就是这样：



 < bean id =......> 
 ... 
< property name =validationQueryvalue =select 1/> 
< / bean> 
  





简单的java代码





  dataSource.setValidationQuery（select 1）; 
  




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:

• (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"); 

这篇关于Java Hibernate与SQL Server 2012无法正常工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！