How to use mybatis to return a table or setof from procedure call with postgresql?

Question

How can I solve it use mybatis to return a table or setof from procedure call with postgresql. thanks psotgreaql:

CREATE OR REPLACE FUNCTION public.p_find_battery_detail(
item character varying, 
vaulestr character varying, 
islimit boolean)
 RETURNS TABLE(
id bigint, 
no character varying, 
chassis character varying, 
channel character varying, 
module character varying, 
isformat bit, 
isgrad bit, 
ismatch bit, 
ismastop bit, 
sfile json, 
sftimesign character varying, 
groupclass character varying, 
gradtimesign character varying, 
gradf json)
 LANGUAGE plpgsql

mybatis in java wiht annotation

@Select("call p_find_battery_detail(#{0}, #{1}, #{2})")
@Options(statementType = StatementType.CALLABLE)
List<BatteryInfoDetail> findBatteryDetail(String itemStr, String valueStr, boolean isLimit);

when i run this serch, will throw some error:

org.apache.ibatis.binding.BindingException: Parameter '0' not found. Available parameters are [arg2, arg1, arg0, param3, param1, param2]
and so on....

blackwizard · Accepted Answer · 2017-03-26 22:09:43Z

1

the shortest way to go forward is to apply the hint provided by error message:

@Select("call p_find_battery_detail(#{arg0}, #{arg1}, #{arg2})")

to go farther you name parameters by annotation to reuse this name in query:

@Select("call p_find_battery_detail(#{itemStr}, #{valueStr}, #{isLimit})")
@Options(statementType = StatementType.CALLABLE)
List<BatteryInfoDetail> findBatteryDetail(@Param("itemStr") String itemStr, Param("valueStr") String valueStr, Param("isLimit") boolean isLimit);

params may have any name you want.

answered Mar 26, 2017 at 22:09

blackwizard

2,0541 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A_Wen Over a year ago

thank you for you answer. but i use your plan also throw error.At the same time, I remembered the Postgresql function can be obtained by the select * from data. Finally I have modified the statement after successful return a value

blackwizard Over a year ago

indeed @Select("SELECT * FROM p_find_battery_detail(#{0}, #{1}, #{2})") is a better way to call function and will error for same reason (parameters names) then eventually: @Select("SELECT * FROM p_find_battery_detail(#{arg0}, #{arg1}, #{arg2})")

A_Wen · Accepted Answer · 2017-03-27 03:17:23Z

0

@Select("select * from  p_find_battery_detail(#{itemStr}, #{valueStr}, #{isLimit})")
List<BatteryDetail> findAll(@Param("itemStr") String itemStr, @Param("valueStr") String valueStr, @Param("isLimit") boolean isLimit);

answered Mar 27, 2017 at 3:17

A_Wen

8132 gold badges7 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to use mybatis to return a table or setof from procedure call with postgresql?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related