MYSQL nested queries in JPA NativeQuery

Question

I'm trying to implement the Nested Subset algorithm to handle hierarchies in a relational database (MySQL).

Specifically I'm following this excellent guide at http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

The algorithm for insertion gives the following query to insert a new node and renumber all nodes:

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);

I have tried to insert the above in a JPA Native Query as follows:

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();

But I get an Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

Is there some way to modify the above query into an equivalent query or somehow let Native Query handle the above query?

I tried some nested subquery as follows:

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

But I get an #1093 - You can't specify target table 'nested_category' for update in FROM clause

apparently I can't update from a table from which I select.

dendini · Accepted Answer · 2014-01-24 16:43:47Z

3

I dont' know if you want to solve the above in a single query but easiest way would be to split into 4 easy native queries.

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();
            }

and then go on treating each query separately.

answered Jan 24, 2014 at 16:43

dendini

3,9529 gold badges43 silver badges75 bronze badges

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

Collectives™ on Stack Overflow

MYSQL nested queries in JPA NativeQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related