XML ResultMap in MyBatis with association

Question

I see this issue very strange descibed on google or stack. Let me explain.

I have Result Maps in annotations in my interface method. Only in this particular case I need dynamic query, and that is the reason I decided to write the whole mapper for interface in xml file. Below I paste the whole file. The select query should be ok, but I am occuring some difficulties with <resultMap>.

On the different websides I was searching for a good explanation of one to one, one to many, many to one association and construct of this result map in general.

I saw there is some kind of possibility to part it into subqueries, subresultmaps. But I have that already done with myBatis annotations, and I want to use it. Could you direct me, how should the resultMap be constructed? I dont see need for constructor, discriminator, but it is still shouting... (that is why I added <collection> mark) - IntelliJ is underscoring the whole <resultMap>saying: "The content of element type "resultMap" must match "(constructor?,id*,result*,association*,collection*,discriminator?)"

I know it seems obvious, but I have completly no idea how to do it correctly. Please help me.

xml 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="pl.net.manager.dao.UsageCounterDAO">
    <select id="getUsageCounterList" resultType="pl.net.manager.domain.UsageCounter"
            resultMap="getUsageCounterListMap">
        SELECT * FROM usage_counter WHERE
        <if test="apiConsumerIdsList != null">
            api_consumer IN
            <foreach item="item" index="index" collection="apiConsumerIdsList"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
            AND
        </if>
        <if test="serviceConsumerIdsList != null">
            service IN
            <foreach item="item" index="index" collection="serviceConsumerIdsList"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
            AND
        </if>
        <if test="dateFrom != null">
            date &gt;= #{dateFrom} AND
        </if>
        <if test="dateTo != null">
            date &lt;= #{dateTo} AND
        </if>
        <if test="status != null">
            status = #{status}
        </if>
    </select>
    <resultMap id="getUsageCounterListMap" type="">

                    ofType="pl.net.manager.domain.UsageCounter">
            <id property="id" column="id"/>
            <result property="date" column="date"/>
            <result property="apiConsumer" column="api_consumer"
                    javaType="pl.net.manager.domain.ApiConsumer"/>
            <association property="apiConsumer" column="api_consumer"
                         resultMap="pl.net.manager.dao.ApiConsumerDAO.getApiConsumer"/>
            <result property="service" column="service" javaType="pl.net.manager.domain.Service"/>
        <association property="service" column="service"
                     resultMap="pl.net.manager.dao.ServiceDAO.getService"/>

            <result property="counterStatus" column="counter_status"/>
            <result property="ratingProcessId" column="rating_process_id"/>
            <result property="value" column="value"/>
            <result property="createdDate" column="created_date"/>
            <result property="modifiedDate" column="modified_date"/>

    </resultMap>
</mapper>

Answer 1

The XSD for the Mapper XML expects that in a <resultMap>:

the <association> must come after all the <result> tags, which means you need to group <result> tags and then add <association> tags after that.

Secondly, You dont need both resultType and resultMap in the getUsageCounterList. Use any one which in your case is resultMap

Third, Your WHERE clause in getUsageCounterList is broken. What if ONLY first condition is met, then in that case your SELECT will be something like:

SELECT * FROM usage_counter WHERE api_consumer IN (1,2,3) AND 

So as I mentioned in my previous answer to your query you can use <where> tags and follow the syntax mentioned in that answer.

Fourth, In the resultMap getUsageCounterListMap you just need type which will be your Java class you are trying to populate, so you can move the ofType to type.

For One-One mapping, you can just use a <association> tag, or there's another approach, suppose you have a Java POJOs as given below:

public class Country{
    private String name;
    private City capital;
    //getters and setters
}
public class City{
    private String name;
    //getters and setters
}

You can write a Mapping in XML as shown below:

<select id="getCountries" resultType="Country">
    SELECT c.name, cy.name "capital.name" 
    FROM country c 
    JOIN city cy ON cy.name = c.capital
</select>

So Mybatis will make sure to create an instance of City and assign the value in capital.name to that instance's name property.

For One to Many OR Many to Many Mapping you can explore the <collection> tag of the MyBatis result map. For more information check Advacned Result Maps section in the link provided.