ResultSets can not divide received lists

MHSFisher 0
Viewed 57

I have two ResultSets that both of theme has completely separated SELECT and has not any relation to each other. but MyBatis always try to mix the result.

Movie table

ID_ NAME_ YEAR_
1 Movie1 2020
2 Movie2 2008
3 Movie3 1988

Artist table

ID_ NAME_
1 John
2 Jane

Select:

<select id="selectMultiple" resultSets="movies,artists" resultMap="multipleQueriesResult" statementType="CALLABLE">
    BEGIN
      SELECT M.ID_         AS mId,
             M.NAME_       AS mName,
             M.YEAR_       AS mYear
      FROM TestMyBatis.dbo.Movie AS M
    END
    BEGIN
      SELECT A.ID_   AS aId,
             A.NAME_ AS aName
      FROM TestMyBatis.dbo.Artist AS A
    END
  </select>

ResultMap:

<resultMap id="multipleQueriesResult" type="DivideOut" autoMapping="true">

    <collection property="movie"
                ofType="Movie"
                javaType="list"
                resultSet="movies">
      <id property="id" column="mId"/>
      <result property="name" column="mName"/>
      <result property="year" column="mYear"/>
    </collection>

    <collection property="artist"
                ofType="Artist"
                javaType="list"
                resultSet="artists">
      <id property="id" column="aId"/>
      <result property="name" column="aName"/>
    </collection>
  </resultMap>

DivideOut:

public class DivideOut {

  public List<Object> movie;
  public List<Object> artist;

  @Override
  public String toString() {
    return "DivideOut{" +
      "movie=" + movie +
      ", artist=" + artist +
      '}';
  }
}

Expected Output:

[
DivideOut{movie=[Movie{id=1, name=Movie1, year=2020}], artist=[Artist{id=1, name='john}]}, 
DivideOut{movie=[Movie{id=1, name=Movie1, year=2020}], artist=[Artist{id=2, name='jane}]},  
DivideOut{movie=[Movie{id=2, name=Movie2, year=2008}], artist=[Artist{id=1, name='john}]},  
DivideOut{movie=[Movie{id=2, name=Movie2, year=2008}], artist=[Artist{id=2, name='jane}]},  
DivideOut{movie=[Movie{id=3, name=Movie3, year=1988}], artist=[Artist{id=1, name='jane}]},  
DivideOut{movie=[Movie{id=3, name=Movie3, year=1988}], artist=[Artist{id=2, name='john}]}
]

Actual Output:

[
DivideOut{id=1, name=Movie1, year=2020, artist=[Artist{id=1, name='john}]}, 
DivideOut{id=1, name=Movie1, year=2020, artist=[Artist{id=2, name='jane}]}, 
DivideOut{id=2, name=Movie2, year=2008, artist=[Artist{id=1, name='john}]},
DivideOut{id=2, name=Movie2, year=2008, artist=[Artist{id=2, name='jane}]},
DivideOut{id=3, name=Movie3, year=1988, artist=[Artist{id=1, name='john}]},
DivideOut{id=3, name=Movie3, year=1988, artist=[Artist{id=2, name='jane}]}
]

Records of Movie is 3 and records of Artist is 2.

Artist and Movie records should be in separated lists, but they are mix.

Database is MSSQLSERVER 2016 and Driver JDBC.SQLServerDriver 11.2.0

mybatis
1 Answers

The result map maps the query result to DivideOut object, so the first key question is "How many DivideOut instances do you expect?".
It seems that you just need two independent lists and don't care about DivideOut, so I assumed the answer is 1.

In the following statement, I added an extra query and named the result set dummy.
This extra query returns one row that creates the single DivideOut instance.

<select id="selectMultiple" resultSets="dummy,movies,artists"
  resultMap="multipleQueriesResult" statementType="CALLABLE">
  BEGIN
    SELECT 1
  END
  BEGIN
    SELECT
           M.ID_         AS mId,
           M.NAME_       AS mName,
           M.YEAR_       AS mYear
    FROM Movie AS M
  END
  BEGIN
    SELECT A.ID_   AS aId,
           A.NAME_ AS aName
    FROM Artist AS A
  END
</select>

If you just want two independent lists, it probably is better to 1) execute two independent SELECTs or 2) specify two result maps like this test.

Here is an executable demo:
https://github.com/harawata/mybatis-issues/tree/master/so-73685798

ave0
Related