springmvc+mybatis使用存储过程查询多结果集

<span style="background-color: rgb(255, 255, 255); font-family: Arial, Helvetica, sans-serif;">有时候查询一些数据需要存储过程，使用存储过程能大大的减少查询次数，增强性能。如下是使用存储过程完成一段复杂的查询。查询内容是根据课程的ID，难易程度和题目数量随机查出来题目。数据库如下</span>

DROP TABLE IF EXISTS `exam_examquestions`;
CREATE TABLE `exam_examquestions` (
  `examQuestion_ID` int(11) NOT NULL AUTO_INCREMENT,
  `examQuestion_UseType` int(11) NOT NULL DEFAULT '1' COMMENT '1:作业；2:模拟试题；3:正式考试；4:项目',
  `examQuestion_Type` int(100) NOT NULL COMMENT '0:单选题；1:多选题；2:判断题；3:填空题；4:问答题',
  `examQuestion_Stem` varchar(4000) NOT NULL,
  `examQuestion_Score` int(11) NOT NULL,
  `examQuestion_Answer` varchar(4000) NOT NULL DEFAULT '<p></p>' COMMENT '判断题中（0:错；1:对）',
  `examQuestion_Analysis` varchar(500) DEFAULT NULL,
  `examQuestion_Metas` varchar(4000) DEFAULT NULL,
  `examQuestion_Difficulty` int(11) NOT NULL,
  `examQuestion_TargetClassIDs` varchar(100) DEFAULT NULL,
  `examQuestion_StatusID` int(11) DEFAULT NULL,
  `examCategory_ID` int(11) NOT NULL,
  PRIMARY KEY (`examQuestion_ID`),
  KEY `Refexam_category1` (`examCategory_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=145 DEFAULT CHARSET=utf8;

存储过程如下

BEGIN
	SELECT
		@min := MIN(examQuestion_ID),
		@max := MAX(examQuestion_ID),
		@gap := MAX(examQuestion_ID) - MIN(examQuestion_ID)
	FROM
		exam_examquestions
	WHERE
  find_in_set(examQuestion_TargetClassIDs,_classIDs)
	AND examQuestion_UseType =_useType
	AND examQuestion_Type = 0;

PREPARE q1
FROM
	'SELECT
	a.examQuestion_ID,
	a.examQuestion_Answer,
	a.examQuestion_Stem,
	a.examQuestion_Type,
	a.examQuestion_Score,
	a.examQuestion_Analysis,
	a.examQuestion_UseType,
	b.examQuestionItem_ID,
	b.examQuestionItem_Title,
	b.examQuestionItem_StatusID
FROM
	exam_examquestions a
JOIN (
	SELECT
		examQuestion_ID
	FROM
		(
			SELECT
				examQuestion_ID
			FROM
				(
					SELECT
						? + (? - ? + 1 - ?) * RAND() AS START
					FROM
						DUAL
				) AS init
			JOIN exam_examquestions y
			WHERE
				y.examQuestion_ID > init. START
			ORDER BY
				y.examQuestion_ID
			LIMIT ?
		) z
	ORDER BY
		RAND()
	LIMIT ?
) r ON a.examQuestion_ID = r.examQuestion_ID
LEFT JOIN exam_examquestionitems b ON a.examQuestion_ID = b.examQuestion_ID
WHERE
find_in_set(a.examQuestion_TargetClassIDs,?)
AND a.examQuestion_UseType = ?
AND a.examQuestion_Difficulty = ?
AND a.examQuestion_Type = 2
Order By a.examQuestion_ID;';


SET @classIDs = _classIDs;
SET @difficultyID = _difficultyID;
SET @singleCount = _singleCount;
SET @useType = _useType;

EXECUTE q1 USING @min ,@max, @min, @gap, @gap, @singleCount, @classIDs, @useType, @difficultyID;
DEALLOCATE PREPARE q1;


END

写入mapper

<resultMap type="ExamIDResult" id="examIDResult">
		<result column="@min" property="minid" javaType="java.lang.Integer" jdbcType="INTEGER" />
		<result column="@max" property="maxid" javaType="java.lang.Integer" jdbcType="INTEGER" />
		<result column="@gap" property="gap" javaType="java.lang.Integer" jdbcType="INTEGER" />
	</resultMap>

	<resultMap type="QuestionResult" id="questionList">
		<result column="examQuestion_ID" property="examQuestion_ID" javaType="java.lang.Integer" jdbcType="INTEGER" />
		<result column="examQuestion_Stem" property="examQuestion_Stem" javaType="java.lang.String" jdbcType="VARCHAR" />
		<result column="examQuestion_Type" property="examQuestion_Type" javaType="java.lang.Integer" jdbcType="INTEGER" />
		<result column="examQuestion_Score" property="examQuestion_Score" javaType="java.lang.Integer" jdbcType="INTEGER" />
		<result column="examQuestion_Analysis" property="examQuestion_Analysis" javaType="java.lang.String" jdbcType="VARCHAR" />
		<result column="examQuestion_UseType" property="examQuestion_UseType" javaType="java.lang.Integer" jdbcType="INTEGER" />
		<result column="examQuestionItem_ID" property="examQuestionItem_ID" javaType="java.lang.Integer" jdbcType="INTEGER" />
		<result column="examQuestionItem_Title" property="examQuestionItem_Title" javaType="java.lang.String" jdbcType="VARCHAR" />
		<result column="examQuestionItem_StatusID" property="examQuestionItem_StatusID" javaType="java.lang.Integer" jdbcType="INTEGER" />
	</resultMap>


	<!-- @classIDs @difficultyID @singleCount @useType -->
	<!-- 根据 classIDs 查找随机查找单选题 -->
	<select id="createTestpaperForSingle" parameterType="java.util.Map" statementType="CALLABLE"  resultMap="examIDResult,questionList">
		{call sel_exam_questionsanditem_classids_single(#{_classIDs,mode=IN,jdbcType=VARCHAR},#{_difficultyID,mode=IN,jdbcType=INTEGER},#{_testPaperItem_Count,mode=IN,jdbcType=INTEGER},#{_useType,mode=IN,jdbcType=INTEGER})}
	</select>

写dao接口

public interface DaoForCreateTestpaper {

	 public List<List<?>> createTestpaperForSingle(String mapper, Map<String, Object> map);
}

dao实现

@Repository("daoForCreateTestpaperSupport")
public class DaoForCreateTestpaperSupport implements DaoForCreateTestpaper{

	@Resource(name = "sqlSessionTemplate")
	private SqlSessionTemplate sqlSessionTemplate;
	
	@Override
	public List<List<?>> createTestpaperForSingle(String mapper, Map<String, Object> map) {
		// TODO Auto-generated method stub
		return sqlSessionTemplate.selectList(mapper, map);
	}

}

实现service

	@Override
	public List<List<?>> createTestpaperForSingle(Map<String, Object> map) throws Exception {
		
		return createDao.createTestpaperForSingle("DaoForCreateTestpaper.createTestpaperForSingle", map);
	}

controller调用

        parametersToSqlSelect.put("_useType", tp.get("testPaper_Type"));
        parametersToSqlSelect.put("_difficultyID", tp.get("testPaper_DifficultType"));
        parametersToSqlSelect.put("_classIDs", tp.get("testPaper_ClassIDs"));
	parametersToSqlSelect.put("_testPaperItem_Count", tp.get("testPaperItem_Count"));
	parametersToSqlSelect.put("_isBasedOnCourse", tp.get("testPaper_IsBaseOnCourse"));
	parametersToSqlSelect.put("_testPaper_CourseID", tp.get("testPaper_CourseID"));
	if ((Integer)parametersToSqlSelect.get("_isBasedOnCourse") == 0) {
	     List<List<?>> singleListresults = TestpaperService.createTestpaperForSingle(parametersToSqlSelect);								
	     System.out.println(".................."+singleListresults);
	     System.out.println(".....----......."+ (List<QuestionResult>)singleListresults.get(1));}