Database independence through JDBC in java

Question

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?

Answer 1

I think I'm qualified to answer, being the author of jOOQ, which was already suggested in another answer. As I've shown, it's totally possible to achieve what you're trying to do, but there is a long long road ahead for you, if you want to roll your own.

Let's talk about JDBC

JDBC is an excellent network protocol abstraction, so it's a great starting point. There are quite a few caveats though as you move on to solving more complex problems inside of an API like the one you're trying to build. For instance:

• Fetching generated keys is really hard. Few JDBC drivers get this right
• Are you sure you're handling LOBs correctly? Hint: You're not
• What's worse than LOBs? LOBs inside of Oracle OBJECT types
• Have I mentioned Oracle OBJECT types? They can be put inside of arrays (and arrays of arrays. That's when stuff gets really hairy
• But Oracle's OBJECT types are wonderful, compared to PostgreSQL's TYPE types. The JDBC driver doesn't help you at all, there
• Try binding DB2 NULL values. Sometimes it works, sometimes it doesn't.
• Want to support java.sql.Date and java.time.LocalDate? Good luck!
• Speaking of dates, do you know how many different kinds of interpretations of the TIMESTAMP WITH TIME ZONE data type there are?
• Want to support INTERVAL types? Really?
• What if the database throws more than one exception?
• What if the database raises errors through a different API than exceptions (hello SQL Server)
• What if you need to collect warnings prior to fetching exceptions?
• Did you know that some databases first send you an update count, and only then the actual result set (e.g. when triggers fire)
• Have you thought of handling multiple result sets?
• Now combine the above with formal OUT parameters
• Let's talk about the BOOLEAN type
• ... I could go on for hours. More examples on this website
• Did you know that some PostgreSQL statements don't work when autoCommit is set to true?
• Not everyone supports savepoints
• Want to use JDBC DatabaseMetaData to reverse engineer your schema? Forget it!
• Want to use ResultSetMetaData to discover qualified column names? Well...

As you've seen, even if JDBC does its job really well for most people (and there's always a hacky workaround for each of the above that works for an individual database. But you want to write an API that works on all databases, so you have to fix / work around all of the above. Trust me. That'll keep you busy for a while!

Let's talk about SQL

But thus far, we've only discussed how hard it is to bind to JDBC. We haven't discussed how hard it is to standardise SQL. So let's discuss that for a moment:

• LIMIT n OFFSET m is nice, eh? Or is it LIMIT m, n? Or TOP n START AT m? Or OFFSET m ROWS FETCH NEXT n ROWS ONLY? What if you want to support older databases? Will you roll your own ROW_NUMBER() filtering? Here, I've documented it for you.
• Some databases support SELECT without FROM. In other databases, you need something like a DUAL table. There you go, all documented.
• Some databases pretend they don't need that DUAL table, until their parser breaks and you still need it (hello MySQL)
• Some databases support SELECT without FROM, but they do require FROM for WHERE / HAVING / GROUP BY
• What's your take on this: (SELECT 1 UNION SELECT 2) UNION ALL SELECT 3. Will it work on all databases? (I mean the parenthesised nesting)
• Is EXCEPT ALL supported? Is EXCEPT even supported?
• Is FULL OUTER JOIN supported?
• Do derived tables need an alias or can they live without one?
• Is the keyword AS permitted on derived tables?
• Can the ORDER BY clause contain expressions referencing aliases from the SELECT clause? Or only expressions referencing columns from the FROM clause?
• Can the ORDER BY clause contain expressions at all?
• Can derived tables contain an ORDER BY clause?
• Let's talk about functions. Is it call SUBSTRING() or SUBSTR() or INSTR() or what?
• Hint, this is how to emulate the REPEAT() function on SQLite
• How would you emulate the VALUES() constructor, as in SELECT * FROM (VALUES (1), (2)) t(a)? Few databases have native support
• In fact, how would you emulate the derived column list (aliasing table(column) in one go) if it's not supported? Here's a funky idea.
• In fact, let's discuss row value expressions and predicates built with them. This: (a, b) > (x, y) is the same as this: a > x OR a = x AND b > y. The former isn't supported everywhere

• PostgreSQL's UPDATE .. RETURNING can be emulated using a PL/SQL block in Oracle 12c:

  declare
    t0 dbms_sql.number_table;
    t1 dbms_sql.date_table;
    c0 sys_refcursor;
    c1 sys_refcursor;
  begin
    update "TEST"."T_2155"
    set "TEST"."T_2155"."D1" = date '2003-03-03'
    returning 
      "TEST"."T_2155"."ID", 
      "TEST"."T_2155"."D1"
    bulk collect into t0, t1;
    ? := sql%rowcount; // Don't forget to fetch the row count
    open c0 for select * from table(t0);
    open c1 for select * from table(t1);
    ? := c0; // These need to be bound as OracleTypes.CURSOR OUT params
    ? := c1; // These need to be bound as OracleTypes.CURSOR OUT params
  end;
  

Conclusion

As you can see, it can totally be done. I've done it, it's called jOOQ. It's probably been the biggest challenge of my professional life and it has been fun. jOOQ 3.10 will feature a parser, which can translate from a SQL string (in any dialect) to another SQL string (in a specific dialect), which is the next level of vendor agnosticity.

But it was a long way to go to get here. Before I did jOOQ (started in 2009), I've worked with Oracle SQL and in-house JDBC-based frameworks (like the one you're planning to write) intensively. I wrote jOOQ because I've seen many in-house frameworks being written and none of them did the job well. The developers always tackled SELECT .. FROM .. WHERE - which is the easy part. Some managed to get JOIN in the game, and perhaps GROUP BY and that's it. They then abandoned the task, because they had more important stuff to do than maintain boring and buggy infrastructure software.

