mybatis调用存储过程

https://blog.csdn.net/hj7jay/article/details/75334581?utm_source=copy

感谢大神，置顶

 

 

 

坑点。。。。

select  xxx into outparam ;  这句话后，接着写下面的一句

select  xxx;

这样才能接着out值。麻蛋。第一种，采用map的形式。

---

public int menuInsert(MenuInfo record) throws Exception {
        if (record.getMenuParentId() == null) {
            record.setMenuParentId(0);
        }
        Map mapParam = new HashMap(20);
        Date date = new Date();
        SimpleDateFormat slf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        mapParam.put("menuCreatePerson", "无");
        mapParam.put("menuCreateTime", slf.format(date));
        mapParam.put("menuName", record.getMenuName());
        mapParam.put("menuUrl", record.getMenuUrl());
        mapParam.put("menuType", record.getMenuType());
        mapParam.put("menuDescription", record.getMenuDescription());
        mapParam.put("menuParentId", record.getMenuParentId());
        mapParam.put("menuSort", record.getMenuSort());
        mapParam.put("flagParam", 0);


        Map iResult = authorityMapper.menuInsert(mapParam);


        Integer result1 = (Integer) iResult.get("flagParam");
        return result1 > 0 ? 200 : 500;
    }

---

 

    <!--存储过程，menu菜单插入数据-->
    <select id="menuInsert" parameterType="java.util.Map"  resultType="java.util.HashMap"  statementType="CALLABLE">
      {
        call authorityAdd(
            #{menuCreatePerson,mode=IN,jdbcType=VARCHAR},
            #{menuCreateTime,mode=IN,jdbcType=VARCHAR},
            #{menuName,mode=IN,jdbcType=VARCHAR},
            #{menuParentId,mode=IN,jdbcType=INTEGER},
            #{menuSort,mode=IN,jdbcType=DOUBLE},
            #{flagParam,mode=OUT,jdbcType=INTEGER}
         )
      }
  </select>

---

CREATE DEFINER=`root`@`localhost` PROCEDURE `authorityAdd`(
IN `menuCreatePerson` VARCHAR ( 20 ),
IN `menuCreateTime` VARCHAR ( 20 ),
IN `menuName` VARCHAR ( 20 ),
IN `menuUrl` VARCHAR ( 20 ),
IN `menuType` INT,
IN `menuDescription` VARCHAR ( 20 ),
IN `menuParentId` INT,
IN `menuSort` DOUBLE ( 10, 5 ) ,
OUT `flagParam` INT
)
BEGIN


DECLARE
	countResult INT DEFAULT - 1;
DECLARE
	maxResult INT DEFAULT - 1;
DECLARE
	maxResult2 INT DEFAULT - 1;
DECLARE
	maxResult3 INT DEFAULT - 1;
DECLARE
	maxResult4 INT DEFAULT - 1;
DECLARE
	maxResult5 INT DEFAULT - 1;
	
	
	DECLARE t_error INTEGER DEFAULT 0;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION	SET t_error=1;
	START TRANSACTION;


	
IF t_error = 1 THEN
		ROLLBACK;
		SELECT
			500 INTO flagParam;
			SELECT flagParam;
		ELSE COMMIT;
		SELECT
			200 INTO flagParam;
				SELECT flagParam;
		
	END IF;




END

 

---

第二种，参数是javabean，返回值也是javabean

 

---

public int menuInsert(MenuInfo record) throws Exception {
        if (record.getMenuParentId() == null) {
            record.setMenuParentId(0);
        }
        Date date = new Date();
        SimpleDateFormat slf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        record.setMenuCreateTime(slf.format(date));
        record.setMenuCreatePerson("无");
        record.setFlagParam(null);
        MenuInfo iResult = authorityMapper.menuInsert(record);
        /*Integer iResult =*/
        return iResult.getFlagParam() > 0 ? 200 : 500;
    }

---

 

MenuInfo  menuInsert(MenuInfo mapParam)throws Exception;

