-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 10 posts ] 
Author Message
 Post subject: HQL Delete Query
PostPosted: Thu Aug 24, 2006 1:11 am 
Newbie

Joined: Thu Jun 22, 2006 12:22 am
Posts: 9
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.app.entity.FunctionResourcePermissionData" table="FUNC_RSRC_PRMSN">
<composite-id name="id" class="com.app.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.app.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.app.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 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 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


Last edited by chandrasekhar_s on Wed Jul 04, 2007 6:32 am, edited 3 times in total.

Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 24, 2006 2:08 am 
Newbie

Joined: Tue Aug 22, 2006 6:12 am
Posts: 5
createQuery("delete from FunctionResourcePermissionData as frp" +
" where frp.id.elementaryResourceId=:elemRes" +
" and frp.id.compositeResourceId=:compRes " +
" and frpid.functionId=:fnId")

Should work.

Regards,

Udo


Top
 Profile  
 
 Post subject: RE: HQL Delete Query
PostPosted: Thu Aug 24, 2006 2:13 am 
Beginner
Beginner

Joined: Mon Nov 22, 2004 11:21 am
Posts: 42
Hi,

I cannot reproduce this problem with Hibernate 3.1.3. But you can try a workaround: specify a table alias yourself. The query would look like this:

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


If this doesn't work, you can use the Hibernate delete() method (by creating a FunctionResourcePermissionId as parameter), at least for this special query.

Regards,
Georg

_________________
Real programmers confuse Halloween and Christmas because OCT 31 = DEC 25


Top
 Profile  
 
 Post subject: HQL Delete Query
PostPosted: Thu Aug 24, 2006 2:15 am 
Newbie

Joined: Thu Jun 22, 2006 12:22 am
Posts: 9
Hi,

Thanks a lot for the reply.

I tried this

[code]createQuery("delete from FunctionResourcePermissionData as frp" +
" where frp.id.elementaryResourceId=:elemRes" +
" and frp.id.compositeResourceId=:compRes " +
" and frpid.functionId=:fnId") [/code]

But now i'm getting an exception

[code]org.hibernate.hql.ast.QuerySyntaxError: [b]unexpected token: as near line 1, column 91[/b] [delete from com.app.entity.FunctionResourcePermissionData as frp where frp.id.elementaryResourceId=:elemRes and frp.id.compositeResourceId=:compRes and frp.id.functionId=:fnId]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:865)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.app.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: line 1:91: unexpected token: as
at org.hibernate.hql.antlr.HqlBaseParser.deleteStatement(HqlBaseParser.java:242)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:139)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)
... 21 more
[/code]


Can you please help me to resolve this.

Thanks & Regards
Chandrasekhar S.


Last edited by chandrasekhar_s on Wed Jul 04, 2007 6:20 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: HQL Delete Query
PostPosted: Thu Aug 24, 2006 2:16 am 
Newbie

Joined: Thu Jun 22, 2006 12:22 am
Posts: 9
Hi,

Thanks a lot for the reply.

I tried this

[code]createQuery("delete from FunctionResourcePermissionData as frp" +
" where frp.id.elementaryResourceId=:elemRes" +
" and frp.id.compositeResourceId=:compRes " +
" and frpid.functionId=:fnId") [/code]

But now i'm getting an exception

[code]org.hibernate.hql.ast.QuerySyntaxError: [b]unexpected token: as near line 1, column 91[/b] [delete from com.app.entity.FunctionResourcePermissionData as frp where frp.id.elementaryResourceId=:elemRes and frp.id.compositeResourceId=:compRes and frp.id.functionId=:fnId]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:865)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.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: line 1:91: unexpected token: as
at org.hibernate.hql.antlr.HqlBaseParser.deleteStatement(HqlBaseParser.java:242)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:139)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)
... 21 more
[/code]


Can you please help me to resolve this.

Thanks & Regards
Chandrasekhar S.


Last edited by chandrasekhar_s on Wed Jul 04, 2007 6:23 am, edited 2 times in total.

Top
 Profile  
 
 Post subject: HQL Delete Query
PostPosted: Thu Aug 24, 2006 2:26 am 
Newbie

Joined: Thu Jun 22, 2006 12:22 am
Posts: 9
Hi George,

I tried by giving an alias as you suggested.

But it gave me an exception "Un expected Token frp" ,

where frp was the alias name i had given.

Since I have not mapped my Id class (no separate hbm for that class) i can't

use the delete method with FunctionResourcePermissionId as parameter.

Now as you sugessted let me try in Hibernate 3.1.3 as i'm using

Hibernate 3.0.5.

Thanks & Regards
Chandrasekhar S.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 24, 2006 2:51 am 
Beginner
Beginner

Joined: Mon Nov 22, 2004 11:21 am
Posts: 42
Hi Chandrasekhar,

you do not need any additional mapping for the class FunctionResourcePermissionId beside from that you already have. So the delete() method should work.

About the alias problem: It seems you are using a database that cannot handle aliases. What are the setting for "hibernate.connection.driver_class" and "hibernate.dialect" in your hibernate.cfg.xml?

Regards,
Georg

