I am trying to use the <collection> in MyBatis3. However, all the items that form the part of the List/collection are always NULL.
Here is what my SQL is -
<select id="fetchPaymentWorkingALL" resultType="paymentWorkingALL" parameterType="java.util.Map">
select
(person_id + '-' + convert(char(10), end_date, 126) + '-' + company + '-' + plan) as paymentid
,person_id as personid
,(person_id + '-' + convert(char(10), end_date, 126) + '-' + company + '-' + plan + '-' + 'non_mid_year') as otherid
,'non_mid_year' as typename
,'0' as typeid
,sum(amount) as amount
,sum(return1_amount) + sum(return2_amount) as returnamount
,sum(endamount) as endamount
from #ABCD
group by person_id, income_type, end_date, company, plan, plan_id
</select>
The output of the query looks like this -
paymentid | personid | otherid | typename | typeid | amount | returnamount | endamount
---------------------------------|-------------|-----------------------------------------------|-----------------|---------|-------------------|---------------|------------------
3520-2017-12-31-ABCD-Mandatory | 3520 | 3520-2017-12-31-ABCD-Mandatory-non_mid_year | non_mid_year | 0 | 10000.0000 | 1200.0000 | 11200.0000
3520-2017-12-31-ABCD-Mandatory | 3520 | 3520-2017-12-31-ABCD-Mandatory-total | total | 2 | 10000.0000 | 1200.0000 | 11200.0000
3520-2018-12-31-ABCD-Mandatory | 3520 | 3520-2018-12-31-ABCD-Mandatory-mid_year | mid_year | 1 | 15000.0000 | 1150.0000 | 16150.0000
3520-2018-12-31-ABCD-Mandatory | 3520 | 3520-2018-12-31-ABCD-Mandatory-non_mid_year | non_mid_year | 0 | 10000.0000 | 1200.0000 | 11200.0000
3520-2018-12-31-ABCD-Mandatory | 3520 | 3520-2018-12-31-ABCD-Mandatory-total | total | 2 | 25000.0000 | 2350.0000 | 27350.0000
3520-2019-12-31-EFGH-Mandatory | 3520 | 3520-2019-12-31-EFGH-Mandatory-non_mid_year | non_mid_year | 0 | 10000.0000 | 1200.0000 | 11200.0000
3520-2019-12-31-EFGH-Mandatory | 3520 | 3520-2019-12-31-EFGH-Mandatory-total | total | 2 | 10000.0000 | 1200.0000 | 11200.0000
3520-2019-12-31-ABCD-Mandatory | 3520 | 3520-2019-12-31-ABCD-Mandatory-mid_year | mid_year | 1 | 15000.0000 | 1150.0000 | 16150.0000
3520-2019-12-31-ABCD-Mandatory | 3520 | 3520-2019-12-31-ABCD-Mandatory-non_mid_year | non_mid_year | 0 | 10000.0000 | 1200.0000 | 11200.0000
3520-2019-12-31-ABCD-Mandatory | 3520 | 3520-2019-12-31-ABCD-Mandatory-total | total | 2 | 25000.0000 | 2350.0000 | 27350.0000
3520-2020-12-31-ABCD-Mandatory | 3520 | 3520-2020-12-31-ABCD-Mandatory-mid_year | mid_year | 1 | 15000.0000 | 1150.0000 | 16150.0000
3520-2020-12-31-ABCD-Mandatory | 3520 | 3520-2020-12-31-ABCD-Mandatory-total | total | 2 | 15000.0000 | 1150.0000 | 16150.0000
The resultMaps look like so -
<resultMap id="paymentWorkingALL" type="PaymentWorkingALL">
<id property="paymentid" column="paymentid" />
<result property="personid" column="personid" />
<collection property="paymentWorkings"
ofType="PaymentWorking"
resultMap="paymentWorkingMap" />
</resultMap>
<resultMap id="paymentWorkingMap" type="PaymentWorking">
<id property="otherid" column="otherid" />
<result property="typename" column="typename"/>
<result property="typeid" column="typeid"/>
<result property="amount" column="amount"/>
<result property="endamount" column="endamount"/>
<result property="returnamount" column="returnamount"/>
</resultMap>
I have also added the aliases like so -
<typeAlias type="com.abcd.PaymentWorkingALL" alias="PaymentWorkingALL"/>
<typeAlias type="com.abcd.PaymentWorking" alias="PaymentWorking"/>
and my classes look like so -
public class PaymentWorkingALL {
private String paymentid;
private String personid;
private List<PaymentWorking> paymentWorkings;
public String getPaymentid() {
return paymentid;
}
public void setPaymentid(String paymentid) {
this.paymentid = paymentid;
}
public String getPersonid() {
return personid;
}
public void setPersonid(String personid) {
this.personid = personid;
}
public List<PaymentWorking> getPaymentWorkings() {
return paymentWorkings;
}
public void setPaymentWorkings(List<PaymentWorking> paymentWorkings) {
this.paymentWorkings = paymentWorkings;
}
}
public class PaymentWorking {
private String otherid;
private String typename;
private String typeid;
private Double amount;
private Double returnamount;
private Double endamount;
public String getOtherid() {
return otherid;
}
public void setOtherid(String otherid) {
this.otherid = otherid;
}
public String getTypename() {
return typename;
}
public void setTypename(String typename) {
this.typename = typename;
}
public String getTypeid() {
return typeid;
}
public void setTypeid(String typeid) {
this.typeid = typeid;
}
public Double getAmount() {
return amount;
}
public void setAmount(Double amount) {
this.amount = amount;
}
public Double getReturnamount() {
return returnamount;
}
public void setReturnamount(Double returnamount) {
this.returnamount = returnamount;
}
public Double getEndamount() {
return endamount;
}
public void setEndamount(Double endamount) {
this.endamount = endamount;
}
}
My expectation was that I would get an List which would look like this -
[
{
"paymentid": "3520-2017-12-31-ABCD-Mandatory",
"personid": "3520",
"paymentWorkings": [
{
"otherid": "3520-2017-12-31-ABCD-Mandatory-non_mid_year",
"typename": "non_mid_year",
"typeid": "0",
"amount": 10000.00,
"returnamount": 1200.00,
"endamount": 11200.00
},
{
"otherid": "3520-2017-12-31-ABCD-Mandatory-total",
"typename": "total",
"typeid": "2",
"amount": 10000.00,
"returnamount": 1200.00,
"endamount": 11200.00
}
]
},
{
"paymentid": "3520-2018-12-31-ABCD-Mandatory",
"personid": "3520",
"paymentWorkings": [
{
"otherid": "3520-2018-12-31-ABCD-Mandatory-non_mid_year",
"typename": "non_mid_year",
"typeid": "0",
"amount": 10000.00,
"returnamount": 1200.00,
"endamount": 11200.00
},
{
"otherid": "3520-2018-12-31-ABCD-Mandatory-mid_year",
"typename": "mid_year",
"typeid": "1",
"amount": 15000.00,
"returnamount": 1150.00,
"endamount": 16150.00
},
{
"otherid": "3520-2018-12-31-ABCD-Mandatory-total",
"typename": "total",
"typeid": "2",
"amount": 25000.00,
"returnamount": 2350.00,
"endamount": 27350.00
}
]
}
]
However, when the query runs, I get a List<PaymentWorkingALL> with size as 12 and each having paymentWorkings as NULL.
As per my understanding, it should have returned a List<PaymentWorkingALL> of length 5 and their IDs would be one of -
'3520-2017-12-31-ABCD-Mandatory',
'3520-2018-12-31-ABCD-Mandatory',
'3520-2019-12-31-EFGH-Mandatory',
'3520-2019-12-31-ABCD-Mandatory',
'3520-2020-12-31-ABCD-Mandatory'
I am using the following versions of mybatis and mybatis-spring -
<mybatis.version>3.2.3</mybatis.version>
<mybatis.spring.version>1.2.0</mybatis.spring.version>
paymentWorkingALL result map is not used because you haven't instruct mybatis to use it, so it has two consequences:
id field of the PaymentWorkingALL and treats all rows as unique objects (hence 12 objects in the result)paymentWorkings association is not mapped at all (so objects are not created in the collection)To fix this specify result map as the attribute of the select node like this:
<select id="fetchPaymentWorkingALL" resultMap="paymentWorkingALL" parameterType="java.util.Map">
...
</select>