mybatis注解方式调用mysql存储过程

创建存储过程

需求:
随机返回某一数据表中枚举类型的属性的某一个值给后台。
具体来说，假设数据库中有一个表名为’test’的数据表，其中有一属性名为major的属性，这一属性为枚举类型{计算机技术,计算机科学与技术}，希望能够随机返回其中的某一个值。数据表名以及属性名均为变量，需要动态传参。

具体存储过程的sql语句

delimiter //
drop procedure IF EXISTS solve_enum;
create procedure solve_enum (IN tablename VARCHAR (25) , IN attriname VARCHAR (25) ,OUT value VARCHAR (25)) 
begin
    set @count := CONCAT("select count(*) into @c from (select DISTINCT ", attriname, " from ", tablename, " ) a");
    PREPARE stmt1 from @count;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    set @count1 := floor(rand()* @c );
    set @sql := CONCAT("select * into @value from (select DISTINCT " ,attriname, " from ", tablename, ") a order by ",attriname, " limit ?,1"); 
     prepare stmt from @sql; 
     execute stmt using @count1; 
     DEALLOCATE PREPARE stmt;
     select @value as value;
end;
//

可以在命令行中测试一下

CALL solve_enum ('test','major',@value);//

spring boot+mybatis

Mapper代码

/**
     * 注解形式调用存储过程
     * @param map
     */
    @Select("call solve_enum(#{map.tablename,mode=IN,jdbcType=VARCHAR},#{map.attriname,mode=IN,jdbcType=VARCHAR},#{map.value,mode=OUT,jdbcType=VARCHAR})")
    @Options(statementType=StatementType.CALLABLE)
    HashMap solve_enum(@Param("map")Map map);

Service代码

    @Transactional
    public HashMap solve_enum(@Param("map")Map map) {
         HashMap re = this.maskDynamicDao.solve_enum(map);
         return re;
    }

Controller代码

 String value = "";
 HashMap<String, String> map = new HashMap();
 map.put("tablename", "test");
 map.put("attriname", "major");
 map.put("value", value);
 value = String.valueOf(Service.solve_enum(map).get("value"));
 System.out.println(value);