JDBC CallableStatement 存储过程与自定义函数

一、存储过程

创建

CREATE DEFINER=CURRENT_USER PROCEDURE `adder`(IN a int, IN b int, OUT sum int)
BEGIN
    DECLARE c int;
    
    if a is null
        then set a = 0;
    end if;
    
    if b is null
        then set b = 0;
    end if;
    
    set sum  = a + b;
END

代码方式

import com.alibaba.druid.pool.DruidDataSource;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.sql.*;

public class StoredProcedureTest {

    private Connection conn;
    private Statement statement;
    private CallableStatement callableStatement;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    @BeforeEach
    public void testDruid() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://192.168.8.136:3306/jdbc");
        dataSource.setUsername("root");
        dataSource.setPassword("root");

        conn = dataSource.getConnection();
    }

    @AfterEach
    public void end() throws Exception {
        if (resultSet != null) {
            resultSet.close();
        }
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (callableStatement != null) {
            callableStatement.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (conn != null) {
            conn.close();
        }
    }

    @Test
    public void createProcedureAdder() throws SQLException {
        String queryDrop = "DROP PROCEDURE IF EXISTS adderP";
        String createProcedure = "CREATE DEFINER=`root`@`%` PROCEDURE `adderP`(IN a int, IN b int, OUT sum int)\n" +
                "BEGIN\n" +
                "\tDECLARE c int;\n" +
                "\tif a is null then set a = 0;\n" +
                "\tend if;\n" +
                "\tif b is null then set b = 0;\n" +
                "\tend if;\n" +
                "\tset sum  = a + b;\n" +
                "END";
        try {
            statement = conn.createStatement();
            statement.execute(queryDrop);
            statement.executeUpdate(createProcedure);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

调用

@Test
public void runStoredProcedures() {
    try {
        callableStatement = conn.prepareCall("{call adderP(?, ?, ?)}");
        // 输入
        callableStatement.setInt(1, 5);
        callableStatement.setInt(2, 5);
        // 输出
        callableStatement.registerOutParameter(3, Types.INTEGER);
        callableStatement.executeQuery();

        // 获取结果
        Integer result = callableStatement.getInt(3);

        System.out.println(result);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

 

二、自定义函数

创建

CREATE DEFINER=`root`@`%` FUNCTION `adder`(`a` int,`b` int) RETURNS int(10)
BEGIN
--     变量定义需在函数开头
    DECLARE sum int UNSIGNED DEFAULT 0;
    
    if a is null then
        set a = 0;
    end if;
    
    if b is null then
        set b = 0;
    end if;
    
    set sum  = a + b;
    RETURN sum;
END

代码方式

@Test
public void createFunctionAdder() throws SQLException {
    String queryDrop = "DROP FUNCTION IF EXISTS adderF";
    String createProcedure = "CREATE DEFINER=CURRENT_USER FUNCTION `adderF`(`a` int,`b` int) RETURNS int(10)\n" +
            "BEGIN\n" +
            "-- \t变量定义需在函数开头\n" +
            "\tDECLARE sum int UNSIGNED DEFAULT 0;\n" +
            "\tif a is null then\n" +
            "\t\tset a = 0;\n" +
            "\tend if;\n" +
            "\tif b is null then\n" +
            "\t\tset b = 0;\n" +
            "\tend if;\n" +
            "\tset sum  = a + b;\n" +
            "\tRETURN sum;\n" +
            "END";
    try {
        statement = conn.createStatement();
        statement.execute(queryDrop);
        statement.executeUpdate(createProcedure);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

调用

@Test
public void runFunctions() {
    try {
        preparedStatement = conn.prepareStatement("SELECT adderF(?, ?)");
        // 输入参数
        preparedStatement.setInt(1, 5);
        preparedStatement.setInt(2, 5);
        // 输出
        preparedStatement.executeQuery();

        // 获取结果
        resultSet = preparedStatement.getResultSet();
        if (resultSet.next()) {
            System.out.println(resultSet.getInt(1));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

 

---

https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html

https://dev.mysql.com/doc/refman/8.3/en/create-function.html