Mybatis 对存储过程的操作

创建存储过程

#删除数据库
drop database if exists mybatis;
#创建数据库
create database if not exists mybatis;
#切换数据库
use mybatis;
#创建表
create table t_worker(
	id int primary key auto_increment,
	name varchar(20) unique,
	age int default 1,
	sex char(6) default '男',
	birthday date,
	address varchar(200)
	
);
#插入测试数据库
insert into t_worker values
(null,'小丽',20,'女','1992-9-3','北京'),
(null,'小强',18,'男','1998-9-3','上海'),
(null,'赵信',20,'男','1982-9-3','广州'),
(null,'胡娜',20,'女','1992-9-3','深圳');
#查看数据
select * from t_worker;


#删除存储过程
drop procedure  pro_selectWorkers;
# 1. 创建无参的存储过程
DELIMITER $ -- 声明存储过程的结束符
CREATE PROCEDURE  pro_selectWorkers()
BEGIN
	SELECT * FROM t_worker;
END$
DELIMITER ;

#调用存储过程
CALL pro_selectWorkers();





#删除存储过程
drop procedure pro_selectWorkerByName;
# 2. 创建带输入参数的存储过程
DELIMITER $ -- 声明存储过程的结束符  IN：代表参数的方向为输入参数，即实参传值到形参的过程，此时IN可以省略
CREATE PROCEDURE pro_selectWorkerByName(IN param_name varchar(20))
BEGIN
	SELECT * FROM t_worker WHERE name=param_name;
END$
DELIMITER ;

#调用存储过程
CALL pro_selectWorkerByName('小丽');



# 3. 创建带输出参数的存储过程
DELIMITER $  -- OUT：代表参数是输出参数，所谓输出参数就是在存储过程中赋值反向传递到实参的过程,输出参数的OUT不能省略
CREATE PROCEDURE pro_worker_count(OUT param_count INT)
BEGIN
  SELECT COUNT(1) INTO param_count FROM t_worker;
END $
DELIMITER ; #中间必须有空格，否则报错

#调用带输出参数的存储过程;@表示定义局部变量
CALL pro_worker_count(@param_count);
SELECT @param_count; #显示变量的值

# 4. 创建带输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_worker_inout(IN param_name VARCHAR(20),OUT param_age INT,OUT param_sex VARCHAR(2),OUT param_birthday date)
BEGIN
	SELECT age,sex,birthday INTO param_age,param_sex,param_birthday  FROM t_worker WHERE name=param_name; 
END $
DELIMITER ;

#调用存储过程
CALL pro_worker_inout('小丽',@age,@sex,@birthday);
SELECT @age,@sex,@birthday;


# 5. 创建插入记录的存储过程
DELIMITER $
CREATE PROCEDURE pro_worker_insert(param_name VARCHAR(20), param_age INT,param_sex VARCHAR(2),param_birthday date,param_addr varchar(100))
BEGIN
	insert into t_worker values(null,param_name,param_age,param_sex,param_birthday,param_addr);
END $
DELIMITER ;

#调用存储过程
CALL pro_worker_insert('小白',20,'女','1985-9-4','北京');
#查看数据
select * from t_worker;

worker类

public class Worker {

	private int id;
	private String name;
	private int age;
	private String sex;
	private Date birthday;
	private String address;
 ..............

WorkerDao接口

public interface WorkerDao {
	
	/**
	 * 调用无参的存储过程
	 * @return
	 * @throws Exception
	 */
	List<Map<String,Object>> callProcedureWithNoParam() throws Exception;
	
	/**
	 * 调用带输入参数的存储过程
	 * @param name
	 * @return
	 * @throws Exception
	 */
	List<Map<String,Object>> callProcedureWithInParam(String name) throws Exception;
	
	/**
	 * 调用无参的存储过程返回Worker类型的对象
	 * @param name
	 * @return
	 * @throws Exception
	 */
	List<Worker> selectWorkersWithNoParam(String name) throws Exception;
	
	/**
	 * 调用带输入参数的存储过程
	 * @param name
	 * @return
	 * @throws Exception
	 */
	List<Worker> selectWorkerByNameWithInputParam(String name) throws Exception;
	
	/**
	 * 统计工人的数量并且通过输出参数返回
	 * @return
	 * @throws Exception
	 */
	void selectWorkerCountWithOutputParam(Map<String,Object> map) throws Exception;
	
	/**
	 * 带输入输出参数的存储过程：根据工人的姓名返回工人的年龄，性别和生日
	 * @param map
	 * @throws Exception
	 */
	void selectWorkerWithInputOutputParam(Map<String,Object> map) throws Exception;
	
	/**
	 * 插入数据
	 * @param map
	 * @throws Exception
	 */
	int callProcedureWithInsertPro(Map<String,Object> map) throws Exception;
}

WorkerDao.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.hsj.dao.WorkerDao">

