Saturday, March 3, 2012

wm_concat for Oracle

I need to add a style description to a Phone domain.  However Style has a many to many relationship to Phone in Database.  Usually, one would just create the Style Domain and the Mapping in Hibernate to add it as a set in Phone Domain.  But in my case, all I want is a concatenated String of all style names a Phone has. And to do so, I'll have to iterate through the set to join all the style names as a string.  Consider Phone is already heavily packed and we usually query for large list of Phones throughout our application, plus that we only display style on one newly designed page, the easy way will not be an efficient way.

So I tried to google out a better way to do it.  What I gathered most is that for MySql, group_concat can achieve what I want.  Unfortunately, it's not valid for Oracle.  And, there are a lot discussions on Oracle forum on how to achieve the same effort through very complicated queries and I got lost about half way through.

Then I found this little post on wm_concat for Oracle.  On my first tryout, I was able to get it working in my project.

In my hibernate mapping, instead of:
        <set
            name="stylePhoneMap"
            table="style_phone_map"
            batch-size="5"
            lazy="false"
            cascade="none">
            <key column="phone_id"/>
            <one-to-many class="com.vzw.my.company.domain.phone.StylePhoneMap"/>
        </set>
I used:
        <property
            name="style"
            type="java.lang.String"
            formula="(select wm_concat(a.style_name) from style a, style_phone_map b where a.style_id=b.style_id and b.phone_id = phone_id)"
            insert="false"
            update="false"
        />
The result of the formula will return as a CLOB in SQL Navigator.  I would put a length attribute on the property if for fact the result will be a long String.  But for my case, I know for sure I don't need to.  All style names are concatenated as one String with comma as separator.  No extra domain object, no extra Java code to convert the Set to a String.  What a great trick!