Nested <collection> and <association> in MyBatis gives NULL as result

Question

I have a MyBatis mapper file which looks like this -

<mapper namespace="ABCDEFGH">

  <resultMap id="userWiseData" type="UserWiseData">
    <id property="personId" column="person_id"/>

    <association
        property="mandatorySTC"
        javaType="TypeWiseData"
        resultSet="mandatorySTCResult">
      <id property="typeId" column="type_id"/>
      <result property="personId" column="person_id"/>

      <collection
          property="hrCompanyWiseData"
          ofType="HRCompanyWiseData">

        <id property="companyId" column="company_id"/>
        <result property="companyCode" column="company_code"/>

        <association
            property="nonMidYear"
            javaType="PeriodWiseData">

          <id property="periodId" column="non_mid_year_period_id"/>

          <collection
              property="investmentDateWiseData"
              ofType="InvestmentDateWiseData">

            <id property="investmentDateId" column="non_mid_year_investment_date_id"/>

            <collection
              property="vestingDateWiseData"
              ofType="VestingDateWiseData">

              <id property="vestingDateId" column="non_mid_year_vesting_date_id"/>
              <result property="awardDate" column="non_mid_year_award_date"/>
              <result property="investmentDate" column="non_mid_year_investment_date"/>
              <result property="vestingDate" column="non_mid_year_vesting_date"/>
              <result property="amount" column="non_mid_year_amount"/>
              <result property="returnAmount" column="non_mid_year_return_amount"/>
              <result property="vestingAmount" column="non_mid_year_vesting_amount"/>

            </collection>

          </collection>
        </association>

        <association
            property="midYear"
            javaType="PeriodWiseData">

          <id property="periodId" column="mid_year_period_id"/>

          <collection
              property="investmentDateWiseData"
              ofType="InvestmentDateWiseData">

            <id property="investmentDateId" column="mid_year_investment_date_id"/>

            <collection
                property="vestingDateWiseData"
                ofType="VestingDateWiseData">

              <id property="vestingDateId" column="mid_year_vesting_date_id"/>
              <result property="awardDate" column="mid_year_award_date"/>
              <result property="investmentDate" column="mid_year_investment_date"/>
              <result property="vestingDate" column="mid_year_vesting_date"/>
              <result property="amount" column="mid_year_amount"/>
              <result property="returnAmount" column="mid_year_return_amount"/>
              <result property="vestingAmount" column="mid_year_vesting_amount"/>

            </collection>

          </collection>
        </association>

      </collection>

    </association>
  </resultMap>

  <select id="fetchUserWiseData" resultSets="mandatorySTCResult"
      resultMap="userWiseData">

    select '3520' as person_id

    <!-- This gives results as shown in the CSV file below, which is - mandatorySTCResult -->
  </select>

</mapper>

and these are the classes to which I am trying to get the data to -

public class UserWiseData {
    private String personId;
    private TypeWiseData mandatorySTC;

    // Setter-Getters
}

public class TypeWiseData {
    private String typeId;
    private String personId;
    private List<HRCompanyWiseData> hrCompanyWiseData;

    // Setter-Getters
}


public class HRCompanyWiseData {
    private String companyId;
    private String companyCode;
    private PeriodWiseData nonMidYear;
    private PeriodWiseData midYear;

    // Setter-Getters
}

public class PeriodWiseData {
    private String periodId;
    private List<InvestmentDateWiseData> investmentDateWiseData;

    // Setter-Getters
}

public class InvestmentDateWiseData {
    private String investmentDateId;
    private List<VestingDateWiseData> vestingDateWiseData;

    // Setter-Getters
}


public class VestingDateWiseData {
    private String vestingDateId;
    private String awardDate;
    private String investmentDate;
    private String vestingDate;
    private Double amount;
    private Double returnAmount;
    private Double vestingAmount;

    // Setter-Getters
}

The result of the query looks like so -

