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?

Answer 1

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.