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 Jane Phillips <ja...@yahoo.com> on 2006/07/31 15:02:38 UTC
1 to N in queryForObject()
Hi, I am new to iBatis. I have a question for one of my query. I have tables:
1. Product
-- product_id
-- product_description
-- product_name
2. Items
-- item_id
-- product_id
-- item_description
-- item_name
The relationship is one product can have many items. query I would like to do is :
<sqlMap>
<select id="getProductItems" resultClass="com.abc.products">
SELECT product_id, product_description,product_name, item_name from product a, items b WHERE a.product_id = b.product_id and a.product_id = #id#
</select>
</sqlMap>
in java, com.abc.products:
String product_name;
String product_description;
String product_id;
String[] item_name;
and all the getXXX and setXXX methods.
The goal is run this query using queryForObject() to get one product and all its items to a products object instead of queryForList() to get collection of product object with duplicate product_name, product_description and product_id. Is it feasible? And how to config the sqlMap or other things to do it ?
Please advise
---------------------------------
See the all-new, redesigned Yahoo.com. Check it out.
Re: 1 to N in queryForObject()
Posted by Jeff Butler <je...@gmail.com>.
Make sure you're using the latest version of iBATIS. Then make sure your
DOCTYPE matches what's shown on the iBATIS home page.
Also - I just changed the WIKI page to reflect the correct DOCTYPE so you
could pick it up from there too.
Jeff Butler
On 8/1/06, Jane Phillips <ja...@yahoo.com> wrote:
>
> I looked at the wiki. Not sure I got them all. I still have error: "*Attribute
> "groupBy" must be declared for element type "resultMap*". Please help !
>
> Here are my sqlmap and java.
>
> -- java
> String productId;
> String productName;
> String productDesc;
> ArrayList itemName;
> all getXXX and setXXX
>
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "
> http://www.ibatis.com/dtd/sql-map-2.dtd">
> <sqlMap namespace="prod">
> <resultMap id="prodMap" class="com.abc.Products" groupBy="productId">
> <result property="productId" column="productId"/>
> <result property="productName" column="productName"/>
> <result property="productDesc" column="productDesc"/>
> <result property="item" resultMap="prod.itemMap"/>
> </resultMap>
>
> <resultMap id="itemMap" class="java.lang.String">
> <result property="itemName" column="itemName"/>
> </resultMap>
> <select id="getProductItems" resultMap="prodMap">
> select a.product_id as productId,
> a.product_name as productName,
> a.product_desc as productDesc,
> b.item_name as itemName
> from products a , items b
> where a.product_id = b.product_id
>
> </select>
> </sqlMap>
>
> I am using queryForList() trying to get list of product. within each
> product I should have collection of items (item names).
>
> Thanks so much,
>
>
> *Larry Meadors <lm...@apache.org>* wrote:
>
> ...and the WIKI:
>
>
> http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+get+around+the+N+Plus+1+selects+problem
>
> If you follow the instructions on the WIKI and find any issues, please
> feel free to add another example, or make corrections.
>
> Larry
>
>
> On 7/31/06, Jeff Butler wrote:
> >
> > iBATIS has good support for this. You'll need to use a List instead of
> an
> > array, and the "groupBy" keyword on your result map. Look in the
> > developer's guide for more information.
> >
> >
> > Jeff Butler
> >
> >
> >
> > On 7/31/06, Jane Phillips wrote:
> > >
> > >
> > > Hi, I am new to iBatis. I have a question for one of my query. I have
> > tables:
> > >
> > > 1. Product
> > > -- product_id
> > > -- product_description
> > > -- product_name
> > >
> > > 2. Items
> > > -- item_id
> > > -- product_id
> > > -- item_description
> > > -- item_name
> > >
> > > The relationship is one product can have many items. query I would
> like to
> > do is :
> > >
> > > > >> > SELECT product_id, product_description,product_name,> item_name
> from product a, items b WHERE a.product_id = b.product_id and>
> a.product_id = #id#> >
> > >
> > >
> > > in java, com.abc.products:
> > >
> > > String product_name;
> > > String product_description;
> > > String product_id;
> > > String[] item_name;
> > > and all the getXXX and setXXX methods.
> > >
> > > The goal is run this query using queryForObject() to get one product
> and
> > all its items to a products object instead of queryForList() to get
> > collection of product object with duplicate product_name,
> > product_description and product_id. Is it feasible? And how to config
> the
> > sqlMap or other things to do it ?
> > >
> > > Please advise
> > >
> > >
> > >
> > >
> > > ________________________________
> > See the all-new, redesigned Yahoo.com <http://yahoo.com/>. Check it out.
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>
> ------------------------------
> Yahoo! Messenger with Voice. Make PC-to-Phone Calls<http://us.rd.yahoo.com/mail_us/taglines/postman1/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com>to the US (and 30+ countries) for 2¢/min or less.
>
>
Re: 1 to N in queryForObject()
Posted by Jane Phillips <ja...@yahoo.com>.
I looked at the wiki. Not sure I got them all. I still have error: "Attribute "groupBy" must be declared for element type "resultMap". Please help !
Here are my sqlmap and java.
-- java
String productId;
String productName;
String productDesc;
ArrayList itemName;
all getXXX and setXXX
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="prod">
<resultMap id="prodMap" class="com.abc.Products" groupBy="productId">
<result property="productId" column="productId"/>
<result property="productName" column="productName"/>
<result property="productDesc" column="productDesc"/>
<result property="item" resultMap="prod.itemMap"/>
</resultMap>
<resultMap id="itemMap" class="java.lang.String">
<result property="itemName" column="itemName"/>
</resultMap>
<select id="getProductItems" resultMap="prodMap">
select a.product_id as productId,
a.product_name as productName,
a.product_desc as productDesc,
b.item_name as itemName
from products a , items b
where a.product_id = b.product_id
</select>
</sqlMap>
I am using queryForList() trying to get list of product. within each product I should have collection of items (item names).
Thanks so much,
Larry Meadors <lm...@apache.org> wrote:
...and the WIKI:
http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+get+around+the+N+Plus+1+selects+problem
If you follow the instructions on the WIKI and find any issues, please
feel free to add another example, or make corrections.
Larry
On 7/31/06, Jeff Butler wrote:
>
> iBATIS has good support for this. You'll need to use a List instead of an
> array, and the "groupBy" keyword on your result map. Look in the
> developer's guide for more information.
>
>
> Jeff Butler
>
>
>
> On 7/31/06, Jane Phillips wrote:
> >
> >
> > Hi, I am new to iBatis. I have a question for one of my query. I have
> tables:
> >
> > 1. Product
> > -- product_id
> > -- product_description
> > -- product_name
> >
> > 2. Items
> > -- item_id
> > -- product_id
> > -- item_description
> > -- item_name
> >
> > The relationship is one product can have many items. query I would like to
> do is :
> >
> > > >> > SELECT product_id, product_description,product_name,> item_name from product a, items b WHERE a.product_id = b.product_id and> a.product_id = #id#> >
> >
> >
> > in java, com.abc.products:
> >
> > String product_name;
> > String product_description;
> > String product_id;
> > String[] item_name;
> > and all the getXXX and setXXX methods.
> >
> > The goal is run this query using queryForObject() to get one product and
> all its items to a products object instead of queryForList() to get
> collection of product object with duplicate product_name,
> product_description and product_id. Is it feasible? And how to config the
> sqlMap or other things to do it ?
> >
> > Please advise
> >
> >
> >
> >
> > ________________________________
> See the all-new, redesigned Yahoo.com. Check it out.
> >
> >
> >
> >
> >
> >
>
>
---------------------------------
Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.
Re: 1 to N in queryForObject()
Posted by Larry Meadors <lm...@apache.org>.
...and the WIKI:
http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+get+around+the+N+Plus+1+selects+problem
If you follow the instructions on the WIKI and find any issues, please
feel free to add another example, or make corrections.
Larry
On 7/31/06, Jeff Butler <je...@gmail.com> wrote:
>
> iBATIS has good support for this. You'll need to use a List instead of an
> array, and the "groupBy" keyword on your result map. Look in the
> developer's guide for more information.
>
>
> Jeff Butler
>
>
>
> On 7/31/06, Jane Phillips <jane22124@yahoo.com > wrote:
> >
> >
> > Hi, I am new to iBatis. I have a question for one of my query. I have
> tables:
> >
> > 1. Product
> > -- product_id
> > -- product_description
> > -- product_name
> >
> > 2. Items
> > -- item_id
> > -- product_id
> > -- item_description
> > -- item_name
> >
> > The relationship is one product can have many items. query I would like to
> do is :
> > <sqlMap>
> > <select id="getProductItems" resultClass="com.abc.products">
> >
> > SELECT product_id, product_description,product_name,
> item_name from product a, items b WHERE a.product_id = b.product_id and
> a.product_id = #id#
> > </select>
> > </sqlMap>
> >
> > in java, com.abc.products:
> >
> > String product_name;
> > String product_description;
> > String product_id;
> > String[] item_name;
> > and all the getXXX and setXXX methods.
> >
> > The goal is run this query using queryForObject() to get one product and
> all its items to a products object instead of queryForList() to get
> collection of product object with duplicate product_name,
> product_description and product_id. Is it feasible? And how to config the
> sqlMap or other things to do it ?
> >
> > Please advise
> >
> >
> >
> >
> > ________________________________
> See the all-new, redesigned Yahoo.com. Check it out.
> >
> >
> >
> >
> >
> >
>
>
Re: 1 to N in queryForObject()
Posted by Jeff Butler <je...@gmail.com>.
iBATIS has good support for this. You'll need to use a List instead of an
array, and the "groupBy" keyword on your result map. Look in the
developer's guide for more information.
Jeff Butler
On 7/31/06, Jane Phillips <ja...@yahoo.com> wrote:
>
> Hi, I am new to iBatis. I have a question for one of my query. I have
> tables:
>
> 1. Product
> -- product_id
> -- product_description
> -- product_name
>
> 2. Items
> -- item_id
> -- product_id
> -- item_description
> -- item_name
>
> The relationship is one product can have many items. query I would like to
> do is :
> <sqlMap>
> <select id="getProductItems" resultClass="com.abc.products">
>
> SELECT product_id, product_description,product_name, item_name from
> product a, items b WHERE a.product_id = b.product_id and a.product_id =
> #id#
> </select>
> </sqlMap>
>
> in java, com.abc.products:
>
> String product_name;
> String product_description;
> String product_id;
> String[] item_name;
> and all the getXXX and setXXX methods.
>
> The goal is run this query using queryForObject() to get one product and
> all its items to a products object instead of queryForList() to get
> collection of product object with duplicate product_name,
> product_description and product_id. Is it feasible? And how to config the
> sqlMap or other things to do it ?
>
> Please advise
>
>
>
> ------------------------------
> See the all-new, redesigned Yahoo.com <http://yahoo.com/>. Check it out.<http://us.rd.yahoo.com/evt=40762/*http://www.yahoo.com/preview>
>
>