Cross-vendor BYTEA / BLOB fetch

This question boils down to "How do I work around boneheaded / outright buggy JDBC implementations" and the answer should be obvious: Custom-code every corner case you find.

In basis there is no meaningful difference between:

doThingInDbAgnosticWay();

and

switch (dbType) {
case POSTGRES -> doInPostgresSpecificWay();
case ORACLE -> doInOracleSpecificWay();
case DbType u -> throw new UnsupportedOperationException("DB engine not supported: " + u);
}

Except for a few niggles. Your question appears to be "I do not want to do the second thing, how do I do the first thing", which makes it sound like you would strongly prefer those niggles to go away. The niggles are presumably:

• I want to be compatible with other DB engines I haven't even tested with yet if possible.
• I find it annoying to have to write db-specific code; I worry that my code is not 'highest standards' java. Also, db-specific code means if blocks/switches and thus, usually, a lot of code.
• I want to be compatible with future changes. Asking the JDBC driver to do a thing is more likely to continue to work than hacking something together that so happens to work right now.

All fair points. However, unfortunately, not possible.

Background

The 'solution' is coming, but you're not going to like it. You're going to keep looking. Hence, part of the answer is to extinguish all hope, and save you the time.

If you don't care about the why and already decided to stop looking, feel free to skip this chapter.

• The JDBC spec is intentionally written somewhat vaguely.
• The JDBC spec's aim is not to be a convenient API for 'end users' (programmers writing java code that interacts with databases). That's why you should always be using JDBI, JOOQ, or similar tools (or Hibernate/JPA, but that's another can of worms. Don't do that unless you really know it fits).
• ... instead the aim of JDBC is to ensure all functionality that even a single DB engine has, is accessible in some way with JDBC. Not necessarily a nice way.

That last part is important: Most java APIs are written as the exact opposite of that last point: They capture that functionality which every platform supports. The problem with writing an API that is designed to expose functional that any platform supports (i.e. if oracle has a feature do to X and no other DB engine can do X, JDBC still wants to expose it), is that it becomes impossible to do good API design, and difficult to even spec things properly. After all, some DB engine might be created from scratch later, or existing DB engines could add new functionality.

The upshot is that it is fine for JDBC implementations to have failed to implement certain parts of the spec (or, rather, if you want to get technical, they are implementing the spec correctly, but 'do nothing and throw some bizarre error' is, as per the spec, an acceptable implementation).

And that will always be. At a fundamental level, "I want a nice clean way to do somewhat exotic thing" is just not something JDBC can ever do for you, and never will, because that's not what JDBC is for.

JDBC is designed by committee, that committee being major DB vendors, and its audience for implementors of the spec is also only that committee. JDBC is weird in this regard too: It 'breaks backwards compatibility' all the time. Normally in java, any interface exposed in java.* will never add new methods unless those methods have a default implementation. JDBC interfaces are an exception to that rule: As long as all vendors are 'on board' they can add stuff.

Can't I predict?

You can't predict either: JDBC methods are, unfortunately, not separated into multiple tiers. It'd have been nice if the JDBC spec consisted of a segment of 'all implementations MUST provide non-useless implementations that confirm to a very detailed spec, and failure to follow this is cause to be thrown out', and a second tier of 'this layer exists to expose all functionality; implementing it as a one-liner throws SQLException("Not supported") is acceptable, and vendors are given leeway to implement it however they feel like'.

But, it isn't so. Or if it had been so, then the CLOB and stream handling would be in the second tier anyway.

Solution

Custom code it. If you want to be more future proof, instead of literally a switch on db engine type, you can try it a few ways, catching the exceptions, and dealing with the fallout.

Unfortunately, exceptions are mostly untyped in JDBC land: They have an 'error state' which is more or less an error code but these are unspecced (as in, each JDBC driver makes up their own code lists. Those lists are often properly specced - for example, Postgres error codes - those are what you get in sqlEx.getSQLState().

So you're mostly just stumbling in the dark: Try a thing, if it fails, don't try to ascertain the reason - just try another thing. If any of the 'things' worked, great, if not, propagate that SQLException that was thrown by the most obvious of the many ways you tried, perhaps stapling all the others on as suppressed exceptions.

public InputStream streamBinary(ResultSet rs, int columnIndex) {
  try {
    return rs.getBinaryStream(columnIndex);
  } catch (SQLException streamEx) {
    try {
      return rs.getBlob(columnIndex).getBinaryStream();
    } catch (SQLException blobEx) {
      streamEx.addSuppressed(blobEx);
      throw streamEx;
    }
  }
}

With perhaps a second method that takes String columnName but is otherwise identical.