Java 获取输出 CallableStatement JDBC

Question

我是java新手。我尝试在 SQL SERVER 中创建一个存储过程(SP)。SP 是:

go
create procedure sp_calculate
    @value1 int = 0,
    @value2 int = 0,
    @sum int OUTPUT,
    @multiply int OUTPUT
as
begin
    set nocount on
    set @sum = @value1 + @value2
    set @multiply = @value1 * @value2
end

我按照Getting the Return Value from JDBC MSSQL进行训练。所以我在下面用 Java 来做:

public static void main(String[] args) {
    try (Connection conn = ConnectionUtil.getConnection()) {
        CallableStatement cst = conn.prepareCall("{ (?, ?) = call sp_calculate ( @value1 =?, @value2=? ) }");

        cst.setInt(3, 4);//try some value
        cst.setInt(4, 10);//try some value

        cst.registerOutParameter(1, java.sql.Types.INTEGER);
        cst.registerOutParameter(2, java.sql.Types.INTEGER);
        cst.executeUpdate();
        int sum = cst.getInt(1);
        int multiply = cst.getInt(2);
        System.out.println("sum = " + sum + " - product = " + multiply);
    }
    catch (SQLException ex) {
        Logger.getLogger(StoredProcedured.class.getName()).log(Level.SEVERE, null, ex);
    }
}

但结果是“com.microsoft.sqlserver.jdbc.SQLServerException:'{'附近的语法不正确。”任何人都可以告诉我我创建了什么错误？

最后，我解决了:

public static void main(String[] args) {
    try (Connection conn = ConnectionUtil.getConnection()) {
        CallableStatement cst = conn.prepareCall("{ call sp_calculate ( ?,?,?,? ) }");

        cst.setInt(1, 10);//try some value
        cst.setInt(2, 4);//try some value

        cst.registerOutParameter(3, java.sql.Types.INTEGER);
        cst.registerOutParameter(4, java.sql.Types.INTEGER);
        cst.executeUpdate();
        int sum = cst.getInt(3);
        int product = cst.getInt(4);
        System.out.println("sum = " + sum + " - product = " + product);
    }
    catch (SQLException ex) {
        Logger.getLogger(StoredProcedured.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Answer 1

尝试像这样调用过程:

String callableQuery = "{call sp_calculate(?, ?, ?, ?)}";

cst.setInt(1, 1); // IN parameter
cst.setInt(2, 4); // IN parameter

cst.registerOutParameter(3, java.sql.Types.INTEGER); // OUT parameter
cst.registerOutParameter(4, java.sql.Types.INTEGER); // OUT parameter