Hi, I have read the section on dynamic sql, but can't see how to do what I'm trying to do exactly. It looks like I will need 2 separate iterator tags (one for the from clause and one for the to clause) so I assume that means iBatis would be iterating my list twice to build the query. The other thing that I don't see is what notation I use to extract the index of each element of the list so that I can use it as part of the table synonyms so I can join the correct 2 rows of the attribute table and the item attribute table.
I'm sorry that my original email was not clear enough. Hopefully, this helps better clarify what I am asking. Thanks again for your help John > -----Original Message----- > From: [email protected] > Sent: Tue, 27 Jan 2009 22:39:16 -0700 > To: [email protected] > Subject: Re: Adding Tables To From Clause and Corresponding Entries To > Where Clause > > Try reading the developer's guide pdf. It will put you on the right > track > when you get to the dynamic sql section. > Nathan > > On Mon, Jan 26, 2009 at 5:26 PM, John Bz <[email protected]> wrote: > >> Hi, >> >> I'm new to iBatis and am trying to figure out the best way to >> dynamically >> build a query that requires you to add both entries to the from clause >> and the where clause. Below is an example of the sort of thing I'm >> trying to accomplish. >> >> Any help would be appreciated. >> >> Thanks, John >> >> import java.util.*; >> public class ItemFinder { >> private List<ItemAttribute> attributeList; >> public ItemFinder() { >> this.attributeList = new ArrayList<ItemAttribute>(); >> } >> public void addItemAttribute(ItemAttribute itemAttribute) { >> this.attributeList.add(itemAttribute); >> } >> public List<Item> getItemList() { >> List<Item> itemList = new ArrayList<Item>(); >> StringBuffer queryBuf = new StringBuffer(); >> StringBuffer fromBuf = new StringBuffer(); >> StringBuffer whereBuf = new StringBuffer(); >> queryBuf.append("select it.*"); >> fromBuf.append(" from item it "); >> whereBuf.append(" where "); >> Iterator<ItemAttribute> it = attributeList.iterator(); >> int idx = 0; >> while (it.hasNext()) { >> ItemAttribute itemAttr = it.next(); >> if (idx > 0) { >> fromBuf.append(", "); >> whereBuf.append(" and "); >> } >> fromBuf.append(itemAttr.getFrom(idx)); >> whereBuf.append(itemAttr.getWhere(idx)); >> idx++; >> } >> queryBuf.append(fromBuf.toString() + whereBuf.toString()); >> //Get Connection >> //Execute query & build list from result of query >> return itemList; >> } >> } >> >> import java.util.*; >> public class ItemAttribute { >> private String attributeName; >> private float attributeValue; >> public ItemAttribute(String name, float val) { >> this.attributeName = name; >> this.attributeValue = val; >> } >> public String getFrom(int idx) { >> return "attribute attr" + idx + ", item_attribute itemAttr" + idx; >> } >> public String getWhere(int idx) { >> return "attr" + idx + ".attribute_name = '" + this.attributeName + >> "'" >> + >> "attr" + idx + ".attribute_id = itemAttr" + idx + >> ".attribute_id >> " + >> " and itemAttr" + idx + ".item_id = it.item_id and itemAttr" >> + >> idx + ".attribute_value = " + this.attributeValue; >> } >> } >> >> ____________________________________________________________ >> FREE 3D EARTH SCREENSAVER - Watch the Earth right on your desktop! >> Check it out at http://www.inbox.com/earth >> ____________________________________________________________ GET FREE 5GB EMAIL - Check out spam free email with many cool features! Visit http://www.inbox.com/email to find out more!
