Mybatis调用存储过程返回查询结果的通用接口（Oracle 存储过程游标）

猫萌萌公司

已于 2022-08-19 10:59:06 修改

阅读量2.5k

点赞数 1

分类专栏：工作记录文章标签： sql 数据库 oracle

于 2022-08-18 11:53:47 首次发布

本文链接：https://blog.csdn.net/qq_41605927/article/details/126398367

版权

工作记录专栏收录该内容

4 篇文章 0 订阅

订阅专栏

背景：

在通常的业务处理中，需要前端展示一个表单，后端通常要写配套的返回实体类 VO好将Mybatis查询的结构映射为实体类，即便是简单的单表查询也需要写常用的三层结构。如果返回的结构需要变动，例如加上一列，前后端几乎每个层级都需要改动添加上新的返回字段。

本文主要是为了业务大量存放于存储过程的项目服务，如果是单纯为了方便可以稍作修改，将Mybatis调用存储过程，修改为普通的SQL。其余的思路也是相通的。

如下，Java通常的三层结构对于SQL较为简单或者业务写在存储过程的查询实在是太繁重了！！

//实体类
public class SysUser extends BaseEntity
{
    private static final long serialVersionUID = 1L;

    /** 用户ID */
    @Excel(name = "用户序号", cellType = ColumnType.NUMERIC, prompt = "用户编号")
    private Long userId;
....
//Service接口
public interface ISysUserService
{
    /**
     * 根据条件分页查询用户列表
....
//imp
@Service
public class SysUserServiceImpl implements ISysUserService
{
    private static final Logger log = LoggerFactory.getLogger(SysUserServiceImpl.class);
    @Autowired
    private SysUserMapper userMapper;
....
//Controller
@RestController
@RequestMapping("/system/user")
public class SysUserController extends BaseController
{
    @Autowired
    private ISysUserService userService;
    @GetMapping("/list")
    public TableDataInfo list(SysUser user)
    {
        startPage();
        List<SysUser> list = userService.selectUserList(user);
        return getDataTable(list);
    }
....
//Mapper
public interface SysUserMapper
    public List<SysUser> selectUserList(SysUser sysUser);
//XML
	<sql id="selectUserVo">
        select * from sys_user u
    </sql>

存储过程

将SQL写入存储过程中，Oracle版本为11g，注释为测试存储过程多参数的情况

create or replace package pkg_package

as

type type_cursor is ref cursor;

end;

create or replace procedure p_temp_procedure

(
--v_a varchar,
--v_b varchar,
cur_out_arg out pkg_package.type_cursor

)

is

begin

open cur_out_arg for select 
--v_a,v_b  ,
a.* from sys_user a;

end;

JDBC

先使用JDBC进行测试，注释部分为存储过程单参数的情况。使用JDBC预编译，将需要的变量填入？之中。将获取的游标转换为List<Map> 的形式，和平常的List<User>一样，只不过将User干掉修改为Map类型。

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        List<Map<String, Object>> tableData = new ArrayList<Map<String, Object>>();
        List<String> columnName = new ArrayList<>();
        JSONObject res =new JSONObject();
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521/orcl";
        String username = "yam";
        String password = "******";
        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, username, password);
//        CallableStatement callableStatement = con.prepareCall("{call p_temp_procedure(?)}");
        CallableStatement callableStatement = con.prepareCall("{call p_temp_procedure(?,?,?)}");
            callableStatement.setInt(1, 0);
            callableStatement.setInt(2, 10);
        callableStatement.registerOutParameter(3, OracleTypes.CURSOR);
//        callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
        callableStatement.execute();
//        ResultSet rs = ((OracleCallableStatement) callableStatement).getCursor(1);
        ResultSet rs = ((OracleCallableStatement) callableStatement).getCursor(3);
        int cl = rs.getMetaData().getColumnCount();
        //columnName 可以配合前端如vantd之类的框架，进行表单的展示，不用前端再去写动态表单。根据实际情况进行改造
        for (int i = 0; i < cl; i++) {
            String key = rs.getMetaData().getColumnName(i + 1);
            columnName.add(key);
        }
        res.put("columnName",columnName);
        while (rs.next()) {
            Map<String, Object> each = new HashMap<String, Object>();
            for (int i = 0; i < cl; i++) {
                String key = rs.getMetaData().getColumnName(i + 1);
                Object val = rs.getObject(key);
                each.put(key, val);
            }
            tableData.add(each);
        }
        res.put("tableData",tableData);
        System.out.println("输出的结果是：" + res);
    }

      输出的结果是： {
   "columnName": [":B2", ":B1", "USER_ID", "DEPT_ID", ...],
   "tableData": [{
       "DEPT_ID": 103,
       "PHONENUMBER": "15888888888",
       "SEX": "1",
       ":B1": "10",
       "USER_ID": 1...
   },{
       "SEX": "0",
       "UPDATE_BY": "admin",
       ":B1": "10",
       "USER_ID": 100...
   }]
}

上面输出字段不一样是因为推测是HashMap的value不能为空，所以在put的时候需要判断一下val是否为空，如果为空put “”即可。

实践之后发现Map中是可以有存有null的value，而转换成json的时候json中也存在。原因是fastjson生成json后他的toString方法中Null属性不显示。

解决方案有两个，一个是刚刚说的给value赋空字符串

                if (val ==null){
                    val="";
                }

另一个是在toString时候指定参数为空也要写入。这个方法只能fastJson使用，fastJson2无效。

        String str = JSONObject.toJSONString(res,SerializerFeature.WriteMapNullValue);
        System.out.println("输出的结果是：" + str);

如果不要toString的话，可以无视这些，直接debug看。

Mybatis

Mapper ，返回和输入的参数请务必定义为Map<String, Object>，不要@parm。本质是对输入的Map进行修改，添加新的Key-value。所以必须要有输入的Map!

    public Map<String, Object> getTable(Map<String, Object> result);

XML，定义一个resultMap类型为HashMap进行映射，因为靠变量拼接存储过程名和变量名，所以只能用${}，但是因为存储过程是已经在数据库中编译过的，所以不必担心SQL注入。输出游标基本是定死，请不要修改。

    <resultMap id="cursorMap" type="java.util.HashMap">
    </resultMap>    
    <select id="getTable" statementType="CALLABLE" parameterType="map" >
        {call ${procedureName}(
                ${procedureVar},
                #{cur_out_arg, jdbcType=CURSOR, mode=OUT, javaType=ResultSet, resultMap=cursorMap}
            )}
    </select>

Controller层，将存储过程的过程名字和变量数组传入，

    @GetMapping("/list")
    public R<JSONObject> userList() throws ClassNotFoundException, SQLException {
        JSONObject res =new JSONObject();
        Map tableData=new HashMap();
//    procedureName 为需要调用的存储过程名
        tableData.put("procedureName","p_temp_procedure");
//    procedureVar为需要调用的存储过程的输入变量
        tableData.put("procedureVar","09492,9512");
//    调用写好的通用Mapper，通过Mybatis的映射将结果写入输入的tableData中
        autoDBtoJSONMapper.getTable(tableData);
        res.put("tableData",tableData);
        return R.ok(res);
    }

但是返回的Map怎么size不一样？有东西什么东西漏了？

和前面JDBC的转换一样，Mybatis也同样有问题，但是问题原因不一样，他在处理结果的Map中就直接缺少了空的字段。

Mybatis查询数据映射数据类型使用的是Map，当字段值为null时，mybatis映射返回字段的时候会忽略。为了解决这个问题，先修改Mybatis的配置文件。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 全局参数 -->
    <settings>
        <!-- 使全局的映射器启用或禁用缓存 -->
        <setting name="cacheEnabled"             value="true"   />
        <!-- 允许JDBC 支持自动生成主键 -->
        <setting name="useGeneratedKeys"         value="false"   />
        <!-- 配置默认的执行器.SIMPLE就是普通执行器;REUSE执行器会重用预处理语句(prepared statements);BATCH执行器将重用语句并执行批量更新 -->
        <setting name="defaultExecutorType"      value="SIMPLE" />
		<!-- 指定 MyBatis 所用日志的具体实现 -->
        <setting name="logImpl"                  value="SLF4J"  />
        <!-- 使用驼峰命名法转换字段 -->
		<!-- <setting name="mapUnderscoreToCamelCase" value="true"/> -->
        <!-- 修改映射Map，返回所有值，即便是Null -->
        <setting name="callSettersOnNulls" value="true"/>
	</settings>

</configuration>

经过Mybatis的配置后如果查询结果为空则传入null。如果要直接toString 的话也会遇到上面JDBC输出的问题，不过这里是放在controller中，直接把json返回给前端所以并无影响。