create stored procedure adding node in nested set in H2 database

I have a sql that will be use to add a node for nested set,this is my sql 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);

Normally i can put this in stored procedure, but create procedure is not supported in H2, it seems like the solution was开发者_Go百科 to use java function with create alias. Can anyone please help me out here.

Sorry for the late answer. The problem was, you didn't add the h2 tag, so the question didn't show up on my list.

drop table nested_category;
drop alias cat_add;
create table nested_category(id identity, lft int, rgt int, name varchar);
create alias cat_add as $$
void catAdd(Connection conn, String name, String after) throws SQLException {
  Statement stat = conn.createStatement();
  stat.execute("SET @myRight 0");
  PreparedStatement prep = conn.prepareStatement(
    "SELECT @myRight := rgt FROM nested_category WHERE name = ?");
  prep.setString(1, after);
  prep.execute();
  stat.execute("UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight");
  stat.execute("UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight");
  prep = conn.prepareStatement(
    "INSERT INTO nested_category(name, lft, rgt) VALUES(?, @myRight + 1, @myRight + 2)");
  prep.setString(1, name);
  prep.execute();
}
$$;
call cat_add('television', null);
call cat_add('game consoles', 'television');
select * from nested_category;