MyBatis collection property

Question

I have following tables:

CREATE TABLE users (
  id VARCHAR (255) NOT NULL,
  name VARCHAR (255) NOT NULL,
  create_date TIMESTAMP NOT NULL,
  email VARCHAR (255) NOT NULL,
  password VARCHAR (255) NOT NULL,
  last_visit TIMESTAMP,
  avatar_id VARCHAR (255)
);

CREATE TABLE user_friends (
  user_id VARCHAR (255) NOT NULL,
  friend_id VARCHAR (255) NOT NULL
);

CREATE TABLE file (
  id VARCHAR (255) NOT NULL,
  file_path VARCHAR (255) NOT NULL,
  create_date TIMESTAMP NOT NULL,
  user_id VARCHAR (255) NOT NULL
);

I want to select user with all his friends, so I've made mapper

<sql id="coreUserAttributes">
      users.id,
      users.name,
      users.last_visit as lastVisit
</sql>

<select id="getUser" resultMap="UserRM">
    SELECT
    <include refid="coreUserAttributes"/>,
    f.id as friendId,
    f.name as friendName
    FROM users
    LEFT JOIN user_friends ON users.id = user_friends.user_id
    LEFT JOIN users f ON f.id = user_friends.friend_id
    <where>
        users.id = #{id}
    </where>
</select>

I've tried to run select - it's working without any problems. The problem is in ResultMap

If I insert resultMap without <collection> tag, everything is working well and mapper returns my User object

<resultMap id="UserRM" type="User">
    <id column="id"/>
</resultMap>

But when I insert <collection> tag

<resultMap id="UserRM" type="User">
    <id column="id"/>
    <collection property="friends" ofType="User" javaType="ArrayList">
        <id column="friendId"/>
        <result column="friendName" property="name"/>
    </collection>
</resultMap>

the result of mapper invocation is null. I don't see any errors in console. How can I load user with all his friends?

P.S Also, what is interesting, I see the following text in logs

2017-01-03 22:29:19 DEBUG u.s.network.query.UserMapper.getUser - ==>  Preparing: SELECT users.id, users.name, users.last_visit as lastVisit , f.id as friendId, f.name as friendName FROM users LEFT JOIN user_friends ON users.id = user_friends.user_id LEFT JOIN users f ON f.id = user_friends.friend_id WHERE users.id = ? 
2017-01-03 22:29:19 DEBUG u.s.network.query.UserMapper.getUser - ==>  Preparing: SELECT users.id, users.name, users.last_visit as lastVisit , f.id as friendId, f.name as friendName FROM users LEFT JOIN user_friends ON users.id = user_friends.user_id LEFT JOIN users f ON f.id = user_friends.friend_id WHERE users.id = ? 
2017-01-03 22:29:19 DEBUG u.s.network.query.UserMapper.getUser - ==> Parameters: 1(String)
2017-01-03 22:29:19 DEBUG u.s.network.query.UserMapper.getUser - ==> Parameters: 1(String)
2017-01-03 22:29:19 DEBUG u.s.network.query.UserMapper.getUser - <==      Total: 1
2017-01-03 22:29:19 DEBUG u.s.network.query.UserMapper.getUser - <==      Total: 1

As far as I understand, the select works normally, but when mybatis builds object, some problem has place.

Answer 1

The solution is quite simple: I've added aliases to this columns and everything started work well

<sql id="coreUserAttributes">
      users.id AS id,
      users.name AS name,
      users.last_visit as lastVisit
</sql>

Also, if there is no at least one property to map to your class - result will be null value