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裡可以做自己的邏輯.