Quantcast

Conditional List Mapping

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Conditional List Mapping

vl cheong
Hi,
I've some POJO which like the following:

public class SubsidiaryCompany {

   
private String code;

   
private String name;

   
private boolean active;

   
//setters and getters
}

public class SubsidiaryGroup {

   
private List<SubsidiaryCompany> authorized;

   
private List<SubsidiaryCompany> unauthorized;

   
//setters and getters
}

What I'm trying to achieve is to execute this SQL and get back a SubsidiaryGroup object grouped by the status either A(Authorized) or U(Unauthorized) into the authorized and unauthorized properties. May I know how to configure the resultMap ?

SELECT
    a
.code,
    a
.holding_company_code,
    a
.name,
    a
.active,
    CASE WHEN b
.subsidiary_company_code IS NULL THEN 'U' ELSE 'A' END AS status
FROM
    subsidiary_company a
LEFT JOIN
    authorized_company b
ON a
.code=b.subsidiary_company_code

Thanks

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Conditional List Mapping

Guy Rouillier-2
Your resultMap should return a SubsidiaryGroup object.  That resultMap should include two collections, one for each of the lists in the SubsidiaryGroup.  Take a look at the discussion of Collection in the MyBatis User Guide.

--
Guy Rouillier



------ Original Message ------
From: "vl cheong" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 2/21/2017 11:22:44 PM
Subject: Conditional List Mapping

Hi,
I've some POJO which like the following:

public class SubsidiaryCompany {

   
private String code;

   
private String name;

   
private boolean active;

   
//setters and getters
}

public class SubsidiaryGroup {

   
private List<SubsidiaryCompany> authorized;

   
private List<SubsidiaryCompany> unauthorized;

   
//setters and getters
}

What I'm trying to achieve is to execute this SQL and get back a SubsidiaryGroup object grouped by the status either A(Authorized) or U(Unauthorized) into the authorized and unauthorized properties. May I know how to configure the resultMap ?

SELECT
    a
.code,
    a
.holding_company_code,
    a
.name,
    a
.active,
    CASE WHEN b
.subsidiary_company_code IS NULL THEN 'U' ELSE 'A' END AS status
FROM
    subsidiary_company a
LEFT JOIN
    authorized_company b
ON a
.code=b.subsidiary_company_code

Thanks

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.



Avast logo

This email has been checked for viruses by Avast antivirus software.
www.avast.com


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Conditional List Mapping

vl cheong
Hi Guy,
Actually, I know I can achieve the expected result by executing 2 queries. I'm just curious whether it can be done by 1 query and I've tried like this. Unfortunately, I got the TooManyResultsException, may be I'm missing something.

SubsidiaryGroup subsidiaryGroup = sqlSessionTemplate.selectOne("findSubsidiaryGroup");

<resultMap id="SubsidiaryCompanyMap" type="SubsidiaryCompany">
   
<id property="code" column="code"/>
   
<result property="name" column="name"/>
   
<result property="active" column="active"/>
</resultMap>

<resultMap id="SubsidiaryGroupMap" type="SubsidiaryGroup">
    <collection property="authorized"
                column="status"
                javaType="ArrayList"
                resultMap="SubsidiaryCompanyMap"/
>

   
<collection property="unauthorized"
                column
="status"
                javaType
="ArrayList"
                resultMap
="SubsidiaryCompanyMap"/>
</resultMap>

<select id="findSubsidiaryGroup"
        resultMap="SubsidiaryGroupMap">
    SELECT
        a.code,
        a.holding_company_code,
        a.name,
        a.active,
        CASE WHEN b.subsidiary_company_code IS NULL THEN 'U' ELSE 'A' END AS status
    FROM
        subsidiary_company a
    LEFT JOIN
        authorized_company b
    ON a.code=b.subsidiary_company_code        
</
select>

Regards,
vlcheong


On Wednesday, February 22, 2017 at 12:39:22 PM UTC+8, Guy Rouillier wrote:
Your resultMap should return a SubsidiaryGroup object.  That resultMap should include two collections, one for each of the lists in the SubsidiaryGroup.  Take a look at the discussion of Collection in the MyBatis User Guide.

