Mybatis calls the mysql stored procedure and returns the result set, mybatismysql

Mybatis calls the mysql stored procedure and returns the result set, mybatismysql

A result set is often returned in a stored procedure. Mysql directly uses select to return the result set. Oracle uses a cursor to return the result set. This is relatively convenient for Mysql. The following code can implement the output result set:

Stored Procedure Definition:

DELIMITER $$ DROP procedure IF EXISTS pro_sql_data1 $$  CREATE procedure pro_sql_data1(in sear_name  varchar(2000))  BEGIN   if sear_name is not null and sear_name!='' thenselect id,name,date_format(create_time,'%Y-%m-%d') as repDate from ad_place where name like concat('%',sear_name,'%');ELSEselect id,name,date_format(create_time,'%Y-%m-%d') as repDate from ad_place;end if;END$$DELIMITER; 

Execution result:

Call the stored procedure in mybatis and obtain the result set:

1. xml configuration file

<?xml version="1.0" encoding="UTF-8" ?>  <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.ifeng.iis.bean.iis.Report" ><resultMap type="java.util.HashMap" id="resultMap">       <result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>       <result column="name" property="name" javaType="java.lang.String" jdbcType="VARCHAR"/>       <result column="repDate" property="repDate" javaType="java.lang.String" jdbcType="VARCHAR"/>       <result column="summ" property="summ" javaType="java.lang.Long" jdbcType="BIGINT"/>    </resultMap>     <select id="test123" parameterType="java.util.Map"  resultMap="resultMap" statementType="CALLABLE" >        {call pro_sql_data(      #{obj,jdbcType=VARCHAR,mode=IN}   )      }    </select>  </mapper>

Java code

public String query(String param) throws Exception {logger.info(param);Map queryMap = new HashMap();queryMap.put("obj", param);//List<Map> listIis1 = reportDao.select4MapParam(queryMap, "currentSql");List<Map> listIis2 =reportDao.select4MapParam(queryMap,"test123");return JSONArray.fromObject(listIis2).toString();}

Note:As we can see above, mysql can directly use the select statement to return the result set in the stored procedure, and mybatis can directly use list to receive the result set (no cursor is required ).

Reference: http://yhjhappy234.blog.163.com/blog/static/316328322012455714892/

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.