Return-Path: <prashanthsukumaran@yahoo.com>
Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm
Delivered-To: mailing list ibatis-user-java@incubator.apache.org
Received: (qmail 10892 invoked by uid 99); 18 Jan 2005 18:59:49 -0000
X-ASF-Spam-Status: No, hits=0.4 required=10.0
	tests=DNS_FROM_RFC_ABUSE
X-Spam-Check-By: apache.org
Received-SPF: pass (hermes.apache.org: local policy)
Received: from web53104.mail.yahoo.com (HELO web53104.mail.yahoo.com)
 (206.190.39.207)
  by apache.org (qpsmtpd/0.28) with SMTP; Tue, 18 Jan 2005 10:59:47 -0800
Received: (qmail 28009 invoked by uid 60001); 18 Jan 2005 18:59:45 -0000
Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  b=px582bpNgWijhJGwY2dVFDHJdJQ2Ptd79Ray/lSgc61VGAoABmt78XNFA2Q39XISb5JSSC1TTlrfCa/2Z413NlM53YfZM0eWz1mT5zurREMCV9hEJ4KerhsVTjgVHCUYqYhyUx67My+tv2boeck9jH59mCZq+uwCWsAcrggTtMU=
  ;
Message-ID: <20050118185945.28007.qmail@web53104.mail.yahoo.com>
Received: from [20.137.2.51] by web53104.mail.yahoo.com via HTTP;
 Tue, 18 Jan 2005 10:59:45 PST
Date: Tue, 18 Jan 2005 10:59:45 -0800 (PST)
From: Prashanth Sukumaran <prashanthsukumaran@yahoo.com>
Subject: RE: change in property value inside <select/>
To: ibatis-user-java@incubator.apache.org
In-Reply-To: <006701c4fd85$6daa6de0$69e8ce84@genome.mcgill.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Checked: Checked

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 <philippe.laflamme@mail.mcgill.ca> 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:prashanthsukumaran@yahoo.com] 
> > Sent: January 18, 2005 12:35 PM
> > To: ibatis-user-java@incubator.apache.org
> > 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 <nilesh@outlinesys.com> 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