java - HQL - 删除并出现 JOIN 错误

Question

我正在尝试使用 join 执行 HQL 删除。搜索后我发现我需要创建一个查询，就像这里建议的那样:

http://dasunhegoda.com/1093-you-cant-specify-target-table-table_name-for-update-in-from-clause/104/

这是我的查询:

    dao.executeByHql(
        "DELETE FROM FinalGradeResult e WHERE e.id IN "
        + "( SELECT id FROM "
            + "( SELECT x FROM FinalGradeResult x " 
            + "where x.student.id = :studentId "
            + " AND x.classDiscipline IN " + 
                + "(SELECT cd from ClassDiscipline cd " 
                + " where cd.clazz.id = :clazzId ) ) as X )",
    new HqlParameter("studentId", student.getId()), 
    new HqlParameter("clazzId", from.getId()));

但我一直收到这个错误:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token:( near line 1, column 94 [DELETE FROM xxxxxxxxxxxx.entity.FinalGradeResult e WHERE e.id IN ( SELECT id FROM ( SELECT x FROM xxxxxxxxxxxxxxx.entity.FinalGradeResult x where x.student.id = :studentId AND x.classDiscipline IN (SELECT cd from xxxxxxxxxxxxxxxx.entity.ClassDiscipline cd where cd.clazz.id = :clazzId ) ) as X )]

错误指出第二个( 是错误的，紧跟在"SELECT id FROM"之后的那个

编辑 我试过这样，但出现同样的错误:

    dao.executeByHql(
        "DELETE FROM FinalGradeResult e WHERE e.id IN "
        + "( SELECT id FROM "
        + "( SELECT x FROM FinalGradeResult x "
            + " where x.student.id = :studentId "
            + " AND x.classDiscipline.clazz.id = :clazzId )"
        + " as X )",

编辑 2:由于我在这个问题中发布的链接中描述的问题，这样的查询不起作用:

dao.executeByHql(
        "DELETE FROM FinalGradeResult  e WHERE e.id IN " + "( SELECT x.id FROM FinalGradeResult as x "
                + " where x.student.id = :studentId " + " AND x.classDiscipline.clazz.id = :clazzId )",
        new HqlParameter("studentId", student.getId()), new HqlParameter("clazzId", from.getId()));

错误是:

Caused by: java.sql.SQLException: You can't specify target table 'tb_final_grade_result' for update in FROM clause

解决方案

在尝试了一切之后，我们的结论是:

• 我们不能直接只有一个查询，因为我们不能在 DELETE 上进行联接。
• 我们不能只有 2 个查询(一个子查询)，因为我们有一个 MYSQL BUG(在提供的链接中有描述)
• 我们不能有 3 个查询(2 个子查询)，因为我们不能在 FROM 子句中有子查询。这就是为什么我们的第二个查询不起作用 (select * from (select ...)) is invalid.

所以我决定使用NativeSQL来解决问题:

dao.executeBySQL(
        "   delete from tb_final_grade_result where id in "
                + " (select * from ( select finalgrade1_.id from tb_final_grade_result finalgrade1_ cross join tb_class_discipline classdisci2_ "
                + " where finalgrade1_.id_class_discipline=classdisci2_.id and finalgrade1_.id_student= :studentId and classdisci2_.id_class= :clazzId ) as tmp )",
        new HqlParameter("studentId", student.getId()), new HqlParameter("clazzId", from.getId()));

特别感谢@scaisEdge

Answer 1

为什么要在要删除的同一个表上使用in (select？难道不能把条件放在where子句中吗？

DELETE FROM FinalGradeResult  e WHERE e.student.id = :studentId " + " AND e.classDiscipline.clazz.id = :clazzId )",
    new HqlParameter("studentId", student.getId()), new HqlParameter("clazzId", from.getId()));

此外，我不确定您指的参数 classDiscipline.clazz.id 是什么？ classDiscipline 是否是其他实体，其字段名为 clazz 的又是另一个实体？这就是查询似乎在说的内容。