Hibernate generating bad syntax sql

Question

There are two tables: bb_players and bb_player_skills. player skills table has one to one relation with bb_players, and also foreign key to bb_players.

Error happens at executing this code:

Query q = em.createNamedQuery(PlayerSkill.DELETE_SKILL_BY_PLAYER_ID);
q.setParameter("playerID", playerID);
q.executeUpdate();

The named query is:

 @NamedQuery(name = PlayerSkill.DELETE_SKILL_BY_PLAYER_ID, query = "DELETE FROM   PlayerSkill s " +
 " WHERE s.player.id = :playerID")

The error from postgresql logs is:

ERROR,42601,"syntax error at or near ""cross""",,,,,, 
 "delete from bb_player_skills cross join bb_players player1_ where id=$1",30,,""

Is my named query wrong and how should I rewrite it?

I suggest pure Hibernate: use proper mapping for your Player class and use session.delete() to delete it. And Hibernate would create the query by itself without your help. — torvin
– torvin, Commented Sep 24, 2015 at 0:45

Nate · Accepted Answer · 2015-09-24 12:11:32Z

8

It appears that this may be an open Hibernate issue depending on your Hibernate version.

From: https://hibernate.atlassian.net/browse/HHH-7314

Using a JPA Delete query with conditions requiring a join through Hibernate entity-manager generates invalid SQL for PostgreSQL. PostgreSQL cannot use CROSS JOIN in the FROM clause of a DELETE query.

answered Sep 24, 2015 at 12:11

Nate

5493 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M-Soley · Accepted Answer · 2019-03-09 10:22:12Z

3

In case anyone else faces the same problem,Here is one solution.
The named query can be rewritten to avoid join cross.

@NamedQuery(name = "PlayerSkill.DELETE_SKILL_BY_PLAYER_ID", query = "DELETE FROM   PlayerSkill s " +
 " WHERE s.player IN (SELECT player FROM Player player where player.id=:playerID)")

answered Mar 9, 2019 at 10:22

M-Soley

2851 gold badge4 silver badges13 bronze badges

Comments

v.ladynev · Accepted Answer · 2015-09-24 10:36:37Z

0

Looks like it is not a one to one relation. From bb_players it shoutd be a one to many relation, from bb_player_skills it should be a many to one relation.

answered Sep 24, 2015 at 10:36

v.ladynev

20.1k9 gold badges50 silver badges70 bronze badges

Collectives™ on Stack Overflow

Hibernate generating bad syntax sql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related