Java H2 postgresql 模式似乎对我不起作用

H2 postgresql mode seems not working for me

提问by Umesh K

Hi my application accesses Postgres database and I have many predefined queries(Rank,Parition,complex join etc) I fire against Postgres. Now I want to go for unit testing these queries behaviour with small test data. So I started with H2/Junit. I found out that most of Postgres queries like Rank, Partition, Complex case when update etc. So I thought of using H2 PosgreSQL compatibility mode by thinking all postgres queries will work on H2 please correct me if I am wrong.

嗨，我的应用程序访问 Postgres 数据库，我有许多预定义的查询（排名、分区、复杂连接等），我针对 Postgres 进行了测试。现在我想用小测试数据对这些查询行为进行单元测试。所以我从 H2/Junit 开始。我发现大多数 Postgres 查询，如 Rank、Partition、Complex case when update 等。所以我想到了使用 H2 PosgreSQL 兼容模式，认为所有 postgres 查询都可以在 H2 上运行，如果我错了，请纠正我。

I followed H2 documentation saying To use the PostgreSQL mode, use the database URL jdbc:h2:~/test;MODE=PostgreSQL or the SQL statement SET MODE PostgreSQL.

我按照 H2 文档说要使用 PostgreSQL 模式，请使用数据库 URL jdbc:h2:~/test;MODE=PostgreSQL 或 SQL 语句 SET MODE PostgreSQL。

I enabled mode using SET MODE PostgreSQLand I tried to fire one of the query which involves rank() and works in postgres but it did not work H2. It gives me the following exception

我启用模式 usingSET MODE PostgreSQL并尝试触发涉及 rank() 并在 postgres 中工作的查询之一，但它在 H2 中不起作用。它给了我以下异常

Function "RANK' not found; in SQL statement

Please guide I am new to H2 and database testing. Thanks in advance. I am using H2 jdbc driver to fire postgres queries by thinking H2 Posgress compatibility mode will allow me to fire postgres queries.

请指导我是 H2 和数据库测试的新手。提前致谢。我正在使用 H2 jdbc 驱动程序通过认为 H2 Posgress 兼容模式将允许我触发 postgres 查询来触发 postgres 查询。

采纳答案by Craig Ringer

So I thought of using H2 PosgreSQL compatibility mode by thinking all postgres queries will work on H2 please correct me if I am wrong

所以我想到了使用 H2 PosgreSQL 兼容模式，认为所有 postgres 查询都可以在 H2 上工作，如果我错了请纠正我

I'm afraid that's not true.

恐怕这不是真的。

H2 tries to emulate PostgreSQL syntax and support a few features and extensions. It'll never be a full match for PostgreSQL's behaviour, and doesn't support all features.

H2 尝试模拟 PostgreSQL 语法并支持一些功能和扩展。它永远不会完全匹配 PostgreSQL 的行为，并且不支持所有功能。

The only options you have are:

你唯一的选择是：

• Use PostgreSQL in testing; or
• Stop using features not supported by H2

• 测试中使用PostgreSQL；或者
• 停止使用 H2 不支持的功能

I suggest using Pg for testing. It is relatively simple to write a test harness that initdb's a postgres instance and launches it for testing then tears it down after.

我建议使用 Pg 进行测试。编写一个测试工具相对简单，initdb 是一个 postgres 实例，然后启动它进行测试，然后将其拆除。

Update based on comments:

根据评论更新：

There's no hard line between " unit" and "integration" tests. In this case, H2 is an external component too. Purist unit tests would have a dummy responder to queries as part of the test harness. Testing against H2 is just as much an "integration" test as testing against PostgreSQL. The fact that it's in-process and in-memory is a convenience, but not functionally significant.

“单元”和“集成”测试之间没有强硬的界限。在这种情况下，H2 也是一个外部组件。纯粹的单元测试将有一个虚拟响应者作为测试工具的一部分。针对 H2 的测试与针对 PostgreSQL 的测试一样是一种“集成”测试。它在进程内和内存中这一事实很方便，但在功能上并不重要。

