mybatis調用mysql預存程序

標籤：

返回類似 select *from 的做法
過程：
create procedure selectAll()
BEGIN
select * from user;
end

xml配置：
<select id="selectall" resultType="map" statementType="CALLABLE">
{call selectAll()}
</select>

java配置：
//service層調用
List<Map<String, Object>> ss = accountMapper.selectall();
//DAO調用
public List<Map<String,Object>> selectall();

Sql代碼
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `selectCount`(
IN pcsId int,
IN drId int,
IN partnerId int,
IN customerId int,
OUT pcsCount int,
OUT drCount int

)
BEGIN

select count(md.id) into @pcsC from mdm_device md
left join mdm_device_security mds on mds.device_id = md.id
where mds.device_rooted = pcsId
and md.partner_id = partnerId and md.customer_id = customerId;
set pcsCount = @pcsC;


select count(md.id) into @drC from mdm_device md
where md.managed_status = drId and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(md.un_manage_date)
and md.partner_id = partnerId and md.customer_id = customerId;
set drCount = @drC;



END


1.java調用傳入MAP。擷取通過MAP擷取。
1.1 mapper檔案寫法
Xml代碼
<parameterMap type="map" id="homeVO">
<parameter property="pcsId" jdbcType="INTEGER" mode="IN"/>
<parameter property="drId" jdbcType="INTEGER" mode="IN"/>
<parameter property="partnerId" jdbcType="INTEGER" mode="IN"/>
<parameter property="customerId" jdbcType="INTEGER" mode="IN"/>
<parameter property="pcsCount" jdbcType="INTEGER" mode="OUT"/>
<parameter property="drCount" jdbcType="INTEGER" mode="OUT"/>
</parameterMap>
<select id="selectForHome" parameterMap="homeVO"
statementType="CALLABLE">
{call selectCount(
?,?,?,?,?,?
)}
</select>
1.2 java調用寫法
Java代碼
@Override
public StringselectHomeCount(HomeVO home) throws Exception {
Map<String, Object> map = new HashMap<String, Object>();
map.put("pscId", 0);
map.put("drId", 1);
map.put("partnerId", 25);
map.put("customerId", 50);
map.put("isolation", 1);
selectOne("Mapper.selectForHome", map);
System.out.println(map.get("pcsCount"));

return map.get("drCount");
}

mybatis調用mysql預存程序