MyBatis调用存储函数
针对MyBasits对存储函数的调用,今天老师在课上讲了一下,我觉得挺重要,就记录一下吧!
下面从两个例子来进行探讨!
一、有输入参数的存储过程
1、创建存储函数
-- ----------------------------
-- Procedure structure for getCustomerByName
-- ----------------------------
DROP PROCEDURE IF EXISTS `getCustomerByName`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getCustomerByName`(IN `customer_name` varchar(50))
BEGIN
#Routine body goes here...
SELECT * FROM t_customer WHERE username LIKE CONCAT("%",customer_name,"%");
END
;;
DELIMITER ;
2、Mapper文件
<select id="getCustomerByName" parameterType="String" resultType="customer">
call getCustomerByName(#{username})
</select>
3、Test类
@Test
public void getCustomerByNameTest(){
SqlSession sqlsession = MyBatisUtils.getSqlsession();
CustomerMapper mapper = sqlsession.getMapper(CustomerMapper.class);
List<Customer> customers = mapper.getCustomerByName("ja");
sqlsession.close();
customers.forEach(c -> System.out.println(c));
}
4、运行结果
可以正确的查询到结果!
针对普通的含有一个输入参数的存储过程,其调用过程非常简单,只需要编写对应的mapper文件,编写查询语句进行传参调用
二、有输入参数和输出参数的存储过程
1、创建存储过程
-- ----------------------------
-- Procedure structure for getCustomerCountByJobs
-- ----------------------------
DROP PROCEDURE IF EXISTS `getCustomerCountByJobs`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getCustomerCountByJobs`(IN `job` varchar(50),OUT `customer_count` int)
BEGIN
#Routine body goes here...
SELECT COUNT(*) FROM t_customer WHERE jobs=job INTO customer_count;
END
;;
DELIMITER ;
2、编写Mapper文件
<parameterMap type="Map" id="paramsMap">
<parameter property="job" mode="IN" jdbcType="VARCHAR"/>
<parameter property="customer_count" mode="OUT" jdbcType="INTEGER"/>
</parameterMap>
<select id="getCustomerCountByJobs" parameterMap="paramsMap" statementType="CALLABLE">
call getCustomerCountByJobs(?,?)
</select>
❓ 注意
🍦 此处既有输入参数,还有输出参数,因此,参数类型要用resultMap类型的Map类型
🍦Map中的参数要设置成jdbcType类型,要用MyBatis含有的JdbcTyle类型
MyBatis 通过包含的jdbcType类型
BIT FLOAT CHAR TIMESTAMP OTHER UNDEFINED TINYINT REAL VARCHAR BINARY BLOB NVARCHAR SMALLINT DOUBLE LONGVARCHAR VARBINARY CLOB NCHAR INTEGER NUMERIC DATE LONGVARBINARY BOOLEAN NCLOB BIGINT DECIMAL TIME NULL CURSOR
🍦要设置参数的输入输出模式
🍦调用时要用
?
作为占位符,而不是用#{}
3、Test类
@Test
public void selectByName4Test() {
SqlSession sqlsession = MyBatisUtils.getSqlsession();
CustomerMapper mapper = sqlsession.getMapper(CustomerMapper.class);
QueryCustomer queryCustomer = new QueryCustomer();
Customer customer = new Customer();
customer.setUsername("an");
queryCustomer.setCustomer(customer);
List<Customer> customers = mapper.selectByName4(queryCustomer);
customers.forEach(c -> System.out.println(c));
sqlsession.close();
}
4、运行结果
👿值得注意的就是含有多参数的存储过程的mapper文件编写