目录
一、什么是存储过程?
二、MySQL 中的存储过程代码演示
(一)不带参数的存储过程
(二)带参数的存储过程
三、在SpringBoot+Mybatis项目中使用过程存储
完整代码示例
一、什么是存储过程?
存储过程(Stored Procedure)是数据库中的一种可编程对象,它是一组为了完成特定功能的SQL语句集合,存储在数据库中并以名称标识。存储过程可以接收输入参数,返回输出参数,并在服务器端运行。这种方式提高了代码的复用性和性能,因为多次执行只需要发送调用命令,而不是多次传输SQL语句。
存储过程的优点包括:
- 性能优化:由于存储过程在数据库端运行,减少了网络通信。
- 代码复用:可以定义复杂的逻辑并重复使用。
- 安全性:通过限制访问权限,可以防止直接访问底层表。
- 简化复杂性:封装复杂的SQL逻辑,提高代码的可读性。
二、MySQL 中的存储过程代码演示
下面是一个完整的存储过程创建、调用和删除的示例:
(一)不带参数的存储过程
1. 创建示例数据库和表
CREATE DATABASE IF NOT EXISTS example_db;
USE example_db;CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100),salary DECIMAL(10, 2),department VARCHAR(50)
);-- 插入一些测试数据
INSERT INTO employees (name, salary, department)
VALUES
('Alice', 5000.00, 'HR'),
('Bob', 7000.00, 'IT'),
('Charlie', 6500.00, 'Finance');
2. 创建存储过程
需求:创建一个存储过程,目的是,根据部门名称查询员工信息。
DELIMITER $$CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50) -- 输入参数,部门名称
)
BEGIN-- 查询指定部门的员工SELECT id, name, salary, departmentFROM employeesWHERE department = dept_name;
END $$DELIMITER ;
说明:
DELIMITER $$
:更改语句结束符,防止存储过程定义中的分号冲突。IN dept_name VARCHAR(50)
:定义输入参数
,表示调用时需要提供部门名称。CALL GetEmployeesByDepartment('IT');
SELECT ...
:存储过程的主体逻辑。
3. 调用存储过程
使用 CALL
关键字调用存储过程。
CALL GetEmployeesByDepartment('IT');
输出示例:
+----+------+--------+------------+
| id | name | salary | department |
+----+------+--------+------------+
| 2 | Bob | 7000.00| IT |
+----+------+--------+------------+
(二)带参数的存储过程
需求:计算某个部门的员工平均工资,并通过输出参数返回。
DELIMITER $$CREATE PROCEDURE GetAverageSalaryByDepartment(IN dept_name VARCHAR(50), -- 输入参数OUT avg_salary DECIMAL(10, 2) -- 输出参数
)
BEGIN-- 计算平均工资SELECT AVG(salary) INTO avg_salaryFROM employeesWHERE department = dept_name;
END $$DELIMITER ;
调用存储过程并获取结果:
SET @avg_salary = 0; -- 定义变量
CALL GetAverageSalaryByDepartment('HR', @avg_salary);
SELECT @avg_salary AS AverageSalary; -- 查看结果
输出示例:
+---------------+
| AverageSalary |
+---------------+
| 5000.00 |
+---------------+
5. 删除存储过程
如果需要删除存储过程,可以使用 DROP PROCEDURE
语句。
DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;
DROP PROCEDURE IF EXISTS GetAverageSalaryByDepartment;
总结
存储过程是数据库中一个强大的工具,它封装了复杂的业务逻辑,提高了性能和代码复用性。MySQL 的存储过程支持输入参数、输出参数和逻辑控制语句,可以满足多种业务需求。在设计存储过程时需要注意性能优化和安全性,避免过度复杂化导致难以维护。
三、在SpringBoot+Mybatis项目中使用过程存储
详细步骤如下:
-
存储过程的定义 存储过程在 MySQL 数据库中定义并创建。建议使用 SQL 脚本直接在数据库中执行,而不是通过 MyBatis 的
<sql>
标签动态创建。 -
Mapper XML 配置 在 Mapper XML 中,通过
CALLABLE
类型调用存储过程,并为输入和输出参数明确指定jdbcType
。 -
DAO 层 使用
Map
或单独参数的方式传递输入和输出参数。Map
是一种更灵活的方式。 -
Service 层 在 Service 层封装调用逻辑,并处理业务逻辑或错误情况。
-
Controller 层 接收前端请求并调用 Service 层,将结果返回给前端。
完整代码示例
1. 存储过程定义
存储过程需要直接在数据库中创建:
DELIMITER $$CREATE PROCEDURE GetFaceCollectedCount(IN input_community_id INT, -- 输入参数:社区IDOUT face_collected_count INT, -- 输出参数:已采集人脸的居民人数OUT total_count INT -- 输出参数:社区居民人数
)
BEGIN-- 查询社区居民总人数SELECT COUNT(*) INTO total_countFROM personWHERE community_id = input_community_id;-- 查询已采集人脸的居民人数SELECT COUNT(*) INTO face_collected_countFROM personWHERE community_id = input_community_id AND state = 2;
END $$DELIMITER ;
代码含义:
- 输入参数
input_community_id
:指定目标社区的 ID。 - 输出参数
total_count
:存储该社区中居民的总人数。 - 输出参数
face_collected_count
:存储该社区中已缴采集人脸信息的居民人数。 - 逻辑:
- 第一部分统计符合条件的总人数,并赋值给
total_count
。 - 第二部分统计符合条件且
state = 2
的人数(表示人脸采集已完成的居民)。
- 第一部分统计符合条件的总人数,并赋值给
作用:这是在数据库层定义的逻辑,用于处理社区相关统计。
2. Mapper XML 配置
配置用于调用‘存储过程’的sql语句:
注意 statementType="CALLABLE"
是必须的,否则 MyBatis 无法正确调用存储过程。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.CommunityMapper"><!-- 调用存储过程 --><select id="getFaceCollectedCount" statementType="CALLABLE">CALL GetFaceCollectedCount(#{input_community_id, mode=IN, jdbcType=INTEGER},#{face_collected_count, mode=OUT, jdbcType=INTEGER},#{total_count, mode=OUT, jdbcType=INTEGER})</select></mapper>
具体说明
-
jdbcType
的必要性:- MyBatis 在处理存储过程时,对于
OUT
参数,需要知道参数的 JDBC 类型。 - 常用的 JDBC 类型包括
INTEGER
、VARCHAR
、DECIMAL
等,具体类型需要与你的存储过程输出参数的实际类型一致。
- MyBatis 在处理存储过程时,对于
-
mode
属性:IN
表示输入参数。OUT
表示输出参数。
-
完整参数说明:
#{input_community_id, mode=IN, jdbcType=INTEGER}
:指定输入参数的 JDBC 类型。#{face_collected_count, mode=OUT, jdbcType=INTEGER}
:指定输出参数face_collected_count
的 JDBC 类型。#{total_count, mode=OUT, jdbcType=INTEGER}
:指定输出参数total_count
的 JDBC 类型。
3. DAO 接口
定义 Mapper 接口:
package com.example.mapper;import java.util.Map;public interface CommunityMapper {/*** 调用存储过程获取人脸采集和总人数** @param resultMap 包含输入参数和输出参数的 Map*/void getFaceCollectedCount(Map<String, Integer> resultMap);
}
代码含义:
- 参数:
- 使用
Map<String, Integer>
作为输入和输出容器。 resultMap
中包含输入参数(如input_community_id
)和输出参数(如face_collected_count
和total_count
)。
- 使用
- Map 的作用:
- MyBatis 会通过
Map
的键名找到与存储过程占位符相对应的参数。 - 输入参数会从
Map
中读取,存储过程的输出参数会写回到Map
中。
- MyBatis 会通过
作用:定义存储过程的调用方法,供 Service 层使用。
4. Service 层
封装存储过程的调用逻辑:
package com.example.service;import com.example.mapper.CommunityMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.HashMap;
import java.util.Map;@Service
public class CommunityService {@Autowiredprivate CommunityMapper communityMapper;/*** 调用存储过程** @param communityId 社区ID* @return 存储过程返回的数据*/public Map<String, Integer> getFaceCollectedCount(int communityId) {// 创建 Map 用于存储输入和输出参数Map<String, Integer> resultMap = new HashMap<>();resultMap.put("input_community_id", communityId); // 输入参数resultMap.put("face_collected_count", null); // 初始化输出参数resultMap.put("total_count", null);// 调用存储过程communityMapper.getFaceCollectedCount(resultMap);return resultMap;}
}
getFaceCollectedCount()
方法的含义:
- 输入:参数
communityId是
前端传入的社区 ID。 - 逻辑:
- 创建
resultMap
,初始化输入参数(input_community_id
)和输出参数(face_collected_count
、total_count
)。 - 调用 Mapper 的
getFaceCollectedCount
方法,执行存储过程,resultMap
会更新输出参数的值。
- 创建
- 输出:返回包含存储过程结果的
Map
。
作用:封装业务逻辑,为 Controller 层提供服务。
5. Controller 层
处理前端请求并返回结果:
package com.example.controller;import com.example.service.CommunityService;
import com.example.utils.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import java.util.Map;@RestController
@RequestMapping("/community")
public class CommunityController {@Autowiredprivate CommunityService communityService;/*** 调用存储过程** @param id 前端传入的社区ID数组* @return 存储过程的返回结果*/@PostMapping("/procedure")public Result procedure(@RequestBody Integer[] id) {int communityId = id[0];System.out.println("Community ID: " + communityId);Map<String, Integer> resMap = communityService.getFaceCollectedCount(communityId);if (resMap.get("face_collected_count") == null || resMap.get("total_count") == null) {return Result.error("存储过程调用失败");}return Result.ok().put("data", resMap);}
}
6. 前端请求示例
返回示例:
{ "code": 200, "message": "成功","data": { "face_collected_count": 10, "total_count": 50 }
}
关键点说明
-
参数名与 MyBatis XML 配置一致:
resultMap
中的键名(如face_collected_count
和total_count
)必须与 MyBatis XML 文件中的参数名称一致。 -
jdbcType
是必需的: 在 MyBatis 中调用存储过程时,jdbcType
是输出参数的必填项,必须与数据库字段类型匹配。 -
Map 的使用: Map 是一种灵活的方式,用于传递输入参数和接收输出参数,适用于存储过程的复杂调用。
-
Service 层封装逻辑: Service 层处理存储过程调用的逻辑,并将异常和错误信息统一处理,提高代码的可维护性。