wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes Hibernate (HQL Delete Query) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate (HQL Delete Query)" Watch "Hibernate (HQL Delete Query)" New topic
Author

Hibernate (HQL Delete Query)

Chandra Sekhar
Ranch Hand

Joined: Sep 26, 2003
Posts: 90
Hi,

Please help me in writing HQL delete query.

My entity Class is like this


public class FunctionResourcePermissionData implements java.io.Serializable {
// Fields
private FunctionResourcePermissionId id;
private CompositeResourceData resourceObject;
private FunctionData function;
private String permissionCode;
private Date recordCreationTimestamp;
private String recordCreationUserId;
private Date lastUpdateTimestamp;
private String lastUpdateUserId;


The Id class is


public class FunctionResourcePermissionId implements java.io.Serializable {
// Fields
private Integer elementaryResourceId;
private Integer compositeResourceId;
private Integer functionId;


Mapping file is as below.

[/b]
<hibernate-mapping>
<class name="com.xxx.yyy.zzz.data.application.entity.FunctionResourcePermissionData" table="FUNC_RSRC_PRMSN">
<composite-id name="id" class="com.xxx.yyy.zzz.data.application.entity.FunctionResourcePermissionId">
<key-property name="elementaryResourceId" type="integer">
<column name="RSRC_ID" />
</key-property>
<key-property name="compositeResourceId" type="integer">
<column name="CMPST_RSRC_ID" />
</key-property>
<key-property name="functionId" type="integer">
<column name="FUNC_ID" />
</key-property>
</composite-id>
<many-to-one name="resourceObject" class="com.xxx.yyy.zzz.data.application.entity.CompositeResourceData" update="false" insert="false" fetch="select">
<column name="CMPST_RSRC_ID" not-null="true" />
<column name="RSRC_ID" not-null="true" />
</many-to-one>
<many-to-one name="function" class="com.xxx.yyy.zzz.data.application.entity.FunctionData" update="false" insert="false" fetch="select">
<column name="FUNC_ID" not-null="true" />
</many-to-one>
<property name="permissionCode" type="string">
<column name="PRMSN_CD" length="10" not-null="true" />
</property>
<property name="recordCreationTimestamp" type="timestamp">
<column name="RCRD_CRTN_TMS" length="26" not-null="true" />
</property>
<property name="recordCreationUserId" type="string">
<column name="RCRD_CRTN_USR_ID" length="8" not-null="true" />
</property>
<property name="lastUpdateTimestamp" type="timestamp">
<column name="LST_UPDT_TMS" length="26" not-null="true" />
</property>
<property name="lastUpdateUserId" type="string">
<column name="LST_UPDT_USR_ID" length="8" not-null="true" />
</property>
</class>
</hibernate-mapping>
[/b]



The query i wrote to delete is as


getSessionFactory().getCurrentSession()
.createQuery("delete from FunctionResourcePermissionData " +
" where id.elementaryResourceId=:elemRes" +
" and id.compositeResourceId=:compRes " +
" and id.functionId=:fnId")
.setInteger("elemRes",fId.getElementaryResourceId().intValue())
.setInteger("compRes",fId.getElementaryResourceId().intValue())
.setInteger("fnId",fId.getElementaryResourceId().intValue())
.executeUpdate();



I'm getting the following exception
org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:99)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:297)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:871)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.xxx.yyy.zzz.data.application.dao.hibernate.AuditLogDAOImplTest.testDelete(AuditLogDAOImplTest.java:92)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:85)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:58)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:60)
at java.lang.reflect.Method.invoke(Method.java:391)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:436)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:311)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL0206N "FUNCTIONRE0_.RSRC_ID" is not valid in the context where it is used. SQLSTATE=42703

at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeUpdate(Unknown Source)
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:76)
... 18 more




The query generated by hibernate is

Hibernate: delete from EPDS.FUNC_RSRC_PRMSN where functionre0_.RSRC_ID=? and functionre0_.CMPST_RSRC_ID=? and functionre0_.FUNC_ID=?

In the query the alias name functionre0_ is not there after the table name ,but before field names the alias name is added.

Can some one help me to solve this issue.

Thanks in Advance


"Luck is when opportunity meets preparation"
Vilpesh Mistry
Ranch Hand

Joined: May 27, 2003
Posts: 62
Hi too late i guess to answer your Query, but lets leave the Answer for the question

Would you modify the Query as below & test :
Adding the Alias (FRPD) to the Table name FunctionResourcePermissionData and then using this Alias with the 'id' property.

getSessionFactory().getCurrentSession()
.createQuery("delete from FunctionResourcePermissionData FRPD" +
" where FRPD.id.elementaryResourceId=:elemRes" +
" and FRPD.id.compositeResourceId=:compRes " +
" and FRPD.id.functionId=:fnId")
.setInteger("elemRes",fId.getElementaryResourceId().intValue())
.setInteger("compRes",fId.getElementaryResourceId().intValue())
.setInteger("fnId",fId.getElementaryResourceId().intValue())
.executeUpdate();
FRPD


Thanks.
Marisol Opreni
Greenhorn

Joined: Nov 30, 2006
Posts: 1
I had the same problem and that has solved it.
Thanks, Vilpesh.
nimesh hindocha
Greenhorn

Joined: Aug 11, 2010
Posts: 20
Hi Guys,
I want to write delete query for below code please any one could help,i am new to hibernate.




Thanks in advance.


SCJP 6.0
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate (HQL Delete Query)