Mybatis调用Oracle存储过程

在工作中遇到了调用Oracle存储过程的有返回值的需求。记录一下使用方法。

首先在百度搜索的方式in和out都封装到一个map中。我这里的出参是对象中有两个属性一个是String一个是List，List里有N个对象。

public class MenudefController {
    @Autowired
    private MenudefMapper menudefMapper;

    public void test1(@RequestBody MenudefRequestVo enudefReqvo){
        HashMap<String,String> map = new HashMap<>();
        map.put("in_logonid",enudefReqvo.getIn_logonid());
        map.put("in_menucode",enudefReqvo.getIn_menucode());
        menudefMapper.queryMenudef1(map);
    }
}

@Mapper
public interface MenudefMapper {
    void queryMenudef1(HashMap map);
}

@Data
public class MenudefRequestVo {
    private String in_logonid;
    private String in_menucode;
}

<resultMap id="munuMap1" type="java.util.HashMap>
    <result column="CMD_MENUCODE" property="CMD_MENUCODE"/>
    <result column="CMD_PARENTMENU" property="CMD_PARENTMENU"/>
    <result column="CMD_LEVEL" property="CMD_LEVEL"/>
    <result column="CMD_ISLEAF" property="CMD_ISLEAF"/>
    <result column="CMD_NAMECN" property="CMD_NAMECN"/>
    <result column="CMD_NAMEEN" property="CMD_NAMEEN"/>
    <result column="CMD_URL" property="CMD_URL"/>
    <result column="CMD_TRANCODE" property="CMD_TRANCODE"/>
    <result column="CMD_SEQNO" property="CMD_SEQNO"/>
    <result column="CMD_GROUPVERSION" property="CMD_GROUPVERSION"/>
</resultMap>

