CODEWITHSUNDEEP
javaspring-bootmybatis
Cleo
Cleo

Reputation: 43

How to implement 2 Result Sets from myBatis

I'm working on a springboot app in which mybatis is calling a DB2 stored procedure and this stored procedure is returning 2 result sets: and for each result set I defined a model(Fruit.java and Animal.java)

result set #1

ID NAME QTY
1 Apple 2
2 Orange 3
3 banana 5

result set #2

TYPE NUM LOC
monkey 2000 London
dog 3000 New York
cat 8000 LA

in MyMapper.java, what should my resultType be?

I have tried:

@Select(Value = "{call ....}) 
@Options(StatementType =    StatementType.CALLABLE) 
@Results({   
  @Result(property="id",  column="ID",    
  @Result(property="name", column="NAME",      
  @Result(property="qty", column="QTY" })
List<Fruit> getFruits(Map<String, String> params); 

@Select(Value = "{call ....})
@Options(StatementType = StatementType.CALLABLE)
@Results({
  @Result(property="type", column="TYPE", 
  @Result(property="num", column="NUM", 
  @Result(property="loc", column="LOC"
})
List<Animal> getAnimals(Map<String, String> params);

which didn't work, I did get result set #1 ok this way but not getting result set #2 (all fields from result set#2 came back null) .

I also tried:

 @Select(Value = "{call ....})
 @Options(StatementType = StatementType.CALLABLE)
 List<?> getResultSets(Map<String, String> params);

and:

  @Select(Value = "{call ....})
  @Options(StatementType = StatementType.CALLABLE)
  List<List<?>> getResultSets(Map<String, String> params);

For both I got "java.lang.UnsupportedOperationException":

Caused by: java.lang.UnsupportedOperationException
    at org.apache.ibatis.reflection.wrapper.CollectionWrapper.findProperty(CollectionWrapper.java:42)
    at org.apache.ibatis.reflection.MetaObject.findProperty(MetaObject.java:76)
    at org.apache.ibatis.executor.resultset.FastResultSetHandler.applyAutomaticMappings(FastResultSetHandler.java:342)
    at org.apache.ibatis.executor.resultset.FastResultSetHandler.getRowValue(FastResultSetHandler.java:267)
    at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleRowValues(FastResultSetHandler.java:216)
    at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSet(FastResultSetHandler.java:188)
    at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSets(FastResultSetHandler.java:154)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:57)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
    at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:54)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)

Google says it's because I should use a concrete type instead of generics. But the result sets are of different types: List of Fruit and List of Animal , what do I do?

I'm using all annotations and no xml for mybatis. in pom.xml I'm using mybatis-spring-boot-starter if that helps.

I have been stuck on this forever. Thanks.

Upvotes: 3

Views: 1150

Answers (1)

ave
ave

Reputation: 3594

I tested this with the following procedure.
It takes two IN parameters and returns the results for fruit first, then animal.
Let me know if you need me to make some adjustments.

create or replace procedure test_proc (
  in p1 varchar(20),
  in p2 varchar(20)
)
dynamic result sets 2
begin
  declare c1 cursor with return to caller for
  select * from animal;
  declare c2 cursor with return to caller for
  select * from fruit;
  open c2;
  open c1;
end

The Java mapper method would look as follows.
As you receive two result sets, you need to specify two result maps in @ResultMap.
Note that the order matters.

@Select("{call test_proc(#{p1,mode=IN,jdbcType=VARCHAR},#{p2,mode=IN,jdbcType=VARCHAR})}")
@Options(statementType = StatementType.CALLABLE)
@ResultMap("fruitResultMap,animalResultMap")
List<List<?>> execProc(Map<String, String> params);

The returned list contains two lists. The first one is List<Fruit> and the second one is List<Animal>.

Now, to define those result maps without XML mapper, you need to declare extra methods.

@Results(id = "fruitResultMap", value = {
  @Result(property = "id", column = "ID"),
  @Result(property = "name", column = "NAME"),
  @Result(property = "qty", column = "QTY")
})
@Select("select * from fruit")
List<Fruit> getFruits();

@Results(id = "animalResultMap", value = {
  @Result(property = "type", column = "TYPE"),
  @Result(property = "num", column = "NUM"),
  @Result(property = "loc", column = "LOC")
})
@Select("select * from animal")
List<Animal> getAnimals();

And here is an executable demo project:
https://github.com/harawata/mybatis-issues/tree/master/so-69145531

Upvotes: 2

Related Questions