If you want to unit testyou should write another database target for your app to go alongside your "PostgreSQL", "SybaseIQ", etc targets. Call it, say, "MockDatabase". This should just return the expected results from queries. It doesn't really run the queries, it only exists to test the behaviour of the rest of the code.

如果您想进行单元测试，您应该为您的应用程序编写另一个数据库目标，以与您的“PostgreSQL”、“SybaseIQ”等目标一起使用。称其为“MockDatabase”。这应该只返回查询的预期结果。它并没有真正运行查询，它只是为了测试其余代码的行为而存在。

Personally, I think that's a giant waste of time, but that's what a unit testing purist would do to avoid introducing external dependencies into the test harness.

就我个人而言，我认为这是一种巨大的时间浪费，但这正是单元测试纯粹主义者为了避免将外部依赖项引入测试工具而要做的事情。

If you insist on having unit (as opposed to integration) tests for your DB components but can't/won't write a mock interface, you must instead find a way to use an existing one. H2 would be a reasonable candidate for this - but you'll have to write a new backend with a new set of queries that work for H2, you can't just re-use your PostgreSQL backend. As we've already established, H2 doesn't support all the features you need to use with PostgreSQL so you'll have to find different ways to do the same things with H2. One option would be to create a simple H2 database with "expected" results and simple queries that return those results, completely ignoring the real application's schema. The only real downside here is that it can be a major pain to maintain ... but that's unit testing.

如果您坚持对 DB 组件进行单元（而不是集成）测试，但不能/不会编写模拟接口，则必须找到一种使用现有接口的方法。H2 将是一个合理的候选者 - 但是您必须使用一组适用于 H2 的新查询编写一个新后端，您不能只是重复使用您的 PostgreSQL 后端。正如我们已经确定的那样，H2 不支持您需要与 PostgreSQL 一起使用的所有功能，因此您必须找到不同的方法来使用 H2 执行相同的操作。一种选择是创建一个简单的 H2 数据库，其中包含“预期”结果和返回这些结果的简单查询，完全忽略实际应用程序的模式。这里唯一真正的缺点是维护起来可能会很痛苦……但那是单元测试。

Personally, I'd just test with PostgreSQL. Unless I'm testing individual classes or modules that stand alone as narrow-interfaced well-defined units, I don't care whether someone calls it a "unit" or "integration" test. I'll unit test, say, data validation classes. For database interface code purist unit testing makes very little sense and I'll just do integration tests.

就个人而言，我只是用 PostgreSQL 进行测试。除非我正在测试作为窄接口定义明确的单元独立的单个类或模块，否则我不在乎有人将其称为“单元”还是“集成”测试。我将单元测试，比如说，数据验证类。对于数据库接口代码，纯粹的单元测试意义不大，我只会做集成测试。

While having an in-process in-memory database is convenient for that, it isn't required. You can write your test harness so that the setup code initdbs a new PostgreSQL and launches it; then the teardown code kills the postmaster and deletes the datadir. I wrote more about this in this answer.

虽然拥有进程内内存数据库对此很方便，但它不是必需的。您可以编写测试工具，以便设置代码成为initdb新的 PostgreSQL 并启动它；然后拆卸代码杀死 postmaster 并删除 datadir。我在这个答案中写了更多关于这个的内容。

See also:

也可以看看：

• Running PostgreSQL in memory only

• 只在内存中运行 PostgreSQL

As for:

至于：

If all queries with expected end datasets works fine in Postgress I can assume it will work fine in all other dbs

如果所有具有预期结束数据集的查询在 Postgress 中都能正常工作，我可以假设它在所有其他数据库中都能正常工作

If I understand what you're saying correctly then yes, that's the case - if the rest of your codeworks with a dataset from PostgreSQL, it should generally work the same with a dataset containing the same data from another database. So long as it's using simple data types not database specific features, of course.

如果我理解你的意思，那么是的，就是这样 - 如果你的其余代码适用于来自 PostgreSQL 的数据集，它通常应该适用于包含来自另一个数据库的相同数据的数据集。当然，只要它使用简单的数据类型而不是数据库特定的功能。