Database independence through JDBC in java

by

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.

More Related Contents:

  1. Hours and seconds are lost when storing a date in the database [closed]
  2. Simulate Result set closing failed [closed]
  3. How to get the insert ID in JDBC?
  4. Multiple queries executed in java in single statement
  5. java.util.Date vs java.sql.Date
  6. Using setDate in PreparedStatement
  7. Efficient way to do batch INSERTS with JDBC
  8. How to execute IN() SQL queries with Spring’s JDBCTemplate effectively?
  9. How can I get the SQL of a PreparedStatement?
  10. How does a PreparedStatement avoid or prevent SQL injection?
  11. right syntax to use near ‘?’
  12. Running a .sql script using MySQL with JDBC
  13. java.sql.SQLException: Exhausted Resultset
  14. JDBC Pagination
  15. How to enable logging for SQL statements when using JDBC
  16. Getting output from dbms_output.get_lines using JDBC
  17. org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
  18. handling DATETIME values 0000-00-00 00:00:00 in JDBC
  19. JDBC ResultSet get columns with table alias
  20. Java – Can’t use ResultSet after connection close
  21. Timezones in SQL DATE vs java.sql.Date
  22. Queries returning multiple result sets
  23. SQL prepared statement how to select via multiple possible menu selections?
  24. Getting an exception ORA-00942: table or view does not exist – when inserting into an existing table
  25. Class.forName(JDBC_DRIVER) no longer needed?
  26. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  27. Java – Storing SQL statements in an external file [closed]
  28. A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
  29. Java JDBC connection status
  30. MyBatis executing multiple sql statements in one go, is that possible?

Leave a Comment