how to use mybatis collection property when tables don't have direct relationship?

146 Views Asked by At

i have the following data (which were simplified for the purpose of this question):

t1
pk | a     | b   | c     | other1
1  | text1 | 123 | text3 | otherValues1
2  | text2 | 456 | text4 | otherValues2

t2
pk | fk | d     | e  | other2
1  | 1  | text5 | 10 | otherValues3
2  | 1  | text6 | 20 | otherValues4 
3  | 1  | text7 | 30 | otherValues5
4  | 2  | text8 | 40 | otherValues6

with these pojos:

@Data
public class Pojo1 {
  private String p11;
  private Integer p12;
  private String p13;
  private List<Pojo2> list;
}

@Data
public class Pojo2 {
  private String p21;
  private Integer p22;
}

i want an output like this:

{
  "p11": "text1",
  "p12": 123,
  "p13": "text3",
  "list": [ 
            {"p21":"text5","p22":10},
            {"p21":"text6","p22":20},
            {"p21":"text7","p22":30}
          ]
}

unfortunately i'm restriced to use this naming convention (response attributes are named differently than columns) and i have to use these 2 queries separately:

select a, b, c, other1 from table t1 where t1.pk = ${key1}

select d, e, other2 from table t2 where t2.fk = ${key1}

these queries are way bigger and more complex with a huge where clause not related to problem. the issue is: they cannot be joined directly and should be executed separately.

this is my mapper file:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="something.MyMapper">

    <resultMap id="map1" type="Pojo1">
        <result column="a" property="p11"/>
        <result column="b" property="p12"/>
        <result column="c" property="p13"/>
        <collection property="list" column="dontKnowWhatToPutHere" ofType="Pojo2" select="select2"/>
    </resultMap>

    <select id="select1" resultMap="map1">
        select a, b, c, other1 from table t1 where t1.pk = ${key1}
    </select>

    <resultMap id="map2" type="Pojo2">
        <result column="d" property="p21"/>
        <result column="e" property="p22"/>
    </resultMap>

    <select id="select2" resultMap="map2">
        select d, e, other2 from table t2 where t2.fk = ${key1}
    </select>

</mapper>

but this is giving me an answer like this:

{
  "p11": "text1",
  "p12": 123,
  "p13": "text3",
  "list": null
}

looking here made me realize i should create a fake hard coded column at map1 to force the relationship (btw this worked). is this the only option considering my restrictions?

0

There are 0 best solutions below