自学内容网 自学内容网
自学内容网 > 正文

JDBC 调用带输入和输出参数的存储过程

🕗 发布于 2024-11-22 06:48 jdbc  java  存储过程  

🧑 博主简介：CSDN博客专家，历代文学网（PC端可以访问：https://literature.sinhy.com/#/literature?__c=1000，移动端可微信小程序搜索“历代文学”）总架构师，15年工作经验，精通Java编程，高并发设计，Springboot和微服务，熟悉Linux，ESXI虚拟化以及云原生Docker和K8s，热衷于探索科技的边界，并将理论知识转化为实际应用。保持对新技术的好奇心，乐于分享所学，希望通过我的实践经历和见解，启发他人的创新思维。在这里，我希望能与志同道合的朋友交流探讨，共同进步，一起在技术的世界里不断学习成长。
技术合作请加本人wx（注明来自csdn）：foreast_sea

在这里插入图片描述

在这里插入图片描述

JDBC 调用带输入和输出参数的存储过程

引言

数据库操作是至关重要的一环。存储过程作为数据库中的一个强大功能，为我们提供了一种高效、安全且易于维护的数据库操作方式。它就像是数据库中的一个“小助手”，将一系列复杂的 SQL 语句封装在一起，形成一个可重复使用的单元。

存储过程的应用场景广泛得超乎想象。比如在企业级应用中，涉及到复杂的业务逻辑，如订单处理系统。当客户下单时，可能需要对库存进行检查、计算订单总价、更新用户购买历史等多个操作。如果将这些操作分散在应用程序代码中，每次执行订单操作都要发送多条 SQL 语句到数据库，这不仅增加了网络开销，还可能导致数据不一致性。而使用存储过程，我们可以将这些操作整合在一个存储过程中，应用程序只需简单地调用这个存储过程，大大提高了效率和数据的准确性。

再比如，在一个大型的人力资源管理系统中，员工薪资计算可能涉及到基本工资、绩效工资、奖金、扣除项等多个因素。通过存储过程，可以将薪资计算的逻辑封装起来，不同部门调用这个存储过程时，都能得到准确一致的结果，而且可以方便地在数据库端进行修改和优化，而无需在各个使用薪资计算功能的应用程序中逐个修改代码。

我们一起来探讨如何在 Java 应用程序中通过 JDBC 来调用带输入和输出参数的存储过程。这涉及到存储过程的定义、参数传递以及 JDBC 的一系列操作。

一、存储过程的基本概念、作用及意义

（一）基本概念

存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中。它就像是数据库中的一个自定义函数，用户可以通过指定存储过程的名称并传递参数（如果有）来执行它。存储过程可以包含数据操纵语句（如 SELECT、INSERT、UPDATE、DELETE）、变量定义、流程控制语句（如 IF、WHILE）等，是数据库编程的重要组成部分。

（二）作用和意义

  1. 提高性能
    当应用程序需要执行一系列数据库操作时，如果每次都从应用程序发送多条 SQL 语句到数据库，会产生较大的网络开销。而存储过程在数据库中已经预编译好，应用程序只需调用存储过程，减少了 SQL 语句的编译时间和网络传输成本，从而提高了数据库操作的速度。
  2. 增强安全性
    通过存储过程，可以限制用户对数据库表和列的直接访问。用户只能通过执行存储过程来间接操作数据，这样可以隐藏数据库的具体结构和实现细节。例如，只允许用户通过特定的存储过程来修改敏感数据，而不允许直接对表进行更新操作，从而防止用户误操作或恶意篡改数据。
  3. 保证数据一致性
    在复杂的业务场景中，多个相关的数据库操作需要作为一个整体来执行。例如，在银行转账业务中，需要从一个账户扣除金额并在另一个账户增加金额，这两个操作必须同时成功或同时失败，以保证数据的一致性。将这些操作封装在存储过程中，可以通过数据库的事务机制来确保这些操作的原子性，避免出现数据不一致的情况。
  4. 易于维护和复用
    如果业务逻辑发生变化，只需要在数据库中修改存储过程的代码，而不需要在每个使用该逻辑的应用程序中进行修改。而且，存储过程可以在不同的应用程序中被复用，提高了代码的复用性和可维护性。

