You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by John Bz <jo...@inbox.com> on 2009/01/28 13:42:41 UTC
Re: Adding Tables To From Clause and Corresponding Entries To
Where Clause
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: nathan.maves@gmail.com
> Sent: Tue, 27 Jan 2009 22:39:16 -0700
> To: user-java@ibatis.apache.org
> 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 <jo...@inbox.com> 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!