使用hibernate hql update 关联表的问题

[michealp]

我想用hibernate 的hql 去更新表
要更新的表是子表，要求根据父表记录的某属性来决定是否更新子表的某条记录

我是这么写的
String hql = "update Ctable c set c.delflag=1 " +
"where c.delflag=0 and c.ftable.id=?";
/*
ftable是父表类在子表的映射
*/
结果是运行时有异常。

请高手们帮忙看看满足这样的需求的hql该如何写才对。
非常感谢

[yibunengjing]

String hql = "update Ctable c set c.delflag=1 " +
"where c.delflag=0 and c.ftable.id=?";
这句号sql错误，应该是 and c.ftable.id= :aaa，这个aaa就相当于?，需要赋值给他

[learnner]

Hibernate: update HEADMMS_BS.dbo.HEADMMS_OBJ_SD_CARD1, set delflag=1 where delflag=0 and id=?
多了个"，"号，看看怎么产生的

[michealp]

不对,效果一样。hibernate会生成这样的sql：
update Ctable, set delflag=1 where delflag=0 and id=?
结果生成的sql语法错误

[zhdream2003]

String hql = "update Ctable c set c.delflag=1 " +
"where c.delflag=0 and c.ftable.id=:id";

Query query=session.createQuery(hql);
query.setParameter("id",实际值);

[michealp]

也不对。不是参数传递的问题。
难道不能在update 语句中关联表?

[yibunengjing]

当然可以关联的，这个不分什么操作的，楼主还请把异常信息贴出来看看

[michealp]

源码如下:

String hql = "update ObjSdCard1 card1 set card1.delflag=1" +
	"where card1.delflag=0 and card1.cid.objSd.id=:sd";
session.createQuery(hql).setString("sd", sd).executeUpdate();

cid是composite-id, objSd是card1关联的父表
使用的是sqlserver2000

运行后log如下:
Hibernate: update HEADMMS_BS.dbo.HEADMMS_OBJ_SD_CARD1, set delflag=1 where delflag=0 and id=?
2008-02-27 12:50:29,109 WARN (org.hibernate.util.JDBCExceptionReporter:77) - SQL Error: 170, SQLState: 37000
2008-02-27 12:50:29,109 ERROR (org.hibernate.util.JDBCExceptionReporter:78) - 第 1 行: ',' 附近有语法错误。
2008-02-27 12:50:29,125 WARN (org.hibernate.util.JDBCExceptionReporter:54) - SQL Warning: 170, SQLState: 37000
2008-02-27 12:50:29,125 WARN (org.hibernate.util.JDBCExceptionReporter:55) - Preparing the statement failed: 第 1 行: ',' 附近有语法错误。
2008-02-27 12:50:29,125 ERROR (com.head.mms.maintainstatic.tunnel.service.ObjSdCard1Service:223) - 还原垃圾箱失败
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute update query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute update query
Caused by: org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at com.head.mms.maintainstatic.tunnel.service.ObjSdCard1Service$2.doInHibernate(ObjSdCard1Service.java:203)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:333)
at com.head.mms.maintainstatic.tunnel.service.ObjSdCard1Service.revertRecycleInSd(ObjSdCard1Service.java:198)
at com.head.mms.maintainstatic.tunnel.action.ObjSdCard1Action.revertRecycleObjSdCard1s(ObjSdCard1Action.java:280)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.opensymphony.xwork.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:364)
at com.opensymphony.xwork.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:216)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:190)
at com.opensymphony.xwork.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:168)
at com.opensymphony.xwork.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:115)
at com.opensymphony.xwork.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:31)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:31)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:31)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.webwork.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:171)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:31)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.webwork.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:147)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:31)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:151)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:31)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:31)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:31)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:186)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:188)
at com.opensymphony.xwork.DefaultActionProxy.execute(DefaultActionProxy.java:113)
at com.opensymphony.webwork.dispatcher.DispatcherUtils.serviceAction(DispatcherUtils.java:225)
at com.opensymphony.webwork.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:202)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter
Caused by: java.sql.SQLException: 第 1 行: ',' 附近有语法错误。
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505)
at sun.reflect.GeneratedMethodAccessor95.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at $java.sql.PreparedStatement$$EnhancerByProxool$$3f35380f.executeUpdate(<generated>)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
... 66 more

[michealp]

对是多个“,” ，而且产生的sql没有关联表
没人知道吗?
否则只能把数据查出来。然后迭代更新。这样效率低，而且耗内存

[hua0424]

上面的同学不懂不要乱说，在说“肯定可以的呀”之前请自己试试先。
楼主的问题是hibernate 的一个bug引起的，在3.2.6ga中已经修复，下载替换即可

[parddu]

楼主请问你的问题解决了吗?我也遇到相同的问题了,应该怎么解决啊??