11.3 注解调用存储过程

发表于 更新于 分类于 ， ， Waline： 本文字数： 14k 阅读时长 ≈ 13 分钟

11.3 注解调用存储过程

项目结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
G:\workspace_web2\MyAProcedureTest
├─src
│ ├─db.properties
│ ├─domain
│ │ └─User.java
│ ├─fractory
│ │ └─SqlSessionFratoryTools.java
│ ├─log4j.xml
│ ├─mapper
│ │ └─UserMapper.java
│ ├─mybatis-config.xml
│ ├─procedure.sql
│ ├─tb_user.sql
│ └─test
│   ├─DeleteTest.java
│   ├─InsertTest.java
│   ├─SelectAllTest.java
│   ├─SelectByIdTest.java
│   └─UpdateTest.java
└─WebContent
  ├─META-INF
  │ └─MANIFEST.MF
  └─WEB-INF
    └─lib
      ├─commons-logging-1.2.jar
      ├─log4j-1.2.17.jar
      ├─log4j-api-2.3.jar
      ├─log4j-core-2.3.jar
      ├─mybatis-3.4.5.jar
      └─mysql-connector-java-5.1.44-bin.jar


数据库表

/MyAProcedureTest/src/tb_user.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#创建数据表
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(18) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
#插入数据
INSERT INTO `tb_user` VALUES ('1', '小明', '男', '21');
INSERT INTO `tb_user` VALUES ('2', '小王', '男', '22');
INSERT INTO `tb_user` VALUES ('3', '小丽', '女', '18');
INSERT INTO `tb_user` VALUES ('4', '小芳', '女', '18');

创建存储过程

/MyAProcedureTest/src/procedure.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
#创建insert_user存储过程
drop procedure if exists insert_user;
DELIMITER $$ 
CREATE PROCEDURE insert_user (
    OUT v_id int,
    in v_name varchar(18),
    in v_sex varchar(19),
    in v_age int
)
BEGIN
	INSERT INTO tb_user (name,sex,age) VALUES (v_name,v_sex,v_age);
	SET v_id=LAST_INSERT_ID();
END
$$
DELIMITER ;
#创建select_user存储过程
drop procedure if exists select_all_user;
delimiter $$
create procedure select_all_user()
begin
    select id,name,sex,age from tb_user;
end
$$
DELIMITER ;
#创建select_user_by_id存储过程
drop procedure if exists select_user_by_id;
DELIMITER $$
create procedure select_user_by_id(IN in_id INTEGER)
begin
    select id,name,sex,age from tb_user where id=in_id;
end
$$
DELIMITER ;
#创建update_user_by_id存储过程
drop procedure if exists update_user_by_id;
delimiter $$
create procedure update_user_by_id (
    IN p_id int,
    IN p_name varchar(18),
    IN p_sex varchar(19),
    IN p_age int
)
begin
    update tb_user set name=p_name,sex=p_sex,age=p_age
    where id=p_id;
end
$$
delimiter ;
#创建delete_user_by_id存储过程
drop procedure if exists delete_user_by_id;
delimiter $$
create procedure delete_user_by_id(IN p_id integer)
begin
    delete from tb_user where id=p_id;
end
$$
delimiter ;

持久化对象

/MyAProcedureTest/src/domain/User.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package domain;
public class User{
    private Integer id;
    private String name;
    private String sex;
    private Integer age;
    public User()
    {
        super();
    }
    // 此处省略getter和setter方法,请自己补上
    @Override
    public String toString()
    {
        return "User [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + "]";
    }
}

mapper接口

/MyAProcedureTest/src/mapper/UserMapper.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package mapper;

import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.StatementType;
import domain.User;

public interface UserMapper {
    /**
     * 查询所有的用户信息.
     * 
     * @return 包含所有用户信息的List集合.
     */
    @Select("{call select_all_user()}")
    @Options(statementType = StatementType.CALLABLE)
    List<User> selectAllUser();

    /**
     * 根据id查询用户信息.
     * 
     * @param id 主键
     * @return 该主键对应的用户信息
     */
    @Select("{call select_user_by_id(#{id,mode=IN})}")
    @Options(statementType = StatementType.CALLABLE)
    User selectUserById(Integer id);

    /**
     * 插入一个用户信息.
     * 
     * @param user 用户对象
     * @return 插入用户时，系统分配的主键
     */
    @Insert("{call insert_user(" 
                + "#{id,mode=OUT,jdbcType=INTEGER}," 
                + "#{name,mode=IN}," 
                + "#{sex,mode=IN},"
                + "#{age,mode=IN}"
                + ")}")
    @Options(statementType = StatementType.CALLABLE)
    Integer insertUser(User user);

