ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Philippe Laflamme" <philippe.lafla...@mail.mcgill.ca>
Subject RE: change in property value inside <select/>
Date Tue, 18 Jan 2005 21:31:00 GMT
I disagree. You should not apply a design pattern halfway.

DAO is used to abstract the underlying data source (RDBMS, OODBMS, flat
files, LDAP, proprietary API, etc.). Applying the DAO pattern creates a
uniform API regardless of the actual data source used. In other words data
going into and coming out of the DAO should be "data source independent". 

Imagine that Prashanth's bean can be used to query an SQL database and a
flat file, both using the same DAO interface. Both implementations will need
to provide the search method that handles the "beginsWith" business case:

The SQL implementation would need to create a query that looks like:
[...]
firstName LIKE "firstNamePattern%"
[...]

where as the flat file implementaion could possibly need use regular
expressions:

[...]
RegExp re = new RegExp("firstNamePattern.*");
[...]

Using Prashanth's bean, the SQL implementation is quite straightforward. The
"flat file" implementation is another matter. It'll need to know that '%'
has been concatenated and undo what the bean has done to the original
pattern using a bunch of string manipulation method calls. If you think
if-else XML statements are ugly, imagine that... All the information that
describes the query is already contained in the bean, there is absolutely no
need to add the "data source specific" code in this case...

The point of the DAO pattern is to move the burden of handling data source
specific code into diffenrent implementations. If you find the resulting
implementation's code to be ugly, that's fine, but you should not violate
your pattern based on that premise. You'll regret it when you'll need to
support another data source; that was the reason we're applying DAO right?

Philippe

> -----Original Message-----
> From: Chen, Tim [mailto:Tim.Chen@NielsenMedia.com] 
> Sent: January 18, 2005 3:35 PM
> To: ibatis-user-java@incubator.apache.org
> Subject: RE: change in property value inside <select/>
> 
> 
> Actually to the contrary of these (and they are very good 
> points). Adding a % when used in the case that Prashanth 
> suggested as a business case begins with or contains then it 
> makes sense. I think that sql is one of those languages that 
> you get a lot of leeway with since its been a standard for a 
> while. If there was a chance that a database (can't think of 
> one off the top of my head) doesn't support the % then it 
> makes sense not to use it there. Readability will suffer as 
> Philippe pointed out but it's not a major faux pas. I think 
> it's harder to look at a huge extended sql mapping like how 
> Philippe shows to handle the contains issue might be worse 
> than a simple but ugly if else.
> 
> -Tim
> 
> -----Original Message-----
> From: Philippe Laflamme [mailto:philippe.laflamme@mail.mcgill.ca] 
> Sent: Tuesday, January 18, 2005 3:03 PM
> To: ibatis-user-java@incubator.apache.org
> Subject: RE: change in property value inside <select/>
> 
> 
> It's just a matter of principal and good practice. The point 
> of using the DAO pattern is to abstract what the underlying 
> persistence mechanism is. For example, the same DAO 
> interfaces can be used to fetch from an SQL database or an 
> XML file. Hence, nothing in your java beans, business layer, 
> data model, etc. should hint to your DAO implementation.
> 
> Your SearchUserBean is a good way of passing information 
> around, but you must keep in mind that its implementation 
> should not be SQL specific; it should simply encapsulate the 
> query parameters: firstName pattern, the type pattern 
> matching (begins with, contains, exact, etc.) and so forth. 
> Your implementation is good, but it's the DAO layer's job to 
> convert the bean into the proper query, in this case an SQL query.
> 
> That's the main reason why SQL should not be present anywhere 
> in your Java beans/data model. Some other reasons are:
> 
> - SQL query safety (easier to prevent SQL injection)
> - Maintainable (modifying XML  files vs. Java files)
> - Usable (SQL query parts are centralized, not in many 
> different beans)
> - Readble (SQL query outcome is clear)
> 
> There are surely other reasons... Anyone care to add their own?
> 
> Philippe
> 
> > -----Original Message-----
> > From: Prashanth Sukumaran [mailto:prashanthsukumaran@yahoo.com]
> > Sent: January 18, 2005 2:00 PM
> > To: ibatis-user-java@incubator.apache.org
> > Subject: RE: change in property value inside <select/>
> > 
> > 
> > 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
> > 
> 
> 


Mime
View raw message