5.MyBaits调用存储过程

1.创建一个javaweb项目MyBatis_Part4_Procedure

2.在src下创建procedure.sql文件

--创建表
create table p_user(  
    id number primary key,  
    name varchar2(20),
    sex varchar2(2)
); 

create sequence seq_puser;

insert into p_user values(seq_puser.nextval,'A','男');  
insert into p_user values(seq_puser.nextval,'B','女');  
insert into p_user values(seq_puser.nextval,'C','男');  

commit;

--统计男女的数量
CREATE or replace PROCEDURE holly.get_user_count(sex_id IN number,user_count OUT number)
is
BEGIN 
IF sex_id=0 THEN
 SELECT COUNT(*) INTO user_count FROM p_user WHERE p_user.sex='女';
ELSE
  SELECT COUNT(*) INTO user_count FROM p_user WHERE p_user.sex='男' ;
END IF;
END ;
/

3.在src下创建jdbc.properties属性文件

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
username=holly
password=sys

4.在src下创建configuration.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>

  <!-- 1.引用jdbc链接数据库的属性文件 -->
  <properties resource="jdbc.properties"/>
  
  <!-- 2.类路径的别名 --><!--
  <typeAliases>
    <typeAlias type="com.entity.Classes" alias="Classes"/>
    <typeAlias type="com.entity.Teacher" alias="Teacher"/>
    <typeAlias type="com.entity.Student" alias="Student"/>
  </typeAliases>
  
  --><!-- 3.事务和数据源的配置 -->
  <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>
  <!-- 4.注册映射文件 -->
  <mappers>
  <!-- 有映射文件时resource属性=映射文件的路径 -->
  <!-- 使用注解没有映射文件时class属性=映射文件的路径 -->
  <mapper resource="com/mapper/PUserMapper.xml"/>
   
  </mappers>
  
</configuration>

5.在src下com.entity创建PUser.java

package com.entity;

public class PUser {
    private int id;
    private String name;
    private String sex;
    
    public PUser() {
    }
    public PUser(int id, String name, String sex) {
        this.id = id;
        this.name = name;
        this.sex = sex;
    }
    public int getId() {
        return id;
    }
    public void setId(int 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 + "]";
    }
    
    

}

6.在src下com.util包下创建MyBaitsUtil.java

package com.util;

import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBaitsUtil {
    private static SqlSessionFactory sqlSessionFactory;
    
    static{
        try {
            Reader reader=Resources.getResourceAsReader("configuration.xml");
            sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader); 
        } catch (IOException e) {
            e.printStackTrace();
        }
        
    }
    /**
     * 
     * @param isCommit true表示执行完操作提交事务，false表示执行完不自动提交事务
     * @return
     */
    public static SqlSession getSqlSession(boolean isCommit){
        return sqlSessionFactory.openSession(isCommit);
    }

}

7.在src下com.mapper包下创建PUserMapper.java

package com.mapper;

import java.util.Map;

public interface PUserMapper {
    /**
     * 统计某个性别的数据
     * @param paramMap
     * @return
     */
    int getCountBySex(Map<String, Integer> paramMap);

}

8.在src下com.mapper包下创建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.mapper.PUserMapper">
   <!--parameterMap表示参数是一个集合，
   statementType="CALLABLE" 表示该节点下写的是调用存储过程的语句 -->
   <select id="getCountBySex" parameterMap="paramMap" 
     statementType="CALLABLE">
      CALL holly.get_user_count(?,?)
   </select>
   <!-- 配置参数集合 -->
   <parameterMap id="paramMap" type="java.util.Map" >
     <!-- 配置集合的输入参数 -->
    <parameter property="sexid" mode="IN" jdbcType="INTEGER"/>
    <!-- 配置集合的输出参数 -->
    <parameter property="usercount" mode="OUT" jdbcType="INTEGER"/>
   </parameterMap>
   
</mapper>

9.在src下com.mapper.impl包下创建PUserMapperImpl.java

package com.mapper.impl;

import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.mapper.PUserMapper;
import com.util.MyBaitsUtil;

public class PUserMapperImpl implements PUserMapper {
   
    public int getCountBySex(Map<String, Integer> paramMap) {
        SqlSession sqlSession=MyBaitsUtil.getSqlSession(false);
        String statement="com.mapper.PUserMapper.getCountBySex";
        //调用存储过程，输出参数参数给usercount
        sqlSession.selectOne( statement, paramMap);
        //通过key获取value
        int count=paramMap.get("usercount");
        sqlSession.close();
        return count;
    }

}

10.在src下com.test包下创建Test.java

package com.test;

import java.util.HashMap;
import java.util.Map;

import com.mapper.PUserMapper;
import com.mapper.impl.PUserMapperImpl;

public class Test {

    /**
     * @param args
     */
    public static void main(String[] args) {
        PUserMapper mapper=new PUserMapperImpl();
        
        Map<String, Integer> paramMap=new HashMap<String, Integer>();
        paramMap.put("sexid", 0);//0女 1男
        paramMap.put("usercount", -1);
        
        int count=mapper.getCountBySex(paramMap);
        
        if(count>0){
            System.out.println("男员工的数目为："+count);
        }

    }

}