英文:
JPQL IN operator in DELETE query with 2-deep relationship WHERE clause
问题
我有一个类似这样的JPQL查询：
@Modifying
@Query("delete from Thing t where t.manyToOne1.manyToOne2 = :farThing and t.id in :ids")
void delete(ManyToOne2 manyToOne2, List<String> ids);
导致了一个异常：
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 many_to_one1 manytoone1_ where manytoone2_id=1039 and (id in (' at line 1
英文:
I have a JPQL query like this :
@Modifying
@Query("delete from Thing t where t.manyToOne1.manyToOne2 = :farThing and t.id in :ids")
void delete(ManyToOne2 manyToOne2, List<String> ids);
Resulting in an exception :
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 many_to_one1 manytoone1_ where manytoone2_id=1039 and (id in (' at line 1
答案1
得分: 1
将请求更改为：
@Modifying
@Query("delete from Thing t where t.manyToOne1 = :manyToOne1 and t.id in :ids")
void delete(ManyToOne1 manyToOne1, List<String> ids);
修复了这个问题。
理论上对于其他一些请求来说，使用两层深度的多对一关系链可能没有问题，但是与IN操作符组合使用，或者可能与修改查询结合使用，在实践中却无法正常工作。
英文:
Changing the request to
@Modifying
@Query("delete from Thing t where t.manyToOne1 = :manyToOne1 and t.id in :ids")
void delete(ManyToOne1 manyToOne1, List<String> ids);
fixes the issue.
It seems that there is theoretically nothing wrong with using 2-deep many-to-one relationship chain for some other requests, but combining with the IN operator or maybe with a modifying query just doesn't work in practice.
答案2
得分: 0
问题是，这种类型的查询使用隐式连接，这将在DML语句中生成一个SQL连接。并非所有的DBMS都允许这样做，而且Hibernate目前也不支持这种操作。正如你所发现的，你可以避免隐式连接。对于删除语句，你也可以使用一个存在子查询来建模连接，类似这样：
delete from Thing t
where t.id in :ids
and exists (
select 1
from t.manyToOne1 a
where a.manyToOne2 = :farThing
)
英文:
The problem is, that this kind of query uses an implicit join which would generate a SQL join in a DML statement. This is not allowed for all DBMS and there is no support in Hibernate for this yet. As you figured out, you can avoid the implicit join. For delete statements, you could also use an exists subquery to model joins, something like this:
delete from Thing t
where t.id in :ids
and exists (
select 1
from t.manyToOne1 a
where a.manyToOne2 = :farThing
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论