Type.java
public class Type
{
private Long type_id;
private String type;
//getters and setters
}
Item.java
{
private Long item_id;
private String title;
private String description;
private String imageLoc;
private List<Type> types;
//getters and setters
}
DB Tables
item
----------
item_id
title
description
image_loc
type
---------
type_id
type
item_type
-------------
item_id
type_id
Now the data in item_type might look something like
1 - 1
1 - 2
1 - 3
So item_id 1 would have types 1,2,and 3.
I can populate Item and I can populate Type. What I can't seem to figure out is how to get the List of types populated for my Item object.
Thanks for any suggestions.
Gregg
On 1/18/06, Albert L. Sapp <[EMAIL PROTECTED]> wrote:
Gregg D Bolinger wrote:
> You are correct. There is no need for that column in the item table.
> That relation exists in the items_types table. My mistake. The
> problem still remains though.
>
> Gregg
>
> On 1/18/06, *Albert L. Sapp* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
> wrote:
>
> Gregg D Bolinger wrote:
>
> > I've search the archives and have found a few similar issues but
> > nothing conclusive to what I am doing. I can only assume no one
> else
> > has this problem because they know what they are doing and I don't
> > (most likely) or no one is simply doing things the way I am doing
> > them. At any rate...
> >
> > I have a table called items and this table contains a fk to a
> type_id
> > from a table called type. I then have a 3rd table called
> items_types
> > which contains an item_id and a type_id.
> >
> > Got that? So in my Item class I will need to get a List<Type> of
> > types. I know how to do the n+1 solutions but by adding in this
> > third table I am at a loss on how to map it. If anyone could
> point me
> > into the right direction, I would appreciate it. A link to an
> > archived question, a page in the user docs, etc.
> >
> > Thanks a bunch.
> >
> > Gregg
>
> Gregg,
>
> Is there a reason why you need the type_id as a foreign key in the
> item
> table? You have a relationship defined between and iten and its
> type in
> the third table. If you are worried about uniqueness, simply
> define the
> combinations in the third table as needing to be unique. We do a
> similar thing in many of the modules in our application. It requires
> additional reads to get all the information together and this may
> not be
> the best design, but it works for us.
>
> This probably did not directly answer your question, just curious
> as to
> why you had that foreign key defined.
>
> Respects,
>
> Al
>
>
Ok, let me see if I understand what you want to do. You want to get all
the items that are of a certain type, correct? The solution in the
documentation is to use a join in the select and map all the result
fields together.
Say we want the following:
<resultMap id="get-item-result" class="whatever">
<result property="item.id" column=" ITEMS.ITEM_ID"/>
<result property="item.description" column="ITEMS.DESCRIPTION"/>
<result property="type.id" column="TYPES.TYPE_ID "/>
<result property="type.description" column="TYPES.DESCRIPTION"/>
</resultMap>
and we have this select:
<statement id="getItemOfType" parameterClass="int"
resultMap="get-item-result">
select * from ITEMS, ITEM_TYPES, TYPES
where ITEMS.ITEM_ID=ITEM_TYPES.ITEM_ID
and ITEM_TYPES.TYPE_ID=TYPES.TYPE_ID
and TYPES.TYPE_ID=#value#
</statement>
Would this work? I have never done any joins before as I am still
learning things as I go along. Can you do more than one join in a
select? I don't know if I have the idea right or not. If I do, I may
rethink some of my own queries.
Hope I am not totally off base on this.
