java - 无法从 Java 执行以下查询

Question

我正在尝试从 Java 执行以下查询。
查询看起来像这样，当我尝试在 Maria DB 中执行它时，它工作得很好。

LOCK TABLE item_tree WRITE;
SELECT @myRight := rgt FROM item_tree WHERE item_id = 7; 
UPDATE item_tree SET rgt = rgt + 2 WHERE rgt >= @myRight; 
UPDATE item_tree SET lft = lft + 2 WHERE lft > @myRight; 
INSERT INTO item_tree(item_name, lft, rgt) VALUES('BLA', @myRight , @myRight + 1);
UNLOCK TABLES;

我为此编写了以下代码。

  try {
            DatabaseConnection dbConn = new DatabaseConnection();
            Connection conn = dbConn.initiateConnection();

        PreparedStatement stmt = conn.prepareStatement(Queries.queryInsertChildNode);
        stmt.setInt(1, Integer.parseInt(nodeId));
        stmt.setString(2,newNodeName);

        ResultSet resultSet = stmt.executeQuery();
        System.out.println(resultSet);

        dbConn.closeConnection(conn);
    } catch (Exception e) {
        e.printStackTrace();
    }

其中 Queries.queryInsertChildNode = 代表上述查询。

它给出了错误:

You have an error in your SQL syntax;

check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE item_tree SET rgt = rgt + 2 WHERE rgt >= @myRight; UPDATE item_tree SET l'.

提前致谢。

Answer 1

我使用存储过程解决了这个问题。我创建了一个类似于以下代码的存储过程:

CREATE DEFINER=`XXXX`@`localhost` PROCEDURE `insertChildNode`(IN `nodeId` INT, IN `newNodeName` VARCHAR(50))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    Rollback;

START TRANSACTION;

    SELECT @myRight := rgt FROM item_tree WHERE item_id = nodeId;

    UPDATE item_tree SET rgt = rgt + 2 WHERE rgt >= @myRight;
    UPDATE item_tree SET lft = lft + 2 WHERE lft > @myRight;
    INSERT INTO item_tree(item_name, lft, rgt) VALUES(newNodeName, @myRight , @myRight + 1);

COMMIT;

END

我用下面的代码从 java 中调用了这个存储过程:

CallableStatement cstmt = null;
try {
    DatabaseConnection dbConn = new DatabaseConnection();
    Connection conn = dbConn.initiateConnection();

    String SQL = "{call insertChildNode(?, ?)}";

    //CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}");

    cstmt = conn.prepareCall(SQL);

    cstmt.setInt(1, Integer.parseInt(nodeId));
    cstmt.setString(2, newNodeName);

    cstmt.execute();

    System.out.println("Query executed...");
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    try {
        cstmt.close();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}