MyBatis之存储过程调用

我们在上一篇整合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单元测试，成功调用了存储过程。