How to map tables where additional columns can be added?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

How to map tables where additional columns can be added?

Jason
I am working with a database where columns can be added to any table
by the users.  Is there any approach I can take and map out these
additional/unknown columns to my Java objects?
Reply | Threaded
Open this post in threaded view
|

RE: How to map tables where additional columns can be added?

Poitras Christian
I don't think it's possible to add properties to a Java class at runtime unless you use Proxies. And even then, it will be almost impossible for your applications to guess such changes.
What you can do is to put all new columns inside a Map property and use the Map.keySet() method to get the new columns.

Christian

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de Jason
Envoyé : October-06-10 8:19 AM
À : mybatis-user
Objet : How to map tables where additional columns can be added?

I am working with a database where columns can be added to any table
by the users.  Is there any approach I can take and map out these
additional/unknown columns to my Java objects?
Reply | Threaded
Open this post in threaded view
|

Re: How to map tables where additional columns can be added?

Jason
Thanks for the reply.  Do you have a code example that I can
reference?

On Oct 6, 8:56 am, Poitras Christian <[hidden email]>
wrote:

> I don't think it's possible to add properties to a Java class at runtime unless you use Proxies. And even then, it will be almost impossible for your applications to guess such changes.
> What you can do is to put all new columns inside a Map property and use the Map.keySet() method to get the new columns.
>
> Christian
>
> -----Message d'origine-----
> De : [hidden email] [mailto:[hidden email]] De la part de Jason
> Envoyé : October-06-10 8:19 AM
> À : mybatis-user
> Objet : How to map tables where additional columns can be added?
>
> I am working with a database where columns can be added to any table
> by the users.  Is there any approach I can take and map out these
> additional/unknown columns to my Java objects?
Reply | Threaded
Open this post in threaded view
|

Re: How to map tables where additional columns can be added?

Andrius Juozapaitis
Hey,

I was facing a similar issue, with the added complexity that those
might not only be simple properties, but also lists, maps, structures,
or hierarchical structures or lists of hierarchical structures.
Initially, I came up with a metamodel driven architecture, that used
multiple tables for storing those entities, flattened to a list of
interconnected containers and simple property attributes.

Basically, this looked something like:

[DomainObject]<>----1:n---[Attribute], which essentially translated into

class DomainObject {
     List<Attribute> attrs;
}

Then, I would map the data in the tables to that object or a list of
objects by doing a join with the attribute values table, and use a
sophisticated resultMap with collections, associations and
discriminators to map the joined result to my domain objects. This had
a nice side efffect that I could do paging and sorting of the entries
using standard sql, without loading huge datasets into memory, and I
could search objects by the attribute values, depending on the
metamodel.

The relevant mapper xml code looked something like this:
..... skipped
    <resultMap id="customerResult" type="Customer">
        <id property="id" column="customer_id"/>
        <result property="name" column="name"/>
        <result property="birthday" column="birthday"/>

        <collection property="attributes" ofType="Attribute"
javaType="ArrayList" column="attr_type_id"

resultMap="com.ajaxelements.server.dao.AttributeMapper.attrResult">
            <id property="id" column="attr_id"/>
            <association property="type" column="attr_type_id"

