I have a resultSet which looks like this:
<resultMap type="User" id="userMap">
<id column="id" property="id" />
<result property="userName" column="user_name" />
<result property="enabled" column="enabled" />
<result property="firstName" column="first_name" />
<result property="lastName" column="last_name" />
<result property="email" column="email" />
<result property="password" column="user_password" />
<result property="passwordSalt" column="user_password_salt" />
<result property="lastLogin" column="last_login_time" />
<result property="creationTime" column="creation_time" />
<result property="lastEdit" column="last_edit_time" />
<result property="validTill" column="valid_till" />
<collection property="roles" javaType="ArrayList" ofType="Role" column="id">
<id property="id" column="role_id" />
<result property="name" column="role_name" />
<result property="description" column="role_description" />
</collection>
</resultMap>
The select query mapping to this resultSet is:
<select id="getWithRole" resultMap="userMap" parameterType="User">
SELECT * FROM view_boss_user_role
<trim prefix="WHERE" prefixOverrides="AND | OR ">
<if test="id != null">id = #{id}</if>
<if test="userName != null">AND user_name = #{userName}</if>
<if test="firstName != null">AND first_name = #{firstName}</if>
<if test="lastName != null">AND last_name = #{lastName}</if>
<if test="email != null">AND email = #{email}</if>
</trim>
</select>
And the view query is like this:
CREATE OR REPLACE VIEW public.view_boss_user_role(
id,
user_name,
enabled,
first_name,
last_name,
email,
user_password,
user_password_salt,
last_login_time,
creation_time,
last_edit_time,
valid_till,
role_id,
role_name,
role_description)
AS
SELECT bu.id,
bu.user_name,
bu.enabled,
bu.first_name,
bu.last_name,
bu.email,
bu.user_password,
bu.user_password_salt,
bu.last_login_time,
bu.creation_time,
bu.last_edit_time,
bu.valid_till,
br.id AS role_id,
br.name AS role_name,
br.description AS role_description
FROM boss_user bu
LEFT JOIN boss_role_user_map ru ON ru.user_id = bu.id
LEFT JOIN boss_role br ON ru.role_id = br.id;
Which produces result similar to:
"id" "user_name" "enabled" "first_name" "last_name" "email" "user_password" "user_password_salt" "last_login_time" "creation_time" "last_edit_time" "valid_till" "role_id" "role_name" "role_description"
"1" "admin" "True" "Admin" "User" "adminuser@gmail.com" "SEli9hsMTTxGgjrlCz48+5w1CaMnUe/8xJJ0enOZ2ag=" "mBPa0wNPjvnGK3gzDMOHOg==" "28-05-2017" "28-05-2017" "28-05-2017" "31-05-2018" "1" "ADMIN" "Administrator user"
But, when I try to get the roles list from this resultmap, it shows it as blank. I don't know what am I doing wrong. The documentation is either not apt to distinguish this case of collection tag or I'm just not getting it right.
For some reasons, I cannot make a single change to resultMap definition but can change the query. And it is guaranteed that the resultMap definition is correct.
Need an expert guidance