java使用mybatis 调用存储过程返回一个游标结果集

瀚高数据库

目录

环境

文档用途

详细信息

环境

系统平台：IBM:Linux on System z Red Hat Enterprise Linux 7

版本：6.0,4.5

文档用途

mybatis调用存储过程返回游标接收结果集。

详细信息

1.service实现类

@Override@Transactionalpublic 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;}

2.dao层

@Mapperpublic interface HighgoProOneRefcursorDao extends BaseMapper<HighgoProOneRefcursorEntity> {ArrayList<Map<String, Object>> getProOneRefcursor(HashMap map);}

3.mapper

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"><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>

4.数据库过程

CREATE OR REPLACE PROCEDURE test.sp_one_refcursor(integer, INOUT refcursor)LANGUAGE plpgsqlAS $procedure$beginif $1 = 1 then open $2 for select * from hzpyszm limit 100;elseif $1 = 2 thenopen $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 thenraise exception 'sql exception--%',sqlerrm;end;$procedure$;

5.jdbc参数

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

6.数据库表

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);