--
Guy Rouillier



------ Original Message ------
From: "vl cheong" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="sAQN8wAACQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">vlche...@...>
To: "mybatis-user" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="sAQN8wAACQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybati...@...>
Sent: 2/21/2017 11:22:44 PM
Subject: Conditional List Mapping

Hi,
I've some POJO which like the following:

public class SubsidiaryCompany {

   
private String code;

   
private String name;

   
private boolean active;

   
//setters and getters
}

public class SubsidiaryGroup {

   
private List<SubsidiaryCompany> authorized;

   
private List<SubsidiaryCompany> unauthorized;

   
//setters and getters
}

What I'm trying to achieve is to execute this SQL and get back a SubsidiaryGroup object grouped by the status either A(Authorized) or U(Unauthorized) into the authorized and unauthorized properties. May I know how to configure the resultMap ?

SELECT
    a
.code,
    a
.holding_company_code,
    a
.name,
    a
.active,
    CASE WHEN b
.subsidiary_company_code IS NULL THEN 'U' ELSE 'A' END AS status
FROM
    subsidiary_company a
LEFT JOIN
    authorized_company b
ON a
.code=b.subsidiary_company_code

Thanks

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="sAQN8wAACQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.



<a href="https://www.avast.com/antivirus" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fantivirus\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFKLvnpDv0lgK5pU3VEfIU9tx2wrg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fantivirus\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFKLvnpDv0lgK5pU3VEfIU9tx2wrg&#39;;return true;"> Avast logo

This email has been checked for viruses by Avast antivirus software.
<a href="https://www.avast.com/antivirus" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fantivirus\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFKLvnpDv0lgK5pU3VEfIU9tx2wrg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fantivirus\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFKLvnpDv0lgK5pU3VEfIU9tx2wrg&#39;;return true;">www.avast.com


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re[2]: Conditional List Mapping

Guy Rouillier-2
Take a look at the notNullColumn attribute on the Collection.  In the SQL, you can create two separate columns corresponding to your authorized and unauthorized status; each would be non-null only in the appropriate situation.  That would then coerce each row into the appropriate collection.

There's also discriminator, but I don't think you could nest a collection inside of that.  If the first suggestion doesn't pan out, you can give it a try.

--
Guy Rouillier



------ Original Message ------
From: "vl cheong" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 2/22/2017 7:52:51 PM
Subject: Re: Conditional List Mapping

Hi Guy,
Actually, I know I can achieve the expected result by executing 2 queries. I'm just curious whether it can be done by 1 query and I've tried like this. Unfortunately, I got the TooManyResultsException, may be I'm missing something.

SubsidiaryGroup subsidiaryGroup = sqlSessionTemplate.selectOne("findSubsidiaryGroup");

<resultMap id="SubsidiaryCompanyMap" type="SubsidiaryCompany">
   
<id property="code" column="code"/>
   
<result property="name" column="name"/>
   
<result property="active" column="active"/>
</resultMap>

<resultMap id="SubsidiaryGroupMap" type="SubsidiaryGroup">
    <collection property="authorized"
                column="status"
                javaType="ArrayList"
                resultMap="SubsidiaryCompanyMap"/
>

   
<collection property="unauthorized"
                column
="status"
                javaType
="ArrayList"
                resultMap
="SubsidiaryCompanyMap"/>
</resultMap>

<select id="findSubsidiaryGroup"
        resultMap="SubsidiaryGroupMap">
    SELECT
        a.code,
        a.holding_company_code,
        a.name,
        a.active,
        CASE WHEN b.subsidiary_company_code IS NULL THEN 'U' ELSE 'A' END AS status
    FROM
        subsidiary_company a
    LEFT JOIN
        authorized_company b
    ON a.code=b.subsidiary_company_code        
</
select>

Regards,
vlcheong


On Wednesday, February 22, 2017 at 12:39:22 PM UTC+8, Guy Rouillier wrote:
Your resultMap should return a SubsidiaryGroup object.  That resultMap should include two collections, one for each of the lists in the SubsidiaryGroup.  Take a look at the discussion of Collection in the MyBatis User Guide.

--
Guy Rouillier



