File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate (HQL Delete Query)" Watch "Hibernate (HQL Delete Query)" New topic

Hibernate (HQL Delete Query)

Chandra Sekhar
Ranch Hand

Joined: Sep 26, 2003
Posts: 90

Please help me in writing HQL delete query.

My entity Class is like this

public class FunctionResourcePermissionData implements {
// 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 {
// Fields
private Integer elementaryResourceId;
private Integer compositeResourceId;
private Integer functionId;

Mapping file is as below.

<class name="" table="FUNC_RSRC_PRMSN">
<composite-id name="id" class="">
<key-property name="elementaryResourceId" type="integer">
<column name="RSRC_ID" />
<key-property name="compositeResourceId" type="integer">
<column name="CMPST_RSRC_ID" />
<key-property name="functionId" type="integer">
<column name="FUNC_ID" />
<many-to-one name="resourceObject" class="" update="false" insert="false" fetch="select">
<column name="CMPST_RSRC_ID" not-null="true" />
<column name="RSRC_ID" not-null="true" />
<many-to-one name="function" class="" update="false" insert="false" fetch="select">
<column name="FUNC_ID" not-null="true" />
<property name="permissionCode" type="string">
<column name="PRMSN_CD" length="10" not-null="true" />
<property name="recordCreationTimestamp" type="timestamp">
<column name="RCRD_CRTN_TMS" length="26" not-null="true" />
<property name="recordCreationUserId" type="string">
<column name="RCRD_CRTN_USR_ID" length="8" not-null="true" />
<property name="lastUpdateTimestamp" type="timestamp">
<column name="LST_UPDT_TMS" length="26" not-null="true" />
<property name="lastUpdateUserId" type="string">
<column name="LST_UPDT_USR_ID" length="8" not-null="true" />

The query i wrote to delete is as

.createQuery("delete from FunctionResourcePermissionData " +
" where id.elementaryResourceId=:elemRes" +
" and id.compositeResourceId=:compRes " +
" and id.functionId=:fnId")

I'm getting the following exception
org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.SQLStateConverter.convert(
at org.hibernate.exception.JDBCExceptionHelper.convert(
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(
at org.hibernate.impl.SessionImpl.executeUpdate(
at org.hibernate.impl.QueryImpl.executeUpdate(
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(
at sun.reflect.NativeMethodAccessorImpl.invoke(
at sun.reflect.DelegatingMethodAccessorImpl.invoke(
at java.lang.reflect.Method.invoke(
at junit.framework.TestCase.runTest(
at junit.framework.TestCase.runBare(
at junit.framework.TestResult$1.protect(
at junit.framework.TestResult.runProtected(
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(
Caused by: [IBM][CLI Driver][DB2/LINUX] SQL0206N "FUNCTIONRE0_.RSRC_ID" is not valid in the context where it is used. SQLSTATE=42703

at Source)
at Source)
at Source)
at Source)
at Source)
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(
... 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.

.createQuery("delete from FunctionResourcePermissionData FRPD" +
" where" +
" and " +
" and")

Marisol Opreni

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

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:
subject: Hibernate (HQL Delete Query)
It's not a secret anymore!