mybatis 调用mysql存储过程_mybatis调用mysql存储过程

返回类似 select *from 的做法

过程：

create procedure selectAll()

BEGIN

select * from user;

end

xml配置：

{call selectAll()}

java配置：

//service层调用

List> ss = accountMapper.selectall();

//DAO调用

public List> 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代码

statementType="CALLABLE">

{call selectCount(

?,?,?,?,?,?

)}

1.2 java调用写法

Java代码

@Override

public StringselectHomeCount(HomeVO home) throws Exception {

Map map = new HashMap();

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");

}