    @Update("{call update_user_by_id(" 
                + "#{id,mode=IN}," 
                + "#{name,mode=IN}," 
                + "#{sex,mode=IN}," 
                + "#{age,mode=IN}"
                + ")}")
    @Options(statementType = StatementType.CALLABLE)
    void updateUserById(User user);

    /**
     * 根据id删除用户.
     * 
     * @param id 用户的主键
     */
    @Delete("{call delete_user_by_id(#{id,mode=IN})}")
    @Options(statementType = StatementType.CALLABLE)
    void deleteUserById(Integer id);
}

UserMapper.java只是将之前写在XML文件当中调用存储过程的SQL语句写在了注解当中,注意需要使用@Options(statementType=StatementType.CALLABLE)提供调用存储过程的CALLBALE选项,其他并无不同。

调用存储过程select

查询全部

存储过程如下:

1
2
3
4
5
6
7
8
9
#创建select_user存储过程
drop procedure if exists select_all_user;
delimiter &&
create procedure select_all_user()
begin
    select id,name,sex,age from tb_user;
end
&&
DELIMITER ;

查询所有的mapper接口方法如下:

1
2
3
@Select("{call select_all_user()}")
@Options(statementType=StatementType.CALLABLE)
List<User> selectAllUser();

select_all_user这个存储过程没有参数,直接在前面加上call调用即可,注意存储过程时不要忘了加上括号,也就是:call 存储过程名称 括号,
还有就是放在注解中的存储过程调用语句要用大括号包裹起来,至于为什么,我还不知道,先这样背着.

SelectAllTest.java

/MyAProcedureTest/src/test/SelectAllTest.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class SelectAllTest{
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> users = userMapper.selectAllUser();
            System.out.println("________________________________");
            users.forEach(user-> System.out.println(user));
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if(sqlSession != null)
                sqlSession.close();
        }
    }
}

运行效果

1
2
3
4
5
6
7
8
9
DEBUG [main] ==>  Preparing: {call select_all_user()} 
DEBUG [main] ==> Parameters: 
DEBUG [main] <==      Total: 4
DEBUG [main] <==    Updates: 0
________________________________
User [id=1, name=小明, sex=男, age=21]
User [id=2, name=小王, sex=男, age=22]
User [id=3, name=小丽, sex=女, age=18]
User [id=4, name=小芳, sex=女, age=18]

根据id查询

用到的存储过程如下:

1
2
3
4
5
6
7
8
9
#创建select_user_by_id存储过程
drop procedure if exists select_user_by_id;
DELIMITER &&
create procedure select_user_by_id(IN in_id INTEGER)
begin
    select id,name,sex,age from tb_user where id=in_id;
end
&&
DELIMITER ;

mapper接口方法如下:

1
2
3
@Select("{call select_user_by_id(#{id,mode=IN})}")
@Options(statementType=StatementType.CALLABLE)
User selectUserById(Integer id);

注意存储过程的参数写法,#{id,mode=IN}这个是mybatis的表达式,该表达式中第一项id是参数名称,第二项mode=IN表示这个参数是存储过程的输入参数。

SelectByIdTest.java

/MyAProcedureTest/src/test/SelectByIdTest.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class SelectByIdTest{
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.selectUserById(1);
            System.out.println("_______________________________________________");
            System.out.println(user);
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if(sqlSession != null)
                sqlSession.close();
        }
    }
}

运行效果如下:

1
2
3
4
5
6
DEBUG [main] ==>  Preparing: {call select_user_by_id(?)} 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
_______________________________________________
User [id=1, name=小明, sex=男, age=21]

调用存储过程update

对应的存储过程如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#创建update_user_by_id存储过程
drop procedure if exists update_user_by_id;
delimiter &&
create procedure update_user_by_id (
    IN p_id int,
    IN p_name varchar(18),
    IN p_sex varchar(19),
    IN p_age int
)
begin
    update tb_user set name=p_name,sex=p_sex,age=p_age
    where id=p_id;
end
&&
delimiter ;

mapper接口方法如下:

1
2
3
4
5
6
7
8
@Update("{call update_user_by_id("
        + "#{id,mode=IN},"
        + "#{name,mode=IN},"
        + "#{sex,mode=IN},"
        + "#{age,mode=IN}"
        + ")}")
@Options(statementType=StatementType.CALLABLE)
void updateUserById(User user);

UpdateTest.java

