Mybatis快速上手6 定义和调用存储过程

其他章节及开发包：https://blog.youkuaiyun.com/XIMAX/article/details/106255196# 环境 ：Eclipse+Tomcat9+JDK10+Mybatis-3.1.1、Navicat

test1~8分别对应八章，本章使用包test5、tool，文件config.xml、db.properties、log4j.properties

需求

查询得到男性或女性的数量, 如果传入的是 0 就是女性否则是 男性。

代码

建库，建表

create table p_user(  
 id int primary key auto_increment,  
 name varchar(10),
 sex char(2)
); 
insert into p_user(name,sex) values('A',"男");  
insert into p_user(name,sex) values('B',"女");  
insert into p_user(name,sex) values('C',"男");  

创建存储过程

DELIMITER $
CREATE PROCEDURE mybatis.get_user_count(IN sex_id INT, OUT user_count INT)
BEGIN  
IF sex_id=0 THEN
SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='女' INTO user_count;
ELSE
SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='男' INTO user_count;
END IF;
END 
$

config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <properties resource="db.properties"></properties>
  <typeAliases>
    <package name="com.rjxy.test6"/>
 <typeAliases>
 
 <typeAliases>
   <package name="com.rjxy.test2"/>
 <typeAliases>
 <environments default="development">
   <environment id="development">
    <transactionManager type="JDBC" />
    <dataSource type="POOLED">
     <property name="driver" value="${driver}" />
     <property name="url" value="${url}" />
     <property name="username" value="${username}" />
     <property name="password" value="${password}" />
    </dataSource>
   </environment>
  </environments>  
 
 <mappers>
   <mapper resource="com/rjxy/test6/userMapper.xml"/>
 <mappers>
</configuration>

userMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 
<mapper namespace="com.rjxy.test6.userMapper">
 <!-- 调用存储过程
   查询得到男性或女性的数量，如果传入的是0就是女性，否则就是男性
 -->
 
 <select id="getUserCount" statementType="CALLABLE" parameterMap="getUserCountMap">
  CALL mybatis.get_user_count(?,?)
 </select>
 <parameterMap type="java.util.Map" id="getUserCountMap">
  <parameter property="sexid" mode="IN" jdbcType="INTEGER"/>
  <parameter property="usercount" mode="OUT" jdbcType="INTEGER"/>
 </parameterMap>
 
 <!-- 
  parameterMap:引用<parameterMap>
  statementType:指定Statement的真实类型 CALLABLE 执行调用存储过程的语句
   
   <parameterMap>:定义多个参数的键值对
   type:需要传递的参数的真实类型
   <parameter>:指定一个参数key-value>
  -->
</mapper>

PUser.java

package com.rjxy.test6;
public class PUser {
 private String id;
 private String name;
 private String sex;

public PUser() {}
public PUser(String id,String name,String sex) {
  this.id = id;
  this.name = name;
  this.sex = sex;
 }
 //省略 get、set 及 toString 方法

JTest6.java

package com.rjxy.test6;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.jupiter.api.Test;

import com.rjxy.tool.MybatisUtils;
class JTest6 {
@Test
 void test() throws IOException {
  SqlSessionFactory factory = MybatisUtils.getFactory();
  SqlSession session = factory.openSession();

  String statement = "com.rjxy.test6.userMapper.getUserCount";

  Map<String,Integer> parameterMap = new HashMap<String,Integer>();
  parameterMap.put("sexid", 1);
  parameterMap.put("usercount", -1);

 session.selectOne(statement,parameterMap);

  Integer result = parameterMap.get("usercount");
  System.out.println(result);
  session.close();
 }
}

其它

其它文件与该系列第一篇相同 链接： https://blog.youkuaiyun.com/XIMAX/article/details/106244701

结果

2020-05-21 11:44:58,355 [main] DEBUG [com.rjxy.test6.userMapper.getUserCount] - ooo Using Connection [com.mysql.cj.jdbc.ConnectionImpl@5c3b6c6e]
2020-05-21 11:44:58,356 [main] DEBUG [com.rjxy.test6.userMapper.getUserCount] - ==>  Preparing: CALL mybatis.get_user_count(?,?) 
2020-05-21 11:44:58,752 [main] DEBUG [com.rjxy.test6.userMapper.getUserCount] - ==> Parameters: 1(Integer)
2