1000字范文 > mybatis获取mysql存储过程out参数的值_mybatis接受mysql存储过程out的值

mybatis获取mysql存储过程out参数的值_mybatis接受mysql存储过程out的值

时间：2019-01-03 13:54:08

这是题目的实体类

privateInteger id;private Integer type;//题型单选，多选

private String category;//类型数据字典配置

private String problem;//问题

private String choose1;//选择1

privateString choose2;privateString choose3;privateString choose4;privateString choose5;privateString choose6;privateString choose7;privateString choose8;private String answer;//答案

卷子的实体类

privateInteger id;privateString 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 NULLAUTO_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 SETutf8mb4 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 BYqp.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 = '';OPENqp_cursor;

qpLoop : LOOPFETCH qp_cursor INTOqpCategory,qpSum;IF done = 1 THENLEAVE 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) asa;set idstr = CONCAT(idstr,qpid,',');ENDLOOP qpLoop;CLOSEqp_cursor;select length(idstr)-length(replace(idstr,',','')) INTOq_sum;WHILE q_sum = (SELECT FLOOR(RAND()*(SELECT MAX(id) FROM tb_multi_question_problem WHERE del_flag = '0'))) order by rand() LIMIT lim) as a WHEREFIND_IN_SET(a.id,pno);set idstr = CONCAT(idstr,qpid,',');select length(idstr)-length(replace(idstr,',','')) INTOq_sum;END WHILE;end

得到所得的值有两种方法，第一种：

使用map传递接受参数

mapper.xml

{

call rand_question(

#{id,jdbcType=INTEGER,mode=IN},

#{idstr, jdbcType=VARCHAR,mode=OUT})

}

service

public List randomList(Mapparam) {

questionProblemDao.randomList(param);return questionProblemDao.getListByIds((String)param.get("idstr"));

}