_________________
Real programmers confuse Halloween and Christmas because OCT 31 = DEC 25


Top
 Profile  
 
 Post subject: HQL Delete Query
PostPosted: Thu Aug 24, 2006 3:15 am 
Newbie

Joined: Thu Jun 22, 2006 12:22 am
Posts: 9
Hi George,

Thanks a lot for the reply.

Now I changed my Hibernate Version to 3.1.After that my previous query

is working fine.

Before that I tried using the delete method with

FunctionResourcePermissionId as parameter. But I got this exception

[code]org.hibernate.MappingException: Unknown entity: com.app.entity.FunctionResourcePermissionId
[/code]

I think i did something wrong here.

Can you please let me know how to use the delete method with

FunctionResourcePermissionId as parameter.

Anway my issue is solved after changing the hibernate version,but still it

will be helpful for me in some other scenarios.

Thanks & Regards
Chandrasekhar S


Last edited by chandrasekhar_s on Wed Jul 04, 2007 6:24 am, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 24, 2006 3:42 am 
Beginner
Beginner

Joined: Mon Nov 22, 2004 11:21 am
Posts: 42
Hi Chandrasekhar,

the error message says that the class com.wellpoint.eps.epds.data.application.entity.FunctionResourcePermissionId must exist. If you have not used a code generator to generate it: just create a simple java bean within the above package. Here's an example for such an implementation:

Code:
package com.wellpoint.eps.epds.data.application.entity;
import java.io.Serializable;
public class RefCodeId implements Serializable {
   /** default hash code */
   private int hashCode = Integer.MIN_VALUE;
   /** property rvDomain */
   private java.lang.String rvDomain;
   /** property rvLowValue */
   private java.lang.String rvLowValue;

   /**
    * The default constructor
    */
   public RefCodeId() {
   }
   
   /**
    * The constructor for the required key fields
    * @param rvDomain The required key field to set
    * @param rvLowValue The required key field to set
    */
   public RefCodeId(String rvDomain, String rvLowValue) {
      this.setRvDomain(rvDomain);
      this.setRvLowValue(rvLowValue);
   }

   /**
    * Return the value associated with the column: RV_DOMAIN
    * @return The associated value
    */
   public String getRvDomain() {
      return rvDomain;
   }

   /**
    * Set the value related to the column: RV_DOMAIN
    * @param rvDomain the RV_DOMAIN value
    */
   public void setRvDomain(String rvDomain) {
      this.rvDomain = rvDomain;
   }

   /**
    * Return the value associated with the column: RV_LOW_VALUE
    * @return The associated value
    */
   public String getRvLowValue()   {
      return rvLowValue;
   }

   /**
    * Set the value related to the column: RV_LOW_VALUE
    * @param rvLowValue the RV_LOW_VALUE value
    */
   public void setRvLowValue(String rvLowValue) {
      this.rvLowValue = rvLowValue;
   }

   /**
    * @see java.lang.Object#equals(java.lang.Object)
    * @param obj The object to compare with this
    * @return true if both business objects are same or equal
    */
   public boolean equals(Object obj)
   {
      if (null == obj) {
         return false;
      }
      if (!(obj instanceof RefCodeId)) {
         return false;
      } else   {
         RefCodeId mObj = (RefCodeId) obj;
         if (null != this.getRvDomain() && null != mObj.getRvDomain()) {
            if (!this.getRvDomain().equals(mObj.getRvDomain())) {
               return false;
            }
         } else {
            return false;
         }
         if (null != this.getRvLowValue() && null != mObj.getRvLowValue()) {
            if (!this.getRvLowValue().equals(mObj.getRvLowValue())) {
               return false;
            }
         } else {
            return false;
         }
         return true;
      }
   }

   /**
    * @see java.lang.Object#hashCode()
    * @return The generated hash code
    */
   public int hashCode() {
      if (Integer.MIN_VALUE == this.hashCode) {
         StringBuffer sb = new StringBuffer();
         if (null != this.getRvDomain()) {
            sb.append(this.getRvDomain().hashCode());
            sb.append(":");
         } else {
            return super.hashCode();
         }
         if (null != this.getRvLowValue()) {
            sb.append(this.getRvLowValue().hashCode());
            sb.append(":");
         } else {
            return super.hashCode();
         }
         this.hashCode = sb.toString().hashCode();
      }
      return this.hashCode;
   }
}


Regards,
Georg

P.S.: Please vote if postings are helpful.

_________________
Real programmers confuse Halloween and Christmas because OCT 31 = DEC 25


Top
 Profile  
 
 Post subject: Re: HQL Delete Query
PostPosted: Fri Nov 18, 2016 2:46 am 
Newbie

Joined: Fri Nov 18, 2016 2:01 am
Posts: 1
You can try:

delete from FunctionResourcePermissionData where
( id.elementaryResourceId, id.compositeResourceId, id.functionId ) in
( select id.elementaryResourceId, id.compositeResourceId, id.functionId from FunctionResourcePermissionData where id.elementaryResourceId=:elemRes and id.compositeResourceId=:compRes and id.functionId=:fnId )


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 10 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.