Change UserSQL.xml to automatically fetch roles instead of manually doing it

http://www.nabble.com/Re%3A-Question-about-default-Appfuse-getUserRoles-iBatis-mapping-p6192137s2369.html

Hi Matt,

I managed to get "the iBATIS way" working. Here are the changes if you're interested:

Change:

<!-result property="roles" column="username" select="getUserRoles"/->

to:

<result property="roles" resultMap="UserSQL.userRolesResult"/>

and add:

<resultMap id="userRolesResult" class="role">
<result property="id" column="ROLE_ID"/>
<result property="name" column="ROLE_NAME"/>
<result property="description" column="ROLE_DESCRIPTION"/>
</resultMap>

Then change getUser/getUserByUsername to support a left outer join with the "role" table... my test SQL for getUserByUsername (which works) below:

<select id="getUserByUsername" resultMap="userResult">
<![CDATA[
select U.id, U.username, U.version, U.password, U.first_name, U.last_name , U.address, U.city, U.province, U.country, U.postal_code,
U.email, U.phone_number, U.website, U.password_hint, U.account_enabled, U.account_expired, U.account_locked, U.credentials_expired,
R.id as role_id, R.name as role_name, R.description as role_description
from
(app_user U left outer join user_role X
on U.id = X.user_id)
left outer join role R
on R.id = X.role_id
where username=#value#
]]>
</select>

Then make sure you change:

<resultMap id="userResult" class="user">

to:

<resultMap id="userResult" class="user" groupBy="id">

Finally, you can remove:

List roles = getSqlMapClientTemplate().queryForList("getUserRoles", user);
user.setRoles(new HashSet(roles));

From UserDaoiBatis...

Don't know if this is any use to you. Thanks for your response.

On 9/7/06, Matt Raible <mraible@...> wrote:

On 9/7/06, Robert Campbell <rrc7cz@...> wrote:
> After a clean install of Appfuse (WebWork w/iBatis, multiple versions) we
> have this:
>
> <!--result property="roles" column="username"
> select="getUserRoles"/-->
>
> in the "UserSQL" iBatis sqlMap, specifically in the "userResult" resultMap.
> Why is this commented out. Instead of using this iBatis functionality (found
> on page 37 of
> http://people.apache.org/dist/ibatis/ibatis.java/docs/iBATIS-SqlMaps-2_en.pdf)
> this functionality was instead moved to UserDaoiBatis in the following code:
>
> List roles =
> getSqlMapClientTemplate().queryForList("getUserRoles",
> user);
> user.setRoles(new HashSet(roles));
>
> where the user roles are manually added.
>
> I'm sure there is a good reason for doing it this way, I'm just curious as
> to what that reason is.
>
> Also, I tried to get the iBatis way of doing it to work, and was unable to
> get it working...

My reason is the same as yours - I couldn't get "the iBATIS way" to
work - so I did it manually.

Matt

>
> Sorry if this has already been answered, I couldn't find a reference to it
> in the mailing list.
>
> Thanks,
>
> Rob
>