JDBC 使用 CallableStatement 调用存储过程

数据库和表设计

-- 创建数据库
CREATE DATABASE `bank`;

-- 切换到 bank 库
USE `bank`;

-- 创建表
CREATE TABLE `user_balance` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `balance` BIGINT NOT NULL ,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;

在 user_balance 表中准备两条数据：

mysql> select * from user_balance;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | xigua |    1000 |
|  2 | fanqie |    1001 |
+----+--------+---------+
2 rows in set (0.00 sec)

创建存储过程

我们创建一个存储过程，名为 select_by_id 。作用是根据id查询记录信息。

use bank;
delimiter $$
create procedure select_by_id(in p_id bigint(20))
begin
    select *
    from user_balance 
    where id=p_id;
end$$
delimiter ;

使用 CallableStatement 调用存储过程

package demo;

import java.sql.*;

/**
 * 存储过程
 */
public class CallableStatementSelect {

    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/bank";

    public static void selectById(Long id) throws ClassNotFoundException, SQLException {
        Class.forName(JDBC_DRIVER);
        Connection conn =  DriverManager.getConnection(DB_URL, USER, PASSWORD);
        CallableStatement cstmt = null;
        try {
            cstmt = conn.prepareCall("{call select_by_id(?)}");
            cstmt.setLong(1, id);
            ResultSet resultSet = cstmt.executeQuery();
            while (resultSet.next()) {
                System.out.printf("id: %s, name: %s, balance: %s\n",
                        resultSet.getLong("id"),
                        resultSet.getString("name"),
                        resultSet.getLong("balance"));
            }
            resultSet.close();
        } finally {
            if (cstmt != null) {
                cstmt.close();
            }
            conn.close();
        }
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        selectById(1L);
    }

}

运行后输出：

id: 1, name: xigua, balance: 1002