JDBC连着连着连接池、事务、批处理都玩过一轮后DAY04最容易被问到的就是到底要不要在Java里调用MySQL存储过程和存储函数我的回答通常是如果你的业务规则足够稳定、改动频率不高JDBC调用存储过程是一把非常趁手的工具如果只是写普通CRUD那大概率你不需要折腾。这一篇我会把“调用存储过程、存储函数”这整条链路拆开从MySQL侧建存储过程到Java侧用CallableStatement调用再到结果集、输出参数、多结果集、常见坑点全部过一遍。不管你是刚学到JDBC的新手还是被项目里“存储过程Java”整得头疼的同学这篇都能给你一份能直接抄作业的参考答案。1. 存储过程这玩意儿为什么非要在JDBC里调1.1 存储过程与存储函数一句话分清很多同学容易把存储过程和存储函数混在一起其实最核心的区别只有三个存储过程用CALL调用存储函数用SELECT调用或者表达式引用存储过程没有返回值只能通过OUT/INOUT参数往回带数据存储函数必须有返回值存储函数适合做“输入一个值、输出一个值”的计算存储过程适合做“一段带流程控制的多步SQL操作”。拿MySQL举例一个存储过程可以包含多条SQL、循环、判断、异常处理而存储函数只能返回单个值不允许使用SELECT直接返回结果集。这个区别直接在JDBC调用时也有体现存储函数用{? CALL func(?)}这种语法存储过程用{CALL proc(?,?)}。1.2 JDBC里不直接拼SQL非要用存储过程图什么以前我也嫌存储过程麻烦Java里写好SQL不香吗后来在几个项目里被教育了几次才真正理解为什么要用存储过程。第一个好处是减少网络往返。有些业务逻辑动不动就五六条SQL比如订单支付要查余额、扣库存、写流水、更新订单状态。如果每步都在Java里单独发一个SQL一次操作可能要4到6个网络往返全部装进存储过程客户端只需要发一次调用请求MySQL内部顺序执行完再一次性返回。内网环境不太明显但跨机房、跨网络时差距立刻出来了。第二个好处是统一逻辑入口。同一个“支付”流程可能在Java端、报表端、定时任务里都会用到如果把逻辑写在存储过程里谁调都是同一份逻辑否则同一套业务规则分散在多个系统的代码里维护成本翻倍。第三个好处是权限控制。我们可以让Java应用只连一个低权限账号只允许它执行存储过程不允许直接操作表。这样即使Web层被入侵或者业务代码写错也摸不到底层表结构。当然这些都是“能用”的必要条件不是“必须用”的铁律。到底什么时候用我会留到最后一节再讲。2. 写Java之前先把MySQL侧的存储过程建好2.1 先造一张能用的表为了说明白咱们用电商最常见的两张表用户表和订单表。CREATE DATABASE IF NOT EXISTS demo_jdbc DEFAULT CHARSET utf8mb4; USE demo_jdbc; CREATE TABLE t_user ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, register_time DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE t_order ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME DEFAULT CURRENT_TIMESTAMP ); INSERT INTO t_user (username) VALUES (张三), (李四), (王五); INSERT INTO t_order (user_id, amount, status) VALUES (1, 99.50, 1), (1, 39.00, 0), (2, 199.00, 1), (2, 59.00, 1), (3, 1000.00, 0);这里的t_user是用户主数据t_order是订单流水。后面所有的存储过程都用这两张表。2.2 建一个带IN和OUT参数的存储过程现在建一个“根据用户ID统计订单总金额和订单数量”的存储过程输入用户ID输出两个值订单总数、总金额。USE demo_jdbc; DROP PROCEDURE IF EXISTS get_order_stat_by_user; DELIMITER $$ CREATE PROCEDURE get_order_stat_by_user( IN p_user_id INT, OUT p_order_count INT, OUT p_total_amount DECIMAL(10,2) ) BEGIN SELECT COUNT(*), IFNULL(SUM(amount), 0.00) INTO p_order_count, p_total_amount FROM t_order WHERE user_id p_user_id; END$$ DELIMITER ;要注意MySQL存储过程的参数顺序非常关键JDBC端的参数索引是从1开始的而IN参数索引和OUT参数索引都参与排序。这个过程中p_user_id是第1个p_order_count是第2个p_total_amount是第3个。后面Java注册输出参数时必须按这个顺序注册第2和第3个不能错位。另外我的个人习惯是在存储过程里尽量用IFNULL处理聚合结果。为什么因为COUNT本身不会返回NULL但SUM在没有匹配行时一定返回NULL如果JDBC端直接getBigDecimal一个NULL很容易拿到NullPointerException。所以我在SUM外面套了IFNULLMySQL侧就把默认值兜住了。2.3 建一个返回单值的存储函数存储函数必须返回一个值我造一个“根据用户ID返回用户名”的函数。USE demo_jdbc; DROP FUNCTION IF EXISTS get_username_by_id; DELIMITER $$ CREATE FUNCTION get_username_by_id(p_user_id INT) RETURNS VARCHAR(50) DETERMINISTIC BEGIN DECLARE v_username VARCHAR(50); SELECT username INTO v_username FROM t_user WHERE id p_user_id; RETURN v_username; END$$ DELIMITER ;这里RETURNS VARCHAR(50)决定了JDBC侧要用getString来接。DECLARE在函数里定义一个变量SELECT INTO把查到的用户名塞进去最后RETURN。补充一点如果函数体里只有一条SQLMySQL允许省略BEGIN END直接写成RETURN (SELECT username FROM t_user WHERE id p_user_id);。但多语句场景必须写BEGIN END所以建议一开始就养成带BEGIN END的习惯。2.4 MySQL 8下必须留神的一个开关MySQL的存储函数有一个很经典的坑当bin_log开启也就是默认开启二进制日志的情况下如果函数里面包含数据修改语句或者函数不是DETERMINISTIC / NO SQL / READS SQL DATAMySQL会拒绝创建报错类似于This function has none of DETERMINISTIC, NO SQL...。JDBC调用时会直接抛SQLException。解决办法有两种看场景选。第一种如果当前函数确实不修改数据就在函数里声明DETERMINISTIC或者READS SQL DATA就像我上面写的。第二种如果公司的数据库允许可以执行SET GLOBAL log_bin_trust_function_creators 1;但注意这个变量是全局的对生产库影响面很大有DBA就找DBA评估不要自己随手开。重点存储函数中如果使用了查询必须明确声明DETERMINISTIC、NO SQL或READS SQL DATA否则MySQL会因为二进制日志校验拒绝创建。3. JDBC调用存储过程把接口和数据对起来3.1 写一个最小可用的JDBC调用框架先看Java侧怎么准备。如果是老项目用DriverManager直接获取连接如果是Spring项目连接池拿到的Connection一样只是不需要手动关连接。这里我写一个最简单的演示版本方便看到全貌。import java.sql.*; public class CallProcedureExample { static final String URL jdbc:mysql://localhost:3306/demo_jdbc?useSSLfalseserverTimezoneAsia/ShanghaiallowPublicKeyRetrievaltrue; static final String USER root; static final String PASSWORD your_password; public static void main(String[] args) { try (Connection conn DriverManager.getConnection(URL, USER, PASSWORD)) { // 调用带OUT参数的存储过程 String sql {CALL get_order_stat_by_user(?, ?, ?)}; try (CallableStatement cstmt conn.prepareCall(sql)) { cstmt.setInt(1, 1); // IN 参数 cstmt.registerOutParameter(2, Types.INTEGER); // OUT 参数 cstmt.registerOutParameter(3, Types.DECIMAL); // OUT 参数 cstmt.execute(); int orderCount cstmt.getInt(2); BigDecimal totalAmount cstmt.getBigDecimal(3); System.out.println(订单数量: orderCount); System.out.println(订单总金额: totalAmount); } } catch (SQLException e) { e.printStackTrace(); } } }代码不长但有两个细节值得讲。第一个细节是prepareCallJDBC中调用存储过程不能使用prepareStatement({CALL ...})虽然这样写也能运行但规范是用prepareCall因为驱动会针对CallableStatement做特殊处理包括提前注册OUT参数等。第二个细节是registerOutParameter注册的参数类型必须和MySQL存储过程里定义的参数类型在同一组映射范围内。MySQL的INT对接Types.INTEGERDECIMAL(10,2)对接Types.DECIMAL。如果注册成Types.VARCHAR再拿去取数值有些驱动会警告结果也可能异常。3.2 存储过程返回结果集时该怎么拿存储过程并不一定只有OUT参数很多时候它还直接返回一个ResultSet。比如我改造一个过程根据用户ID返回该用户的全部订单数据。USE demo_jdbc; DROP PROCEDURE IF EXISTS get_orders_by_user; DELIMITER $$ CREATE PROCEDURE get_orders_by_user(IN p_user_id INT) BEGIN SELECT id, user_id, amount, status, create_time FROM t_order WHERE user_id p_user_id ORDER BY create_time DESC; END$$ DELIMITER ;JDBC调用时不需要注册OUT参数执行后直接用getResultSet()拿第一个结果集。String sql {CALL get_orders_by_user(?)}; try (CallableStatement cstmt conn.prepareCall(sql)) { cstmt.setInt(1, 1); boolean hasResult cstmt.execute(); if (hasResult) { try (ResultSet rs cstmt.getResultSet()) { while (rs.next()) { int orderId rs.getInt(id); BigDecimal amount rs.getBigDecimal(amount); int status rs.getInt(status); System.out.println(订单ID: orderId 金额: amount 状态: status); } } } }这里有一个新手最容易被坑的点调用存储过程获取结果集不要直接while(rs.next())要先看execute()返回的布尔值。如果为true代表第一个结果是ResultSet如果为false代表第一个结果是更新计数或者没有结果。虽然大多数情况下存储过程都会返回结果集但万一有分支语句导致第一次execute()没有返回结果集程序就会漏掉。3.3 一个过程返回多个结果集怎么办复杂一点的情况是存储过程里先查列表再查统计最后返回两个结果集。USE demo_jdbc; DROP PROCEDURE IF EXISTS get_orders_and_totals; DELIMITER $$ CREATE PROCEDURE get_orders_and_totals(IN p_user_id INT) BEGIN SELECT id, amount, status FROM t_order WHERE user_id p_user_id; SELECT COUNT(*) AS cnt, IFNULL(SUM(amount), 0) AS total FROM t_order WHERE user_id p_user_id; END$$ DELIMITER ;Java侧要循环获取String sql {CALL get_orders_and_totals(?)}; try (CallableStatement cstmt conn.prepareCall(sql)) { cstmt.setInt(1, 1); boolean hasResult cstmt.execute(); while (true) { if (hasResult) { try (ResultSet rs cstmt.getResultSet()) { while (rs.next()) { // 第一个结果集订单明细 } } } else { int updateCount cstmt.getUpdateCount(); if (updateCount -1) { break; // 没有更多结果 } // 如果是 UPDATE/INSERT 的更新数可以做日志 } hasResult cstmt.getMoreResults(); } }getMoreResults()会关闭当前打开的ResultSet并移动到下一个结果集所以ResultSet没有必要再手动关闭但我习惯还是放在try-with-resources里。这样循环直到hasResult false且getUpdateCount() -1说明所有结果都读完了。多结果集在报表类场景特别常见第一个结果集放明细列表第二个结果集放统计合计一次数据库往返全部拿回Java端。虽然SQL也能用UNION拼接但带不同列宽不同含义的表用多结果集更清晰。4. JDBC调用存储函数参数少但返回值别接错4.1 语法差异只有一处但很容易记混调用存储函数的标准JDBC语法是{? CALL 函数名(?, ?)}注意这里有三个符号问号、等号、CALL。第一个问号是函数返回值所在位置后面跟着的才是函数入参。和存储过程相比存储函数的参数索引多了第0位或者第1位的问题不同的驱动处理不一样。为了兼容性最好统一理解{? CALL func(?)}里等号左边的?是返回值注册时使用registerOutParameter(1, Types.VARCHAR)而函数的入参从第2个索引开始。但有些老驱动或特殊配置对索引的定义不同稳妥做法是在写完后先在本地JUnit里跑一下。4.2 存储函数完整调用示例接着用之前那个get_username_by_id函数。String sql {? CALL get_username_by_id(?)}; try (CallableStatement cstmt conn.prepareCall(sql)) { cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setInt(2, 1); cstmt.execute(); String username cstmt.getString(1); System.out.println(用户名: username); }这段代码里registerOutParameter(1, Types.VARCHAR)对应函数的RETURNS VARCHAR(50)。返回值类型是字符串就必须用getString读取。如果函数返回DECIMAL却用getInt去取结果虽然没有编译错误但可能会丢精度或者拿到0。有一点值得注意MySQL的函数可以通过SELECT get_username_by_id(1)直接查但JDBC的Statement.executeQuery(SELECT get_username_by_id(1))其实也能拿返回值。那我为什么推荐用CallableStatement主要原因是参数绑定。SELECT get_username_by_id(1)这种方式如果改成字符串拼接很容易引入SQL注入风险。用CallableStatement可以做到参数预编译调用函数的过程和调用存储过程一样安全。另外以后函数参数多了{? CALL func(?,?,?)}也比拼接可维护得多。4.3 函数返回NULL时Java侧怎么优雅处理存储函数非常容易出现NULL返回值比如用户ID不存在SELECT username INTO会没有赋值函数返回NULL。Java端如果直接getString返回的是null后续业务处理要注意空指针。处理思路有两个。最好的方案在MySQL侧在函数里用IFNULL或者COALESCE把默认值兜住CREATE FUNCTION get_username_by_id_safe(p_user_id INT) RETURNS VARCHAR(50) DETERMINISTIC BEGIN RETURN COALESCE( (SELECT username FROM t_user WHERE id p_user_id), UNKNOWN ); END$$Java侧处理则是另一个思路读到null之后判断一下再给默认值。String username cstmt.getString(1); if (username null) { username 默认用户; }这两种方式各有利弊MySQL侧兜底适合“返回值一定要有默认值”的业务规则Java侧兜底适合把“默认值策略”留给上层业务决定。我建议核心逻辑尽量放在数据库侧因为不管谁来用这个函数行为都是一致的。5. 踩坑指南JDBC 存储过程的高频翻车现场5.1 参数索引和类型映射是重灾区先说参数索引。存储过程里写了3个参数JDBC就一定要按顺序注册和设置。很多同学的报错是Parameter index out of range十有八九是registerOutParameter用的索引和存储过程定义对不上尤其是前面有多个IN参数时有人以为OUT参数从1开始实际从IN参数之后继续往后排。再说类型映射我列一个常用对照表。MySQL参数类型JDBC注册/获取类型说明INT / INTEGERTypes.INTEGER / getInt()注意范围大整数用BIGINTBIGINTTypes.BIGINT / getLong()主键常用VARCHAR / CHARTypes.VARCHAR / getString()中文要保证连接编码utf8mb4DECIMAL / NUMERICTypes.DECIMAL / getBigDecimal()金额必须用别用doubleDATETIME / TIMESTAMPTypes.TIMESTAMP / getTimestamp()时区要在URL里配serverTimezoneTEXTTypes.LONGVARCHAR / getString()有些驱动需要getString处理一个很隐蔽的坑是MySQL的DECIMAL(10,2)在驱动里映射到java.math.BigDecimal如果直接getDouble(3)去取金额可能在精度上出现微小的误差特别是在做汇总统计时。所以强烈建议金额类全部用getBigDecimal。5.2 空值、中文乱码和时区的坑空值的坑前面讲到了函数返回值其实存储过程也一样。比如你调用统计存储过程如果没有匹配的订单SUM(amount)返回NULLJava端getBigDecimal拿到null继续做加减计算就会出事。我建议在存储过程内部就处理好默认值Java侧拿到后也最好做一次判空。中文乱码这个坑根子不在存储过程而在JDBC URL。MySQL 8驱动默认使用utf8mb4但老连接串如果写着characterEncodingutf8某些特殊字符比如表情符号会直接乱码。连接串里尽量写成jdbc:mysql://localhost:3306/demo_jdbc?useUnicodetruecharacterEncodingutf8mb4serverTimezoneAsia/Shanghai时区问题就更常见了。MySQL的DATETIME没有时区概念但你如果连接串没指定serverTimezone驱动有时会拿本地默认时区去转换TIMESTAMP导致Java取到的时间差8个小时。我在生产环境遇到过几次这种“阴间Bug”所以在所有项目里都统一在URL里加serverTimezoneAsia/Shanghai。5.3 连接、事务、性能三件套不能少调用存储过程本质还是数据库连接上的一次执行要遵守三条纪律。第一条是连接不要裸奔。DriverManager.getConnection()只能用于学习或脚本真正的项目里一定要用连接池比如HikariCP、Druid。存储过程通常执行时间较长如果每次都新建连接连接建立的开销会吃掉存储过程省下的性能。第二条是事务边界要想清楚。存储过程内部可以写事务但如果你在Java侧已经开启了事务同时又希望在存储过程中提交就必须注意事务边界。默认情况下Java侧拿到Connection后调用存储过程中的COMMIT会直接影响当前连接的事务状态很容易把你的Java事务搞乱。我的习惯是事务控制在Java侧做存储过程内部只做数据操作不轻易写START TRANSACTION或COMMIT除非这个存储过程被设计成完全自治。第三条是性能监控。存储过程难调试的根源在于一条语句失控可能拖死整个数据库。建议在MySQL侧用EXPLAIN分析存储过程中的主要SQL再配合performance_schema去看调用高频过程有没有慢查询。平时只需要关注平均值和95分位不要被个别极端值带偏。6. 到底该不该把业务写进存储过程说说我的取舍6.1 适合用存储过程的场景根据我过去的项目经验适合用存储过程的场景往往有这几个特征业务逻辑涉及多条SQL并且需要保证整体一致性业务逻辑被多个系统或团队复用表结构和规则相对稳定迭代速度不快数据库连接网络质量不稳定减少往返意义重大。典型例子就是定时任务里的统计汇总、跨行转账、订单对账。这些操作如果用Java代码做要么写一大堆事务模板要么在多次网络调用里提心吊胆。6.2 不适合用存储过程的场景反过来如果你的团队里Java开发经验丰富、DBA资源有限或者业务规则每周都在变那存储过程就会成为维护成本黑洞。尤其是一旦出现几百行甚至上千行的存储过程新人上手会非常痛苦Git版本管理也不如Java代码方便测试也更难自动化。我个人的原则是超过10个参数的存储过程要警惕超过50行的存储过程必须写注释超过100行的存储过程就要考虑拆解。存储过程不是不能用而是要有纪律地用。6.3 和ORM框架配合的实践现在很多团队用MyBatis或者MyBatis PlusJDBC调用存储过程一样可以整合进去。MyBatis里通过Select注解或者XML的方式都可以调用。XML参数和CallableStatement是对应的比如select idcallOrderStat statementTypeCALLABLE {CALL get_order_stat_by_user( #{userId, modeIN, jdbcTypeINTEGER}, #{orderCount, modeOUT, jdbcTypeINTEGER}, #{totalAmount, modeOUT, jdbcTypeDECIMAL} )} /select映射类里要定义和OUT参数同名的属性调用后再从对象里取。MyBatis对存储过程的封装其实很薄底层还是JDBC那套所以你在JDBC层踩的坑在MyBatis里基本都会遇到理解了这一篇再去看框架文档就轻松得多。最后分享一个我自己的调试习惯我平时在写JDBC调用存储过程之前一定会先在MySQL命令行或者Navicat里手工调一遍。命令行就是CALL get_order_stat_by_user(1, cnt, total); SELECT cnt, total;看输出和预想是否一致。存储函数就SELECT get_username_by_id(1);先在数据库侧把SQL的正确性确认掉再到Java里写CallableStatement。这样排查问题时可以快速定位是SQL写得不对还是Java传参不对能省掉大量联调时间。这个习惯陪了我好几个项目每次都能在别人还在“查日志猜问题”的时候直接把范围缩小到一两行。你也可以试试至少不会一上来就拿着Java的异常栈在存储过程里大海捞针。