O/R mapper for complex native SQL

vitalir · **Joined:** Tue May 24, 2005 11:25 am **Posts:** 16

Hi all!

For legacy systems with tons of complex SQL queries there can be a better option. It is an open-source persistency engine I've been crafting for myself for a few years. I think it would very useful for legacy systems that inherited tons of huge SQL queries from there past. Successfully used it in 2 commercial projects already. It is built around SQL instead of introducing a new OQL, features simple API (you deal with 2 classes only), it treats joins and native SQL statements of any complexity and is very well documented.

Best regards,
Vitaly

jd001982 · **Posted:** Sat Jun 10, 2006 5:35 am

Roughly scanned your web site and have a question -- with mappings like the following:

Code:

<class name="LineItemJoinTradeableJoinProduct">
    <statement name="default">
        <from>
            <![CDATA[
            from
                LineItem, Tradeable, Product, VatRate, SpikeControl,
                SupplierItem,
                BuyingCycle startBuyingCycle,
                BuyingCycle endBuyingCycle
            ]]>
        </from>
        <where>
            <![CDATA[
            where
                LineItem.tradeableId = Tradeable.id AND
                Tradeable.productId = Product.id AND
                Product.vatCode = VatRate.code AND
                LineItem.id = SpikeControl.lineItemId AND
                SpikeControl.buyingCycleId = :cycleId AND
                LineItem.prefSupplierItemId = SupplierItem.id AND
                -- limit by cycle
                startBuyingCycle.id = LineItem.liveCycleId AND
                endBuyingCycle.id (+) = LineItem.deadCycleId AND
                trunc(startBuyingCycle.intendedStartDate) <=
                trunc(:cycleDate) AND
                ( (trunc(:cycleDate) <
                  trunc(endBuyingCycle.intendedStartDate)) OR
                  (LineItem.deadCycleId is null) )
            ]]>
        </where>
    </statement>.....

aren't you just writing SQL statements in XML format? Why not just write your data access code using plain JDBC?

damnhandy · **Posted:** Sat Jun 10, 2006 12:41 pm

While I 100% agree that custom SQL can sometimes out perform Hibernate generated SQL, I question the need for another framework since Hibernate enables you to do the exact same thing today. Lychee also seems to be missing some important things like caching. In particular, something akin to Hibernates Session-level cache. So with Lychee, you'll be hitting the database everytime you need to access and entity.

Ryan-

vitalir · **Joined:** Tue May 24, 2005 11:25 am **Posts:** 16

jd001982 wrote:

Roughly scanned your web site and have a question -- with mappings like the following:
...
aren't you just writing SQL statements in XML format? Why not just write your data access code using plain JDBC?

Plain JDBC is unsafe (always possible to miss leaks) and awkward. Wrapping JDBC with lychee is

1) Safer. No cursor leaks since all JDBC queries executed by a single class throughout the whole system. No statement leaks since all JDBC queries executed by a single class throughout the whole system. No column references by number, compile-time checking instead.
1) Simpler.
2) Less code (=> cleaner code => better code). Code is at least half as much as the JDBC variant.

But what's most important - it let's you be flexible about how you think about things. Having this join class defined you can now think of it as of an 'entity' and define relationships to other entities within it:

Code:

<class name="LineItemJoinTradeableJoinProduct">
    <statement name="default">
        <from>
            <![CDATA[
            from 
                LineItem, Tradeable, Product, VatRate, SpikeControl,
                SupplierItem, 
                BuyingCycle startBuyingCycle, 
                BuyingCycle endBuyingCycle
            ]]>
        </from>
        <where>
            <![CDATA[
            where
                LineItem.tradeableId = Tradeable.id AND
                Tradeable.productId = Product.id AND
                Product.vatCode = VatRate.code AND
                LineItem.id = SpikeControl.lineItemId AND
                SpikeControl.buyingCycleId = :cycleId AND
                LineItem.prefSupplierItemId = SupplierItem.id AND 
                -- limit by cycle
                startBuyingCycle.id = LineItem.liveCycleId AND
                endBuyingCycle.id (+) = LineItem.deadCycleId AND
                trunc(startBuyingCycle.intendedStartDate) <= 
                trunc(:cycleDate) AND 
                ( (trunc(:cycleDate) < 
                  trunc(endBuyingCycle.intendedStartDate)) OR 
                  (LineItem.deadCycleId is null) )
            ]]>
        </where>
    </statement>
    <field name="id" column="LineItem.id" type="Long" isId="true"/>
    <field name="price" column="LineItem.price" type="BigDecimal"/>
    <field name="rrp" column="LineItem.rrp" type="BigDecimal"/>
<field name="isVisible" column="LineItem.visible" 
    type="boolean_Y_N"/>
    <field name="sku" column="Tradeable.sku" type="String"/>
<field name="tradeableEanCode" column="Tradeable.eanCode"       
    type="String"/>
<field name="productTypeId" column="Product.productTypeId" 
    type="Long"/>
    <field name="name" column="Product.name" type="String"/>
    <field name="itemSize" column="Product.itemSize" type="String"/>
    <field name="vatRate" column="VatRate.rate" type="BigDecimal"/>
    <field name="vatCode" column="VatRate.code" type="Character"/>
<field name="isUnderSpike" column="SpikeControl.underSpike" 
    type="boolean_Y_N"/>
<field name="forecastQuantity" 
    column="SpikeControl.forecastQuantity" type="Long"/>
