Re: iBatis and entity-attribute-value style tables

[Ted Schrader <te...@gmail.com>]

Hi Collin,

If you can do it with straight-up JDBC, you can probably do it with
iBATIS.  I sense you may be looking for some magic in the form of
avoiding N+1 selects.

Based on your example,  perhaps the following two value objects are called for:

UserVO
    Integer id;
    String firstName;
    String lastName;

    // List of UserPropertyVOs
    List userProperties;

UserPropertyVO
   Integer id;
   Integer userId;
   String propertyName;
   String propertyValue;

If you structure your result set properly and specify some result maps
per the examples in the Developers Guide, you can probably get iBATIS
to cough up a List of UserVOs that each contain a List of
UserPropertyVOs.  It would be up to your application to read/eat/loop
the data from there.

Perhaps there are other strategies as well.

Ted


On 27/02/07, Collin Peters <ca...@gmail.com> wrote:
> Does iBatis have any kind of built-in support for
> entity-attribute-value (EAV) style tables?  An EAV table (or key-value
> table) is one where the data is represented in rows instead of in
> columns.   A simple example is here:
> http://www.devshed.com/c/a/MySQL/Database-Design-Using-KeyValue-Tables/
>
> So for example, if you had a users table such as:
> USERS
> ----------
> user_id  integer,
> firstname text,
> lastname text
>
> And you needed to represent properties for each users, you could add a
> few new columns:
> property1  text,
> property2 text,
> etc...
>
> But the problem is that you are locked down by the columns. i.e. for
> each property you add, you need a to add a column to represent that
> property.  EAV would re-structure this by having another table called
> user_properties which would look like:
> user_property_id integer,
> user_id integer,
> property_name text,
> property_value text,
>
> So you gain flexibility at the cost of complexity.  We have a
> situation like this and I am wondering if iBatis can do some kind of
> magic to convert the properties automatically.  Let me know if you
> need a more concrete example to understand this.  Before I spend the
> time to do that I will see if anyone has any comments.
>
> Regards,
> Collin Peters
>