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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] > 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 <[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: 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 <[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 >
