Hibernate version: 3.1.2
Hi, I am hibernate newbie and have to perform a bulk delete on an entity.
I wrote a HQL query in DML style but I always get a syntax error and I don't really understant why. The mapping class has a map component.
Here's an extract of my mapping file:
Code:
<class name="Job" table="jobs" >
....
<map name="stateDates" table="jobstates" lazy="false" sort="unsorted">
<key column="jobid" />
<index type="StateEnum" column="state"/>
<element column="statedate" type="timestamp" not-null="true"/>
</map>
<property name="state" type="StateEnum" access="field" column="activestate"/>
<join table="joblogs">
<key column="jobid"/>
<property name="logData" type="org.springframework.orm.hibernate3.support.ClobStringType" column="logs" lazy="true" />
</join> -->
<one-to-one name="logData" lazy="proxy" cascade="all" constrained="true"/>
...
</class>
<class name="LogData" table="joblogs" >
<id name="id" column="jobid"/>
<property name="data" type="org.springframework.orm.hibernate3.support.ClobStringType" column="logs" lazy="true" not-null="false"/>
</class>
The Job class is mapped to 3 tables: jobs(id,activestate....), jobstates(jobid,state,statedate) and jologs(jobid,logdata).
Here's my query in plain English:
"I want to delete all jobs whose active state's date is older than a given date."
I tried the following code:
Code:
Date beforeDate = ...;
Session session = this.getSession();
int purgedJobsCount = session.createQuery("[b]delete from Job j where j.stateDates[j.state] < :beforeDate[/b]").setTimestamp("beforeDate",beforeDate).executeUpdate();
The following SQL code is generated and fails in MySQL:
Hibernate: delete from jobs, jobstates statedates1_ where statedates1_.statedate<?
Here's the stacktrace:
org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
....
Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where statedates1_.statedate<'2006-04-25'' at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1162)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1079)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1064)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
... 32 more
The SQL code that is generated is not exactly what I expected.
I tried different syntaxes but never managed to express in HQL what I wanted to achieve .
Could someone help me,
Thanks
Login
FAQ
Search