Mybatis学习总结（七）——调用存储过程
一、返回select结果集
1、创建存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS proc_queryUser;
CREATE PROCEDURE proc_queryUser(
IN user_name VARCHAR(50) CHARACTER SET utf8
)
BEGIN
SET @exeSql = CONCAT('SELECT id,username,sex,birthday,address ',
'from t_user where username like \'',user_name,'%\' order by id');
-- 打印sql
-- SELECT @exeSql;
-- 预定义一个语句，并将它赋给stmtsql
PREPARE stmtsql FROM @exeSql;
EXECUTE stmtsql;
-- 释放一个预定义语句的资源
DEALLOCATE PREPARE stmtsql;
END //
DELIMITER ;
2、存储过程调用
SET @user_name='小'; CALL proc_queryUser(@user_name);
3、在UserMapper.java中添加接口方法
/**调用存储过程查询用户**/
public List findUserByProc(String user_name);
/**调用存储过程查询用户**/
public List findUserByProc1(Map map);
4、在UserMapper.xml中添加如下配置项：
<!-- 调用存储过程 -->
<!-- 第一种方式，参数使用parameterType -->
<select id="findUserByProc" parameterType="java.lang.String" statementType="CALLABLE"
resultType="com.mybatis.entity.User">
{call proc_queryUser(#{user_name,jdbcType=VARCHAR,mode=IN})}
</select>
<parameterMap type="java.util.Map" id="userMap">
<parameter property="user_name" mode="IN" jdbcType="VARCHAR"/>
</parameterMap>
<!-- 调用存储过程 -->
<!-- 第二种方式，参数使用parameterMap -->
<select id="findUserByProc1" parameterMap="userMap" statementType="CALLABLE"
resultType="com.mybatis.entity.User">
{call proc_queryUser(?)}
</select>
说明：这里使用两种方式调用存储过程，两种方式的区别主要在于参数的使用方式上，第一种方式使用parameterType，第二种方式使用parameterMap。
5、测试代码：
package com.mybatis.test;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.mybatis.entity.User;
import com.mybatis.mapper.UserMapper;
public class TestMybatisProceduce {
private SqlSessionFactory sqlSessionFactory;
// 此方法是在执行@Test方法之前执行
@Before
public void setUp() throws Exception {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建SqlSessionFcatory
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void TestProceduce(){
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建Usermapper对象，mybatis自动生成mapper代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List userList = mapper.findUserByProc("小");
System.out.println(userList);
sqlSession.close();
}
@Test
public void TestProceduce1(){
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建Usermapper对象，mybatis自动生成mapper代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map map = new HashMap();
map.put("user_name", "小");
List userList = mapper.findUserByProc1(map);
System.out.println(userList);
sqlSession.close();
}
}
6、运行结果：
[User [id=8, username=小A, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], User [id=9, username=小B, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], User [id=10, username=小C, sex=1, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], User [id=11, username=小D, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015]] [User [id=8, username=小A, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], User [id=9, username=小B, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], User [id=10, username=小C, sex=1, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], User [id=11, username=小D, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015]]
二、带输入输出参数的存储过程
1、创建存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS proc_queryUserCount;
CREATE PROCEDURE proc_queryUserCount(
IN user_name VARCHAR(50) CHARACTER SET utf8,
OUT count INT
)
BEGIN
SET @exeSql = CONCAT('SELECT count(*) into @rowsCount from t_user where username like \'',user_name,'%\'');
-- 打印sql
-- SELECT @exeSql;
-- 预定义一个语句，并将它赋给stmtsql
PREPARE stmtsql FROM @exeSql;
EXECUTE stmtsql;
-- 释放一个预定义语句的资源
DEALLOCATE PREPARE stmtsql;
SET count = @rowsCount;
END //
DELIMITER ;
2、存储过程调用：
SET @user_name='小'; CALL proc_queryUserCount(@user_name,@count); SELECT @count;
3、在UserMapper.java中添加接口方法
/**调用存储过程(带输入输出参数的存储过程)**/ public void findUserCountByProc(Map map);
4、在UserMapper.xml中添加如下配置项：
<parameterMap type="java.util.HashMap" id="userMap1">
<parameter property="user_name" jdbcType="VARCHAR" mode="IN"/>
<parameter property="count" jdbcType="INTEGER" mode="OUT"/>
</parameterMap>
<!-- 调用存储过程 (带输入输出参数的存储过程)-->
<select id="findUserCountByProc" parameterMap="userMap1" statementType="CALLABLE">
{call proc_queryUserCount(?,?)}
</select>
5、测试代码：
@Test
public void TestProceduce2(){
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建Usermapper对象，mybatis自动生成mapper代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map map = new HashMap();
map.put("user_name", "小");
mapper.findUserCountByProc(map);
System.out.println("userCount="+map.get("count"));
sqlSession.close();
}
6、运行结果：
userCount=4
三、返回多个结果集
1、创建存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS proc_query;
CREATE PROCEDURE proc_query(
IN user_id INT,
OUT order_count INT
)
BEGIN
SET @exeSql = CONCAT('SELECT id,username,sex,date_format(birthday,\'%Y-%m-%d\')birthday,address ',
'from t_user where id=\'',user_id,'\'');
PREPARE stmtsql FROM @exeSql;
EXECUTE stmtsql;
DEALLOCATE PREPARE stmtsql;
SET @exeSql1 = CONCAT('SELECT id,user_id,number,date_format(createtime,\'%Y-%m-%d %H:%i:%s\')createtime ',
'from orders where user_id=\'',user_id,'\'');
PREPARE stmtsql1 FROM @exeSql1;
EXECUTE stmtsql1;
DEALLOCATE PREPARE stmtsql1;
SET @exeSql2 = CONCAT('SELECT count(*) into @rowsCount from orders ',
'where user_id=\'',user_id,'\'');
PREPARE stmtsql2 FROM @exeSql2;
EXECUTE stmtsql2;
DEALLOCATE PREPARE stmtsql2;
SET order_count = @rowsCount;
END //
DELIMITER ;
2、存储过程调用：
SET @user_id=2; CALL proc_query(@user_id,@order_count); SELECT @order_count;
3、在UserMapper.java中添加接口方法
/**调用存储过程(返回多个结果集)**/ public List<List<?>> findUserOrdersByProc(Map map);
4、在UserMapper.xml中添加如下配置项：
<resultMap type="java.util.HashMap" id="userInfoMap">
<result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<result column="username" property="username" javaType="java.lang.String" jdbcType="VARCHAR"/>
<result column="birthday" property="birthday" javaType="java.lang.String" jdbcType="DATE"/>
<result column="sex" property="sex" javaType="java.lang.String" jdbcType="CHAR"/>
<result column="address" property="address" javaType="java.lang.String" jdbcType="VARCHAR"/>
</resultMap>
<resultMap type="java.util.HashMap" id="ordersMap">
<result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<result column="user_id" property="user_id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<result column="number" property="number" javaType="java.lang.String" jdbcType="VARCHAR"/>
<result column="createtime" property="createtime" javaType="java.lang.String" jdbcType="TIMESTAMP"/>
</resultMap>
<!-- 调用存储过程 (返回多个结果集)-->
<select id="findUserOrdersByProc" parameterType="java.util.Map" resultMap="userInfoMap,ordersMap"
statementType="CALLABLE">
{call proc_query(#{user_id,jdbcType=INTEGER,mode=IN},
#{order_count,jdbcType=INTEGER,mode=OUT})}
</select>
5、测试代码：
@Test
public void TestProceduce3(){
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建Usermapper对象，mybatis自动生成mapper代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map map = new HashMap();
map.put("user_id", "2");
List<List<?>> resultList = mapper.findUserOrdersByProc(map);
List<Map> list1 = (List<Map>)resultList.get(0);
List<Map> list2 = (List<Map>)resultList.get(1);
System.out.println(list1);
System.out.println(list2);
System.out.println("orderCount="+map.get("order_count"));
sqlSession.close();
}
6、运行结果：
[{id=2, birthday=2014-07-10, sex=1, username=张三, address=北京市}]
[{createtime=2015-07-17 14:13:23, id=3, number=1000012, user_id=2}]
orderCount=1
四、总结：
如果sql中用的是select出结果，不需要配置out参数。多个结果集/结果集可以配置resultMap 来返回LIST，主要是调用selectList方法会自动把结果集加入到list中去的。