<relation name="productType"   type="one_child" 
    other-class="ProductType" this-field="productTypeId" other-field="id"/>
<relation name="vatRate"       type="one_child"    
    other-class="VatRate"      this-field="vatCode"       other-field="code"/>
<relation name="preferredSupplierItem" type="one_child"     
    other-class="SupplierItem" this-field="preferredSupplierItemId" other-field="id"/>
<relation name="categories" type="many_via_link" 
    other-class="Category"     this-field="id" other-field="id" 
    link-class="LineItemCategory" link-this-field="lineItemId" link-other-field="categoryId"/>
</class>

So you've got the same tool to use both for 'traditional' O/R mapping (Lychee got relationships mechanism for that, you can traverse object graph (database) as well) and for unrestricted native SQL.

Apparently the mechanism allows to vary the fields at run-time, this is a minor advantage though:

Code:

mapper.loadAll("*", LineItemJoinTradeableJoinProduct.class)
mapper.loadAll("id, price, rrp", LineItemJoinTradeableJoinProduct.class)

Now imagine you come to a project with loads (~1000) queries like this and even more complex. What would you do? JDBC - nah, no good (explained earlier), Hibernate - won't do it. Lychee will do it safely and tidily.

Note that you can define many statements within one class.

All of these features form a very flexible base which combines the power of joins and native SQL with traditional O/R mapper features like relationships traversing.

Regards,
Vitaly

vitalir · **Joined:** Tue May 24, 2005 11:25 am **Posts:** 16

damnhandy wrote:

While I 100% agree that custom SQL can sometimes out perform Hibernate generated SQL, I question the need for another framework since Hibernate enables you to do the exact same thing today.

Not really so. Hibernate cannot execute SQL statements as above not even saying about slightly more complex ones. Now imagine two cases:

1) you need to build a cache for an online shop every morning (and at server startup) with catalogues tailored for each customer. You need to load massive amounts of data for that. Hibernate will do it in 40 minutes. Lychee with joins and native SQL support will do it in 10 minutes still avoiding the hassle of plain JDBC (plus allowing you to think of join classes as of entities - please refer to my post above).

2) you come to a project with loads of complex SQL queries. What would you do? JDBC? - nah (explianed in my post above). Hibernate? - cannot cope with it.

damnhandy wrote:

Lychee also seems to be missing some important things like caching. In particular, something akin to Hibernates Session-level cache. So with Lychee, you'll be hitting the database everytime you need to access and entity.
Ryan-

True. This is something I intentionally wanted to avoid. Because I don’t believe in blind object caching. I prefer building cache explicitly and only when and where the system needs it. So I wanted the tool to operate directly on the database, without any implicit caching and without the need for a developer to cope with its session attachment matters.

Regards,
Vitaly

vitalir · **Joined:** Tue May 24, 2005 11:25 am **Posts:** 16

max · **Posted:** Mon Jun 12, 2006 5:26 pm

Hi Vitalir,

I just need to put something straight on your assumptions that seem to be wrong (at least in context of Hibernate 3.x and especially Hibernate 3.2 release)

#1 Hibernate promotes unit-of-work a context, but it does not *require* it. Look at StatelessSession

#2 Native sql does not require {}'s

#3 Maybe i'm just not seeing your point regards what you can't load with native-sql....native-sql supports the same loading mechanism as you showed....

#4 I haven't remember seeing any jira requests or enhancement regarding any documentation or functionallity about this from you - so I don't have much else to say to your comments about the "docs/featured being missing or lying"

vitalir · **Joined:** Tue May 24, 2005 11:25 am **Posts:** 16

I was doing assesment of Hibernate applicability to our system about 1+ year ago. I remember some 3.x.x development version just went out. Times simpler SQL than shown above did NOT execute, not even saying about more complex ones. And our system got ~1000 of such. HQL was not an option. I was not satisfied working with Hibernate at all. One point I recall documentation was lying about: it clearly put a phrase in two places in docs that execution of an HQL query goes straightly in the database where it did NOT! It went through 1st level cache. Well we are software engineers here, accustomed to formal language. If you put such phrases it should work this way, if it does not - this is lie. Technical documents that allow such things are not technical to me anymore.

I remember forum was screaming with problems people should not have had at all. They were trying simple things! O/R mapping is not a complex issue of computer scinece. It is not a real-time operating system of a military aircraft. It exposes just very ordinary functionality! Very ordinary. O/R mapping should not be as complex as Hibernate perverts it. Forum is still screaming. Why? Because it is not clear for developers how to achieve the simple things they need with Hibernate.

I would not enjoy being a developer dealing with Hibernate simply because the authors did not put enough thought in the design. It is just not pleasant using Hibernate I mean. You know there is software you like dealing with - well designed, thoroughly thought out, very well documented - Oracle Server is a good example - and the awkward ones - for example the one I dealt in the past - Documentum. Urrrr dreadful stuff - leaky abstractions, not just leaky - falling apart, misfiring cache, bugs here and there, crappy docs - all that forms dreadful developer's experience. Developers don't get satisfaction from programming anymore and that's no good. I am not saying Hibernate is that bad, I am saying experince with it is still unpleasant.

Are you saying latest Hibernate can execute SQL similar to the one shown above? And that you've got rid of '{}' syntax?

max · **Posted:** Mon Jul 24, 2006 7:03 am

just to make it clear #1-#4 is also the answer to your last question.