JPA NativeQuery 中的 MYSQL 嵌套查询

Question

我正在尝试实现嵌套子集算法来处理关系数据库 (MySQL) 中的层次结构。

具体来说，我在 http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ 上关注这个优秀的指南

插入算法给出了以下查询来插入一个新节点并对所有节点重新编号:

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

我已尝试将上述内容插入 JPA native 查询，如下所示:

Query query = em.createNativeQuery("SELECT @myRight := rgt FROM nested_category
    WHERE name = 'TELEVISIONS';
    UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
    UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
    INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);");
query.getResultList();

但是我得到一个内部异常:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

有没有办法将上面的查询修改成等价的查询或者以某种方式让 Native Query 处理上面的查询？

我尝试了一些嵌套子查询如下:

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > (SELECT rgt FROM nested_category WHERE name = 'TELEVISIONS');

但我得到一个#1093 - 你不能在 FROM 子句中指定目标表'nested_category'进行更新

显然我无法从我选择的表格中更新。

Answer 1

我不知道您是否想在单个查询中解决上述问题，但最简单的方法是拆分为 4 个简单的 native 查询。

Query query = em.createNativeQuery("SELECT rgt FROM nested_category
    WHERE name = 'TELEVISIONS';");
List<Object> rightSiblings = query.getResultList();
for (Object sibling : rightSiblings)
            {
                Integer siblingId = (Integer) sibling;
                query = em.createNativeQuery("UPDATE nested_category SET rgt = rgt + 2 WHERE rgt >"+siblingId);
                query.executeUpdate();
            }

然后继续分别处理每个查询。