[JIRA] Created: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

11 messages

[JIRA] Created: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

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

Key: APF-419
URL: http://issues.appfuse.org/browse/APF-419
Project: AppFuse
Type: Improvement

Components: Persistence Layer
Versions: 1.9.3
Reporter: Matt Raible
Assigned to: Matt Raible
Priority: Minor
Fix For: 2.0

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:



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:
>
> 
>
> 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
>

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.appfuse.org/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

AppFuse - Issues mailing list

[JIRA] Updated: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

[ http://issues.appfuse.org/browse/APF-419?page=all ]

Matt Raible updated APF-419:
----------------------------

Fix Version/s: (was: 2.0-M1)
2.0-M2

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assigned To: Matt Raible
> Priority: Minor
> Fix For: 2.0-M2
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.appfuse.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

AppFuse - Issues mailing list

[JIRA] Updated: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

In reply to this post by AppFuse - Issues mailing list

[ http://issues.appfuse.org/browse/APF-419?page=all ]

Matt Raible updated APF-419:
----------------------------

Fix Version/s: (was: 2.0-M2)
2.0-M3

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assigned To: Matt Raible
> Priority: Minor
> Fix For: 2.0-M3
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >

AppFuse - Issues mailing list

[JIRA] Updated: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

In reply to this post by AppFuse - Issues mailing list

[ http://issues.appfuse.org/browse/APF-419?page=all ]

Matt Raible updated APF-419:
----------------------------

Fix Version/s: (was: 2.0-M3)
2.0-M4

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assigned To: Matt Raible
> Priority: Minor
> Fix For: 2.0-M4
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >

AppFuse - Issues mailing list

[JIRA] Updated: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

In reply to this post by AppFuse - Issues mailing list

[ http://issues.appfuse.org/browse/APF-419?page=all ]

Matt Raible updated APF-419:
----------------------------

Fix Version/s: (was: 2.0-M4)
2.0-M5

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assigned To: Matt Raible
> Priority: Minor
> Fix For: 2.0-M5
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >

AppFuse - Issues mailing list

[JIRA] Updated: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

In reply to this post by AppFuse - Issues mailing list

[ http://issues.appfuse.org/browse/APF-419?page=all ]

Matt Raible updated APF-419:
----------------------------

Attachment: userRolesResult.patch

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.

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assigned To: Matt Raible
> Priority: Minor
> Fix For: 2.0-M5
>
> Attachments: userRolesResult.patch
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >

AppFuse - Issues mailing list

[JIRA] Updated: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

In reply to this post by AppFuse - Issues mailing list

[ http://issues.appfuse.org/browse/APF-419?page=all ]

Matt Raible updated APF-419:
----------------------------

Fix Version/s: (was: 2.0-M5)
2.0.1

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assigned To: Matt Raible
> Priority: Minor
> Fix For: 2.0.1
>
> Attachments: userRolesResult.patch
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >

AppFuse - Issues mailing list

[JIRA] Commented: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

In reply to this post by AppFuse - Issues mailing list

[ http://issues.appfuse.org/browse/APF-419?page=comments#action_11720 ]

Matt Raible commented on APF-419:
---------------------------------

The error message above should have "role_id" instead of "ROLE_ID". I tried with both cases and neither worked.

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assigned To: Matt Raible
> Priority: Minor
> Fix For: 2.0.1
>
> Attachments: userRolesResult.patch
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >

AppFuse - Issues mailing list

[JIRA] Updated: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

In reply to this post by AppFuse - Issues mailing list

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assignee: Matt Raible
> Priority: Minor
> Fix For: 2.0.2
>
> Attachments: userRolesResult.patch
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >

AppFuse - Issues mailing list

[JIRA] Updated: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

In reply to this post by AppFuse - Issues mailing list

[ http://issues.appfuse.org/browse/APF-419?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Matt Raible updated APF-419:
----------------------------

Fix Version/s: (was: 2.0.2)
2.1

Bumping from 2.0.2 to 2.1 as there doesn't seem to be a whole lot of interest in fixing this. In addition, there hasn't been a new release of iBATIS in a while, so any issues I experienced last time probably still exist.

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assignee: Matt Raible
> Priority: Minor
> Fix For: 2.1
>
> Attachments: userRolesResult.patch
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >

AppFuse - Issues mailing list

[JIRA] Updated: (APF-419) Change UserSQL.xml to automatically fetch roles instead of manually doing it

Reply Threaded

In reply to this post by AppFuse - Issues mailing list

> Change UserSQL.xml to automatically fetch roles instead of manually doing it
> ----------------------------------------------------------------------------
>
> Key: APF-419
> URL: http://issues.appfuse.org/browse/APF-419
> Project: AppFuse
> Issue Type: Improvement
> Components: Persistence Layer
> Affects Versions: 1.9.3
> Reporter: Matt Raible
> Assignee: Matt Raible
> Priority: Minor
> Fix For: 2.1.0-M2
>
> Attachments: userRolesResult.patch
>
>
> 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:
> 
> 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:
> >
> > 
> >
> > 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
> >