Mybatis 存储过程

     使用Mybatis调用存储过程。

MySql中存储过程

--根据用户id查询用户信息
Create PROCEDURE `proc_getUserById`(in uid bigint)
begin
select * from users where id=uid;
end

--获取所有用户信息
Create PROCEDURE `proc_getAllUser`()
begin
select * from users;
end

--新增用户信息
Create PROCEDURE `proc_insertUser`(
in u_username varchar(10),
in u_sex char(1),
in u_birthday date,
in u_address varchar(30)
)
begin
insert into users(username,sex,birthday,address)
values(u_username,u_sex,u_birthday,u_address);
end

--更新用户信息
Create PROCEDURE `proc_updateUserById`(
in u_username varchar(20),
in u_sex char(1),
in u_birthday date,
in u_address varchar(30),
in u_id bigint
)
begin
update users set username=u_username,sex=u_sex,birthday=u_birthday,
address=u_address where id=u_id;
end

--根据id删除用户信息
Create PROCEDURE `proc_deleteUserById`(
in u_id bigint
)
begin
delete from users where id=u_id;
end

接口UserMapper.java

public interface UserMapper {
    int insertUser(User user);
    int updateUserById(User user);
    int deleteUserById(long id);
    User queryUserById(long id);
    List<User> queryUserList();
}

sql映射文件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.company.mapper.UserMapper">
    <insert id="insertUser" parameterType="user" statementType="CALLABLE">
       {call proc_insertUser(
       #{username,mode=IN,jdbcType=VARCHAR},
       #{sex,mode=IN,jdbcType=CHAR},
       #{birthday,mode=IN,jdbcType=DATE},
       #{address,mode=IN,jdbcType=VARCHAR}
       )}
    </insert>
    <update id="updateUserById" parameterType="user" statementType="CALLABLE">
        {call proc_updateUserById(
        #{username,mode=IN,jdbcType=VARCHAR},
        #{sex,mode=IN,jdbcType=CHAR},
        #{birthday,mode=IN,jdbcType=DATE},
        #{address,mode=IN,jdbcType=VARCHAR},
        #{id,mode=IN,jdbcType=BIGINT}
        )}
    </update>
    <delete id="deleteUserById" parameterType="long" statementType="CALLABLE">
        {call proc_deleteUserById(
        #{id,mode=IN,jdbcType=BIGINT}
        )}
    </delete>
    <select id="queryUserById" statementType="CALLABLE" parameterType="long" resultType="user">
        {call proc_getUserById(
         #{id,mode=IN,jdbcType=BIGINT}
        )}
    </select>
    <select id="queryUserList" statementType="CALLABLE" resultType="user">
        {call proc_getAllUser()}
    </select>
</mapper>

测试方法

public class ProcedureTest {
    private SqlSession sqlSession;
    @Before
    public void before() throws IOException {
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        inputStream.close();
        sqlSession=sqlSessionFactory.openSession();
    }

    @Test
    public void insertUserTest() throws ParseException {
        User user = new User();
        user.setUsername("甄姬");
        user.setSex('0');
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = simpleDateFormat.parse("1800-01-10");
        user.setBirthday(date);
        user.setAddress("河北");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int i = userMapper.insertUser(user);
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void queryUserByIdTest(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.queryUserById(108l);
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    public void queryUserListTest(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = userMapper.queryUserList();
        users.forEach(System.out::println);
        sqlSession.close();
    }

    @Test
    public void updateUserById() throws ParseException {
        User user = new User();
        user.setId(115l);
        user.setUsername("甄姬娘娘");
        user.setSex('1');
        user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("2019-09-10"));
        user.setAddress("迁到北京");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int i = userMapper.updateUserById(user);
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUserById(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int i = userMapper.deleteUserById(115);
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();
    }
}

注意事项：

1. 存储过程的参数和名称无关，只和顺序有关系。
2. 存储过程的output参数，只能通过传入的map获取。
3. 存储过程返回的结果集可直接用返回的map接收。
4. 存储过程的return结果需要使用？=call procName(?,?)的第一个参数接收，需要指定对应的mode为OUT类型。
5. 存储过程对应的数据类型为枚举类型，需要使用大写，如VARCHAR。

output是在存储过程中的参数的返回值（输出参数），而ReturnValue是存储过程返回的值（使用return关键字），一个存储过程可以有任意多个依靠参数返回的值，但只有一个ReturnValue。

 

获取输出参数示例：

存储过程

Create PROCEDURE `proc_getUserNameById`(
in uid bigint,
out u_username varchar(20)
)
begin
select username from users where id=uid into u_username;
end

接口

void getUserNameById(Map<String,Object> map);

sql映射

    <select id="getUserNameById" statementType="CALLABLE" parameterType="map">
        {call proc_getUserNameById(
        #{id,mode=IN,jdbcType=BIGINT},
        #{username,mode=OUT,jdbcType=VARCHAR}
        )}
    </select>

测试语句

    @Test
    public void queryUserNameByIdTest(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Map<String,Object> map=new HashMap<>();
        map.put("id",3);
        map.put("username","");
        userMapper.getUserNameById(map);
        String username =(String) map.get("username");
        System.out.println(username);
    }

 

存储过程主要分成三类：

1. 返回记录集的存储过程：执行结果是一个记录集。如，从数据库中检索出符合某一个或几个条件的记录。
2. 返回数值的存储过程（也可以称为标量存储过程），其执行完后返回一个值，如数据库中执行一个有返回值的函数或命令。
3. 行为存储过程，用来实现数据库的某个功能，而没有返回值，如在数据库中的更新和删除操作。

 

返回多个结果集示例：

存储过程

Create PROCEDURE `proc_getUserCount`(
in uid int,
out cnt int
)
begin
select * from users;
select * from users where id<uid;
select count(*) from users where id<uid into cnt;
end

接口

List<List<?>> getUserCount(Map<String,Object> map);

sql映射

    <select id="getUserCount" statementType="CALLABLE" parameterType="map" resultMap="rm1,rm2">
        {call proc_getUserCount(
        #{uid,mode=IN,jdbcType=INTEGER},
        #{rowcount,mode=OUT,jdbcType=INTEGER}
        )}
    </select>
    <resultMap id="rm1" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="address" property="address"/>
    </resultMap>
    <resultMap id="rm2" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="address" property="address"/>
    </resultMap>

测试

    @Test
    public void queryUserCount(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Map<String,Object> map=new HashMap<>();
        map.put("uid",108);
        map.put("rowcount",0);
        List<List<?>> userList = userMapper.getUserCount(map);
        System.out.println("符合条件的记录数："+ map.get("rowcount"));
        for (int i = 0; i < userList.size(); i++) {
            userList.get(i).forEach(System.out::println);
            System.out.println("::::::::::::");
        }
    }