Now, I don't know what your motivation is to do this yourself, but my advice here is:

• Use jOOQ if you want to write vendor-agnostic SQL
• Use Hibernate if you want to implement vendor-agnostic object-graph persistence

You can try building your own jOOQ (or Hibernate). It's a fun challenge. But if you have deadlines, I really suggest you review the above options.

Answer 2

Firstly, database independence is hard. Really, really hard; to achieve it without using an ORM or other tool, you will have to trade some other aspect of your solution design. Simplicity and maintainability will suffer, as will the effort to implement the solution.

So, I'd make really, really sure that this is a high priority requirement - a hypothetical "what if we want to switch from Oracle to SQL Server" question is - in my opinion - not sufficient justification to incur the additional costs...

If you must deliver this feature, and ORM is by far the easiest way to do it. The ORM frameworks are specifically designed with database independence in mind (and their complexity is at least in part due to that requirement). They do so by abstracting away the database implementation into a higher level; instead of thinking in SQL statements, you're encouraged to think about domain objects.

Having said all that...

I have delivered a solution (not in Java, but the principle stands) which allowed database independence. We stored our SQL statements as resources, and loaded them via resource files. The default was ANSI SQL, which should work on any modern SQL-compatible database.

We had resource files for each database flavour we supported (in our case MySQL and Oracle), and used overrides to load in database-specific SQL statement if they existed.

This works like internationalization in most languages - look for locale-specific strings first, fall back to default if you can't find one.

Java's resource bundle would make this pretty easy. Not hardcoding your SQL in your application has other benefits - fixing a database problem without changing the application code is much easier, you can deploy fixes as "resource" updates rather than shipping a new binary etc.

Answer 3

There are two ways to address database portability:

1. Subtracting non-common features - You can remove all database-specific features, which will leave you with a SQL-92 feature set. This is what ORM tools do because the common CRUD statements are handled almost identically by all RDBMS. However, when you need to execute database-specific queries, you turn to native SQL.
2. Specialization - You can use database-specific dialects and emulate functionality from one DB to another, like PIVOT. This is the approach used by jOOQ.

However, since you explicitly stated that:

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

You need to solve this problem using JDBC API, right?

The only way to do it, without taking neither the ORM or jOOQ approach which practically means implementing your own data access framework, is to use a custom DAO for each supported database.

So, you can define the DAO interfaces that are used by the Service layer, like ProductDAO and implement each and one for every supported database:

• OracleProductDAOImpl
• MySQLProductDAOImpl
• PostgreSQLProductDAOImpl
• SQLServerProductDAOImpl

Or, you can use a single ProductDAOImpl, but then you need to use stored procedures and make sure each stored procedure is implemented in the database.

So, although it's possible, you are basically where we were in the early 2000s when we didn't have either Hibernate or jOOQ. From my experience, it's way more work than if you used a mature data access framework.

Answer 4

It seems to me that what you're actually trying to build there by creating a library that allows you to generically access a database is quite similar to what many of the ORMs already do.

Have you tried jOOQ? it's quite different to the other ORMs out there and does what you're looking for. One could refer to it as a "tool" but then I could also refer to what you're trying to build as a "tool".

jOOQ strives to be a java native language for making portable DB calls so sounds like it's exactly what you're looking for.

https://www.jooq.org/

Answer 5

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…