/MyAProcedureTest/src/test/UpdateTest.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class UpdateTest{
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.selectUserById(3);
            System.out.println("________________________________");
            if(user != null)
            {
                System.out.println("更新之前的数据:" + user);
                System.out.println("________________________________");
                user.setName("李四");
                user.setSex("男");
                user.setAge(22);
                userMapper.updateUserById(user);
                System.out.println("________________________________");
                user = userMapper.selectUserById(user.getId());
                System.out.println("________________________________");
                System.out.println("更新之后的数据:" + user);
            }
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if(sqlSession != null)
                sqlSession.close();
        }
    }
}

运行结果如下:

DEBUG [main] ==>  Preparing: {call select_user_by_id(?)} 
DEBUG [main] ==> Parameters: 3(Integer)
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
________________________________
更新之前的数据:User [id=3, name=小丽, sex=女, age=18]
________________________________
DEBUG [main] ==>  Preparing: {call update_user_by_id(?,?,?,?)} 
DEBUG [main] ==> Parameters: 3(Integer), 李四(String), 男(String), 22(Integer)
DEBUG [main] <==    Updates: 1
________________________________
DEBUG [main] ==>  Preparing: {call select_user_by_id(?)} 
DEBUG [main] ==> Parameters: 3(Integer)
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
________________________________
更新之后的数据:User [id=3, name=李四, sex=男, age=22]

调用存储过程delete

测试的存储过程如下:

1
2
3
4
5
6
7
8
9
#创建delete_user_by_id存储过程
drop procedure if exists delete_user_by_id;
delimiter &&
create procedure delete_user_by_id(IN p_id integer)
begin
    delete from tb_user where id=p_id;
end
&&
delimiter ;

对应的mapper接口方法如下:

1
2
3
@Delete("{call delete_user_by_id(#{id,mode=IN})}")
@Options(statementType=StatementType.CALLABLE)
void deleteUserById(Integer id);

DeleteTest.java

/MyAProcedureTest/src/test/DeleteTest.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package test;

import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;

public class DeleteTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 查找要删除的记录
            User user = userMapper.selectUserById(2);
            // 如果找到要删除的记录
            if (user != null) {
                System.out.println("_________________________________________");
                System.out.println("即将删除用户:" + user);
                System.out.println("_________________________________________");
                // 删除掉该用户
                userMapper.deleteUserById(user.getId());
            }
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }
    }
}

运行效果:

1
2
3
4
5
6
7
8
9
10
DEBUG [main] ==>  Preparing: {call select_user_by_id(?)} 
DEBUG [main] ==> Parameters: 2(Integer)
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
_________________________________________
即将删除用户:User [id=2, name=小王, sex=男, age=22]
_________________________________________
DEBUG [main] ==>  Preparing: {call delete_user_by_id(?)} 
DEBUG [main] ==> Parameters: 2(Integer)
DEBUG [main] <==    Updates: 1

调用存储过程insert

用到的存储过程定义如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#创建insert_user存储过程
drop procedure if exists insert_user;
DELIMITER && 
CREATE PROCEDURE insert_user (
    OUT v_id int,
    in v_name varchar(18),
    in v_sex varchar(19),
    in v_age int
)
BEGIN
    INSERT INTO tb_user (name,sex,age) VALUES (v_name,v_sex,v_age);
    SET v_id=LAST_INSERT_ID();
END
&&
DELIMITER ;

对应的mapper接口的方法:

1
2
3
4
5
6
7
8
@Insert("{call insert_user("
        + "#{id,mode=OUT,jdbcType=INTEGER},"
        + "#{name,mode=IN},"
        + "#{sex,mode=IN},"
        + "#{age,mode=IN}"
        + ")}")
@Options(statementType=StatementType.CALLABLE)
Integer insertUser(User user);

InsertTest.java

/MyAProcedureTest/src/test/InsertTest.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package test;

import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;

public class InsertTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 创建插入的数据
            User user = new User();
            user.setName("小张");
            user.setSex("男");
            user.setAge(22);
            // 插入数据
            userMapper.insertUser(user);
            System.out.println("______________________________________");
            System.out.println("生成的主键:" + user.getId());
            System.out.println("______________________________________");
            User user1 = userMapper.selectUserById(user.getId());
            System.out.println("插入后的记录:" + user1);
            
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }

    }
}

运行效果如下:

1
2
3
4
5
6
7
8
9
10
11
DEBUG [main] ==>  Preparing: {call insert_user(?,?,?,?)} 
DEBUG [main] ==> Parameters: 小张(String), 男(String), 22(Integer)
DEBUG [main] <==    Updates: 1
______________________________________
生成的主键:5
______________________________________
DEBUG [main] ==>  Preparing: {call select_user_by_id(?)} 
DEBUG [main] ==> Parameters: 5(Integer)
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
插入后的记录:User [id=5, name=小张, sex=男, age=22]