MyBatis 调用储存过程
MyBatis 同样支持调用数据库中的储存过程,以下是一个调用数据库中分页查询储存过程的示例:
MySQL 中的储存过程如下:
# 储存过程,根据 user_name 查询,并接收相关参数进行分页操作,输出符合的结果总数
DROP PROCEDURE IF EXISTS select_user_page;
DELIMITER //
CREATE PROCEDURE select_user_page(
IN userName VARCHAR(30),
IN _offset BIGINT,
IN _limit BIGINT,
OUT total BIGINT)
BEGIN
# 查询结果总数
select count(*) into total
from user
where user_name like concat('%',userName,'%');
# 分页查询
select *
from user
where user_name like concat('%',userName,'%')
limit _offset, _limit;
END //
DELIMITER ;
POJO User 实体映射的 XML 配置文件, mapper/UserMapper.xml:
<mapper namespace="site.assad.dao.UserDao">
<!--User 的基本实体映射-->
<resultMap id="userMap" type="site.assad.domain.User">
<id property="id" column="user_id" />
<result property="name" column="user_name" />
<result property="email" column="user_email" />
</resultMap>
<!--调用储存过程 select_user_page -->
<select id="getUserByNameWithPage" resultMap="userMap" useCache="false" statementType="CALLABLE">
{call select_user_page(
#{userName, mode=IN},
#{offset, mode=IN},
#{limit, mode=IN},
#{total, mode=OUT, jdbcType=BIGINT}
)}
</select>
</mapper>
在Dao接口
site.assad.dao.UserDao
中定义接口方法:
public interface UserDao {
List<User> getUserByNameWithPage(Map<String, Object> params);
}
相关的测试代码:
public class UnitTest {
private UserDao userDao;
public void init() throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = sqlSessionFactory.openSession();
userDao = sqlSession.getMapper(UserDao.class);
reader.close();
}
public void testGetUserByNameWithPage(){
//构建入参Map
Map<String, Object> params = new HashMap<>();
params.put("userName","e");
params.put("offset",0);
params.put("limit",10);
//调用映射接口方法
List<User> resultList = userDao.getUserByNameWithPage(params);
long total = (Long) params.get("total");
System.out.println("total results: " + total);
resultList.forEach(System.out::println);
}
}