使用内存数据库从多个数据库查询

Question

如何使用内存数据库(如H2 db )编写下面的内部连接查询。

select * from emp e inner join other_db.dept d on e.id=d.eid

在db1数据库中创建emp表，在other_db数据库中创建dept表。

内存中数据库的问题是数据库名与数据源无关.因此，我们不能在查询中使用other_db.dept。

正如Thomas所建议的，下面是用Java和Spring框架编写的带有链接表的代码

Spring配置文件：

<jdbc:embedded-database id="db1DataSource" type="H2">
    <jdbc:script location="classpath:schema-db1.sql" />
</jdbc:embedded-database>

<jdbc:embedded-database id="otherdbDataSource" type="H2">
    <jdbc:script location="classpath:schema-other.sql" />
</jdbc:embedded-database>

schema-db1.sql

SET MODE MSSQLServer

CREATE TABLE emp ( id int NOT NULL, name varchar(30) NOT NULL)

CREATE LINKED TABLE other_db ('org.h2.Driver', 'jdbc:h2:mem:test', 'sa', '', 'dept')

schema-other.sql

SET MODE MSSQLServer

CREATE TABLE dept ( id int NOT NULL, name varchar(20) NOT NULL, eid int NOT NULL)

现在，我可以写以下查询：

select * from emp e inner join other_db.dept d on e.id=d.eid

实际上，我在运行代码时遇到了以下异常：

Table dept not found

Answer 1

我在同一个数据库下创建了两个模式，使用h2 url中的INIT属性，如下所示：

<bean class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close" id="db1DataSource">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:mem:test;INIT=CREATE SCHEMA IF NOT EXISTS first_db\;SET SCHEMA first_db" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

<jdbc:initialize-database data-source="db1DataSource">
    <jdbc:script location="classpath:schema-db1.sql" />
</jdbc:initialize-database>

<bean class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close" id="otherdbDataSource">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:mem:test;INIT=CREATE SCHEMA IF NOT EXISTS other_db\;SET SCHEMA other_db" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

<jdbc:initialize-database data-source="otherdbDataSource">
    <jdbc:script location="classpath:schema-other.sql" />
</jdbc:initialize-database>

现在，能够跑到下面：

select * from emp e inner join other_db.dept d on e.id=d.eid

Answer 2

H2支持链接表访问另一个数据库中的表。如果您首先创建一个链接表，那么您可以像在问题中的查询中一样使用它。源和/或目标数据库是否在内存中并不重要。

表名以与数据库元数据标识符相同的方式区分大小写，因此，如果在H2：CREATE TABLE dept中创建这样的表，则链接表需要大写：

CREATE LINKED TABLE other_db
('org.h2.Driver', 'jdbc:h2:mem:test', 'sa', '', 'DEPT')