JDBC调用存储过程与存储函数

本是古典何须时尚 2021-09-17 23:16 555阅读 0赞

在JDBC API中提供了调用存储过程的方法，通过CallableStatement对象进行操作。CallableStatement对象位于java.sql包中，它继承于PreparedStatement对象，PreparedStatement对象又继承于Statement对象。CallableStatement对象主要用于执行数据库中定义的存储过程和存储函数，其调用方法如下：

调用存储过程：\{call <procedure-name>\[(<arg1>,<arg2>, ...)\]\}

调用存储函数：\{?= call <procedure-name>\[(<arg1>,<arg2>, ...)\]\}

CallableStatement对象的常用方法：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70][]

下面将以调用MySQL数据的存储过程和存储函数为示例。

# 1、创建数据库连接与关闭 #

## 1.1 获取MySQL数据库的连接 ##

import java.sql.*; //导入java.sql包

// 数据库驱动
    public static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
    
    // 数据库连接地址
    public static final String DB_URL = "jdbc:mysql://localhost:3306/db_admin?serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8&useSSL=false";
    
    // 数据库用户名称
    public static final String DB_USER = "root";
    
    // 数据库用户密码
    public static final String DB_PASSWORD = "123456";
    
    /**
     * 获取数据库连接
     * 
     * @author pan_junbiao
     * @return 数据库连接对象
     */
    public static Connection getConnection()
    {
    	Connection conn = null;
    
    	try
    	{
    		// 加载数据库驱动类
    		Class.forName(DRIVER_CLASS);
    		System.out.println("数据库驱动加载成功");
    
    		// 获取数据库连接对象
    		conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    		System.out.println("数据库连接成功");
    
    	} catch (ClassNotFoundException cnfe)
    	{
    		cnfe.printStackTrace();
    	} catch (SQLException sqle)
    	{
    		sqle.printStackTrace();
    	} catch (Exception ex)
    	{
    		ex.printStackTrace();
    	}
    
    	return conn;
    }

## 1.2 关闭相关数据操作对象 ##

/**
     * 关闭数据库操作对象
     * 
     * @author pan_junbiao
     * @param res  ResultSet对象
     * @param stmt Statement对象
     * @param conn Connection对象
     */
    public static void closeOperate(ResultSet res, Statement stmt, Connection conn)
    {
    	try
    	{
    		// 关闭ResultSet对象
    		if (res != null)
    		{
    			res.close();
    		}
    
    		// 关闭Statement对象
    		if (stmt != null)
    		{
    			stmt.close();
    		}
    
    		// 关闭Connection对象
    		if (conn != null)
    		{
    			conn.close();
    		}
    
    		System.out.println("关闭数据库操作对象完成");
    
    	} catch (SQLException sqle)
    	{
    		sqle.printStackTrace();
    	}
    }

# 2、调用存储过程 #

**示例：**通过调用存储过程，获取用户分页列表，数据总数和总页数。

## 2.1 创建存储过程 ##

/*
      -- 存储过程：分页查询用户列表
      -- 输入参数：page_index：当前页码
      -- 输入参数：page_size：分页大小
      -- 输出参数：total_count：数据总数
      -- 输出参数：total_page：总页数
    */
    DROP PROCEDURE IF EXISTS proc_search_user;
    CREATE PROCEDURE proc_search_user(IN page_index INT,IN page_size INT, OUT total_count INT, OUT total_page INT)
    BEGIN
    		DECLARE begin_no INT;
    		SET begin_no = (page_index-1)*page_size;
     
    		-- 分页查询列表
    		SELECT * FROM tb_user
    		WHERE id >= (
    			SELECT id FROM tb_user
    			ORDER BY id ASC
    			LIMIT begin_no,1
    		)
    		ORDER BY id ASC
    		LIMIT page_size;
    
    		-- 计算数据总数
    		SELECT COUNT(1) INTO total_count FROM tb_user;
    
    		-- 计算总页数
    		SET total_page = FLOOR((total_count + page_size - 1) / page_size);
    
    END;