---

 

 <!--存储过程，menu菜单插入数据-->
    <select id="menuInsert" parameterType="com.xz.bean.MenuInfo"  resultMap="BaseResultMap"  statementType="CALLABLE">
      {
        call authorityAdd(
            #{menuCreatePerson,mode=IN,jdbcType=VARCHAR},
            #{menuCreateTime,mode=IN,jdbcType=VARCHAR},
            #{menuName,mode=IN,jdbcType=VARCHAR},
            #{menuUrl,mode=IN,jdbcType=VARCHAR},
            #{menuType,mode=IN,jdbcType=INTEGER},
            #{menuDescription,mode=IN,jdbcType=VARCHAR},
            #{menuParentId,mode=IN,jdbcType=INTEGER},
            #{menuSort,mode=IN,jdbcType=DOUBLE},
            #{flagParam,mode=OUT,jdbcType=INTEGER}
         )
      }
  </select>

---

  <resultMap id="BaseResultMap" type="com.xz.bean.MenuInfo">
        <id column="menu_id" jdbcType="INTEGER" property="menuId"/>
        <result column="menu_name" jdbcType="VARCHAR" property="menuName"/>
        <result column="menu_code" jdbcType="VARCHAR" property="menuCode"/>
        <result column="menu_url" jdbcType="VARCHAR" property="menuUrl"/>
        <result column="menu_type" jdbcType="INTEGER" property="menuType"/>
        <result column="menu_description" jdbcType="VARCHAR" property="menuDescription"/>
        <result column="menu_parent_id" jdbcType="INTEGER" property="menuParentId"/>
        <result column="menu_sort" jdbcType="DOUBLE" property="menuSort"/>
        <result column="menu_create_person" jdbcType="VARCHAR" property="menuCreatePerson"/>
        <result column="menu_create_time" jdbcType="DATE" property="menuCreateTime"/>
        <result column="menu_extend1" jdbcType="VARCHAR" property="menuExtend1"/>
        <result column="menu_extend2" jdbcType="VARCHAR" property="menuExtend2"/>
        <result column="menu_extend3" jdbcType="VARCHAR" property="menuExtend3"/>
        <result column="menu_extend4" jdbcType="VARCHAR" property="menuExtend4"/>
        <result column="menu_extend5" jdbcType="VARCHAR" property="menuExtend5"/>
        <result column="flagParam" jdbcType="VARCHAR" property="flagParam"/>
    </resultMap>

 

---

 private static final long serialVersionUID = 1L;
    private String menuCreatePerson;
    private String menuCreateTime;
    private Integer menuId;
    private String menuName;
    private String menuCode;
    private String menuUrl;
    private Integer menuType;
    private String menuDescription;
    private Integer menuParentId;
    private Double menuSort;
    private String menuExtend1;
    private String menuExtend2;
    private String menuExtend3;
    private String menuExtend4;
    private String menuExtend5;
    private Integer flagParam;

---

CREATE DEFINER=`root`@`localhost` PROCEDURE `authorityAdd`(
IN `menuCreatePerson` VARCHAR ( 20 ),
IN `menuCreateTime` VARCHAR ( 20 ),
IN `menuName` VARCHAR ( 20 ),
IN `menuUrl` VARCHAR ( 20 ),
IN `menuType` INT,
IN `menuDescription` VARCHAR ( 20 ),
IN `menuParentId` INT,
IN `menuSort` DOUBLE ( 10, 5 ) ,
OUT `flagParam` INT
)
BEGIN


DECLARE
	countResult INT DEFAULT - 1;
DECLARE
	maxResult INT DEFAULT - 1;
DECLARE
	maxResult2 INT DEFAULT - 1;
DECLARE
	maxResult3 INT DEFAULT - 1;
DECLARE
	maxResult4 INT DEFAULT - 1;
DECLARE
	maxResult5 INT DEFAULT - 1;
	
	
	DECLARE t_error INTEGER DEFAULT 0;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION	SET t_error=1;
	START TRANSACTION;
	
	
	


IF t_error = 1 THEN
		ROLLBACK;
		SELECT
			500 INTO flagParam;
			SELECT flagParam;
		ELSE COMMIT;
		SELECT
			200 INTO flagParam;
				SELECT flagParam;
		
	END IF;




END