Java – Mybatis – Returning Object containing Hashmap from SELECT

hashmapjavamybatisMySQL

I am working with Mybatis-3, and I have the following situation:

I have Class – User which looks as follows:

public class User extends GeneralDto {

    private String userId;
    private String email;
    private String firstName;
    private String lastName;
    private long creationTimestamp;
    private long updateTimestamp;
    private List<String> tags;
    private HashMap<String, String> attributes;
    private HashMap<String, String> accounts;

    get and set to all + equals + hashcode.
}

The two hashmaps contain unknown keys and values of the type String (and they are making me a lot of trouble).

I have already mapped this Class into tables using insert method. and it is mapped OK to the following tables scheme:

User (userIdentity, userId, email, firstName, lastName, creationTimestamp, updateTimestamp)

UserAttribute (userIdentity, attributeName, attributeValue, creationTimestamp, updateTimestamp)

UserTag (userIdentity, tagName, creationTimestamp, updateTimestamp)

UserAccount (userIdentity, accountIdentity, role, creationTimestamp, updateTimestamp)

I need to create a GET method. The method receives UserKey object which contains the userId which is the key of a user. and returns an instance of the User Class.

This is the SELECT statement which joins all tables and gets the relevant data from each:

<select id="getUser" parameterType="com.intel.aa.iot.mybatis.UserResultHandler" resultMap="userResultMap" resultOrdered="true">
    SELECT
        U.userId as userId,
        U.email as email,
        U.firstName as firstName,
        U.lastName as lastName,
        U.creationTimestamp as creationTimestamp,
        U.updateTimestamp as updateTimestamp,
        UT.tagName as tagName,
        UAT.attributeName as attributeName,
        UAT.attributeValue as attributeValue,
        A.accountId as accountId,
        UAC.role as role
    FROM User U
        LEFT OUTER JOIN UserTag UT ON U.userIdentity = UT.userIdentity
        LEFT OUTER JOIN UserAttribute UAT ON U.userIdentity = UAT.userIdentity
        LEFT OUTER JOIN UserAccount UAC ON U.userIdentity = UAC.userIdentity
        LEFT OUTER JOIN ACCOUNTS A ON UAC.accountIdentity = A.accountIdentity
    WHERE U.userId = #{userKey.userId}

This query might return more than one row, because of the joins, but all rows are of the given userId.

My question is how do i map this into one result which is an instance of the User Class.
I tried using result map but encountered a problem with mapping the hashmap.
Then I tried using ResultHandler – returning class called UserProperties which contains each hashmap as two lists (see code below), but unfortunately it didn't work as well – only one value for each list is saved eventually.

The UserProperties class:

public static class UserProperties {
    private User user;
    private List<String> attributeNames;
    private List<String> attributeValues;
    private List<String> accountIds;
    private List<String> accountRoles;

    get and set to all
}

ResultMap:

<resultMap id="userResultMap" type="com.intel.aa.iot.mybatis.UserMapper$UserProperties">
    <association property="user" javaType="com.intel.aa.iot.dto.User">
        <id property="userId" column="userId"/>
        <result property="email" column="email"/>
        <result property="firstName" column="firstName"/>
        <result property="lastName" column="lastName"/>
        <result property="creationTimestamp" column="creationTimestamp"/>
        <result property="updateTimestamp" column="updateTimestamp"/>
        <collection property="tags" javaType="java.util.ArrayList" column="tagName" ofType="java.lang.String"/>
    </association>
    <collection property="attributeNames"  javaType="java.util.ArrayList" column="attributeName" ofType="java.lang.String"/>
    <collection property="attributeValues" javaType="java.util.ArrayList" column="attributeValue" ofType="java.lang.String"/>
    <collection property="accountIds" javaType="java.util.ArrayList" column="accountId" ofType="java.lang.String"/>
    <collection property="accountRoles" javaType="java.util.ArrayList" column="role" ofType="java.lang.String"/>
</resultMap>

What are my ways for dealing with these Hasmaps?

Thanks!

Best Answer

I've had this same issue myself over the last couple of years, but I've not found an ideal solution yet. Ultimately the problem is that mybatis does not allow you to specify the key of a hashmap - it uses the result property name as the key.

The two approaches I've taken to work around this are far from perfect, but I'll outline them here in case they're useful for you:

  1. Use a result handler. I see you say you've tried this but are only getting the first value - any chance you could share your code? The result handler gets called once per row returned in the query, so you don't want to create a new object each time. You want to create an object only if you don't already have one that matches the ID in this row, and then iteratively build it up based on the following rows.

  2. Build the names and values as a list of hashmaps and then use a select interceptor (mybatis plugin) to call a method inside the object to construct your real hashmaps. To do this, in your result map you need to specify your name and value results under a association of type hashmap. Then Mybatis will turn this into a list that looks like:

    [List [Hashmap {"attributeName"=name1,"attributeValue"=value1}], [Hashmap {"attributeName"=name2,"attributeValue"=value2}]]

Then you can call a method in the interceptor that iterates over this hashmap to build the hashmap you want.

Neither solution is pretty. The first solution loses the elegance of result maps, and the second solution dirties your domain object. But if you are interested in further details let me know and I will add some code examples.

Related Topic