MyBatis调用存储函数

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文件编写