[SEAM] Connection handle has been closed and is unusable
 |
smartcdrw
2008-08-13
我的環境為 eclipse 3.4 seam 2.0.2.sp1 jboss 4.2.2.ga 使用的資料庫為 informix 我的設定 INFORMIX-DS
<local-tx-datasource>
<jndi-name>InformixDS</jndi-name>
<connection-url>jdbc:informix-sqli://172.100.100.100:8007/TEST:INFORMIXSERVER=on_shm_6H1;DELIMIDENT=y;</connection-url>
<driver-class>com.informix.jdbc.IfxDriver</driver-class>
<user-name>＊＊＊＊＊</user-name>
<password>＊＊＊＊＊</password>
<!-- sql to call when connection is created -->
<new-connection-sql>select 1 from zb_file</new-connection-sql>
<!-- sql to call on an existing pooled connection when it is obtained from pool -->
<check-valid-connection-sql>select 1 from zb_file</check-valid-connection-sql>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<blocking-timeout-millis>10000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.InformixExceptionSorter</exception-sorter-class-name>
<track-statements />
<metadata>
<type-mapping>InformixDB</type-mapping>
</metadata>
<connection-property name="autoCommit">false</connection-property>
</local-tx-datasource>
persistence.xml
<persistence-unit name="test" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:/InformixDS</jta-data-source>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.InformixDialect" />
<property name="hibernate.connection.release_mode" value="after_statement" />
<property name="hibernate.connection.autocommit" value="false" />
<!--property name="hibernate.hbm2ddl.auto" value="update" /-->
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />
<property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.JBossTransactionManagerLookup" />
</properties>
</persistence-unit>
使用 jboss tools 產生了一連串的 entity 與網頁 但是在對entity網頁做查詢功能時一直發生錯誤 網上查找了很久都無法解決 錯誤如下
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
at org.jboss.seam.framework.EntityQuery.initResultList(EntityQuery.java:74)
at org.jboss.seam.framework.EntityQuery.getResultList(EntityQuery.java:65)
at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.seam.util.Reflections.invoke(Reflections.java:21)
at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:31)
at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
at org.jboss.seam.transaction.TransactionInterceptor$1.work(TransactionInterceptor.java:38)
at org.jboss.seam.util.Work.workInTransaction(Work.java:41)
at org.jboss.seam.transaction.TransactionInterceptor.aroundInvoke(TransactionInterceptor.java:32)
at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:42)
at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:166)
at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:102)
at com.phihong.b2b.session.ZnFileList_$$_javassist_1.getResultList(ZnFileList_$$_javassist_1.java)
at sun.reflect.GeneratedMethodAccessor123.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at javax.el.BeanELResolver.getValue(BeanELResolver.java:62)
at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53)
at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:64)
at org.jboss.el.parser.AstPropertySuffix.getValue(AstPropertySuffix.java:53)
at org.jboss.el.parser.AstValue.getValue(AstValue.java:67)
at org.jboss.el.parser.AstEmpty.getValue(AstEmpty.java:29)
at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
... 53 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDB
17:12:12,703 ERROR [STDERR] CExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
... 84 more
Caused by: java.sql.SQLException: Connection handle has been closed and is unusable
at org.jboss.resource.adapter.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:537)
at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:524)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:184)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 92 more
17:12:13,125 INFO [STDOUT] Hibernate:
select
first 26 znfile0_.zn01 as zn1_0_,
znfile0_.zn02 as zn2_0_,
znfile0_.zn03 as zn3_0_,
znfile0_.zn04 as zn4_0_,
znfile0_.zn05 as zn5_0_,
znfile0_.zn06 as zn6_0_,
znfile0_.zn07 as zn7_0_
from
zn_file znfile0_
where
lower(znfile0_.zn01) like (lower(?)||'%')
17:12:13,140 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: null
17:12:13,140 ERROR [JDBCExceptionReporter] Connection handle has been closed and is unusable
17:12:13,171 INFO [STDOUT] Hibernate:
select
first 26 znfile0_.zn01 as zn1_0_,
znfile0_.zn02 as zn2_0_,
znfile0_.zn03 as zn3_0_,
znfile0_.zn04 as zn4_0_,
znfile0_.zn05 as zn5_0_,
znfile0_.zn06 as zn6_0_,
znfile0_.zn07 as zn7_0_
from
zn_file znfile0_
where
lower(znfile0_.zn01) like (lower(?)||'%')
17:12:13,187 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: null
17:12:13,187 ERROR [JDBCExceptionReporter] Connection handle has been closed and is unusable
17:12:13,218 INFO [STDOUT] Hibernate:
select
first 26 znfile0_.zn01 as zn1_0_,
znfile0_.zn02 as zn2_0_,
znfile0_.zn03 as zn3_0_,
znfile0_.zn04 as zn4_0_,
znfile0_.zn05 as zn5_0_,
znfile0_.zn06 as zn6_0_,
znfile0_.zn07 as zn7_0_
from
zn_file znfile0_
where
lower(znfile0_.zn01) like (lower(?)||'%')
17:12:13,218 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: null
17:12:13,218 ERROR [JDBCExceptionReporter] Connection handle has been closed and is unusable
17:12:13,265 INFO [STDOUT] Hibernate:
select
first 26 znfile0_.zn01 as zn1_0_,
znfile0_.zn02 as zn2_0_,
znfile0_.zn03 as zn3_0_,
znfile0_.zn04 as zn4_0_,
znfile0_.zn05 as zn5_0_,
znfile0_.zn06 as zn6_0_,
znfile0_.zn07 as zn7_0_
from
zn_file znfile0_
where
lower(znfile0_.zn01) like (lower(?)||'%')
17:12:13,265 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: null
17:12:13,265 ERROR [JDBCExceptionReporter] Connection handle has been closed and is unusable
17:12:13,859 INFO [STDOUT] Hibernate:
select
count(*) as col_0_0_
from
zn_file znfile0_
where
lower(znfile0_.zn01) like (lower(?)||'%')
17:12:13,875 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: null
17:12:13,875 ERROR [JDBCExceptionReporter] Connection handle has been closed and is unusable
來這裡尋找希望 |
 |
