Daniel Rall said:
> Nice writeup, Eric. We need all that, plus an easy and straightfoward
> method of excecuting ad-hoc SQL.
Here is another approach. Since SQL does not map to OO cleanly. Define SQL in
XML and then generate objects to use from java.
Think of it as an extension to torque. The difference in this approach to
torque is that the SQL is hand written and then the objects are generated off
that (instead of off the tables). So you can execute the exact SQL you want.
The following sample demonstrates writing very custom SQL (a stored proc with
paging) along with having multiple conditions that can be used. The column
alias are used to map to object fields. This has the benefit of providing a
loose coupling with the physical database columns. The other advantage to this
approach is that is very easy to test all the SQL in the system.
On the down side the example maps columns based on position and the SQL author
is responsible keeping the SQL portable.
Having used this system it encourages very efficient database access and it
allows significant tuning with very few side effects.
<DataAccess id="DataAccess_OrderTracking" connection="application_db">
<Statement id="list" value = "{ call GET_ORDER_TRACKING_DATA ">
<Column alias="RQN_KEY"/>
<Column alias="DISPLAY_RQN_KEY"/>
<Column alias="OR_DESCRIPTION"/>
<Column alias="LA_KEY"/>
<Column alias="OR_FULLNAME"/>
<Column alias="SPL_NAME"/>
<Column alias="OR_STATUS"/>
<Column alias="RQN_STATUS"/>
<Column alias="UPDATE_DATE"/>
<Column alias="TOTAL_VALUE"/>
<Condition id="list" value = " (?, ?, ?, ?, ?, ?, ?, ?) }">
<Column alias="SELECT_CO_KEY"/>
<Column alias="SELECT_LA_KEY"/>
<Column alias="SPL_KEY"/>
<Column alias="OR_SELECT_STATUS"/>
<Column alias="START_DATE"/>
<Column alias="END_DATE"/>
<Column alias="ORDER_TRACKING_SORT_TYPE"/>
<Column alias="ADMIN_LIMIT"/>
</Condition>
<Condition id="NextSelect" value = " (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?,
?) }">
<Column alias="SELECT_CO_KEY"/>
<Column alias="SELECT_LA_KEY"/>
<Column alias="SPL_KEY"/>
<Column alias="OR_SELECT_STATUS"/>
<Column alias="START_DATE"/>
<Column alias="END_DATE"/>
<Column alias="ORDER_TRACKING_SORT_TYPE"/>
<Column alias="ADMIN_LIMIT"/>
<Column alias="RQN_KEY"/>
<Column alias="OR_FULLNAME"/>
<Column alias="TOTAL_VALUE"/>
<Column alias="UPDATE_DATE"/>
<Column alias="PageType"/>
</Condition>
<Condition type="PrevSelect" value = " (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?,
?) }">
<Column alias="SELECT_CO_KEY"/>
<Column alias="SELECT_LA_KEY"/>
<Column alias="SPL_KEY"/>
<Column alias="OR_SELECT_STATUS"/>
<Column alias="START_DATE"/>
<Column alias="END_DATE"/>
<Column alias="ORDER_TRACKING_SORT_TYPE"/>
<Column alias="ADMIN_LIMIT"/>
<Column alias="RQN_KEY"/>
<Column alias="OR_FULLNAME"/>
<Column alias="TOTAL_VALUE"/>
<Column alias="UPDATE_DATE"/>
<Column alias="PageType"/>
</Condition>
</Statement>
</DataAccess>
-Russell
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
