Mybatis+Oracle总结

Mybatis+Oracle总结

Mybatis

在if中使用大于、小于的判断

直接在test中写尖括号是错误的，会导致xml编译不通过。需要使用gt（大于）、lt（小于）、gte（大于等于）、lte（小于等于）来代替。

choose…when…otherwise

类似于switch case吧，可以使用if来代替，但是感觉在某些时候可读性不好吧？（会有效率问题吗？）

<choose>
       <when test="isAcct !=  null and isAcct != ''">
       </when>
       <otherwise>
       </otherwise>
</choose>

Mybatis调用Oracle的函数

mybatis调用Oracle的存储过程请参考我的另一篇博客
Oracle创建存储过程及在Mybatis中的调用

oracle函数结构

create or replace package body CPA_PROC_CPAF_BIR_SUM_ALL is
    function FN_CPA_CPAF_BIR_BASIC_INFO(REP_YEAR IN VARCHAR2)
      RETURN TYPES.CURSORTYPES --返回结果集
     IS
      RESULTSET TYPES.CURSORTYPES;
    BEGIN
      BEGIN
        OPEN RESULTSET FOR
          SELECT CADIVISION.DIVISION_CODE,...--省略查询语句
      END;
      RETURN RESULTSET;
      CLOSE RESULTSET;
    END;
    --省略其他函数

mybatis调用

-2018/5/15 经同事实践证明resultMap无需枚举所有项目，只留一个空壳即可，如下

 <resultMap id="CpafReportAllResult" type="java.util.HashMap">  
  </resultMap>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.ufgov.mapper.CpaReportSearchMapper">

    <resultMap type="java.util.Map" id="CPA_PROC_CPAF_BIR_SUM_ALL_CURSORMAP">
        <result column="DIVISION_CODE" property="DIVISION_CODE"/>
        <result column="DIVISION_NAME" property="DIVISION_NAME"/>
        <result column="TOTAL_INVES" property="TOTAL_INVES"/>
        <result column="NA_ENDYEAR" property="NA_ENDYEAR"/>
        <result column="TOTAL_INC" property="TOTAL_INC"/>
        <result column="AB_INC" property="AB_INC"/>
        <result column="EXTRACTION_ORF_TOTAL" property="EXTRACTION_ORF_TOTAL"/>
        <result column="PLI" property="PLI"/>
        <result column="APICL" property="APICL"/>
        <result column="AMOUNT_BEGINYEAR" property="AMOUNT_BEGINYEAR"/>
        <result column="EXTRA_AMOUNT_CURYEAR" property="EXTRA_AMOUNT_CURYEAR"/>
        <result column="AMOUNT_ENDYEAR" property="AMOUNT_ENDYEAR"/>
        <result column="USE_AMOUNT_CURYEAR" property="USE_AMOUNT_CURYEAR"/>
        <result column="CAOPLIC_THISYEAR" property="CAOPLIC_THISYEAR"/>
        <result column="AUDIT_REPORT_NUM" property="AUDIT_REPORT_NUM"/>
        <result column="CPA_NUM" property="CPA_NUM"/>
        <result column="NO_CPA_NUM" property="NO_CPA_NUM"/>
        <result column="SHUIWU_IC" property="SHUIWU_IC"/>
        <result column="ZIXUN_IC" property="ZIXUN_IC"/>
        <result column="QITA_IC" property="QITA_IC"/>
        <result column="CPAFB_NUM" property="CPAFB_NUM"/>
        <result column="INTER_INCOME" property="INTER_INCOME"/>
        <result column="INTER_SERVICE_PAYOUT" property="INTER_SERVICE_PAYOUT"/>
        <result column="INTER_CPAF_NUM" property="INTER_CPAF_NUM"/>
        <result column="INTER_PARTNER_INCOME" property="INTER_PARTNER_INCOME"/>
        <result column="ZHIGONGXINCHOU" property="ZHIGONGXINCHOU"/>
        <result column="JIAOSUI_TOTAL" property="JIAOSUI_TOTAL"/>
        <result column="GAOJIJINGLI_TOTAL_NUM" property="GAOJIJINGLI_TOTAL_NUM"/>
        <result column="JINGLI_TOTAL_NUM" property="JINGLI_TOTAL_NUM"/>
        <result column="HEHUOREN_NUM" property="HEHUOREN_NUM"/>
        <result column="KAOSHI_NUM" property="KAOSHI_NUM"/>
        <result column="KAOHE_NUM" property="KAOHE_NUM"/>
        <result column="FEICPA_NUM" property="FEICPA_NUM"/>
        <result column="JINLIRU" property="JINLIRU"/>
        <result column="CPAF_BILI" property="CPAF_BILI"/>
        <result column="CPAF_CPAFB_NUM" property="CPAF_CPAFB_NUM"/>
        <result column="NANV_BILI" property="NANV_BILI"/>
        <result column="AVG_AGE" property="AVG_AGE"/>
        <result column="LESS30" property="LESS30"/>
        <result column="B30A40" property="B30A40"/>
        <result column="B40A50" property="B40A50"/>
        <result column="B50A55" property="B50A55"/>
        <result column="B55A60" property="B55A60"/>
        <result column="B60A65" property="B60A65"/>
        <result column="THAN65" property="THAN65"/>
        <result column="PUTONG_EMP_NUM" property="PUTONG_EMP_NUM"/>
        <result column="AUDIT_MONEY" property="AUDIT_MONEY"/>
        <result column="BUSI_MONEY" property="BUSI_MONEY"/>
    </resultMap>
    <select id="cpafBirSumAll" statementType="CALLABLE" parameterType="java.util.Map">
        <![CDATA[
        {#{resultList,mode=OUT, jdbcType=CURSOR,javaType=ResultSet, resultMap=CPA_PROC_CPAF_BIR_SUM_ALL_CURSORMAP}=call cpa_proc_cpaf_bir_sum_all.fn_cpa_cpaf_bir_basic_info(#{reportYear,mode=IN,jdbcType=VARCHAR})}
        ]]>
    </select>
</mapper>

对返回游标的处理

public ArrayList<Map<String, String>> cpafBirSumAll(String reportYear) {
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("reportYear", reportYear);
        param.put("resultList", new ArrayList<Map<String, String>>());
        cpaReportSearchMapper.cpafBirSumAll(param);
        return (ArrayList<Map<String, String>>) param.get("resultList");
    }

和调用存储过程的差别

可以看出调用函数时，out的并不是一个参数，而是一个返回值，因此，需要用…=call 来获取返回值

遇到的问题

出现格式不正确的 SQL92 串: 2. Expecting “=” got "

这个错误的原因是=call的前边不能有换行，但是空格是可以的。

    <select id="cpafBirSumProvince" statementType="CALLABLE" parameterType="java.util.Map">
        <![CDATA[
        {#{retMap,mode=OUT, jdbcType=CURSOR,javaType=ResultSet, resultMap=CPA_PROC_CPAF_BIR_SUM_CURSORMAP}
        =call CPA_PROC_CPAF_BIR_SUM.CPA_CPAF_BIR_DIVISION_SUM(#{reportYear,mode=IN,jdbcType=VARCHAR},
        #{divisionProvince,mode=IN,jdbcType=VARCHAR})}
        ]]>
    </select>

mybatis不返回查询结果为空的字段，导致前端报错

mybatis返回map类型数据空值字段不显示(三种解决方法)
在mybatis的配置文件中配置一下

<setting name="callSettersOnNulls" value="true"/>