（三）使用场景

  1. 复杂业务逻辑处理
    如前面提到的订单处理、薪资计算等业务场景，涉及到多个数据库操作和复杂的业务规则，使用存储过程可以将这些逻辑封装起来，使应用程序代码更加简洁清晰。
  2. 数据批量处理
    当需要对大量数据进行相同类型的操作时，例如批量更新客户信息、批量插入订单数据等，可以使用存储过程来提高处理效率。存储过程可以利用数据库的高效数据处理能力，减少应用程序与数据库之间的交互次数。
  3. 数据验证和转换
    在数据插入或更新之前，可以在存储过程中对数据进行验证和转换。例如，检查用户输入的日期格式是否正确，对密码进行加密处理等。这样可以保证数据的质量和安全性，并且将数据处理逻辑集中在数据库端，便于管理和维护。

二、存储过程定义的基本格式（以 PostgreSQL 为例）

（一）创建存储过程的基本语法

在 PostgreSQL 中，创建存储过程使用 CREATE OR REPLACE FUNCTION 语句（在 PostgreSQL 中，函数和存储过程在很多情况下可以类似地看待，这里我们主要讨论具有输入和输出参数的情况）。

基本格式如下：

CREATE OR REPLACE FUNCTION function_name (
    -- 输入参数定义
    input_parameter1 data_type1,
    input_parameter2 data_type2,
    -- 更多输入参数...
    -- 输出参数定义，使用 OUT 关键字
    OUT output_parameter1 data_type3,
    OUT output_parameter2 data_type4
) RETURNS record AS
$$
-- 存储过程体，这里是 SQL 语句和流程控制语句等
BEGIN
    -- 在这里编写存储过程的具体逻辑
    -- 可以使用输入参数进行计算和操作
    -- 并将结果赋值给输出参数
END;
$$ LANGUAGE plpgsql;
  1. 输入参数定义
    输入参数在括号内定义，格式为 parameter_name data_type，其中 parameter_name 是参数的名称，data_type 是参数的数据类型。例如，如果要定义一个名为 input_id 的整数类型输入参数，可以写成 input_id integer。可以有多个输入参数，每个参数之间用逗号分隔。
  2. 输出参数定义
    输出参数使用 OUT 关键字定义，格式与输入参数类似。例如，OUT output_result varchar 定义了一个名为 output_result 的字符串类型输出参数。输出参数的值在存储过程体中通过赋值语句来设置。
  3. 存储过程体
    存储过程体在 BEGIN 和 END 之间。在存储过程体中，可以使用标准的 SQL 语句和 PostgreSQL 特有的流程控制语句（如 IF - THEN - ELSE、LOOP、WHILE 等）。可以使用输入参数来执行查询、更新等操作，并将结果赋值给输出参数。例如：
CREATE OR REPLACE FUNCTION get_employee_info (
    input_employee_id integer,
    OUT employee_name varchar,
    OUT department_name varchar
) RETURNS record AS
$$
BEGIN
    SELECT name, department INTO employee_name, department_name
    FROM employees
    WHERE id = input_employee_id;
END;
$$ LANGUAGE plpgsql;

这个存储过程接受一个整数类型的输入参数 input_employee_id，并通过查询 employees 表获取对应的员工姓名和部门名称，将结果赋值给两个输出参数 employee_name 和 department_name。

（二）数据类型支持

