Mybatis调用oracle存储过程

最近使用到了oracle的存储过程, 下面建立一个测试的存储过程:

查询devices表格里面的所有内容, 这里输入的fid没有用到,是为了做其他调试

create or replace procedure get_data_by_procedure(
                                                  fid in Integer, 
                                                  v_cursor out sys_refcursor) 
as
begin
  DBMS_OUTPUT.put_line('fid:'||fid);
  open v_cursor 
  for select 
    d.id, d.name, d.age 
    from devices d
    order by d.id asc;
    --where d.id = fid;
end;

在pl/sql中看到这个存储过程:

下面用mybatis调用存储过程:

注意这里要另写一个resultMap, 如果直接用BaseResultMap, 会提示出错, 所有这里另外写一个;

<resultMap id="BaseResultMap" type="com.xum.demo.pojo.Devices" >
    <id column="ID" property="id" jdbcType="NUMERIC" />
    <result column="NAME" property="name" jdbcType="VARCHAR" />
    <result column="AGE" property="age" jdbcType="NUMERIC" />
  </resultMap>
  <resultMap id="ProcedureInfoMap" type="com.xum.demo.pojo.Devices">
    <id column="ID" property="id" jdbcType="NUMERIC" />
    <result column="NAME" property="name" jdbcType="VARCHAR" />
    <result column="AGE" property="age" jdbcType="NUMERIC" />
  </resultMap>

<select id="get_data_by_procedure" statementType="CALLABLE" parameterType="java.util.Map">
    {call get_data_by_procedure(#{fid, mode=IN,  jdbcType=NUMERIC},
                                #{v_cursor, jdbcType=CURSOR, mode=OUT, resultMap=ProcedureInfoMap, javaType=java.sql.ResultSet}
    )}
   </select>

写的存储过程注意几个点:

1. mybatis中调用存储过程, 要加上statementType="CALLABLE", 默认的输入参数类型是Map, 可以改变, 如加上parameterType="com.xx.Devices"这种类似的.

2. 这里返回的是游标类型, 所以jdbcType是CURSOR, mode是OUT类型.

3. resultMap是刚才定义的ProcedureInfoMap, 不要使用BaseResultMap.

4. javaType是java.sql.ResultSet类型.

Mapper里面的内容:

public interface DevicesMapper {  ……
    List<Devices> get_data_by_procedure(Map<String, Object> mapPara);
    ……
}

我自己封装了一个service:

@Service(value = "devicesService")
@Transactional
public class DevicesServices {

    @Resource
    private DevicesMapper devicesMapper;

    public List<Devices> get_data_by_procedure(Map<String, Object> mapPara) {
        List<Devices> devices = null;
        devices = this.devicesMapper.get_data_by_procedure(mapPara);
        return devices;
    }

}

最后在Conttroller里面加入调用的代码，(看到后台打印出来数据就可以了, 我这里是还要Thymeleaf前台显示, 读者可以不用考虑)

@Controller
@RequestMapping(value = "/main")
public class MainController {

    private static final Logger LOG = LogManager.getLogger(MainController.class);

    @Autowired
    DevicesServices devicesServices;

    @RequestMapping(value = "/getDataByProcedure")
    public String get_data_by_procedure(ModelMap model) {
        Map<String, Object> mapPara = new HashMap<String, Object>();
        mapPara.put("fid", 1);
        this.devicesServices.get_data_by_procedure(mapPara);
        List<Devices> devices = (List<Devices>)mapPara.get("v_cursor");
        for (int i = 0; i < devices.size(); i++) {
            LOG.info("id:" + devices.get(i).getId() + ",name:" + devices.get(i).getName() + ",age:" + devices.get(i).getAge());
        }
        model.addAttribute("devices", devices);
        String viewName = "main/procedure";
        return  viewName;
    }

}

如下打印数据:

2019-11-02 16:01:41.598  INFO 14120 --- [nio-8080-exec-1]   : id:1,name:a,age:111
2019-11-02 16:01:41.598  INFO 14120 --- [nio-8080-exec-1]   : id:2,name:b,age:222
2019-11-02 16:01:41.598  INFO 14120 --- [nio-8080-exec-1]   : id:3,name:c,age:333
2019-11-02 16:01:41.598  INFO 14120 --- [nio-8080-exec-1]   : id:4,name:d,age:444
2019-11-02 16:01:41.598  INFO 14120 --- [nio-8080-exec-1]   : id:5,name:e,age:555

至此, 从java调用Oracle的存储过程流程写完了, Controller里可以做自己的逻辑.