------ Original Message ------
From: "vl cheong" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 2/21/2017 11:22:44 PM
Subject: Conditional List Mapping

Hi,
I've some POJO which like the following:

public class SubsidiaryCompany {

   
private String code;

   
private String name;

   
private boolean active;

   
//setters and getters
}

public class SubsidiaryGroup {

   
private List<SubsidiaryCompany> authorized;

   
private List<SubsidiaryCompany> unauthorized;

   
//setters and getters
}

What I'm trying to achieve is to execute this SQL and get back a SubsidiaryGroup object grouped by the status either A(Authorized) or U(Unauthorized) into the authorized and unauthorized properties. May I know how to configure the resultMap ?

SELECT
    a
.code,
    a
.holding_company_code,
    a
.name,
    a
.active,
    CASE WHEN b
.subsidiary_company_code IS NULL THEN 'U' ELSE 'A' END AS status
FROM
    subsidiary_company a
LEFT JOIN
    authorized_company b
ON a
.code=b.subsidiary_company_code

Thanks

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



Avast logo

This email has been checked for viruses by Avast antivirus software.
www.avast.com



Virus-free. www.avast.com

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Re[2]: Conditional List Mapping

vl cheong
Hi,
After some tests, I ended up rewriting my query to split the authorized and unauthorized in different columns and then map them to the collection. The title column(could be anything) is a dummy field to group everything into a single SubsidiaryGroup instance.

<select id="findSubsidiaryGroup"
       
resultMap="SubsidiaryGroupMap">
            SELECT
                'Subsidiary Group' AS title,
                'A' AS status,
                a.code AS a_code,
                a.name AS a_name,
                a.active AS a_active,

                NULL AS u_code,
                NULL AS u_name,
                NULL AS u_active
            FROM
                subsidiary_company a
            INNER JOIN
                authorized_company b
            ON a.code=b.subsidiary_company_code
        UNION ALL
            SELECT
                'Subsidiary Group' AS title,
                'U' AS status,
                NULL AS a_code,
                NULL AS a_name,
                NULL AS a_active,

                a.code AS u_code,
                a.name AS u_name,
                a.active AS u_active
            FROM
                subsidiary_company a
            LEFT JOIN
                authorized_company b
            ON
                a.code=b.subsidiary_company_code
            WHERE
                b.subsidiary_company_code IS NULL
</select>

<resultMap id="SubsidiaryGroupMap" type="SubsidiaryGroup">
   
<result property="title" column="title"/>
   
<collection property="authorized"
               
column="status"
               
ofType="SubsidiaryCompany"
               
javaType="ArrayList">
       
<id property="code" column="a_code"/>
       
<result property="name" column="a_name"/>
       
<result property="active" column="a_active"/>
   
</collection>
   
<collection property="unauthorized"
               
column="status"
               
ofType="SubsidiaryCompany"
               
javaType="ArrayList">
       
<id property="code" column="u_code"/>
       
<result property="name" column="u_name"/>
       
<result property="active" column="u_active"/>
   
</collection>
</resultMap>

Regards,
vlcheong

On Thursday, February 23, 2017 at 7:28:38 PM UTC+8, Guy Rouillier wrote:
Take a look at the notNullColumn attribute on the Collection.  In the SQL, you can create two separate columns corresponding to your authorized and unauthorized status; each would be non-null only in the appropriate situation.  That would then coerce each row into the appropriate collection.

There's also discriminator, but I don't think you could nest a collection inside of that.  If the first suggestion doesn't pan out, you can give it a try.

--
Guy Rouillier



------ Original Message ------
From: "vl cheong" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="9KciE5duDQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">vlche...@...>
To: "mybatis-user" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="9KciE5duDQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybati...@...>
Cc: <a href="javascript:" target="_blank" gdf-obfuscated-mailto="9KciE5duDQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">guy.ro...@...
Sent: 2/22/2017 7:52:51 PM
Subject: Re: Conditional List Mapping

Hi Guy,
Actually, I know I can achieve the expected result by executing 2 queries. I'm just curious whether it can be done by 1 query and I've tried like this. Unfortunately, I got the TooManyResultsException, may be I'm missing something.

