Menu
I'm trying to perform a bulk delete of an object, Feature, which has a birdirectional ManyToOne relationship with another class, FeaturesMetadata. I'm having a SQLGrammerException thrown.
The hql I'm using:
String hql = "delete from Feature F where F.featuresMetadata.stateGeoId = :stateGeoId"; Turning on show SQL, the following is generated:
delete from FEATURE cross join FEATURESMETADATA featuresme1_ where STATEGEOID=? Running the SQL directly in the db client gives this exception:
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 'cross join FEATURESMETADATA featuresme1_ where stategeoid='01'' at line 1 Since the generated SQL is throwing the Exception, I tried changing dialects from MySQL5InnoDBDialect to MySQLInnoDBDialect, but no change.
Can anyone assist?
566
Join statements are used when one wants to fetch data from multiple tables of database. Hibernate provides support for join statements where one can write single query to fetch data from multiple tables easily.
In MySQL, JOIN , CROSS JOIN , and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise. In general, parentheses can be ignored in join expressions containing only inner join operations.
Hibernate transforms this into a cross join which creates the same result as an inner join when you add the join condition to the WHERE statement.
HQL Join : HQL supports inner join, left outer join, right outer join and full join. For example, select e.name, a. city from Employee e INNER JOIN e.
You may not have joins in such a HQL query. Quote from the reference documentation:
No joins, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.
So I guess something like this should work:
delete from Feature F where F.id in (select f2.id from Feature f2 where f2.featuresMetadata.stateGeoId = :stateGeoId) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
