我们在上一篇整合SpringBoot的基础之上,再进行调用存储过程的实验
首先准备两个用于的存储过程
CREATE PROCEDURE sp_insertUser (
OUT u_id INTEGER,
IN u_name VARCHAR (32),
IN u_age INTEGER
)
BEGIN
INSERT INTO user (NAME,AGE)
VALUES
(u_name,u_age);
SET u_id = LAST_INSERT_ID();
END;
CREATE PROCEDURE sp_getUser(IN u_id INTEGER)
BEGIN
SELECT id,name,age FROM user WHERE id=u_id;
END;
sp_insertUser接受输入参数name,age进行插入操作,sp_getUser接受输入参数id进行查询操作。
UserMapper.xml文件
<insert id="callAddUser" parameterType="User" statementType="CALLABLE">
{call sp_insertUser(
#{id,mode=OUT,jdbcType=INTEGER},#{name,mode=IN},#{age,mode=IN})}
</insert>
<select id="callGetUser" parameterType="User" statementType="CALLABLE"
resultType="User">
{call sp_getUser(#{id,mode=IN,jdbcType=INTEGER})}
</select>
User
package com.yj.model;
public class User{
private Integer id;
private String name;
private Integer age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
UserMapper接口
void callAddUser(User user);
User callGetUser(User user);
UserService
public void callAddUser(User user) {
userMapper.callAddUser(user);
}
public User callGetUser(User user) {
return userMapper.callGetUser(user);
}
UserServiceTest
@Test
public void callAddUser() {
User user =new User();
user.setName("test");
user.setAge(18);
userService.callAddUser(user);
log.info("user:"+user);
}
@Test
public void callGetUser() {
User user =new User();
user.setId(1);
user=userService.callGetUser(user);
log.info("user:"+user);
}
我们进行Junit单元测试,成功调用了存储过程。