Hi Philippe,
I use the SearchUserBean like a capsule for one way transport of query data
from the UI to the DAO
layer. I did not know that the || option of sql could be used in iBatis
directly. This is how my
sql looks like, now i can use the suggestion you have made.
The only reason i see that i should not put a "%" SQL char in a Bean is, when a
new guy take over
the project and is seeing stars as to where to "%" get added. But other than
that i don't see a
problem of adding "%" in the Transport Bean. What are the "many different
reasons" i should not
place SQL in a Bean of this form?
Thanks
Prashanth.
<select id="searchUserByCriteria" resultMap="userSearchResult"
parameterClass="userSearchBean">
SELECT
USERID,
USERNAME,
USERFNAME,
USERLNAME,
DECODE(USERINTERNAL,'YES','Internal','External') AS
USERINTERNAL,
TBLUSERLEVELS.LEVELNAME as USERLEVEL
FROM
TBLUSERS, TBLUSERLEVELS
where TBLUSERS.USERLEVEL = TBLUSERLEVELS.LEVELSID AND
TBLUSERS.ACTIVE = 'Y' AND
TBLUSERLEVELS.ACTIVE = 'Y'
<isNotEmpty prepend="AND" property="userInternal">
<isEqual property="userInternalOptn" compareValue="2">
userInternal = #userInternal#
</isEqual>
<isEqual property="userInternalOptn" compareValue="3">
userInternal != #userInternal#
</isEqual>
</isNotEmpty>
<isGreaterThan prepend="AND" property="userLevelId"
compareValue="0">
<isEqual property="userLevelOptn" compareValue="2">
userLevel = #userLevelId#
</isEqual>
<isEqual property="userLevelOptn" compareValue="3">
userLevel != #userLevelId#
</isEqual>
</isGreaterThan>
<isNotEmpty prepend="AND" property="userFirstName">
<isNotEqual property="userFirstName" compareValue="%">
<isEqual property="userFNameOptn"
compareValue="0">
lower(userFName) like
lower(#userFirstName#)
</isEqual>
<isEqual property="userFNameOptn"
compareValue="1">
lower(userFName) like
lower(#userFirstName#)
</isEqual>
<isEqual property="userFNameOptn"
compareValue="2">
lower(userFName) =
lower(#userFirstName#)
</isEqual>
<isEqual property="userFNameOptn"
compareValue="3">
lower(userFName) !=
lower(#userFirstName#)
</isEqual>
</isNotEqual>
</isNotEmpty>
<isNotEmpty prepend="AND" property="userLastName">
<isNotEqual property="userLastName" compareValue="%">
<isEqual property="userLNameOptn"
compareValue="0">
lower(userLName) like
lower(#userLastName#)
</isEqual>
<isEqual property="userLNameOptn"
compareValue="1">
lower(userLName) like
lower(#userLastName#)
</isEqual>
<isEqual property="userLNameOptn"
compareValue="2">
lower(userLName) = lower(#userLastName#)
</isEqual>
<isEqual property="userLNameOptn"
compareValue="3">
lower(userLName) !=
lower(#userLastName#)
</isEqual>
</isNotEqual>
</isNotEmpty>
<isNotEmpty prepend="AND" property="userName">
<isNotEqual property="userName" compareValue="%">
<isEqual property="userNameOptn"
compareValue="0">
lower(userName) like lower(#userName#)
</isEqual>
<isEqual property="userNameOptn"
compareValue="1">
lower(userName) like lower(#userName#)
</isEqual>
<isEqual property="userNameOptn"
compareValue="2">
lower(userName) = lower(#userName#)
</isEqual>
<isEqual property="userNameOptn"
compareValue="3">
lower(userName) != lower(#userName#)
</isEqual>
</isNotEqual>
</isNotEmpty>
ORDER BY USERNAME
</select>
--- Philippe Laflamme <[EMAIL PROTECTED]> wrote:
> Personnaly, for many different reasons, I wouldn't use SQL keywords in a
> Java bean...
>
> I suggest you use the power of iBatis and test your "userNameOptn" within
> the SQLMap:
>
> [...]
> <isEqual property="userNameOptn" compareValue="beginsWith">
> user_first_name LIKE #userFirstName# || '%';
> </isEqual>
> <isEqual property="userNameOptn" compareValue="contains">
> user_first_name LIKE '%' || #userFirstName# || '%';
> </isEqual>
> [...]
>
> You can replace the || with whatever your RDBMS uses to concatenate strings.
>
> Philippe
>
> > -----Original Message-----
> > From: Prashanth Sukumaran [mailto:[EMAIL PROTECTED]
> > Sent: January 18, 2005 12:35 PM
> > To: [email protected]
> > Subject: Re: change in property value inside <select/>
> >
> >
> > Hi Nilesh,
> >
> > I have a UI Layer with its own set of beans(Form Beans) and a
> > DAO layer with its own set of beans. In my dao layer beans
> > i do it this way,
> >
> > See the getUserFirstName() method.
> >
> > /**
> > *
> > * @author Prashanth Sukumaran
> > * @version $Revision: 1.3 $ $Date: 2004/11/08 22:24:48 $
> > */
> > public class SearchUserBean {
> >
> > private String userInternal;
> > private int userLevelId;
> > private String userFirstName;
> > private String userLastName;
> > private String userName;
> > private int userNameOptn;
> > private int userLNameOptn;
> > private int userFNameOptn;
> > private int userLevelOptn;
> > private int userInternalOptn;
> >
> > /**
> > * Default Constructor.
> > */
> > public SearchUserBean() {
> > userInternal = "";
> > userFirstName = "";
> > userLastName = "";
> > userName = "";
> > }
> >
> > /**
> > * @return Returns the userFirstName.
> > */
> > public String getUserFirstName() {
> > if (userFNameOptn == Constants.BEGINS_WITH) { // Begins With
> > return userFirstName + "%";
> > }else if (userFNameOptn == Constants.CONTAINS) { // Contains
> > return "%" + userFirstName + "%";
> > }
> > return userFirstName;
> > }
> > /**
> > * @param userFirstName The userFirstName to set.
> > */
> > public void setUserFirstName(String userFirstName) {
> > this.userFirstName = userFirstName;
> > }
> > /**
> > * @return Returns the userInternal.
> > */
> > public String getUserInternal() {
> > return userInternal;
> > }
> > /**
> > * @param userInternal The userInternal to set.
> > */
> > public void setUserInternal(String userInternal) {
> > this.userInternal = userInternal;
> > }
> > /**
> > * @return Returns the userLastName.
> > */
> > public String getUserLastName() {
> > if (userLNameOptn == Constants.BEGINS_WITH) { // Begins With
> > return userLastName + "%";
> > }else if (userLNameOptn == Constants.CONTAINS) { // Contains
> > return "%" + userLastName + "%";
> > }
> > return userLastName;
> > }
> > /**
> > * @param userLastName The userLastName to set.
> > */
> > public void setUserLastName(String userLastName) {
> > this.userLastName = userLastName;
> > }
> > /**
> > * @return Returns the userName.
> > */
> > public String getUserName() {
> > if (userNameOptn == Constants.BEGINS_WITH) { // Begins With
> > return userName + "%";
> > }else if (userNameOptn == Constants.CONTAINS) { // Contains
> > return "%" + userName + "%";
> > }
> > return userName;
> > }
> > /**
> > * @param userName The userName to set.
> > */
> > public void setUserName(String userName) {
> > this.userName = userName;
> > }
> >
> > /**
> > * @return Returns the userFNameOptn.
> > */
> > public int getUserFNameOptn() {
> > return userFNameOptn;
> > }
> > /**
> > * @param userFNameOptn The userFNameOptn to set.
> > */
> > public void setUserFNameOptn(int userFNameOptn) {
> > this.userFNameOptn = userFNameOptn;
> > }
> > /**
> > * @return Returns the userInternalOptn.
> > */
> > public int getUserInternalOptn() {
> > return userInternalOptn;
> > }
> > /**
> > * @param userInternalOptn The userInternalOptn to set.
> > */
> > public void setUserInternalOptn(int userInternalOptn) {
> > this.userInternalOptn = userInternalOptn;
> > }
> > /**
> > * @return Returns the userLevelOptn.
> > */
> > public int getUserLevelOptn() {
> > return userLevelOptn;
> > }
> > /**
> > * @param userLevelOptn The userLevelOptn to set.
> > */
> > public void setUserLevelOptn(int userLevelOptn) {
> > this.userLevelOptn = userLevelOptn;
> > }
> > /**
> > * @return Returns the userLNameOptn.
> > */
> > public int getUserLNameOptn() {
> > return userLNameOptn;
> > }
> > /**
> > * @param userLNameOptn The userLNameOptn to set.
> > */
> > public void setUserLNameOptn(int userLNameOptn) {
> > this.userLNameOptn = userLNameOptn;
> > }
> > /**
> > * @return Returns the userNameOptn.
> > */
> > public int getUserNameOptn() {
> > return userNameOptn;
> > }
> > /**
> > * @param userNameOptn The userNameOptn to set.
> > */
> > public void setUserNameOptn(int userNameOptn) {
> > this.userNameOptn = userNameOptn;
> > }
> > /**
> > * @return Returns the userLevelId.
> > */
> > public int getUserLevelId() {
> > return userLevelId;
> > }
> > /**
> > * @param userLevelId The userLevelId to set.
> > */
> > public void setUserLevelId(int userLevelId) {
> > this.userLevelId = userLevelId;
> > }
> > }
> >
> >
> > Hope this helps,
> > Prashanth.
> >
> >
> >
> > --- Nilesh Bhattad <[EMAIL PROTECTED]> wrote:
> >
> > > Hello,
> > >
> > > Is there a way to change a property value before it is
> > plugged in to
> > > the select statement?
> > >
> > > <select id="search" parameterClass="java.util.HashMap"
> > > resultClass="java.util.HashMap">
> > > select ConsultantId, BlackListed, FirstName, LastName,
> > > HomeTelephone, EmailAddress, InterviewGrade, TotalYrsOfExp from
> > > Consultant
> > > <dynamic prepend=" where ">
> > > <isNotEmpty property="FirstName" prepend=" and
> > > ">FirstName like #FirstName#</isNotEmpty>
> > > <isNotEmpty property="LastName" prepend=" and
> > > ">LastName like #LastName#</isNotEmpty>
> > > <isNotEmpty property="ActiveInactive" prepend=" and
> > > ">ActiveInactive like #ActiveInactive#</isNotEmpty>
> > > </dynamic>
> > > order by FirstName, LastName
> > > </select>
> > >
> > > For example, in the above case, when FirstName is present,
> > is there a
> > > way to attach '%' at the end of the FirstName's value and
> > then perform
> > > the select operation?
> > >
> > > For some reason, in the calling program, I won't be able to
> > attach '%'
> > > at the end of the search fields. Well, somehow I could achieve that
> > > before the iBatis call is made, but I'm kindof hesitant to go with
> > > that approach. So I was wondering is there any way to handle it in
> > > iBatis layer. Any help is highly appreciated.
> > >
> > > Thanks
> > > Nilesh
> > >
> >
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Take Yahoo! Mail with you! Get it on your mobile phone.
> > http://mobile.yahoo.com/maildemo
> >
>
>
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail
