Hibernate (HQL Delete Query)

[Chandra Sekhar]

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.wellpoint.eps.epds.data.application.entity.FunctionResourcePermissionData" table="FUNC_RSRC_PRMSN">
<composite-id name="id" class="com.wellpoint.eps.epds.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.wellpoint.eps.epds.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.wellpoint.eps.epds.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.wellpoint.eps.epds.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]

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]

I had the same problem and that has solved it.
Thanks, Vilpesh.

[nimesh hindocha]

Hi Guys,
I want to write delete query for below code please any one could help,i am new to hibernate.

   public class AccelProductId implements java.io.Serializable {

	private AccelProductIdId id;
	private String prodIdDesc;

	public AccelProductId() {
	}

	public AccelProductId(AccelProductIdId id, String prodIdDesc) {
		this.id = id;
		this.prodIdDesc = prodIdDesc;
	}

	public AccelProductIdId getId() {
		return this.id;
	}

	public void setId(AccelProductIdId id) {
		this.id = id;
	}

	public String getProdIdDesc() {
		return this.prodIdDesc;
	}

	public void setProdIdDesc(String prodIdDesc) {
		this.prodIdDesc = prodIdDesc;
	}
	
	public String toString()
	{
		return "prodIdDesc is :"+prodIdDesc;
	}
} 

   public class AccelProductIdId implements java.io.Serializable {

	private short inst;
	private String cpiProdId;

	public AccelProductIdId() {
	}

	public AccelProductIdId(short inst, String cpiProdId) {
		this.inst = inst;
		this.cpiProdId = cpiProdId;
	}

	public short getInst() {
		return this.inst;
	}

	public void setInst(short inst) {
		this.inst = inst;
	}

	public String getCpiProdId() {
		return this.cpiProdId;
	}

	public void setCpiProdId(String cpiProdId) {
		this.cpiProdId = cpiProdId;
	}

	public boolean equals(Object other) {
		if ((this == other))
			return true;
		if ((other == null))
			return false;
		if (!(other instanceof AccelProductIdId))
			return false;
		AccelProductIdId castOther = (AccelProductIdId) other;

		return (this.getInst() == castOther.getInst())
				&& ((this.getCpiProdId() == castOther.getCpiProdId()) || (this
						.getCpiProdId() != null
						&& castOther.getCpiProdId() != null && this
						.getCpiProdId().equals(castOther.getCpiProdId())));
	}

	public int hashCode() {
		int result = 17;

		result = 37 * result + this.getInst();
		result = 37 * result
				+ (getCpiProdId() == null ? 0 : this.getCpiProdId().hashCode());
		return result;
	}
public String toString()
{
return " institution :"+inst +"Product id : "+cpiProdId;	
}
} 

  <?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Aug 30, 2010 4:54:49 PM by Hibernate Tools 3.3.0.GA -->
<hibernate-mapping>
    <class name="AccelProductId" table="ACCEL_PRODUCT_ID" >
        <composite-id name="id" class="AccelProductIdId">
            <key-property name="inst" type="short">
                <column name="INST" />
            </key-property>
            <key-property name="cpiProdId" type="string">
                <column name="CPI_PROD_ID" length="2" />
            </key-property>
        </composite-id>
        <property name="prodIdDesc" type="string">
            <column name="PROD_ID_DESC" length="25" not-null="true" />
        </property>
    </class>
</hibernate-mapping>
  

Thanks in advance.