Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate (HQL Delete Query)

 
Chandra Sekhar
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Vilpesh Mistry
Ranch Hand
Posts: 62
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Marisol Opreni
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I had the same problem and that has solved it.
Thanks, Vilpesh.
 
nimesh hindocha
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys,
I want to write delete query for below code please any one could help,i am new to hibernate.




Thanks in advance.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic