I have 2 tables, user and role, and an associate table user_role which, as you would expect contains user_role_id,user_id, and role_id keys. I want to read the following query into a map of userRole objects that will be keyed on role name (from the Role object):
<statement id="getUserRoleMap" resultMap="user-role-result">
SELECT role.name, user_role.user_role_id,user_role.user_id, user_role.role_id, user_role.default_role, user_role.status,user_role.created_on,user_role.created_by, user_role.updated_on, user_role.updated_by
FROM role, user_role
WHERE user_role.user_id = #value#
AND user_role.role_id = role.role_id
</statement>
SELECT role.name, user_role.user_role_id,user_role.user_id, user_role.role_id, user_role.default_role, user_role.status,user_role.created_on,user_role.created_by, user_role.updated_on, user_role.updated_by
FROM role, user_role
WHERE user_role.user_id = #value#
AND user_role.role_id = role.role_id
</statement>
And I'm currently trying to do this using the following result map, which mirrors the userRole object:
<resultMap id="user-role-result" class="org.jsurveys.domain.UserRole">
<result property="id" column="user_role_id" columnIndex="2"/>
<result property="userId" column="user_id" columnIndex="3"/>
<result property="roleId" column="role_id" columnIndex="4"/>
<result property="defaultRole" column="default_role" columnIndex="5"/>
<result property="status" column="status" columnIndex="6"/>
<result property="createdOn" column="created_on" columnIndex="7"/>
<result property="createdBy" column="created_by" columnIndex="8"/>
<result property="updatedOn" column="updated_on" columnIndex="9"/>
<result property="updatedBy" column="updated_by" columnIndex="10"/>
</resultMap>
<result property="id" column="user_role_id" columnIndex="2"/>
<result property="userId" column="user_id" columnIndex="3"/>
<result property="roleId" column="role_id" columnIndex="4"/>
<result property="defaultRole" column="default_role" columnIndex="5"/>
<result property="status" column="status" columnIndex="6"/>
<result property="createdOn" column="created_on" columnIndex="7"/>
<result property="createdBy" column="created_by" columnIndex="8"/>
<result property="updatedOn" column="updated_on" columnIndex="9"/>
<result property="updatedBy" column="updated_by" columnIndex="10"/>
</resultMap>
Obviously it doesn't work as name is not an attribute of userRole and I'd rather not add it to the object for just this task. Is there another way I might accomplish this via a SQL Map (ie without extra Java code to build the map after the sql map call)?
Thanks,
Mike
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
