Hi,

We have a legacy application which works with Postgres database. It has sql queries which use postgres specific functions like date_part and date_trunc in the query select clause, where clause etc

Now the target is to port this application to Oracle and potentially SQL server.
This application uses JDBC and does not use Hibernate.

Now what we plan to do is instead of changing each query or putting conditions for each query we would like to transform/translate that query at runtime. So before execution, the query will be intercepted and transformed to support the target Dialect.

Does Hibernate provide API for SQL query parsing and generate a query model having methods like getWhereClause, getFromClause etc for DML's.

And then is there a possibility to transform that query to Oracle Dialect compliant query.

Regards
Piyush

If you use Hibernate, your application will have a dialect that will take your queries and translate them into the appropriate database SQL at runtime. This is done through the dialect setting in the hibernate configuration file.

From there, you can actually send native SQL through the Hibernate Session if need be.

So does it mean that following query will be translated automatically into an Oracle dialect query at runtime

Example of postgres query
String query = "SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40')"

Possibly converted oracle query
select to_char(to_date( '2001-02-16 20:38:40' ,'YYYY-MM-DD HH:MI:SS'),'DD') from dual

Is this a possibility

If yes than please post some sample code on how to achieve this

Regards
Piyush