使用 Hibernate 连接不同数据库中的 2 个表 [英] Doing a join over 2 tables in different databases using Hibernate

问题描述

我在两个独立的 Oracle 数据库(不是架构)中有两个表，我需要加入 Hibernate.目前，我有两个 Hibernate 会话发送到单独的数据库.在有人说查看 Hibernate Shards 之前，我花了一天的大部分时间查看该子项目并发现:它用于水平分区数据(所有表必须在所有AFAIK 数据库)，没有办法告诉 Shards 只查看一个数据库(Hibernate Shards Docs)，并且不再被处理.

I have two tables in two separate Oracle databases (not schemas) that I need to join in Hibernate. Currently, I have two Hibernate sessions going out to the separate databases. Before anybody says look at Hibernate Shards, I have spent a better part of a day looking at that sub-project and have found that: it is for horizontal partitioned data (all the tables must be in all of the databases AFAIK), there is no way for one to tell Shards to look only in one database (Hibernate Shards Docs), and is no longer being worked on.

我想尝试解决这个问题的事情:

Things that I have thought about to try to solve this problem:

• 在两个表上执行 findAll() 或某些受限变体，并使用一些循环手动执行连接.(对于非常小的桌子可以 - 禁止小桌子)

• Doing a findAll() or some restricted variant of that on both of the tables, and manually doing the join using some loops. (Ok for very small tables - prohibitive from small tables on up)

让会话进行某种交互(我不知道这是否可行 - 必须查看 Hibernate Session API)

Have the sessions do some kind of interaction (I have no idea if this is even feasible - will have to look at the Hibernate Session API)

从不同的 hibernate-xxxx.cfg.xml 的 URL 字符串中删除数据库名称，并将它们插入到单独的 hbm.xml 文件中，如下所示:

(从我的初始测试中似乎不起作用，这似乎是卡车大小的安全漏洞)

Removing the database name from the URL string of different hibernate-xxxx.cfg.xml and insert them into the separate hbm.xml files like this:
<class name="foo" table="foo_table" schema="foo_schema" catalog="foo_db">
(Doesn't seem to work from my initial tests and that seems like truck sized security hole)

使用存储库模式(不确定我的 Java-Fu 是否足够强大)

Use the Repository Pattern (Unsure if my Java-Fu is strong enough)

在上面的一个案例中，我是否忽略了某些东西，或者是我上面没有列出的另一种方式?

Is there something that I'm overlooking in one of the cases above or can it be another way that I haven't listed above?

推荐答案

很遗憾，您遇到了几个问题.

You have a couple of problems unfortunately.

• Hibernate 不支持跨多个物理"数据库实例的加入
• 开箱即用，大多数数据库不支持跨多个物理"数据库实例的加入

从根本上说，DB 仅在连接同一数据库中的表方面表现良好/性能良好.有多种连接数据库的方法，但如果两个表的大小都很大，这可能是一个问题，性能可能会受到影响.在谷歌上搜索跨数据库的 oracle 连接"，您会找到一些有关如何执行此操作的指示，但这涉及使用 Oracle 创建从一个数据库到另一个数据库的虚拟链接.

Fundamentally DBs are only good/performant at joining tables that are in the same database. There are ways of joining across databases but if the size of both tables is big this can be a problem, and peformance may suffer. Do some googling on "oracle join across database" and you'll find some pointers on how to do this but it involves fiddling around with Oracle create a virtual link from one DB to the other.

我会考虑在内存中进行连接，如果您觉得数据集适合内存限制，并且您只在一种特殊情况下这样做.

I would consider either doing the join in memory, if you are comfortable the data set will fit within the memory constraints AND you are only doing this in one special case.

如果您需要在这两个数据库之间进行不同的连接，那么我会寻求更持久的解决方案，例如上面的 Oracle 链接.

If you'll need to do different joins between these two databases then I would go for a more permanent solution, like the Oracle linking above.

这篇关于使用 Hibernate 连接不同数据库中的 2 个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！