前言:
上一篇我们说了mysql里面的function()函数,结尾只是说了下它跟存储过程的简单区别,本文着重写下存储过程的定义以及使用。
正文:
闲言少叙,直奔主题:
1.存储过程定义
CREATE [definer = {user|current_user}] PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]]) [ characteristics..] routime_body
说明:
proc_parameter : [IN|OUT|INOUT] parameter_name type
其中IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出,param_name表示参数名称,type表示参数类型。
2.创建存储过程
存储过程完整代码如下:
DROP PROCEDURE IF EXISTS queryByGroupName; DELIMITER // CREATE PROCEDURE queryByGroupName(IN groupName VARCHAR(20)) BEGIN SELECT * FROM hydra_template_group where template_group_name like CONCAT('%',groupName); end // DELIMITER ;
说明:
大意就是查出所有
template_group_name以变量groupName结尾的记录。
在mysql调用存储过程查询 证明类 结尾的记录集合。
CALL queryByGroupName('证明类');
3.在SpringBoot项目里面调用:
创建TestExec.java
TestExec.java完整代码如下:
package com.bj.hydra.controller; import com.alibaba.fastjson.JSONObject; import com.bj.hydra.dto.StrutsDTO; import com.bj.hydra.entity.HydraFile; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.CallableStatementCallback; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import java.sql.CallableStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @package: com.bj.hydra.controller * @description: 测试存储过程调用 * @author: @IT讲坛 * @create: 2018-12-10 15:14 **/ @RestController @Slf4j @RequestMapping("/test") public class TestExec { @Autowired private JdbcTemplate jdbcTemplate; @PostMapping("/testExec") public List<HydraFile> getYxSelfHelpMsgDataListTest(@RequestParam("groupName") String groupName) { //带有输入值的例子 List result = (List) jdbcTemplate.execute( con -> { String storedProc = "{call queryByGroupName(?)}";// 调用的sql CallableStatement cs = con.prepareCall(storedProc); cs.setString(1, groupName);// 设置输入参数的值 //cs.registerOutParameter(2, JDBCType.REF_CURSOR);// 注册输出参数的类型 return cs; }, (CallableStatementCallback) cs -> { List<Map<String, Object>> list = new ArrayList<Map<String,Object>>(); // List<String> list = new ArrayList<>(); boolean exist = cs.execute(); ResultSet resultSet = cs.getResultSet(); ResultSetMetaData md = resultSet.getMetaData(); //获得结果集结构信息,元数据 int columnCount = md.getColumnCount(); //获得列数 if (exist) { while (resultSet.next()) { Map<String,Object> rowData = new HashMap<String,Object>(); for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), resultSet.getObject(i)); } log.info("rowdata:{}",JSONObject.toJSONString(rowData)); list.add(rowData); } } log.info("rowdata:{}",JSONObject.toJSONString(list)); return list; }); System.out.println("存储过程结果:"+result); return null; } }
4.启动boot项目,验证结果:
在postman里面调用url:
localhost:8088/hydra/test/testExec?token=RBoKFZnAUlJBwNZDLx07&groupName=证明类
截图如下:
从上图可以看出来,查询的结果也是12条,跟在mysql客户端直接调用结果相一致。
补充说明:
存储过程优点:
- 执行效率高:存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用,所以执行速度会比普通sql快。
- 可维护性强:存储过程有些时候比程序更容易维护,这是因为可以实时更新DB端的存储过程。有的bug,直接改存储过程里的业务逻辑,就可以了,应用程序无需发版。
- 安全性高:提高代码安全,防止 SQL注入。
- 业务逻辑优化:因为在存储过程创建的时候,数据库已经对其进行了一次解析和优化。
- 扩展性好:应用程序和数据库操作分开,独立进行,而不是相互在一起。方便以后的扩展和DBA维护优化。