通过JDBC中的数据库独立性 [英] Database independence through JDBC in java

问题描述

使用JDBC存在一些限制，数据库依赖性是其中一个限制。

There are some limitations of using JDBC and database dependency is one of those limitations.

是否有任何模式或方法可以在JDBC中实现数据库独立性（不使用任何其他ORM框架或工具）。

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

我试图通过动态多态实现这一点（根据特定的SQL语法为不同的DBMS和覆盖的常见CRUD操作创建特定的类）。

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

例如，有没有办法编写通用SQL语句，以便它们几乎可以在每个SQL相关的DBMS中执行？

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

推荐答案

我认为我有资格回答，是的作者jOOQ ，已在其他答案中提出。正如我所展示的那样，完全有可能实现你想要做的事情，但如果你想自己动手，那么前面还有很长的路要走。

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.

JDBC是一个出色的网络协议抽象，所以它是一个很好的起点。不过有一些注意事项，当你继续解决API中更复杂的问题时，就像你正在尝试构建的那样。例如：

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:

• 获取生成的密钥真的很难。很少有JDBC驱动程序能够做到这一点


• 您确定要正确处理LOB吗？提示：你不是


• 什么比LOB更糟糕？ Oracle OBJECT类型中的LOB


• 我是否提到过Oracle OBJECT类型？它们可以放在数组（和数组的数组中）。那时候东西变得非常毛茸茸


• 但是，与PostgreSQL的TYPE类型相比，Oracle的OBJECT类型很棒.Lin JDBC驱动程序没有帮助 at all ，


• 尝试绑定DB2 NULL 值。有时它可以工作，有时它不会' t。


• 想要支持 java.sql.Date 和 java。祝你好运！

有数据类型吗？

• 想支持 INTERVAL 类型？真的吗？


• 如果数据库抛出多个异常怎么办？


• 如果数据库通过与异常不同的API引发错误怎么办（hello SQL Server）


• 如果您需要在获取异常之前收集警告怎么办？


• 您是否知道某些数据库首先发送y更新计数，仅实际结果集（例如当触发器触发时）


• 您是否考虑过处理多个结果集？


• 现在将上述内容与正式的 OUT结合起来参数


• 让我们来谈谈 BOOLEAN 类型


• ...我可以坚持几个小时。 本网站上的更多示例


• 您知道吗？当 autoCommit 设置为true时，某些PostgreSQL语句不起作用？


• 不是每个人都支持保存点


• 想要使用JDBC DatabaseMetaData 来反向设计架构？算了吧！


• 想要使用 ResultSetMetaData 来发现合格的列名？好吧......

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

正如你所见，即使JDBC对大多数人来说确实很好（而且总是有一个hacky 适用于单个数据库的每一个的解决方法。但是你想编写一个适用于所有数据库的API，所以你必须修复/解决上述所有问题。相信我。会让你忙一阵子！

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!

但到目前为止，我们已经我只讨论了绑定到JDBC的难度。我们还没有讨论标准化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 很好，是吗？还是 LIMIT m，n ？或 TOP n START at m ？或 OFFSET m ROWS FETCH NEXT n ROWS ONLY ？如果你想支持旧数据库怎么办？你推出自己的 ROW_NUMBER（）过滤？在这里，我已经为你记录了它。


• 有些数据库支持 SELECT 不含 FROM 。在其他数据库中，您需要类似 DUAL 表的内容。 你去了，都记录了。


• 有些数据库假装他们不需要 DUAL 表，直到他们的解析器中断并且你仍然需要它（你好MySQL）


• 某些数据库支持 SELECT 而不包含 FROM ，但它们确实需要 FROM for WHERE / HAVING / GROUP BY


• 您对此有何看法：（SELECT 1 UNION SELECT 2）UNION ALL SELECT 3 。它适用于所有数据库吗？ （我的意思是带括号的嵌套）


• 支持除了所有？是否支持 EXCEPT ？


• 是否支持 FULL OUTER JOIN ？


• 派生表需要别名还是没有别名？


• 关键字 AS 派生表允许？


• ORDER BY 子句是否包含引用 SELECT 条款？或者只有表达式引用 FROM 子句中的列？


• 可以 ORDER BY 子句包含表达式？


• 派生表是否包含 ORDER BY 子句？


• 让我们来谈谈功能。是调用 SUBSTRING（）还是 SUBSTR（）或 INSTR（）或什么？


• 提示，这是如何模仿 REPEAT（） SQLite上的函数


• 你如何模仿 VALUES（）构造函数，如 SELECT * FROM（VALUES（1），（2））t（a）？很少有数据库具有本机支持


• 实际上，您将如何模拟派生列列表（一次性别名表（列））如果它不受支持？ 这是一个时髦的想法。


• 实际上，让我们讨论用它们构建的行值表达式和谓词。这：（a，b）> （x，y）与此相同： a> x OR a = x AND b> ÿ。前者在任何地方都不受支持



• PostgreSQL的 UPDATE .. RETURNING 可以使用Oracle中的PL / SQL块进行模拟12c：

• 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;

正如您所看到的，它可以完全完成。我已经完成了它，它被称为 jOOQ 。这可能是我职业生涯中最大的挑战，而且很有趣。 jOOQ 3.10将有一个解析器，它可以从SQL字符串（在任何方言中）转换为另一个SQL字符串（在特定方言中），这是供应商不可知性的下一个层次。

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.

但要到达这里还有很长的路要走。在我开始使用jOOQ（2009年开始）之前，我已经深入研究了Oracle SQL和基于JDBC的内部框架（就像你计划编写的那样）。我写过jOOQ，因为我看到很多内部框架正在编写，但没有一个能够很好地完成工作。开发人员总是处理 SELECT .. FROM .. WHERE - 这是容易的部分。有些人设法在游戏中获得 JOIN ，也许 GROUP BY 就是这样。然后他们放弃了这项任务，因为他们有更重要的事情要做，而不是维护无聊和错误的基础设施软件。

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:

• 使用 jOOQ 如果你想编写与供应商无关的SQL


• 使用 Hibernate 如果你想实现与供应商无关的对象图持久性

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

你可以尝试构建自己的jOOQ（或Hibernate） ）。这是一个有趣的挑战。但如果您有最后期限，我建议您查看以上选项。

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.

这篇关于通过JDBC中的数据库独立性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！