Database independence through JDBC in java

user_2194017 0
Viewed 2846

There are some limitations of using JDBC and database dependency is one of those limitations.

Is there any pattern or way to achieve database independence in JDBC (without using any other ORM framework or tool).

I have tried to achieve this through dynamic polymorphism (created specific classes for different DBMS and overridden common CRUD operations according to specific SQL syntax).

For example, is there a way to write generic SQL statements so that they can be executed in almost every SQL related DBMS?

jdbc sql java
5 Answers

While (very) late to the party and not bringing anything new really, I'd like to endorse a bit and expand on Neville's and Vlad's answers (as a plausible option).

Frameworks like jOOQ and Hibernate are, probably, the better way to go, especially IF what you're trying to obtain is something completely generic (i.e. handle most imaginable DB operations) as opposed to a handful of specific tasks involving SQL. However, since it seems they're not an option for you, externalizing SQL in one way or another could actually take you quite far (from an SQL dialect point of view, not necessarily from the JDBC binding issues so nicely outlined in Lukas' answer before).

I've previously worked on a custom Java-based ETL engine (successfully) running on both Oracle and DB2. As you can imagine, we needed the best queries we could get in terms of speed and our DBAs were optimizing them using every trick in the book and then some! So, not only had we 2 SQL dialects to accommodate, but the queries we had to execute were also highly customized (hints etc.)... The solution we settled upon was to generate the SQLs at runtime, using a template engine (Velocity, at the time) and custom templates, tailored for the target DB (probably similar to the way Hibernate or jOOQ build they final SQL). Admittedly, we had our own "context" with specific and well defined needs…

user_2729390
Related