## 2.2 调用存储过程 ##

/**
     * 调用存储过程
     * 
     * @author pan_junbiao
     */
    public static void execProcedure()
    {
    	Connection conn = null; // 数据库连接对象
    	CallableStatement clbStmt = null; // CallableStatement对象
    	ResultSet res = null; // 结果集对象
    	try
    	{
    		// 获取数据库连接
    		conn = getConnection();
    
    		// 创建CallableStatement对象
    		clbStmt = conn.prepareCall("{CALL proc_search_user(?,?,?,?)}");
    
    		// 设置输入参数
    		clbStmt.setInt(1, 3); // 查询第3页数据
    		clbStmt.setInt(2, 10); // 每页10条数据
    
    		// 注册输出参数
    		clbStmt.registerOutParameter(3, Types.INTEGER);
    		clbStmt.registerOutParameter(4, Types.INTEGER);
    
    		// 执行调用存储过程，并获取结果集
    		res = clbStmt.executeQuery();
    
    		// 循环遍历结果集
    		while (res.next())
    		{
    			// 获取列值
    			int id = res.getInt("id");
    			String name = res.getString("name");
    			Timestamp createTime = res.getTimestamp("create_time");
    
    			// 输出列值
    			System.out.println("编号：" + id + "  姓名：" + name + "  创建时间：" + createTime);
    		}
    
    		// 获取输出参数值
    		int totalCount = clbStmt.getInt(3);
    		int totalPage = clbStmt.getInt(4);
    		System.out.println("数据总数：" + totalCount + " 总页数：" + totalPage);
    
    	} catch (SQLException sqle)
    	{
    		sqle.printStackTrace();
    	} finally
    	{
    		// 关闭数据库操作对象
    		closeOperate(res, clbStmt, conn);
    	}
    }

**执行结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 1][]

# 3、调用存储函数 #

**示例：**通过调用存储函数，根据用户编号，获取用户姓名。

## 3.1 创建存储函数 ##

/*
        -- 存储函数：根据用户编号，获取用户姓名
        -- 输入参数：用户编号
        -- 返回结果：用户姓名
    */
    DROP FUNCTION IF EXISTS func_get_user_name;
    CREATE FUNCTION func_get_user_name(in_id INT)
    RETURNS VARCHAR(30)
    BEGIN
    	DECLARE out_name VARCHAR(30);
     
    	SELECT name INTO out_name FROM tb_user
    	WHERE id = in_id;
     
    	RETURN out_name;
    END;

## 3.2 调用存储函数 ##

/**
     * 调用存储函数
     * 
     * @author pan_junbiao
     */
    public static void execFunction()
    {
    	Connection conn = null; // 数据库连接对象
    	CallableStatement clbStmt = null; // CallableStatement对象
    	try
    	{
    		// 获取数据库连接
    		conn = getConnection();
    
    		// 创建CallableStatement对象
    		clbStmt = conn.prepareCall("{?=CALL func_get_user_name(?)}");
    
    		// 注册输出结果参数
    		clbStmt.registerOutParameter(1, Types.VARCHAR);
    
    		// 设置输入参数
    		clbStmt.setInt(2, 5);
    
    		// 执行调用存储函数
    		clbStmt.execute();
    
    		// 获取输出参数值
    		String userName = clbStmt.getString(1);
    		System.out.println("用户名称：" + userName);
    
    	} catch (SQLException sqle)
    	{
    		sqle.printStackTrace();
    	} finally
    	{
    		// 关闭数据库操作对象
    		closeOperate(null, clbStmt, conn);
    	}
    }

**执行结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70]: /images/20210724/97185b961547401aa41de1ebc6d13753.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 1]: /images/20210724/9ceac89fec3749e58b61adb2cd81f9a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 2]: /images/20210724/422afbab8f224a4da7244c2d9988c5f1.png