Details
-
Type:
Improvement
-
Status:
Resolved
-
Priority:
Minor
-
Resolution: Won't Fix
-
Affects Version/s: 1.9.3
-
Fix Version/s: 2.1.0-M2
-
Component/s: Persistence Layer
-
Labels:None
Description
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
>
I tried the suggested changes in this issue, but it didn't work for me. I've attached a patch with the changes in case someone else wants to try. Here's the error I get when running tests:
-------------------------------------------------------------------------------
Test set: org.appfuse.dao.UserDaoTest
-------------------------------------------------------------------------------
Tests run: 7, Failures: 0, Errors: 4, Skipped: 0, Time elapsed: 0.282 sec <<< FAILURE!
testGetUser(org.appfuse.dao.UserDaoTest) Time elapsed: 0.126 sec <<< ERROR!
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];
— The error occurred in sqlmaps/UserSQL.xml.
— The error occurred while applying a result map.
— Check the UserSQL.userRolesResult.
— Check the result mapping for the 'id' property.
— Cause: com.ibatis.sqlmap.client.SqlMapException: Error getting nested result map values for 'roles'. Cause: java.sql.SQLException: Column 'ROLE_ID' not found.