使用mybatis操作存储过程

1 建立存储过程

create database gas;
use gas;
create table sensor(
id int(11) not null auto_increment,
name int(11) not null,
year2017 int(6),
year2018 int(6) ,
year2019 int(6),
year2020 int(6),
primary key(id)
)engine=InnoDB default charset=utf8;

insert into sensor(name,year2017,year2018,year2019,year2020) values(1,1,1,1,1);
insert into sensor(name,year2017,year2018,year2019,year2020) values(2,2,2,2,2);
insert into sensor(name,year2017,year2018,year2019,year2020) values(3,3,3,3,3);

use gas;
delimiter @@
create procedure select_by(in id_number int )
begin
	select * from sensor where id=id_number;
end @@
delimiter @@;

call select_by(1);

　　结果：

 

 2 实体类

public class Sensor {
    private  long id;
    private  long name;
    private  long year2017;
    private  long year2018;
    private  long year2019;
    private  long year2020;

    public Sensor() {
    }

    public Sensor(long id, long name, long year2017, long year2018, long year2019, long year2020) {
        this.id = id;
        this.name = name;
        this.year2017 = year2017;
        this.year2018 = year2018;
        this.year2019 = year2019;
        this.year2020 = year2020;
    }


    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public long getName() {
        return name;
    }

    public void setName(long name) {
        this.name = name;
    }

    public long getYear2017() {
        return year2017;
    }

    public void setYear2017(long year2017) {
        this.year2017 = year2017;
    }

    public long getYear2018() {
        return year2018;
    }

    public void setYear2018(long year2018) {
        this.year2018 = year2018;
    }

    public long getYear2019() {
        return year2019;
    }

    public void setYear2019(long year2019) {
        this.year2019 = year2019;
    }

    public long getYear2020() {
        return year2020;
    }

    public void setYear2020(long year2020) {
        this.year2020 = year2020;
    }
}

3 编写Mapper

<select id="findById" resultType="Sensor" statementType="CALLABLE" parameterType="Integer">
        CALL select_by(#{id, mode=IN});
</select>

4 测试，使用springMVC测试

@GetMapping("/findById")
@ResponseBody
public List<Sensor> findById(@RequestParam("id") int id){
        return  sensorRepository.findById(id);
}

测试结果：