MyBatisProcedure

注意：有两种传参数的方式，Map 或 bean

mybatis中 Mapper 文件

<!--learn procedure-->
    <!--for test procedure with map paramater-->
    <parameterMap type="map" id="testProcedure">
        <parameter property="p1" jdbcType="INTEGER" mode="IN"/>
        <parameter property="p2" jdbcType="INTEGER" mode="IN"/>
        <parameter property="result" jdbcType="INTEGER" mode="OUT"/>
        <parameter property="report" jdbcType="VARCHAR" mode="OUT"/>
    </parameterMap>
    <select id="selectSummaryReportWithMap" parameterMap="testProcedure"
            statementType="CALLABLE">
        {call micro_message.playMybatis(
        ?,?,?,?
        )}
    </select>

    <!--for test procedure with bean paramater-->
    <select id="selectSummaryReportWithBean" parameterType="com.imooc.bean.SummaryReport"
            statementType="CALLABLE">
        {call micro_message.playMybatis(
        #{p1,jdbcType=INTEGER, mode=IN},
        #{p2,jdbcType=INTEGER, mode=IN},
        #{result, jdbcType=INTEGER,mode=OUT},
        #{report, jdbcType=VARCHAR,mode=OUT}
        )}
    </select>
    <!-- end learn procedure-->

The Client

/**
 * Created by shan on 2016/2/17.
 */
public class CommandDao {

    //使用 map 参数
    public void ProcedureWithMapParamater(int p1,int p2) {
        DBAccess dbAccess = new DBAccess();

        SqlSession sqlSession = null;
        try {
            sqlSession = dbAccess.getSqlSession();

            Map<String, Object> map = new HashMap<String, Object>();
            map.put("p1", p1);
            map.put("p2", p2);
            sqlSession.selectOne("CommandContent.selectSummaryReportWithMap", map);

            Logger logger = Logger.getLogger(CommandDao.class);
            logger.info(map.get("result"));
            logger.info(map.get("report"));
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //使用 bean 参数
    public void ProcedureWithBeanParamater(int p1,int p2) {
        DBAccess dbAccess = new DBAccess();

        SqlSession sqlSession = null;
        try {
            sqlSession = dbAccess.getSqlSession();

            SummaryReport summaryReport = new SummaryReport();
            summaryReport.setP1(p1);
            summaryReport.setP2(p2);
            sqlSession.selectOne("CommandContent.selectSummaryReportWithBean", summaryReport);

            Logger logger = Logger.getLogger(CommandDao.class);
            logger.info(summaryReport.getResult());
            logger.info(summaryReport.getReport());
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

测试代码：

/**
   * test call procedure in map paramater
   */
  @Test
  public void testProcedure(){
      commandDao.ProcedureWithMapParamater(23,22);
  }

  /**
   * test call procedure in bean paramater
   */
  @Test
  public void testProcedureWithBean(){
      commandDao.ProcedureWithBeanParamater(11,31);
  }

output1:

2016-02-24 16:14:24,036 [main] DEBUG [CommandContent.selectSummaryReportWithBean] - ==>  Preparing: {call micro_message.playMybatis( ?, ?, ?, ? )}
2016-02-24 16:14:24,113 [main] DEBUG [CommandContent.selectSummaryReportWithBean] - ==> Parameters: 23(Integer), 22(Integer)
2016-02-24 16:14:24,139 [main] INFO  [com.imooc.dao.CommandDao] - 45
2016-02-24 16:14:24,139 [main] INFO  [com.imooc.dao.CommandDao] - 45 is the result at 2016-02-24 16:14:24

output2:

2016-02-24 16:54:37,184 [main] DEBUG [CommandContent.selectSummaryReportWithBean] - ==>  Preparing: {call micro_message.playMybatis( ?, ?, ?, ? )}
2016-02-24 16:54:37,266 [main] DEBUG [CommandContent.selectSummaryReportWithBean] - ==> Parameters: 11(Integer), 31(Integer)
2016-02-24 16:54:37,272 [main] INFO  [com.imooc.dao.CommandDao] - 42
2016-02-24 16:54:37,273 [main] INFO  [com.imooc.dao.CommandDao] - 42 is the result at 2016-02-24 16:54:37

存储过程，这就是做个加法

DELIMITER $$

USE `micro_message`$$

DROP PROCEDURE IF EXISTS `playMybatis`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `playMybatis`(IN p1 INT,IN p2 INT,OUT result INT,OUT report VARCHAR(50))
BEGIN
    SELECT p1+p2 INTO @a;
    SET result = @a;

    SELECT CONCAT(@a,' is the result at ',NOW()) INTO @b;
    SET report = @b;
    END$$

DELIMITER ;