CODEWITHSUNDEEP
mysqlhibernate
Jason
Jason

Reputation: 4145

Hibernate Exception on MySQL Cross Join Query

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?

Upvotes: 34

Views: 10523

Answers (3)

Sebastiaan van den Broek
Sebastiaan van den Broek

Reputation: 6361

This is indeed rather poor from Hibernate. But you can solve it like this in a repo: (at least in PostgreSQL, not sure if this syntax should be modified for MySql)

@Modifying
@Query(nativeQuery = true, value = """
    DELETE FROM feature f
    USING features_metadata fd
    WHERE f.features_metadata_id = fd.id AND fd.state_geo_id = :stateGeoId
    """)
void deleteByStateGeoIdId(@Param("stateGeoId") UUID stateGeoId);

Upvotes: 0

Big Al Stanley
Big Al Stanley

Reputation: 11

I had the same issue and struggled to find a sensible answer. It seems that, even if you get this approach to work, the SQL generated is highly inefficient (according to what I have read).

So I took a step back and did the following:

    List<Properties> props = propDao.findPropertiesByHotelCode(hotel.getCode());
    propDao.deleteInBatch(props);
    propDao.flush();

Basically rather tan trying to 'delete where', I'm doing a select where and then deleting in batch the set that I retrieved.

Hope this helps.

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 692181

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)

Upvotes: 50

Related Questions