Hi all,
I am new to hibernate.
I am working on a web project which is tightly coupled with oracle 10g, and oracle features and utility functions like nvl, to_date, to_char etc are extensively used in the queries.
Some queries are embedded in code, some are written in xmls to show some reports depending on the outputs of queries written in that xmls.

Now i am said to make the project database independent, i.e., i can't use oracle features now, and i have to convert all oracle sql queries to hql (no native sql queries even) that is standard sql complaint.
So how should i approach to design such a database independence layer.
I have a constraint that in certain cases (xml queries) i can't break the sql in multiple queries(can be done in cases of embedded), and even can't use named parameters.
So i think i should write some wrappers that would complement the lack of oracle functions.
One more query,
In some existing queries groupby caluse has been used on to_date(<field-name>) functions. How should i tackle such queries.
I am posting an example query:

select
to_char(dates.dat, 'YYYYIW') as linedate,
to_char(max(dates.dat), 'DD.MM.YYYY') as maxdate,
to_char(min(dates.dat), 'DD.MM.YYYY') as mindate,
nvl(sum(count), 0) as count
from
help_dates dates
left outer join (
select
l3.dateid as dateid,
sum(count) as count
from
help_dates dates
join l3_dateXsiteXobjecttypeXcount l3 on (dates.id = l3.dateid)
join l2_objecttype obt on (l3.objecttypeid = obt.id)
where ((siteid = 100000 and l3.objecttypeid in (select s2ot.objecttypeid from site2objecttypegroup s2ot where s2ot.siteid = l3.siteid and s2ot.objecttypegroupid = (select otg.id from objecttypegroup otg where lower(otg.name) = lower('Page Views')) and s2ot.objecttypeid in (select id from v_l2_objecttype)))) and
dates.dat >= to_date('23.02.2009', 'DD-MM-YYYY') and
dates.dat <= to_date('08.03.2009', 'DD-MM-YYYY') group by
l3.dateid
) l3 on (l3.dateid = dates.id)
where dat >= to_date('23.02.2009', 'DD-MM-YYYY') and
dat < to_date('08.03.2009', 'DD-MM-YYYY')+1
group by to_char(dates.dat, 'YYYYIW') order by linedate desc


Check for the last group by clause(in bold)
Actually such types of group by is required to group the data daily/weekly/monthly wise as per business logic
Please suggest some wrapper design techniques, so that after getting the data from simple hql queries i can apply such complex group by functionalities or nvl etc.

Thanks in advance.
With warm regards,
Debojit

Have you already tried to translate this statement? Because the function "to_char" is registered within Oracle10gDialect, so it could be used in HQL.

For any other functions not being part of the OracleDialect you could subclass your Dialect and call registerFunction in the Constructor so that these functions could be used in hql.

Hi buddy,
Thanks for the reply.
I know that if i will be using oracle 10g dialect only, there is no problem at all, what in case of other databases that don't support such function.
Actually what i want is a uniform api to be put in between queries and hibernate, so that i can take database independence advantage of hibernate as well as i need to minimally change the query structure.

Query(some sort of hql)-->Custom API(some custom parsing)-->Hibernate.
I was actually asking how should i approach the design.
In case of functions that are used on selected columns like nvl etc, i can tackle such conversions in java code, i mean after getting the record using a simple hql query, i can modify each record according to the functions applied on columns of those records.
But what make it tough is application of oracle specific functions in group by clause.
Actually i wanna write hql queriws in a way such that, no matter what the db is the query should run fine.
For example if my query has a to_char function call, and say mysql does not support such function, i dont need to modify the query, only i would change the dialect and the application should run perfectly.

I think you will have to re-write all your native queries into HQL without using any database specific functions. For eg:
Instead of nvl() you can use "case when" stmts
Instead of to_char() and to_date use Date class and SimpleDateFormat etc..