Mybatis map multiple resultsets into object inner collections

Question

Brief problem description

Following guideline for multiple resultsets and with help from this answer I now able to extract 2 different recordsets but they are just list and do not mapped on result object.

In details

I have classes (simplified):

public class SupplyChain{
    private String id;

    private List<SupplyChainNode> nodes;
    private List<SupplyChainEdge> edges;
}

public class SupplyChainNode {
    private String id;
    private String label;
}

public class SupplyChainEdge {
    private String id;
    private String label;
}

MyBatis interface declared like:

public interface SupplyChainMBDao {
    List<SupplyChain> getByPartyId(@Param("partyId") String partyId);
}

And MyBatis mapping:

<mapper namespace="ru.rlh.egais.portal.backend.dao.mybatis.SupplyChainMBDao">
    <select id="getByPartyId" resultSets="edges,nodes" resultMap="supplyChainMapEdge, supplyChainMapNode"><![CDATA[
    -- There big query returns 2 recordset - first for Edges and second for Nodes. They have different amount of rows and logic of obtain, but share initial computation and it is desire to return them atomic.
    -- Let it be for simplicity:
    SELECT * FROM (VALUES(1, 2)) edges(id, label);

    SELECT * FROM (VALUES(2, 3), (4, 5)) nodes(id, label)
    ]]></select>

    <resultMap id="supplyChainMapEdge" type="ru.rlh.egais.portal.api.dto.bo.supplychain.SupplyChainEdge" >
        <result property="label" column="label"/>
    </resultMap>

    <resultMap id="supplyChainMapNode" type="ru.rlh.egais.portal.api.dto.bo.supplychain.SupplyChainNode" >
        <result property="label" column="label"/>
    </resultMap>
</mapper>

So, basically it works and I got 2 collections. But instead of declared List<SupplyChain> return value I really got List<List> where inner list contain 2 elements in runtime:

0 element is List<SupplyChainEdge>
and 1st: List<SupplyChainNode>.

How to I can wrap this raw collections into object SupplyChain?

Check this out for the section `Advanced Result Maps`: http://www.mybatis.org/mybatis-3/sqlmap-xml.html — Sky, Oct 20 '16 at 06:50
You just need to query the columns that belong to the different `resultMap`. This is my snippet: SELECT , FROM main m LEFT JOIN (SELECT f.id, f.main_id, f.feature_id, d.label feature_name FROM feature f LEFT JOIN sys_dict d ON d.id = f.feature_id WHERE d.type = 'feature') ft ON ft.main_id = m.id I have a class `Main` with a `List`. — Sky, Oct 21 '16 at 06:04
So, basically you insist I should wrap it in single resultset? — Hubbitus, Oct 21 '16 at 09:17

blackwizard · Answer 1 · 2016-10-27T15:00:28.877

I have to guess that the "big query returns 2 recordset [...]" is actually a stored procedure whose body consists in 2 SELECT statements (just as suggested in Multiple ResultSets for Association chapter of Mybatis doc) And that's how you obtain 2 result sets.

You may indeed try to build a single SELECT and map the columns using collections/associations.

Or you may bind your destination collections to stored procedure OUT parameters (here is for Oracle):

CREATE PROCEDURE p(partyId IN VARCHAR2, rc1 OUT SYS_REFCURSOR, rc2 OUT SYS_REFCURSOR) AS
BEGIN
OPEN rc1 FOR SELECT [...];
OPEN rc2 FOR SELECT [...];
END;

And here is the Mapper interface (with annotations, you can transpose it all to the XML):

@Select("{ CALL p(#{partyId}),
#{supplyChain.nodes, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=supplyChainMapNode},
#{supplyChain.edges, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=supplyChainMapEdge} 
}")
@Options(statementType = StatementType.CALLABLE)
void getByPartyId(@Param("partyId") String partyId, @Param("supplyChain") SupplyChain supplyChain);

The resultMaps are those you define in the XML.

So call the mapper and the SupplyChain bean will be filled when the procedure will have responded.

Anyway, I wonder what is the behavior difference between working with SELECTs or CURSORs, especially regarding resources management / performance, considering Java eventually works with resultSets. For exemple I wanted to set a custom value for fetchSize option (Oracle default is 10, which is very slow for large result sets (too much java<-->DB round-trips)). But so far I could not figure out if this statement option is used for bound out-params.

Cheers.

I don't use stored procedure, and want just map from 2 separate results. — Hubbitus, Oct 27 '16 at 10:04
I was confused because Oracle DB that denies this multiple select statement. With Postgres I get the same result as you. Added a new answer (new approach). — blackwizard, Oct 27 '16 at 15:28

score 2 · Answer 2 · answered Oct 27 '16 at 15:28

Another approach: use custom ResultHandler. I've debugged until DefaultResultSetHandler.handleResultSets and figured out that provided custom result handler is used on each "sub" result set and not on the global query. Then the result lists have to be built directly where they are expected: in the supplyChain object.

/* the Custom Result Handler  */
public class CustomResultHandler implements ResultHandler {
    private SupplyChain supplyChain;

    public CustomResultHandler(SupplyChain supplyChain) {
        this.supplyChain = supplyChain;
    }
    @Override
    public void handleResult(ResultContext ctx) {
        Object o = ctx.getResultObject();
        /* access to SupplyChain members is simplified here */
        if (o instanceof SupplyChainEdge) {
            SupplyChainEdge sc = (SupplyChainEdge) o;   
            if (ctx.getResultCount() == 1) { /* lazy list instantiation */
                this.supplyChain.supplyChainEdge = new ArrayList<SupplyChainEdge>();
            }
            this.supplyChain.supplyChainEdge.add(sc);
        } else if (o instanceof SupplyChainNode) {
            SupplyChainNode sc = (SupplyChainNode) o;
            if (ctx.getResultCount() == 1) { /* lazy list instantiation */
                this.supplyChain.supplyChainNode = new ArrayList<SupplyChainNode>();
            }
            this.supplyChain.supplyChainNode.add(sc);
        }   
    }
}

/* in mapper interface  */
void getByPartyId(@Param("partyId") String partyId, ResultHandler handler);

/* how to call it */
SupplyChain supplyChain = new SupplyChain();
ResultHandler handler = new CustomResultHandler();
mapper.getByPartyId(id, handler);

I Hope this matches your expectations. Anyway, I think this is answer to the question: wrap collections into object SupplyChain.

Cheers

It is much closer to solution. But `CustomResultHandler` is implementation detail. Could it be bounded in XML or interface automatically? It must not be public part of API and used manually on each DAO call. — Hubbitus, Oct 27 '16 at 15:34
Fetching multiple result sets in a single call is not the most common use case. And Mybatis lib is not (yet) that "automatic". Extend/override would be required then. To hide Result Handler from API, you will have to wrap the calls either `session.select("getByPartyId", id, handler);` or `mapper.getByPartyId(id, handler);` — blackwizard, Oct 27 '16 at 16:27

Mybatis map multiple resultsets into object inner collections

Brief problem description

In details

2 Answers2