<select id="queryMenudef1" statementType="CALLABLE" parameterType="java.util.HashMap">
    {call co_pckg_initmenuinfo9.proc_queryrolemenuNEW(
    #{in_logonid,mode=IN,jdbcType=VARCHAR},
    #{in_menucode,mode=IN,jdbcType=VARCHAR},
    #{out_procsign,mode=OUT,jdbcType=VARCHAR},
    #{ret_menuinfo,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,ResultMap=munuMap1}
</select>

 

 

从返回的结果看，两个out都被封装到了map中，ret_menuinfo的value是集合，集合中又有map显然是把属性封装到map的key，value中了。接下来我尝试改变返回值先将List里的Map替换成对象。

@Data
public class MenudefList {
    private String CMD_MENUCODE;
    private String CMD_PARENTMENU;
    private String CMD_LEVEL;
    private String CMD_ISLEAF;
    private String CMD_NAMECN;
    private String CMD_NAMEEN;
    private String CMD_URL;
    private String CMD_TRANCODE;
    private String CMD_SEQNO;
    private String CMD_GROUPVERSION;
}

<resultMap id="munuMap1" type="com.bob.pym.user.vo.MenudefList">
    <result column="CMD_MENUCODE" property="CMD_MENUCODE"/>
    <result column="CMD_PARENTMENU" property="CMD_PARENTMENU"/>
    <result column="CMD_LEVEL" property="CMD_LEVEL"/>
    <result column="CMD_ISLEAF" property="CMD_ISLEAF"/>
    <result column="CMD_NAMECN" property="CMD_NAMECN"/>
    <result column="CMD_NAMEEN" property="CMD_NAMEEN"/>
    <result column="CMD_URL" property="CMD_URL"/>
    <result column="CMD_TRANCODE" property="CMD_TRANCODE"/>
    <result column="CMD_SEQNO" property="CMD_SEQNO"/>
    <result column="CMD_GROUPVERSION" property="CMD_GROUPVERSION"/>
</resultMap>

<select id="queryMenudef1" statementType="CALLABLE" parameterType="java.util.HashMap">
    {call co_pckg_initmenuinfo9.proc_queryrolemenuNEW(
    #{in_logonid,mode=IN,jdbcType=VARCHAR},
    #{in_menucode,mode=IN,jdbcType=VARCHAR},
    #{out_procsign,mode=OUT,jdbcType=VARCHAR},
    #{ret_menuinfo,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,ResultMap=munuMap1}
</select>

 

 

现在ret_menuinfo这个key对应的value中是集合，集合里边已经是MenudefList对象了。接下来将入参出参的map改为一个对象，也就是说入参和出参都封装到一个对象中。

<resultMap id="munuMap1" type="com.bob.pym.user.vo.MenudefList">
    <result column="CMD_MENUCODE" property="CMD_MENUCODE"/>
    <result column="CMD_PARENTMENU" property="CMD_PARENTMENU"/>
    <result column="CMD_LEVEL" property="CMD_LEVEL"/>
    <result column="CMD_ISLEAF" property="CMD_ISLEAF"/>
    <result column="CMD_NAMECN" property="CMD_NAMECN"/>
    <result column="CMD_NAMEEN" property="CMD_NAMEEN"/>
    <result column="CMD_URL" property="CMD_URL"/>
    <result column="CMD_TRANCODE" property="CMD_TRANCODE"/>
    <result column="CMD_SEQNO" property="CMD_SEQNO"/>
    <result column="CMD_GROUPVERSION" property="CMD_GROUPVERSION"/>
</resultMap>

<select id="queryMenudef1" statementType="CALLABLE" parameterType="com.bob.pym.user.vo.MenudefAllArgs">
    {call co_pckg_initmenuinfo9.proc_queryrolemenuNEW(
    #{in_logonid,mode=IN,jdbcType=VARCHAR},
    #{in_menucode,mode=IN,jdbcType=VARCHAR},
    #{out_procsign,mode=OUT,jdbcType=VARCHAR},
    #{ret_menuinfo,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,ResultMap=munuMap1}
</select>

@Data
public class MenudefAllArgs {
    private String in_logonid;
    private String in_menucode;
    private String out_procsign;
    private List<MenudefList> menudefList;
}

@Mapper
public interface MenudefMapper {
    void queryMenudef1(MenudefAllArgs menudefAllArgs);
}

public class MenudefController {
    @Autowired
    private MenudefMapper menudefMapper;

    public void test1(@RequestBody MenudefAllArgs menudefAllArgs){
        menudefMapper.queryMenudef1(menudefAllArgs);
    }
}

 

 

这个已经是我最后使用的方案，但我一直疑惑难道我不能把入参和出参拆成两个对象吗？这样看起来很别扭。于是有了下边这一版错误的方案。

@Data
public class MenudefRequestVo {
    private String in_logonid;
    private String in_menucode;
}

@Data
public class MenudefResponseVo {
    private String out_procsign;
    private List<MenudefList> menudefList;
}

public class MenudefController {
    @Autowired
    private MenudefMapper menudefMapper;

    public void test1(@RequestBody MenudefRequestVo menudefRequestVo){
        menudefMapper.queryMenudef1(menudefRequestVo);
    }
}

@Mapper
public interface MenudefMapper {
    MenudefResponseVo queryMenudef1(MenudefRequestVo menudefRequestVo);
}

<resultMap id="munuMap1" type="com.bob.pym.user.vo.MenudefList">
    <result column="CMD_MENUCODE" property="CMD_MENUCODE"/>
    <result column="CMD_PARENTMENU" property="CMD_PARENTMENU"/>
    <result column="CMD_LEVEL" property="CMD_LEVEL"/>
    <result column="CMD_ISLEAF" property="CMD_ISLEAF"/>
    <result column="CMD_NAMECN" property="CMD_NAMECN"/>
    <result column="CMD_NAMEEN" property="CMD_NAMEEN"/>
    <result column="CMD_URL" property="CMD_URL"/>
    <result column="CMD_TRANCODE" property="CMD_TRANCODE"/>
    <result column="CMD_SEQNO" property="CMD_SEQNO"/>
    <result column="CMD_GROUPVERSION" property="CMD_GROUPVERSION"/>
</resultMap>

<select id="queryMenudef1" statementType="CALLABLE" parameterType="com.bob.pym.user.vo.MenudefReqVo" resultType="com.bob.pym.user.vo.MenudefRespVo">
    {call co_pckg_initmenuinfo9.proc_queryrolemenuNEW(
    #{in_logonid,mode=IN,jdbcType=VARCHAR},
    #{in_menucode,mode=IN,jdbcType=VARCHAR},
    #{out_procsign,mode=OUT,jdbcType=VARCHAR},
    #{ret_menuinfo,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,ResultMap=munuMap1}
</select>

 

但是最终报错，错误原因是out的属性在入参vo中找不到，拆分失败。留下个疑问，难道in和out非要在一个对象中吗？