person_id   type_id             company_id              company_code    non_mid_year_period_id                  non_mid_year_investment_date_id                 non_mid_year_vesting_date_id                                non_mid_year_award_date non_mid_year_investment_date    non_mid_year_vesting_date   non_mid_year_amount non_mid_year_return_amount  non_mid_year_vesting_amount mid_year_period_id  mid_year_investment_date_id mid_year_vesting_date_id    mid_year_award_date mid_year_investment_date    mid_year_vesting_date   mid_year_amount mid_year_return_amount  mid_year_vesting_amount
3520        3520-mandatory_stc  3520-mandatory_stc-EFGH EFGH            3520-mandatory_stc-EFGH-non_mid_year    3520-mandatory_stc-EFGH-non_mid_year-2018-01-01 3520-mandatory_stc-EFGH-non_mid_year-2018-01-01-2018-12-31  1/1/2018                1/1/2018                        12/31/2018                  16150               2374.05                     18524.05                                    
3520        3520-mandatory_stc  3520-mandatory_stc-ABCD ABCD            3520-mandatory_stc-ABCD-non_mid_year    3520-mandatory_stc-ABCD-non_mid_year-2018-01-01 3520-mandatory_stc-ABCD-non_mid_year-2018-01-01-2019-12-31  1/1/2018                1/1/2018                        12/31/2019                  6000                882                         6882                                    
3520        3520-mandatory_stc  3520-mandatory_stc-EFGH EFGH            3520-mandatory_stc-EFGH-non_mid_year    3520-mandatory_stc-EFGH-non_mid_year-2018-01-01 3520-mandatory_stc-EFGH-non_mid_year-2018-01-01-2019-12-31  1/1/2018                1/1/2018                        12/31/2019                  16150               2374.05                     18524.05                                    
3520        3520-mandatory_stc  3520-mandatory_stc-ABCD ABCD            3520-mandatory_stc-ABCD-non_mid_year    3520-mandatory_stc-ABCD-non_mid_year-2018-01-01 3520-mandatory_stc-ABCD-non_mid_year-2018-01-01-2020-12-31  1/1/2018                1/1/2018                        12/31/2020                  6000                882                         6882                                    
3520        3520-mandatory_stc  3520-mandatory_stc-EFGH EFGH            3520-mandatory_stc-EFGH-non_mid_year    3520-mandatory_stc-EFGH-non_mid_year-2018-01-01 3520-mandatory_stc-EFGH-non_mid_year-2018-01-01-2020-12-31  1/1/2018                1/1/2018                        12/31/2020                  16150               2374.05                     18524.05                                    

I have tried it with column and foreignColumn with the <association> and <collection> tags as well. But it still gives the same result.

---

I expect my result to look something like this -

{
  "userWiseData": [
    {
      "personId": "3520",
      "mandatorySTC": {
        "typeId": "3520-mandatory_stc",
        "personId": "3520",
        "hrCompanyWiseData": [
          {
            "companyId": "3520-mandatory_stc-ABCD",
            "companyCode": "ABCD",
            "monMidYear": {
              "periodId": "3520-mandatory_stc-ABCD-non_mid_year",
              "investmentDateWiseData": [
                {
                  "investmentDateId": "3520-mandatory_stc-ABCD-non_mid_year-2018-01-01",
                  "vestingDateWiseData": [
                    {
                      "vestingDateId": "3520-mandatory_stc-ABCD-non_mid_year-2018-01-01-2019-12-31",
                      "awardDate": "01/01/2018",
                      "investmentDate": "01/01/2018",
                      "vestingDate": "12/31/2019",
                      "amount": "6000",
                      "returnAmount": "882",
                      "vestingAmount": "6882"
                    },
                    {
                      "vestingDateId": "3520-mandatory_stc-ABCD-non_mid_year-2018-01-01-2020-12-31",
                      "awardDate": "01/01/2018",
                      "investmentDate": "01/01/2018",
                      "vestingDate": "12/31/2020",
                      "amount": "6000",
                      "returnAmount": "882",
                      "vestingAmount": "6882"
                    }
                  ]
                }
              ]
            },
            "midYear": {}
          },
          {
            "companyId": "3520-mandatory_stc-EFGH",
            "companyCode": "EFGH",
            "monMidYear": {
              "periodId": "3520-mandatory_stc-EFGH-non_mid_year",
              "investmentDateWiseData": [
                {
                  "investmentDateId": "3520-mandatory_stc-EFGH-non_mid_year-2018-01-01",
                  "vestingDateWiseData": [
                    {
                      "vestingDateId": "3520-mandatory_stc-EFGH-non_mid_year-2018-01-01-2018-12-31",
                      "awardDate": "01/01/2018",
                      "investmentDate": "01/01/2018",
                      "vestingDate": "12/31/2018",
                      "amount": "16150",
                      "returnAmount": "2374.05",
                      "vestingAmount": "18524.05"
                    },
                    {
                      "vestingDateId": "3520-mandatory_stc-EFGH-non_mid_year-2018-01-01-2019-12-31",
                      "awardDate": "01/01/2018",
                      "investmentDate": "01/01/2018",
                      "vestingDate": "12/31/2019",
                      "amount": "16150",
                      "returnAmount": "2374.05",
                      "vestingAmount": "18524.05"
                    },
                    {
                      "vestingDateId": "3520-mandatory_stc-EFGH-non_mid_year-2018-01-01-2020-12-31",
                      "awardDate": "01/01/2018",
                      "investmentDate": "01/01/2018",
                      "vestingDate": "12/31/2020",
                      "amount": "16150",
                      "returnAmount": "2374.05",
                      "vestingAmount": "18524.05"
                    }
                  ]
                }
              ]
            },
            "midYear": {}
          }
        ]
      }
    }
  ]
}

