0

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

Shubham
  • 97
  • 1
  • 14

0 Answers0