java - 当 SELECT 查询在插入查询之前时，getGeneratedKeys() 不起作用

Question

preparedStatement.getGeneratedKeys() 在查询只有一个 INSERT MySQL 语句时工作正常，但如果查询中有多个语句，它不会返回生成的 ID，即: test 有 (ID (PK, auto_increment), Name, Loan) 列:

        sqlQuery = "SET @Loan = (SELECT Loan FROM customers WHERE CID = 4);"
                + "INSERT INTO test(Name, Loan) VALUES('jihad', @Loan);";
        PreparedStatement st = connection.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS);
        st.executeUpdate();
        ResultSet rs = st.getGeneratedKeys();
        if (rs.next()) {
            int id = rs.getInt(1);
            System.out.println("Generated ID: "+ id);
        }

Answer 1

我不知道这个答案是否有助于解决您的问题，但可以将您的插入写成一条语句:

sqlQuery = "INSERT INTO test (Name, Loan) ";
sqlQuery += "SELECT 'jihad', (SELECT Loan FROM customers WHERE CID = 4)";

PreparedStatement st = connection.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS);
st.executeUpdate();