sql - 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.