	<!-- 
	调用无参的存储过程,
	如果存储过程中没有输出参数，则
	statementType="CALLABLE"可以省略。
	 -->
	<select id="callProcedureWithNoParam" resultType="map" >
		{call pro_selectWorkers()}
	</select>


	<!-- 
	statementType="CALLABLE"
	 -->
	<select id="callProcedureWithInParam" parameterType="String" resultType="map" >
		{call pro_selectWorkerByName(#{name})}
	</select>
	
	<!-- 
	statementType="CALLABLE"
	 -->
	<select id="selectWorkersWithNoParam" resultType="Worker" >
		{call pro_selectWorkers()}
	</select>
	
	
	 <parameterMap type="map" id="parameterMap_InputParam">
	 	<parameter property="param_name"/>
	 </parameterMap>
	 <!-- 
	 statementType="CALLABLE"
	  -->
	<select id="selectWorkerByNameWithInputParam" parameterMap="parameterMap_InputParam" resultType="Worker" >
		{call pro_selectWorkerByName(?)}
	</select>
	
	
	<!-- 
		**3.调用带输出参数的存储过程
	一旦存储过程带输出参数了，则必须在select标签中增加属性：statementType="CALLABLE"
		  必须指定输出参数的数据类型：jdbcType="INTEGER"，参数值必须全部大写
	 -->**
	 <parameterMap type="map" id="parameterMap_OutputParam">
	 	<parameter property="workerCount" mode="OUT" jdbcType="INTEGER"/>
	 </parameterMap>
	 **<!-- statementType="CALLABLE":一旦存储过程中有输出参数，
	 则此时statementType="CALLABLE"这句不能省略
	 
	  -->**
	<select id="selectWorkerCountWithOutputParam" parameterMap="parameterMap_OutputParam" statementType="CALLABLE">
		{call pro_worker_count(?)}
	</select>
	
	<parameterMap type="map" id="parameterMap_InputOutputParam">
	 	<parameter property="param_name"/>
	 	<parameter property="param_age" mode="OUT" jdbcType="INTEGER"/>
	 	<parameter property="param_sex" mode="OUT" jdbcType="VARCHAR"/>
	 	<parameter property="param_birthday" mode="OUT" jdbcType="DATE"/>
	 </parameterMap>
	<select id="selectWorkerWithInputOutputParam" parameterMap="parameterMap_InputOutputParam" statementType="CALLABLE">
		{call pro_worker_inout(?,?,?,?)}
	</select>
	
	
		<insert id="callProcedureWithInsertPro" parameterType="map">
		{call pro_worker_insert(#{name},#{age},#{sex},#{birthday},#{address})}
	</insert>
	
</mapper>

测试Dao中的方法

public class WorkerDaoTest {

	private WorkerDao workerDao;
	@Before
	public void setUp() throws Exception {
		SqlSession sqlSession=MyBatisUtils.getSqlSession();
		this.workerDao=sqlSession.getMapper(WorkerDao.class);
	}

	@Test
	public void testCallProcedureWithNoParam() {
		try {
			List<Map<String,Object>> list=this.workerDao.callProcedureWithNoParam();
			System.out.println(list);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	@Test
	public void testCallProcedureWithInParam() {
		try {
			List<Map<String,Object>> list=this.workerDao.callProcedureWithInParam("小丽");
			System.out.println(list);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	@Test
	public void testSelectWorkersWithNoParam() {
		try {
			List<Worker> list=this.workerDao.selectWorkersWithNoParam("小丽");
			System.out.println(list);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Test
	public void testSelectWorkerByNameWithInputParam() {
		try {
			List<Worker> list=this.workerDao.selectWorkerByNameWithInputParam("小丽");
			System.out.println(list);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Test
	public void testSelectWorkerCountWithOutputParam() {
		try {
			Map<String,Object> map=new HashMap<String, Object>();
			this.workerDao.selectWorkerCountWithOutputParam(map);
			System.out.println(map.get("workerCount"));
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Test
	public void testSelectWorkerWithInputOutputParam() {
		try {
			Map<String,Object> map=new HashMap<String, Object>();
			map.put("param_name", "小丽");
			this.workerDao.selectWorkerWithInputOutputParam(map);
			System.out.println(map.get("param_age"));
			System.out.println(map.get("param_sex"));
			System.out.println(map.get("param_birthday"));
			System.out.println(map);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Test
	public void testCallProcedureWithInsertPro() {
		try {
			Map<String,Object> map=new HashMap<>();
			map.put("name", "小龙女");
			map.put("age", 20);
			map.put("sex", "女");
			map.put("birthday", new Date());
			map.put("address", "北京");
			int count=this.workerDao.callProcedureWithInsertPro(map);
			System.out.println("count="+count);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	

}