七、Mybatis调用存储过程

来源：互联网发布：天猫国际销售数据编辑：程序博客网时间：2024/06/04 01:27

7.1准备数据库表和存储过程

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',"男");

#创建存储过程(查询得到男性或女性的数量, 如果传入的是 0 就女性否则是男性)DELIMITER $CREATE PROCEDURE mybatis.ges_user_count(IN sex_id INT, OUT user_count INT)BEGINIF sex_id=0 THENSELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='女' INTO user_count;ELSESELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='男' INTO user_count;END IF;END$

#调用存储过程DELIMITER ;SET @user_count = 0;CALL mybatis.ges_user_count(1, @user_count);SELECT @user_count;

7.2创建表的实体类

PUser.java

package com.atguigu.mybatis_test.bean;public class PUser {    private String id;    private String name;    private String sex;    public PUser() {        super();    }    public PUser(String id, String name, String sex) {        super();        this.id = id;        this.name = name;        this.sex = sex;    }    public String getId() {        return id;    }    public void setId(String id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getSex() {        return sex;    }    public void setSex(String sex) {        this.sex = sex;    }    @Override    public String toString() {        return "PUser [id=" + id + ", name=" + name + ", sex=" + sex + "]";    }}

7.3创建映射文件

PUserMapper.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.config.PUserMapper">    <!--     查询得到男性或女性的数量, 如果传入的是 0 就女性否则是男性    -->    <select id="getUserCount" parameterMap="getUserCountMap" statementType="CALLABLE">        CALL mybatis.ges_user_count(?,?)    </select>    <!-- parmeterMap.put("sexid", 0);        parmeterMap.put("usercount", -1); -->    <parameterMap id="getUserCountMap" type="java.util.Map">        <parameter property="sexid" mode="IN" jdbcType="INTEGER"/>        <parameter property="usercount" mode="OUT" jdbcType="INTEGER"/>    </parameterMap></mapper>

parameterMap：引用
statementType：指定Statement的真是类型：CALLABLE执行调用存储过程的语句
：定义多个参数的键值对
type：需要传递的参数的真实类型 java.util.Map
：指定一个参数key-value

7.4将映射文件注册到config.xml中

config.xml

<mappers>      <mapper resource="com/config/PUserMapper.xml"/></mappers>

7.5测试类

Test7.java

package com.test;import java.io.IOException;import java.util.HashMap;import java.util.Map;import org.apache.ibatis.session.SqlSession;import org.junit.Test;import com.util.MybatisUtil;public class Test7 {    @Test    public void testGetUserCount() throws IOException    {        SqlSession session=MybatisUtil.getSession();        String statement="com.config.PUserMapper.getUserCount";        Map<String,Integer> parmeterMap=new HashMap<String,Integer>();        parmeterMap.put("sexid", 0);        parmeterMap.put("usercount", -1);        session.selectOne(statement, parmeterMap);        Integer result=parmeterMap.get("usercount");        System.out.println(result);        session.close();    }}

0 0