PostgreSQL 支持多种数据类型，包括但不限于：

  1. 基本数据类型
    • 整数类型：integer、smallint、bigint 等，用于存储整数值。例如，employee_id integer 可以用来存储员工的编号。
    • 浮点类型：real、double precision 等，用于存储带有小数的数值，如商品价格可以用 price double precision 来存储。
    • 字符类型：varchar(n)、char(n)，其中 n 是字符长度。varchar 类型是可变长度字符类型，char 类型是固定长度字符类型。例如，employee_name varchar(50) 可以用来存储员工姓名。
    • 日期和时间类型：date、time、timestamp 等。date 用于存储日期，time 用于存储时间，timestamp 可以同时存储日期和时间，并且具有更高的精度。例如，hire_date date 可以用来存储员工的入职日期。
  2. 复杂数据类型
    • 数组类型：可以定义各种类型的数组，如 integer[] 表示整数数组。例如，scores integer[] 可以用来存储学生的考试成绩数组。
    • 复合类型：可以自定义包含多个字段的复合类型，类似于 C 语言中的结构体。例如，可以创建一个包含员工姓名和工资的复合类型，然后在存储过程中使用。
    • 枚举类型：通过 CREATE TYPE 语句创建枚举类型，用于存储一组预定义的值。例如，可以创建一个表示员工职位等级的枚举类型（如 junior、mid_level、senior），然后在存储过程中使用该类型的参数。

三、JDBC 调用带输入和输出参数的存储过程的基本步骤

（一）加载数据库驱动

在使用 JDBC 调用存储过程之前，首先需要加载相应的数据库驱动。对于 PostgreSQL，通常使用 org.postgresql.Driver。可以使用以下代码加载驱动：

