mybatis接受mysql存储过程out的值
这是题目的实体类
private Integer id; private Integer type;//题型 单选,多选 private String category;//类型 数据字典配置 private String problem;//问题 private String choose1;//选择1 private String choose2; private String choose3; private String choose4; private String choose5; private String choose6; private String choose7; private String choose8; private String answer;//答案
卷子的实体类
private Integer id; private String title; private String problemNo;//题目,逗号分割 private Integer extract;//抽题个数
需求:已经录入若干提,其中每题的类型不完全相同,例如有的题目是语文,有的是数学之类的。
抽取其中若干题,必然是小于录入题的,必须保证每种类型的题都被抽取到,且希望能根据不同类型的题目均匀抽取;
例如录入40题,抽取10题。
假如语文,数学,物理,化学各是16,12,8,4。那么抽取语文,数学,物理,化学应当各是4,3,2,1道题。
假如语文,数学,物理,化学各是19,19,1,1。那么抽取语文,数学,物理,化学应当各是4,4,1,1道题。
作出最优解。
表结构:
CREATE TABLE `tb_multi_question_problem` ( `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID', `type` tinyint(4) DEFAULT NULL COMMENT '题型', `category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '类型', `problem` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '问题', `choose1` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '选择1', `choose2` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '选择2', `choose3` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '选择3', `choose4` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '选择4', `choose5` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '选择5', `choose6` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '选择6', `choose7` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '选择7', `choose8` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '选择8', `answer` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `update_time` datetime DEFAULT NULL COMMENT '更新时间', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_user` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `create_user` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `del_flag` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `tb_multi_questionnaire` ( `id` int(10) NOT NULL AUTO_INCREMENT, `title` varchar(128) DEFAULT NULL, `description` text, `imgurl` varchar(128) DEFAULT NULL, `problem_no` varchar(512) DEFAULT NULL, `parent_area` varchar(32) DEFAULT NULL, `area` varchar(32) DEFAULT NULL, `committee` varchar(32) DEFAULT NULL, `start_time` datetime DEFAULT NULL, `end_time` datetime DEFAULT NULL, `duration` int(10) DEFAULT NULL COMMENT '时长', `number` tinyint(4) DEFAULT NULL, `extract` tinyint(4) DEFAULT NULL, `url1` varchar(256) DEFAULT NULL, `url2` varchar(256) DEFAULT NULL, `url3` varchar(256) DEFAULT NULL, `update_time` datetime DEFAULT NULL COMMENT '更新时间', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_user` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `create_user` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
解决方案,因为涉及到类型,且类型未均匀分布。避免多次访问数据库,使用存储过程;
CREATE DEFINER=`root`@`localhost` PROCEDURE `rand_question`(in pid int, out idstr VARCHAR(128)) begin declare pno VARCHAR(128); declare qp VARCHAR(32); DECLARE ques_ext int DEFAULT 1; DECLARE ques_sum int DEFAULT 0; DECLARE qpid VARCHAR(32); DECLARE qpCategory VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; DECLARE qpSum INT(10); DECLARE q_sum INT(10) DEFAULT 0; DECLARE rem INT(10) DEFAULT 0; DECLARE lim INT(10) DEFAULT 0; declare done int default -1; DECLARE qp_cursor CURSOR FOR SELECT qp.category,COUNT(1) FROM tb_multi_question_problem qp WHERE del_flag = '0' and FIND_IN_SET(qp.id,(SELECT q.problem_no FROM tb_multi_questionnaire q WHERE del_flag = '0' and q.id = pid)) GROUP BY qp.category; DECLARE CONTINUE HANDLER FOR NOT found SET done = 1; SELECT q.problem_no INTO pno FROM tb_multi_questionnaire q WHERE del_flag = '0' and q.id = pid; SELECT q.extract into ques_ext FROM tb_multi_questionnaire q WHERE del_flag = '0' and q.id = pid; SELECT COUNT(1) into ques_sum FROM tb_multi_question_problem qp WHERE del_flag = '0' and FIND_IN_SET(qp.id,(SELECT q.problem_no FROM tb_multi_questionnaire q WHERE del_flag = '0' and q.id = pid)); set idstr = ''; OPEN qp_cursor; qpLoop : LOOP FETCH qp_cursor INTO qpCategory,qpSum; IF done = 1 THEN LEAVE qpLoop; END IF; set rem = floor(qpSum*ques_ext/ques_sum); IF rem < 1 THEN SET rem = 1; END IF; SELECT group_concat(id) INTO qpid FROM (SELECT id FROM tb_multi_question_problem WHERE del_flag = '0' and category = qpCategory and FIND_IN_SET(id,pno) and id >= (SELECT FLOOR(RAND()*(SELECT MAX(id) FROM tb_multi_question_problem WHERE del_flag = '0' and category = qpCategory))) order by rand() LIMIT rem) as a; set idstr = CONCAT(idstr,qpid,','); END LOOP qpLoop; CLOSE qp_cursor; select length(idstr)-length(replace(idstr,',','')) INTO q_sum; WHILE q_sum < ques_ext DO SET lim = ques_ext-q_sum; SELECT ques_ext,q_sum,lim; SELECT group_concat(a.id) INTO qpid FROM (SELECT * FROM tb_multi_question_problem WHERE del_flag = '0' and !FIND_IN_SET(id,idstr) and id >= (SELECT FLOOR(RAND()*(SELECT MAX(id) FROM tb_multi_question_problem WHERE del_flag = '0'))) order by rand() LIMIT lim) as a WHERE FIND_IN_SET(a.id,pno); set idstr = CONCAT(idstr,qpid,','); select length(idstr)-length(replace(idstr,',','')) INTO q_sum; END WHILE; end
得到所得的值有两种方法,第一种:
使用map传递接受参数
mapper.xml
<resultMap type="java.util.HashMap" id="resultMap"> <result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/> <result column="idstr" property="idstr" javaType="java.lang.String" jdbcType="VARCHAR"/> </resultMap> <select id="randomList" statementType="CALLABLE" parameterType="java.util.HashMap" resultMap="resultMap"> { call rand_question( #{id,jdbcType=INTEGER,mode=IN}, #{idstr, jdbcType=VARCHAR,mode=OUT}) } </select>
service
public List<QuestionProblem> randomList(Map<String, Object> param) { questionProblemDao.randomList(param); return questionProblemDao.getListByIds((String)param.get("idstr")); }
如上,通过param传参,也通过param接受参数;
类似于selectkey,我之前写的博客https://www.cnblogs.com/zhengyuanyuan/p/10616616.html
第二种通过javabean,有时间再补充