However, all I get is an empty value for mandatorySTC like so -

{
  "userWiseData": [
    {
      "mandatorySTC": null,
      "personId": "3520"
    }
  ]
}

---

I am using -

<mybatis.version>3.5.0</mybatis.version>
<mybatis.spring.version>2.00</mybatis.spring.version>

---

The reason I am trying to use resultSet is that I have to add more associations like mandatorySTC and I want to re-use the temporary tables that are created in the <select id="fetchUserWiseData"> to get data for other resultSets as well.

---

If I remove the resultSet="mandatorySTCResult" from <association> and the select '3520' as person_id from the query, I am getting the data as expected.

Answer 1

I think you are misunderstanding the meaning of resultSet.
It means java.sql.ResultSet and if you specify resultSet="mandatorySTCResult" in <association />, there has to be two java.sql.ResultSets returned from the statement (i.e. one for userWiseData and the other for the association).
resultSets and resultSet is usually used with a stored procedure. Please see the example in the documentation.
If this actually is the case, you should update your question to include the information about the stored procedure.

Assuming your goal is to reuse result maps when mapping a complex result, columnPrefix comes in handy.
As you already assigned appropriate column aliases, I could get the expected result using the following result maps.

<resultMap type="test.UserWiseData" id="UserWiseDataRM"
  autoMapping="false">
  <id property="personId" column="person_id" />
  <association property="mandatorySTC" resultMap="TypeWiseDataRM" />
</resultMap>

<resultMap type="test.TypeWiseData" id="TypeWiseDataRM">
  <id property="typeId" column="type_id" />
  <result property="personId" column="person_id" />
  <collection property="hrCompanyWiseData"
    resultMap="HRCompanyWiseDataRM" />
</resultMap>

<resultMap type="test.HRCompanyWiseData"
  id="HRCompanyWiseDataRM">
  <id property="companyId" column="company_id" />
  <result property="companyCode" column="company_code" />
  <association property="nonMidYear"
    resultMap="PeriodWiseDataRM" columnPrefix="non_mid_year_" />
  <association property="midYear"
    resultMap="PeriodWiseDataRM" columnPrefix="mid_year_" />
</resultMap>

<resultMap type="test.PeriodWiseData" id="PeriodWiseDataRM">
  <id property="periodId" column="period_id" />
  <collection property="investmentDateWiseData"
    resultMap="InvestmentDateWiseDataRM" />
</resultMap>

<resultMap type="test.InvestmentDateWiseData"
  id="InvestmentDateWiseDataRM">
  <id property="investmentDateId" column="investment_date_id" />
  <collection property="vestingDateWiseData" 
    resultMap="VestingDateWiseDataRM" />
</resultMap>

<resultMap type="test.VestingDateWiseData"
  id="VestingDateWiseDataRM">
  <id property="vestingDateId" column="vesting_date_id" />
  <result property="awardDate" column="award_date" />
  <result property="investmentDate" column="investment_date" />
  <result property="vestingDate" column="vesting_date" />
  <result property="amount" column="amount" />
  <result property="returnAmount" column="return_amount" />
  <result property="vestingAmount" column="vesting_amount" />
</resultMap>

Here is a portable demo (MCVE) that I used to test.

If you add more <association /> to the root result map, you might need columnPrefix in higher result maps, but the idea is the same.

Hope this helps!

Answer 2

You should not use resultSets where your sql just return one resultSet.It's used to distinguish multiple resultsets.