try {
    Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

这一步是告诉 Java 应用程序使用哪个数据库驱动来连接数据库。如果驱动类没有在类路径中找到，会抛出 ClassNotFoundException。

（二）建立数据库连接

使用 DriverManager.getConnection() 方法建立与数据库的连接。需要提供数据库的 URL、用户名和密码。例如，对于 PostgreSQL，连接 URL 的格式通常为 jdbc:postgresql://host:port/database_name，其中 host 是数据库服务器的地址，port 是数据库端口（PostgreSQL 默认端口是 5432），database_name 是要连接的数据库名称。代码示例如下：

String url = "jdbc:postgresql://localhost:5432/mydb";
String username = "myuser";
String password = "mypassword";
Connection connection = null;
try {
    connection = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
    e.printStackTrace();
}

这一步创建了一个与数据库的连接对象，如果连接失败，会抛出 SQLException。

（三）创建 CallableStatement 对象

使用连接对象的 prepareCall() 方法创建 CallableStatement 对象，用于调用存储过程。在创建 CallableStatement 时，需要指定存储过程的调用语法，其格式如下：

CallableStatement callableStatement = connection.prepareCall("{call stored_procedure_name(?,?)}");

其中 stored_procedure_name 是要调用的存储过程的名称，括号内的问号表示存储过程的参数。如果存储过程有输入参数，需要在后续步骤中设置这些参数的值；如果有输出参数，需要注册输出参数的数据类型。

（四）设置输入参数（如果有）

对于每个输入参数，使用 CallableStatement 对象的 setXXX() 方法来设置参数的值，其中 XXX 表示参数的数据类型。例如，如果输入参数是整数类型，可以使用 setInt() 方法；如果是字符串类型，可以使用 setString() 方法。参数的索引从 1 开始。假设存储过程有两个输入参数，一个是整数类型的 input_id，另一个是字符串类型的 input_name，代码示例如下：

callableStatement.setInt(1, 123); // 设置第一个输入参数的值
callableStatement.setString(2, "John Doe"); // 设置第二个输入参数的值

（五）注册输出参数（如果有）

对于每个输出参数，使用 CallableStatement 对象的 registerOutParameter() 方法来注册输出参数的数据类型。参数的索引和设置输入参数时一样，从 1 开始。例如，如果输出参数是整数类型，可以使用 registerOutParameter(1, Types.INTEGER)；如果是字符串类型，可以使用 registerOutParameter(1, Types.VARCHAR)。假设存储过程有两个输出参数，一个是整数类型的 output_result1，另一个是字符串类型的 output_result2，代码示例如下：

callableStatement.registerOutParameter(1, Types.INTEGER);
callableStatement.registerOutParameter(2, Types.VARCHAR);

（六）执行存储过程

使用 CallableStatement 对象的 execute() 方法执行存储过程。代码示例如下：

callableStatement.execute();

这一步会触发数据库执行存储过程，并根据输入参数的值进行计算，将结果存储在输出参数中（如果有）。

（七）获取输出参数的值（如果有）

使用 CallableStatement 对象的 getXXX() 方法获取输出参数的值，其中 XXX 表示输出参数的数据类型。参数的索引和前面步骤一样，从 1 开始。例如，如果要获取整数类型的输出参数的值，可以使用 getInt() 方法；如果是字符串类型，可以使用 getString() 方法。代码示例如下：

int outputValue1 = callableStatement.getInt(1);
String outputValue2 = callableStatement.getString(2);

（八）关闭资源

在完成存储过程的调用后，需要关闭 CallableStatement 对象和数据库连接，以释放资源。可以使用以下代码：

try {
    if (callableStatement!= null) {
        callableStatement.close();
    }
    if (connection!= null) {
        connection.close();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

四、JDBC调用存储过程完整示例

（一）存储过程场景（以员工薪资计算为例）

假设我们有一个员工表 employees，包含员工编号（employee_id）、基本工资（base_salary）、绩效评分（performance_score），我们要创建一个存储过程来根据绩效评分计算员工的实际薪资（实际薪资 = 基本工资 * 绩效系数，绩效系数根据绩效评分来确定），并返回员工编号和实际薪资。

CREATE OR REPLACE FUNCTION calculate_employee_salary (
    input_employee_id integer,
    OUT output_employee_id integer,
    OUT actual_salary numeric
) RETURNS record AS
$$
DECLARE
    base_salary numeric;
    performance_score integer;
    performance_factor numeric;
BEGIN
    -- 根据员工编号查询基本工资和绩效评分
    SELECT base_salary, performance_score INTO base_salary, performance_score
    FROM employees
    WHERE employee_id = input_employee_id;

    -- 根据绩效评分确定绩效系数
    IF performance_score >= 90 THEN
        performance_factor := 1.2;
    ELSIF performance_score >= 80 AND performance_score < 90 THEN
        performance_factor := 1.1;
    ELSIF performance_score >= 70 AND performance_score < 80 THEN
        performance_factor := 1.0;
    ELSIF performance_score >= 60 AND performance_score < 70 THEN
        performance_factor := 0.9;
    ELSE
        performance_factor := 0.8;
    END IF;

    -- 计算实际薪资
    actual_salary := base_salary * performance_factor;
    output_employee_id := input_employee_id;
END;
$$ LANGUAGE plpgsql;

（二）JDBC 调用存储过程

以下是使用 JDBC 调用上述存储过程的 Java 代码示例：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class StoredProcedureExample {
    public static void main(String[] args) {
        // 数据库连接 URL
        String url = "jdbc:postgresql://localhost:5432/mydb";
        // 数据库用户名
        String username = "myuser";
        // 数据库密码
        String password = "mypassword";

        try {
            // 加载数据库驱动
            Class.forName("org.postgresql.Driver");
            // 建立数据库连接
            Connection connection = DriverManager.getConnection(url, username, password);

            // 创建 CallableStatement 对象，调用存储过程
            CallableStatement callableStatement = connection.prepareCall("{call calculate_employee_salary(?)}");

            // 设置输入参数，这里假设要查询员工编号为 1001 的薪资
            callableStatement.setInt(1, 1001);

            // 注册输出参数，第一个输出参数是员工编号（整数类型），第二个输出参数是实际薪资（数值类型）
            callableStatement.registerOutParameter(1, Types.INTEGER);
            callableStatement.registerOutParameter(2, Types.NUMERIC);

            // 执行存储过程
            callableStatement.execute();

            // 获取输出参数的值
            int employeeId = callableStatement.getInt(1);
            double salary = callableStatement.getDouble(2);
            System.out.println("Employee ID: " + employeeId + ", Salary: " + salary);

            // 关闭资源
            callableStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在这个示例中，首先加载了 PostgreSQL 数据库驱动，然后建立了与数据库的连接。接着创建了 CallableStatement 对象来调用 calculate_employee_salary 存储过程，设置了输入参数（员工编号），注册了输出参数（员工编号和实际薪资），执行存储过程后获取并打印了输出参数的值，最后关闭链接。


原文地址：https://blog.csdn.net/lilinhai548/article/details/143852819

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024