select="com.ajaxelements.server.dao.AttributeMapper.selectAttrTypeById"
                         javaType="AttributeType"/>
        </collection>
    </resultMap>

    <select id="selectWithPaging" resultMap="customerResult">
    select c.*, a.* from
    (select distinct c.customer_id as idas from customers c
    order by c.customer_id
    limit #{maxEntries} offset #{startFrom}) as aaa,
     customers c
    left join attributes a on a.customer_id = c.customer_id
     left join attribute_types av on a.value_attr_type_ref_id = av.attr_type_id
     left join attribute_types at on a.attr_type_id = at.attr_type_id
    where aaa.idas=c.customer_id
    </select>
..... skipped

While this solution satisfied my requirements, it proved somewhat slow
and unscalable, when the number of customers got into hundreds of
thousands, and the number of attributes into millions.

I then opted for another solution. While the structure of the domain
object remained more or less the same, the Attribute structure was
changed to use generics:

public class Attribute<T extends Serializable> implements Serializable {
    long attributeId;
    int attributeTypeId;
    T value;
    // parentAttributeId == 0 if it is a top level attribute
    long parentAttributeId;
    public Attribute() {
    }

    public Attribute(T value) {             this.value = value;
    }

    public T getValue() {
        return value;
    }

    public void setValue(T value) {
        this.value = value;
    }
}


To retrieve that from db, I opted for a simple type handler:
----------------

    <resultMap id="productMap" type="Product">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="description" property="description"/>
        <result column="price" property="price"/>
        <result column="create_date" property="createDate"/>
        <result column="status" property="status"/>
        <result column="type" property="type"/>
        <result column="entity_type" property="entityType"/>
        <result column="attributes" property="container"
typeHandler="com.ajaxelements.server.ibatis.type.AttributeContainerTypeHandler"/>
        <collection property="categories" column="id"

select="com.ajaxelements.server.dao.CategoryMapper.selectProductCategories"/>
        <!--<collection property="tags" column="id"-->
        <!--select="com.ajaxelements.server.dao.TagMapper.selectProductTags"/>-->

        <discriminator javaType="string" column="type">
            <case value="BUNDLE" resultMap="bundleMap"/>
        </discriminator>

    </resultMap>

    <resultMap id="bundleMap" type="Product">
        <collection property="bundle" column="id"
select="selectBundledProducts"/>
    </resultMap>

..... skipped

The type handler:
-------------------
public class AttributeContainerTypeHandler extends BaseTypeHandler {
    Logger logger =
LoggerFactory.getLogger(AttributeContainerTypeHandler.class);
    XStream streamer;

    public AttributeContainerTypeHandler() {
        streamer = new XStream();
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
Object parameter, JdbcType jdbcType) throws SQLException {
        AttributeContainer container = (AttributeContainer) parameter;
        try {
            if (parameter != null) {
                ps.setString(i, streamer.toXML(container));
            } else {
                ps.setString(i, null);
            }
        } catch (Exception e) {
            logger.error("Failed to convert AttributeContainer to XML
string", e);
        }
    }

    @Override
    public Object getNullableResult(ResultSet rs, String columnName)
throws SQLException {
        AttributeContainer container = null;
        String s = rs.getString(columnName);
        if (s != null)
            container = (AttributeContainer) streamer.fromXML(s);
        return container;
    }

    @Override
    public Object getNullableResult(CallableStatement cs, int
columnIndex) throws SQLException {

        AttributeContainer container = new AttributeContainer();
        String s = cs.getString(columnIndex);
        if (s != null)
            container = (AttributeContainer) streamer.fromXML(s);
        return container;
    }


}

So essentially it serializes the data from and to xml using XStream.

This had a drawback, that searching the xml-based field using RDBMS is
a bit awkward. Luckily, I had some experience with Apache Lucene,
which made it trivial to index the objects upon the startup of the
webapp, and a simple AspectJ aspect was taking care of synchronizing
the state of the Lucene indexes with the insert/update/delete
operations performed by mybatis. So now I simply use Lucene index for
searching/paging/sorting through my data, then simply select the
relevant objects by id, and the XStream typehandler converts the
attributes into a convenient and easy to navigate structure.
This approach works really well with gwt/smartgwt serialization
mechanisms- no need to maintain additional DTOs, the search with
Lucene is a blast, and for the most part, you can even generate GUIs
automatically from the meta-model. This works best for the mostly-read
scenarios - i.e. on repeated requests, lucene index lookup + database
query through mybatis take 1-5ms for the complete request on my laptop
(that's when it hits mybatis cache though), so your mileage may vary.


I might also opensource mybatis related parts of my ecom application
as separate maven modules, in case anyone would be interest in them.

regards,
Andrius




On Wed, Oct 6, 2010 at 7:46 PM, Jason <[hidden email]> wrote:

> Thanks for the reply.  Do you have a code example that I can
> reference?
>
> On Oct 6, 8:56 am, Poitras Christian <[hidden email]>
> wrote:
>> I don't think it's possible to add properties to a Java class at runtime unless you use Proxies. And even then, it will be almost impossible for your applications to guess such changes.
>> What you can do is to put all new columns inside a Map property and use the Map.keySet() method to get the new columns.
>>
>> Christian
>>
>> -----Message d'origine-----
>> De : [hidden email] [mailto:[hidden email]] De la part de Jason
>> Envoyé : October-06-10 8:19 AM
>> À : mybatis-user
>> Objet : How to map tables where additional columns can be added?
>>
>> I am working with a database where columns can be added to any table
>> by the users.  Is there any approach I can take and map out these
>> additional/unknown columns to my Java objects?