java - 在Java类中调用存储过程

Question

这个问题已经有答案了:

How to call Stored Procedure and prepared statement (2 个回答)

已关闭 9 年前。

如何在java类中调用这个存储过程？并声明一个与其相等的字符串变量？

GenORFinalSP(SP 名称)

BEGIN

DECLARE temp_OR VARCHAR(50);

CALL GenORSP(xRCO, xReceipt, @tempCount, @tempORNo);

SET xROR = @tempORNo;

WHILE xROR = (SELECT TPAY_RECEIPT_NO FROM tbl_tax_payment WHERE TPAY_RECEIPT_NO = xROR) 
    DO
        CALL GenORSP(xRCO, xReceipt, @tempCount, @tempORNo);
END WHILE;

SET xROR = @tempORNo;

SET xCount = @tempCount;

END

例如，我想声明一个与其相等的字符串变量 test。

String test = GenORFinalSP();

更新:这是GenORSP的代码

BEGIN

DECLARE var_MAX BIGINT(20);
DECLARE temp_COUNT BIGINT(20);
DECLARE var_YEAR VARCHAR(4);
DECLARE var_tempYEAR VARCHAR(4);
DECLARE var_END INT(1);

SET temp_COUNT = 0;
SET var_END  = 0;
SET var_YEAR = YEAR(CURRENT_TIMESTAMP);

    SELECT ( CASE WHEN MAX(TPAY_COUNTER) IS NULL THEN 0 ELSE MAX(TPAY_COUNTER) END ), YEAR (DATE(TPAY_ISSUED_DATE)) INTO var_MAX, var_tempYEAR FROM tbl_tax_payment where TPAY_ISSUED_DATE = (SELECT MAX(TPAY_ISSUED_DATE) FROM tbl_tax_payment WHERE RCO_CODE = xRCO AND RECEIPT_TYPE = xReceipt AND YEAR(DATE(TPAY_ISSUED_DATE)) = YEAR(current_date) AND TPAY_COUNTER = (
        SELECT
            MAX(TPAY_COUNTER)
        FROM
            tbl_tax_payment
        WHERE
            RCO_CODE = xRCO
        AND RECEIPT_TYPE = xReceipt
        AND YEAR (DATE(TPAY_ISSUED_DATE)) = YEAR (CURRENT_DATE)
    )) AND TPAY_COUNTER = (
            SELECT
                MAX(TPAY_COUNTER)
            FROM
                tbl_tax_payment
            WHERE
            RCO_CODE = xRCO
        AND RECEIPT_TYPE = xReceipt
            AND YEAR (DATE(TPAY_ISSUED_DATE)) = YEAR (CURRENT_DATE)
        );



        IF var_YEAR <> var_tempYEAR THEN

            SET var_END = (SELECT IF(YEAR(current_date) = var_tempYEAR,0,1));

            IF var_END = 1 THEN

                SET var_MAX = 0;

            END IF;

        END IF;


        IF var_MAX = 0 THEN 

            SET temp_COUNT = 1;
            SET var_OR = CONCAT(xReceipt, var_YEAR, '-', xRCO, '-', LPAD(temp_COUNT, 6, '0'));

        ELSE 

            SET temp_COUNT = var_MAX + 1;
            SET var_OR = CONCAT(xReceipt, var_YEAR, '-', xRCO, '-', LPAD(temp_COUNT, 6, '0'));

        END IF;


            SET var_COUNT = temp_COUNT;



END

Answer 1

对于像您这样的问题和其他情况，答案是通用的，有人可能会受益;-)如果您的过程没有参数并且不希望返回任何内容，则此代码将起作用。

Connection connection = getYourDatabaseConnection();        
CallableStatement callableStatement = connection.prepareCall("{ call GenORFinalSP() }");
ResultSet resultSet = callableStatement.executeQuery();
//OR
//int rowsUpdated = callableStatement.executeUpdate();  

如果您有兴趣向过程传递一些参数，例如

CallableStatement callableStatement = connection.prepareCall("{call GenORFinalSP(?,?)}");
callableStatement.setString(1, firstArgu);
callableStatement.setString(2, secondArgu)
ResultSet resultSet = callableStatement.executeQuery();

如果您有兴趣传递一些参数并希望从过程中返回一些值(例如本例中的两个返回值)，则调用将类似于

CallableStatement callableStatement = connection.prepareCall("{call GenORFinalSP(?,?,?,?)}");
callableStatement.setString(1, firstArgu);
callableStatement.setString(2, secondArgu)
callableStatement.registerOutParameter(3, Types.VARCHAR);
callableStatement.registerOutParameter(4, Types.VARCHAR);
ResultSet resultSet = callableStatement.executeQuery();
String firstReturnedResult = callableStatement.getString(3);//getting returned result back
String secondReturnedResult = callableStatement.getString(4);//getting returned result back

注意:对各种用例的调用也需要相应地更改您的过程