Win a copy of TDD for a Shopping Website LiveProject this week in the Testing forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

Hibernate (HQL Delete Query)

 
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 62
Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I had the same problem and that has solved it.
Thanks, Vilpesh.
 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
You firghten me terribly. I would like to go home now. Here, take this tiny ad:
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic