java使用mybatis 呼叫儲存過程返回一個遊標結果集

@Override

@Transactional


public List<HighgoFunOneRefcursorEntity> getOneRefcursor(Integer id) {





    HashMap<String, Object> 
map = 
new HashMap<String, Object>();

    
map.put(
"id", id);

    highgoFunOneRefcursorDao.getOneRefcursor(
map);

    List<HighgoFunOneRefcursorEntity> 
list = (List<HighgoFunOneRefcursorEntity>)
map.get(
"result");





    
return 
list;

}

@Mapper


public 
interface HighgoProOneRefcursorDao 
extends BaseMapper<HighgoProOneRefcursorEntity> {

    ArrayList<Map<
String, 
Object>> getProOneRefcursor(HashMap map);





}

<?xml version="1.0" encoding="UTF-8"?>


<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "






<
mapper 
namespace=
"com.database.mybatisplus.modules.demo.dao.HighgoProOneRefcursorDao">





   
<!-- 可根據自己的需求，是否要使用 -->

    
<
resultMap 
type=
"com.database.mybatisplus.modules.demo.entity.HighgoProOneRefcursorEntity" 
id=
"highgoProOneRefcursorMap">

        
<
result 
property=
"hanzi" 
column=
"hanzi"/>

        
<
result 
property=
"quanpin" 
column=
"quanpin"/>

        
<
result 
property=
"szm" 
column=
"szm"/>

        
<
result 
property=
"duyin" 
column=
"duyin"/>

        
<
result 
property=
"numbersd" 
column=
"numbersd"/>

        
<
result 
property=
"sd" 
column=
"sd"/>

        
<
result 
property=
"repsd" 
column=
"repsd"/>

        
<
result 
property=
"hzascii" 
column=
"hzascii"/>

    
</
resultMap>





    
<!-- 呼叫儲存過程返回一個遊標 -->

    
<
select 
id=
"getProOneRefcursor" 
parameterType=
"map" 
statementType=
"CALLABLE" 
resultType=
"java.util.Map">

        {call sp_one_refcursor(#{id,mode=IN},null,#{result,mode=OUT,jdbcType=OTHER,javaType=ResultSet,resultMap=highgoFunOneRefcursorMap})}

    
</
select>






</
mapper>

CREATE OR REPLACE PROCEDURE test.sp_one_refcursor(integer, INOUT refcursor) LANGUAGE plpgsqlAS $procedure$begin  if $1 = 1 then     open $2 for select * from hzpyszm limit 100;  elseif $1 = 2 then    open $2 for select hanzi,quanpin,szm,duyin from hzpyszm limit 100;  else     open $2 for select hanzi,quanpin from hzpyszm limit 100;  end if;  exception when others then    raise exception 'sql exception--%',sqlerrm;end;$procedure$;

url: jdbc:highgo://192.168.21.138:5870/test?escapeSyntaxCallMode=callIfNoReturn

CREATE 
TABLE hzpyszm (

  hanzi 
varchar(
4) 
NULL,

  quanpin 
varchar(
10) 
NULL,

  szm 
varchar(
5) 
NULL,

  duyin 
varchar(
10) 
NULL,

  numbersd 
varchar(
1) 
NULL,

  sd 
varchar(
5) 
NULL,

  repsd 
varchar(
5) 
NULL,

  hzascii 
int8 
NULL

);


CREATE 
INDEX hzpyszm_hanzi_idx 
ON test.hzpyszm 
USING btree (hanzi varchar_pattern_ops);






INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'唔', 
'ngn', 
'n', 
'ńgń', 
'2', 
NULL, 
NULL, 
21780);


INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'匌', 
'ge', 
'g', 
'gé', 
'2', 
'é', 
'e', 
21260);


INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'匍', 
'pu', 
'p', 
'pú', 
'2', 
'ú', 
'u', 
21261);


INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'匎', 
'e', 
'e', 
'è', 
'4', 
'è', 
'e', 
21262);


INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'匏', 
'pao', 
'p', 
'páo', 
'2', 
'á', 
'a', 
21263);


INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'匐', 
'fu', 
'f', 
'fú', 
'2', 
'ú', 
'u', 
21264);


INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'匑', 
'gong', 
'g', 
'gōng', 
'1', 
'ō', 
'o', 
21265);


INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'匒', 
'da', 
'd', 
'dá', 
'2', 
'á', 
'a', 
21266);


INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'匓', 
'jiu', 
'j', 
'jiù', 
'4', 
'ù', 
'u', 
21267);


INSERT 
INTO hzpyszm (hanzi, quanpin, szm, duyin, numbersd, sd, repsd, hzascii) 
VALUES(
'匔', 
'gong', 
'g', 
'gōng', 
'1', 
'ō', 
'o', 
21268);