Database independence through JDBC in Java

Question

There are some limitations to using JDBC and database dependencies - this is one of these limitations.

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

I tried to achieve this through dynamic polymorphism (I created specific classes for different DBMSs and redefined the usual CRUD operations according to the specific SQL syntax).

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

Answer 1

I think I can answer, since I am the author of jOOQ , which was already suggested in another answer . As I have already shown, it is quite possible to achieve what you are trying to do, but you still have a long way to go if you want to make your own.

Let's talk about JDBC

JDBC is a great abstraction of the network protocol, so it is a great starting point. There are a few caveats when you move on to solving more complex problems inside the API, such as the one you are trying to create. For instance:

• It is really difficult to get the generated keys. Few JDBC Drivers Get This Right
• Are you sure you process large objects correctly? Hint: you are not
• What is worse than LOBs? Large objects inside Oracle object types
• Did I mention Oracle OBJECT object types? They can be placed in arrays (and arrays of arrays. This is when the material gets really hairy
• But Oracle OBJECT types are great compared to PostgreSQL TYPE types. The JDBC driver doesn't help you at all, there
• Try NULL DB2 NULL Values. Sometimes it works, sometimes not.
• Want to support java.time.LocalDate and java.time.LocalDate ? Good luck
• Speaking of dates, do you know how many different types of interpretations there are for the TIMESTAMP WITH TIME ZONE data TIMESTAMP WITH TIME ZONE ?
• Want to support INTERVAL types? Indeed?
• What if the database throws more than one exception?
• What if the database causes errors through an API other than exceptions (hi SQL Server)
• What if you need to collect warnings before you receive exceptions?
• Did you know that some databases first send you an update counter, and only then the actual result set (for example, when triggers fire)
• Have you thought about working with multiple result sets?
• Now combine the above with the formal parameters OUT
• Let's talk about the BOOLEAN type
• ... I could go for hours. More examples on this site.
• Did you know that some PostgreSQL statements do not work when autoCommit set to true?
• Not all support savepoints
• Want to use JDBC DatabaseMetaData to reverse engineer your schema? Forget it!
• Want to use ResultSetMetaData to find suitable column names? Well...

As you saw, even if JDBC does its job really well for most people (and there is always a hacky workaround for each of the above that works for a separate database. But you want to write an API that works on all databases, so you should fix / get around all of the above. Trust me. It will take you some time!

Let's talk about SQL

But so far, we have only discussed how difficult it is to contact JDBC. We did not discuss how difficult it is to standardize SQL. So let's discuss this for a moment:

• LIMIT n OFFSET m is good, huh? 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 maintain old databases? Will you use your own ROW_NUMBER() filtering? Here I have documented this for you .
• Some databases support SELECT without FROM . In other databases, you need something like a DUAL table. That's all, everything is documented .
• Some databases pretend that they don’t need this DUAL table until their parser breaks and you still need it (hello MySQL)
• Some databases support SELECT without FROM , but they require FROM for WHERE / HAVING / GROUP BY
• What do you think of this: (SELECT 1 UNION SELECT 2) UNION ALL SELECT 3 . Will this work on all databases? (I mean nesting in parentheses)
• Is EXCEPT ALL supported? EXCEPT even supported?
• Is FULL OUTER JOIN supported?
• Do derived tables need an alias or can they live without one?
• Is the AS keyword allowed for views?
• Can an ORDER BY contain expressions that reference aliases from a SELECT ? Or just expressions referring to columns from the FROM ?
• Can an ORDER BY even contain expressions?
• Can views contain an ORDER BY ?
• Let's talk about the features. Is it called SUBSTRING() or SUBSTR() or INSTR() or what?
• Hint is how to emulate REPEAT() function on SQLite
• How would you emulate the VALUES() constructor, as in SELECT * FROM (VALUES (1), (2)) t(a) ? Few databases have built-in support.
• Actually, how would you emulate a derived list of columns ( table(column) aliases table(column) at a time) if it is not supported? Here is an interesting idea .
• In fact, let's discuss expressions with string values ​​and the predicates built with them. This: (a, b) > (x, y) the same as this: a > x OR a = x AND b > y . The first is not supported everywhere

• PostgreSQL UPDATE.. RETURNING can be emulated using the 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, this can be done completely. I did it, it's called jOOQ . This was probably the biggest problem in my professional life, and it was fun. JOOQ 3.10 will introduce a parser that can translate an SQL string (in any dialect) to another SQL string (in a specific dialect) , which is the next level of vendor independence.

But it was a long way to get here. Before I started working with jOOQ (started in 2009), I worked intensively with Oracle SQL and JDBC-based internal environments (such as you plan to write). I wrote jOOQ because I saw many written internal frameworks, and none of them did the job. Developers have always taken up SELECT .. FROM .. WHERE - which is the easy part. Some managed to get a JOIN in the game, and possibly GROUP BY and what it is. Then they abandoned this task because they had a more important business than supporting boring and buggy infrastructure software.

I don’t know what your motivation is to do it yourself, but my advice here is:

• Use jOOQ if you want to write vendor-independent SQL
• Use Hibernate if you want to implement object graph vendor independence

You can try creating your own JOOQ (or Hibernate). This is a fun challenge. But if you have a deadline, I really suggest that you consider the above options.

Answer 2

First, database independence is complex. Really, very hard; to achieve this without using ORM or another tool, you will have to trade another aspect of the design of your solution. Simplicity and maintainability will suffer, as well as efforts to implement the solution.

So, I would really, really convinced that this is a high priority requirement - the hypothetical question "what if we want to switch from Oracle to SQL Server", in my opinion, is not enough justification to bear the additional costs .. .

If you have to deliver this function, and ORM is the easiest way to do this. ORM structures are specifically designed with database independence in mind (and their complexity is at least partially due to this requirement). They do this by abstracting the database implementation to a higher level; Instead of thinking in SQL statements, you are advised to think about domain objects.

Having said all this ...

I put the solution (not in Java, but the principle is worth it), which allowed to ensure the independence of the database. We saved our SQL statements as resources and loaded them through resource files. By default, ANSI SQL was used, which should work with any modern SQL-compatible database.

We had resource files for each database we supported, which we supported (in our case, MySQL and Oracle), and used overrides to load into the SQL specification of the database, if they existed.

This works like internationalization in most languages ​​- first find the lines depending on the language, return to the default if you cannot find it.

The Java Resource Pack will make this pretty simple. The lack of hard coding of your SQL in your application has other advantages: fixing the database problem without changing the application code is much simpler, you can deploy the fixes as "resource" updates, and not send new binary files, etc.

Answer 3

As I explained in my book , there are two ways to solve the database portability problem:

1. Subtracting unusual functions - You can remove all database-specific functions, leaving you with a set of SQL-92 functions. This is what ORM tools do, because common CRUD statements are processed almost equally by all DBMSs. However, when you need to execute database queries, you switch to your own SQL .
2. Specialization. You can use database-specific dialects and emulate functionality from one database to another, for example, PIVOT . This approach is used by jOOQ .

However, since you expressly stated that:

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

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

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

Thus, you can define the DAO interfaces that are used by the service layer, for example ProductDAO , and implement each of them for each supported database:

• OracleProductDAOImpl
• MySQLProductDAOImpl
• PostgreSQLProductDAOImpl
• SQLServerProductDAOImpl

Or you can use one ProductDAOImpl , but then you need to use stored procedures and make sure that each stored procedure is implemented in the database:

• Oracle
• MySQL
• PostgreSQL
• SQL Server

So, although it is possible, you were mostly where we were at the beginning of 2000, when we had neither Hibernate nor jOOQ. In my experience, this is a lot more work than if you were using a mature data access structure.

Answer 4

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

Have you tried jOOQ? it is completely different than other ORMs and does what you are looking for. You could call it a “tool”, but then I could also refer to what you are trying to build as a “tool”.

jOOQ aims to be the native java language for creating portable database calls, so it sounds as if this is exactly what you are looking for.

https://www.jooq.org/

Answer 5

While (very) late at the party and not bringing anything new, I would like to support and expand Neville a little and Vlad is responsible (as a plausible option).

Frames like jOOQ and Hibernate are probably best suited, especially the IF that you are trying to get is something completely universal (i.e. handle the most imaginary database operations), rather than a few specific SQL related tasks . However, since they don't seem to be an option for you, externalizing SQL one way or another may actually get you pretty far (in terms of SQL dialogs, not necessarily due to JDBC binding problems, which are well described in Lucas's answer ) .

Previously, I worked on the Java-based ETL custom engine (successfully), running on both Oracle and DB2. As you can imagine, we need the best queries that we could get in terms of speed, and our database administrators optimized them using every trick in the book, and then some! Thus, we not only had 2 dialects of SQL to accommodate, but the queries that we had to fulfill were also very individual (hints, etc.) ... The solution we settled on was to generate SQL at runtime using the template engine (Velocity at the time) and custom templates designed for the target database (probably similar to how Hibernate or jOOQ build their final SQL). Admittedly, we had our own “context” with specific and clearly defined needs ...