Re: Adding Tables To From Clause and Corresponding Entries To Where Clause

[Nathan Maves <na...@gmail.com>]

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
>

Re: Adding Tables To From Clause and Corresponding Entries To Where Clause

[John Bz <jo...@inbox.com>]

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!