Anatorian
2008-08-15
我在用seam+mysql的时候遇到过类似的问题。原因我们用的mysql服务器，把一次连接最长时间设置成60秒，比mysql服务器本身的默认配置 8小时 短了不知道多少倍！ 我们用的是seam生成的默认的datasource配置文件结果把连接的超时时间设置得比60秒长得多，经果就经常在做查询的时候出现错误，就算加上autoreconnect=true,validateconnection=true等参数都没有作用。最后把datasource的超时时间设置得更短，才解决了问题。 我想这不是seam的问题，是数据库连接池配置的问题。
|
|
smartcdrw
2008-08-15
感謝回覆: 不過又試了好久，還是沒辦法。 已經放棄 seam 了，還是回去用 spring 就好。 |
 |
donghp1979
2009-01-04
这是Informix的问题，是因为生成的SQL预编译语句在有错误导致的。
package com.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestJDBC {
final static String sql = "select first 3 from operation_history where lower(admin_id) like (lower(?) || '%')";
/**
* @param args
*/
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
Class.forName("com.informix.jdbc.IfxDriver");
Connection conn = DriverManager.getConnection("jdbc:informix-sqli://x.x.x.x:9001/crmauth:INFORMIXSERVER=server;DELIMIDENT=n;", "informix", "password");
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "admin");
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
String f = rs.getString(1);
System.out.println(f);
}
rs.close();
pstmt.close();
conn.close();
}
}
错误如下： Exception in thread "main" java.sql.SQLException: A syntax error has occurred. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:348) at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3025) at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3335) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2288) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2208) at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1081) at com.informix.jdbc.IfxResultSet.executePrepare(IfxResultSet.java:196) at com.informix.jdbc.IfxPreparedStatement.setupExecutePrepare(IfxPreparedStatement.java:185) at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:167) at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:1526) at com.test.TestJDBC.main(TestJDBC.java:18) |