SubsidiaryGroup subsidiaryGroup = sqlSessionTemplate.selectOne("findSubsidiaryGroup");

<resultMap id="SubsidiaryCompanyMap" type="SubsidiaryCompany">
   
<id property="code" column="code"/>
   
<result property="name" column="name"/>
   
<result property="active" column="active"/>
</resultMap>

<resultMap id="SubsidiaryGroupMap" type="SubsidiaryGroup">
    <collection property="authorized"
                column="status"
                javaType="ArrayList"
                resultMap="SubsidiaryCompanyMap"/
>

   
<collection property="unauthorized"
                column
="status"
                javaType
="ArrayList"
                resultMap
="SubsidiaryCompanyMap"/>
</resultMap>

<select id="findSubsidiaryGroup"
        resultMap="SubsidiaryGroupMap">
    SELECT
        a.code,
        a.holding_company_code,
        <a href="http://a.name" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fa.name\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFTYJ_MbdQGVbl9uOo0kx8w3rqSMA&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fa.name\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFTYJ_MbdQGVbl9uOo0kx8w3rqSMA&#39;;return true;">a.name,
        a.active,
        CASE WHEN b.subsidiary_company_code IS NULL THEN 'U' ELSE 'A' END AS status
    FROM
        subsidiary_company a
    LEFT JOIN
        authorized_company b
    ON a.code=b.subsidiary_company_code        
</
select>

Regards,
vlcheong


On Wednesday, February 22, 2017 at 12:39:22 PM UTC+8, Guy Rouillier wrote:
Your resultMap should return a SubsidiaryGroup object.  That resultMap should include two collections, one for each of the lists in the SubsidiaryGroup.  Take a look at the discussion of Collection in the MyBatis User Guide.

--
Guy Rouillier



------ Original Message ------
From: "vl cheong" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 2/21/2017 11:22:44 PM
Subject: Conditional List Mapping

Hi,
I've some POJO which like the following:

public class SubsidiaryCompany {

   
private String code;

   
private String name;

   
private boolean active;

   
//setters and getters
}

public class SubsidiaryGroup {

   
private List<SubsidiaryCompany> authorized;

   
private List<SubsidiaryCompany> unauthorized;

   
//setters and getters
}

What I'm trying to achieve is to execute this SQL and get back a SubsidiaryGroup object grouped by the status either A(Authorized) or U(Unauthorized) into the authorized and unauthorized properties. May I know how to configure the resultMap ?

SELECT
    a
.code,
    a
.holding_company_code,
    a
.name,
    a
.active,
    CASE WHEN b
.subsidiary_company_code IS NULL THEN 'U' ELSE 'A' END AS status
FROM
    subsidiary_company a
LEFT JOIN
    authorized_company b
ON a
.code=b.subsidiary_company_code

Thanks

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.



<a href="https://www.avast.com/antivirus" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fantivirus\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFKLvnpDv0lgK5pU3VEfIU9tx2wrg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fantivirus\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFKLvnpDv0lgK5pU3VEfIU9tx2wrg&#39;;return true;"> Avast logo

This email has been checked for viruses by Avast antivirus software.
<a href="https://www.avast.com/antivirus" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fantivirus\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFKLvnpDv0lgK5pU3VEfIU9tx2wrg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fantivirus\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFKLvnpDv0lgK5pU3VEfIU9tx2wrg&#39;;return true;">www.avast.com



<a href="https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=emailclient&amp;utm_term=icon" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fsig-email%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient%26utm_term%3Dicon\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGdgn6XHtGdGX0_ztl7zsznn9rycA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fsig-email%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient%26utm_term%3Dicon\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGdgn6XHtGdGX0_ztl7zsznn9rycA&#39;;return true;"> Virus-free. <a href="https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=emailclient&amp;utm_term=link" style="color:#4453ea" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fsig-email%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient%26utm_term%3Dlink\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEBTW1oVO7X9WxlATGseHnM8Gn-HQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fsig-email%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient%26utm_term%3Dlink\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEBTW1oVO7X9WxlATGseHnM8Gn-HQ&#39;;return true;">www.avast.com

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Loading...