CODEWITHSUNDEEP
javamysqlsqldatabaseoracle-database
giocarmine
giocarmine

Reputation: 580

Best Practices to support MySQL and Oracle

I have a Java application that has to support both MySQL and Oracle DBMS.
It was originally written for MySQL and for various reasons I don't use frameworks like Hibernate and such, so the queries are written in "pure" SQL stored in configuration files.

What I've seen so far is that there are some tools for automatic conversion from MySQL to Oracle and vice-versa, like Razor-SQL.
Using that tool helped me, but now I have to maintain 2 different files for the queries, table DDL etc.. and it's a nightmare!

In order to avoid changing the existing queries too much, I wrote some PL/SQL functions to emulate the MySQL functions that I use most, like UNIX_TIMESTAMP, FROM_UNIXTIME etc..

What I would like to ask is if someone has some advice or has a better approach for handling such problems.
Are there some best-practices to follow?

Thank you in advance

Upvotes: 2

Views: 123

Answers (2)

Brett Okken
Brett Okken

Reputation: 6306

I am not familiar with the differences between oracle and mySQL, but I have done effectively the same thing with oracle and derby.

For many of the differences (such as getting values from sequences, windowing queries, etc), I defined an api in java describing the functionality and provided different implementations based on the specific connection type to either do the work (such as getting sequence values) or assemble the sql pieces correctly (such as windowing queries). This has covered most of the differences.

In other cases, where the same concept is not supported in both (such as tuple in clauses), I end up managing separate sql.

Upvotes: 0

Chris K
Chris K

Reputation: 11927

I've used templating in the past. Think one SQL statement, but with a template if/else in it to support different DBs.

eg

select a,b,c
from T
where
   id = ?
#if ORACLE
   and oracleFunc(d,c)
#else
   and MYSQLFunc(d,c)
#end
order by b

Upvotes: 2

Related Questions