[Opensource] DBObjects limitations

[inadareishvili at worldbank.org]

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.

2. Setting Complex WHERE conditions
Complex WHERE condition needs to be set as
    custList.setCustomWhereClause("CustomerType = \"AA"\ OR CustomerType = \"BA"
\");
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 changes to the database/tables, will have impact on both DB
Object and Business Component JAVA classes.
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.

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). )
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

Thanks
Irakli