Spring+mybatis 调用oacle 存储过程返回游标！

oracle存储过程如下：

 HB_SAVE_MSG.P_QUERY_QUESTION_OF_QTA 

  PROCEDURE P_QUERY_QUESTION_OF_QTA
        (
        v_PaperID         IN          NUMBER,
        v_myCursor        out         MYCURSOR,
        v_optCursor       out         MYCURSOR
        )
        IS
        BEGIN
            OPEN v_myCursor FOR
              SELECT A.QUESTION_ID,A.CONTENT,A.QTYPE_ID FROM T_QUESTION A INNER JOIN T_PQ_RELATION B ON A.QUESTION_ID=B.QUESTION_ID WHERE B.PAPER_ID=v_PaperID;
            OPEN v_optCursor FOR
            SELECT M.OPTION_ID,M.QUESTION_ID,M.OPTION_NO,M.OPTION_CONTENT,M.SCORE,M.SUB_QT_ID FROM T_OPTION M WHERE M.QUESTION_ID IN (SELECT N.QUESTION_ID FROM T_PQ_RELATION N WHERE N.PAPER_ID=v_PaperID) ORDER BY M.OPTION_ID;

        END P_QUERY_QUESTION_OF_QTA;

Java端：

根据查询结果新建bean

import java.util.List;
/**
 * 
 * 
 * 为了方便 HB_SAVE_MSG.P_QUERY_QUESTION_OF_QTA/HB_SAVE_MSG.P_READ_QUESTION_BY_ID存储过程创建的实体bean。
 * 
 * @author ly
 *
 */
public class StrBoys {
    
    private Integer question_id;//题库id
    private String content;//标题
    private Integer qtype_id;//题型表id

    

        public Integer getQuestion_id() {
        return question_id;
    }
    public void setQuestion_id(Integer question_id) {
        this.question_id = question_id;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
    public Integer getQtype_id() {
        return qtype_id;
    }
    public void setQtype_id(Integer qtype_id) {
        this.qtype_id = qtype_id;
    }






}

/**
 * 
 * 
 * 为了方便 HB_SAVE_MSG.P_QUERY_QUESTION_OF_QTA/HB_SAVE_MSG.P_READ_QUESTION_BY_ID存储过程创建的实体bean。
 * 
 * @author ly
 *
 */
public class Question {
    
    private Integer option_id;//选项ID
    private Integer question_id;//问题id
    private String  option_on;//选项编号A,B,C等,填空题为空
    private String option_content;//选项内容
    private Integer score;//分值
    private Integer sub_qt_id;//子问题id
    public Integer getOption_id() {
        return option_id;
    }
    public void setOption_id(Integer option_id) {
        this.option_id = option_id;
    }
    public Integer getQuestion_id() {
        return question_id;
    }
    public void setQuestion_id(Integer question_id) {
        this.question_id = question_id;
    }
    public String getOption_on() {
        return option_on;
    }
    public void setOption_on(String option_on) {
        this.option_on = option_on;
    }
    public String getOption_content() {
        return option_content;
    }
    public void setOption_content(String option_content) {
        this.option_content = option_content;
    }
    public Integer getScore() {
        return score;
    }
    public void setScore(Integer score) {
        this.score = score;
    }
    public Integer getSub_qt_id() {
        return sub_qt_id;
    }
    public void setSub_qt_id(Integer sub_qt_id) {
        this.sub_qt_id = sub_qt_id;
    }

}

 

mybatis :

<mapper namespace="com.ly.sa.dao.WjDao">

    <resultMap id="resultMap1" type="com.ly.sa.entity.StrBoys" >
        <result property="question_id" column="QUESTION_ID" />  
        <result property="content" column="CONTENT"  />  
        <result property="qtype_id" column="QTYPE_ID"   />  
    </resultMap>
    
    <resultMap id="resultMap2" type="com.ly.sa.entity.Question" >
        <result property="option_id" column="OPTION_ID" />  
        <result property="question_id" column="QUESTION_ID" />  
        <result property="option_on" column="OPTION_NO" />  
        <result property="option_content" column="OPTION_CONTENT" />  
        <result property="score" column="SCORE" />  
         <result property="sub_qt_id" column="SUB_QT_ID" />  
    </resultMap>
    
    
    <select id="wjdas" statementType="CALLABLE" parameterType="java.util.HashMap">
    <![CDATA[
     {call HB_SAVE_MSG.P_QUERY_QUESTION_OF_QTA(#{v_paperid,mode=IN,jdbcType=INTEGER},
     #{v_mycursor,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=resultMap1},
     #{v_optcursor,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=resultMap2}
     )}
    ]]>   
</select>

Service：

    public void wjdas(Map<String, Object> map) {
        wjDao.wjdas(map);
    }

 

Controller 关键代码：

Map<String, Object> map = Maps.newHashMap();
        try {
            map.put("v_paperid", pid);
            map.put("v_mycursor",oracle.jdbc.OracleTypes.CURSOR);
            map.put("v_optcursor",oracle.jdbc.OracleTypes.CURSOR);
            wjserver.wjdas(map);//传入存储过程参数调用dao。
            List<Question> dage=(List<Question>) map.get("v_mycursor");//获取游标返回的数据。

 

刚开始学mybatis ,希望大家可以一起交流、

 

转载于:https://www.cnblogs.com/ours/p/4666638.html