[Opensource] DBObjects limitations

[Peter A. Pilgrim]

inadareishvili at worldbank.org wrote:
> I am pretty new to Expresso framework, trying to port from a system that was not
> using persistence layer, and had  several problems with using DBObjects. Not
> sure if its DBObjects problem or me being a newbie.
> Could somebody help, please?
> 
> 1. Setting up UPPER,LOWER, UNIQUE, DECODE
> We were not able to use SQL function like UPPER,LOWER, UNIQUE, DECODE etc.
> 
There is no support for this in Expresso DBObjects.
I think you would be hard pressed to find this support in a generalised OR/M.
EJB-QL 2.0 looks like the closest. OJB possibility but not generally.


> 2. Setting Complex WHERE conditions
> Complex WHERE condition needs to be set as
>     custList.setCustomWhereClause("CustomerType = \"AA"\ OR CustomerType = \"BA"
> \");

But does this not work as you wrote it?

> As you can see, we need to generate the WHERE condition with column names and
> other details. This means, the SQL statements with specific COLUMN_NAME will be
> present in JAVA classes. (Note: These are Business Component java classes and
> not DB objects). Any find changes to the database/tables, will have impact on both DB
> Object and Business Component JAVA classes.

I am not following your argument.

> For Example, we will have to build the SQL WHERE condition as follows inside our
> search
>        s.sco_id = a.sco_id
>  and s.sco_id = l.sco_id
>  and l.country_iso3 in ('AFG')
>  and a.start_year >= '1995'
>  and a.start_year <= '1996'
>  and s.sco_id = sec.sco_id
>  and s.org_harmonized_char in
> ('UNRF','UNRI','UNRW','UNSO','UNTA','UNUE','UNUN','UNVL','UPUN','UTRA',
> 'WFPR','WHOR','WIPO','WMOR','WTOR','UNIT','UAID','UCAP','UNAI','UNAS','UNCC','UNCD',
> 'UNCH','UNCR','UNCT','UNDC','UNDE','UNDM','UNDP','UNEP','UNES','UNFD','UNFF','UNFP','UNFS',
> 'UNHC','UNHQ','UNHR','UNIA','UNIC','UNID','UNIF','PAHO','ITUN','ECAF','ECEU','CLAD','FAOR',
> 'ESCA','ESCW','IAEA','ICAO','IFAD','ILOR','IMOR','IOCO','ITCE')
> 
> Any change to the tables, we need to change the JAVA classes to reflect the
> same.

There is DBCreate command line program that can generate Java skeleton classes.
These are raw skeleton classes, so any changes you make are deleted on a
regeneration.

Since your SQL looks pretty complicated, some times it may be better to
use raw JDBC than trying a square peg into a circle. (The 80/20% rule)
This would be equivalent to writing BMP rather CMP (EJB-QL) for
optimisation and performance. Writing it raw JDBC saves you time
and energy from having to debug the framework as well as very
complex DBOBject. True if DBObjects simply do not fit the design
and what you are trying to do with them.

> 3. Virtual table selects / INNER SELECT statements
> We use inner SELECT statements to get the specified range of records. This is
> not possible in DB Objects as the end result is coming from a virtual table.
> (Note : DB Object only offers a mechanism to limit the number of records
> retrieved (TOPN). )

Can you explain virtual table select? Do you actually mean the `AutoDB'
object? (BTW: Does anyone out there use
`com.jcorporate.expreso.core.dbobj.AutoDBObject' ?)

/**
  * This class provides convenience methods by querying the database table it is
  * set to and automatically setting up it's own fields that way.
  * Creation date: (10/3/00 11:07:02 AM)
  * @author Michael Nash
  */

> This means if we need to display records from 401 to 450, then we need to take
> the records till 450 and ignore the records from 1-400. In the existing
> implementation, we eliminate the 1-400 records in the database itself. Serious
> performance issue

This issue is called `Finite Rowset Optimisation'. In MYSQL

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM BIGBANK.EMPLOYEE
LIMIT  401, 450
~~~~~~~~~~~~~~~

I patched DBObject.java on this issue 18 months ago or so.

-- 
Peter Pilgrim
ServerSide Java Specialist

My on-line resume and for interview videos about myself, J2EE
Open Source, Struts and Expresso.
    ||
    \\===>  `` http://www.xenonsoft.demon.co.uk/no-it-striker.html ''