使用jOOQ对大型SELECT查询进行单元测试 [英] Unit test for a large SELECT query with jOOQ
问题描述
我正在使用 jOOQ 处理关系数据库.我有一个 SELECT 查询，我需要使用模拟编写单元测试.基于此文档和
I am using jOOQ for working with a relational database. I have a SELECT query for which I need to write unit tests with mocking. Based on this doc and this post, I need to define my own data provider, which should look something like this:
class MyProvider implements MockDataProvider {
DSLContext create = DSL.using(SQLDialect.MYSQL);
@Override
public MockResult[] execute(MockExecuteContext mockExecuteContext) throws SQLException {
MockResult[] mock = new MockResult[1];
String sql = mockExecuteContext.sql();
if (sql.startsWith("select")) {
Result<Record2<String, String>> result = create.newResult(COL_1, COL_2);
result.add(create.newRecord(COL_1, COL_2)
.values("val1", "val2"));
mock[0] = new MockResult(1, result);
}
return mock;
}
}
其中 COL_1 和 COL_2 的定义如下:
Field<String> COL_1 = field("Column1", String.class);
Field<String> COL_2 = field("Column2", String.class);
当 SELECT 很小时(如上例中只有2列)，这非常简单明了.我想知道在复杂和大量选择的情况下应该如何做.例如，我有一个 SELECT 语句，该语句从多个表联接中选择30多个列.似乎与
It's quite simple and straightforward when SELECT is a small one (as in the above example, just 2 columns). I am wondering how it should be done in case of complex and large selects. For instance I have a SELECT statement which selects 30+ columns from multiple table joins. Seems the same approach of
Result<Record_X<String, ...>> result = create.newResult(COL_1, ...);
result.add(create.newRecord(COL_1, ...)
.values("val1", ...));
在超过22列的情况下不起作用.
does not work in case of more than 22 columns.
感谢您的帮助.
推荐答案
回答您的问题
没有限制，最多22列.如此处记录:
高学历记录
jOOQ选择显式支持多达22个度，以匹配Scala的类型安全元组，功能和产品支持.但是，与Scala不同，jOOQ还支持更高的程度，而没有额外的类型安全性.
jOOQ chose to explicitly support degrees up to 22 to match Scala's typesafe tuple, function and product support. Unlike Scala, however, jOOQ also supports higher degrees without the additional typesafety.
您仍然可以使用 Record1 - Record22 类型.如果存在这样的重载，则子类型的类型安全性将丢失，因为 values(Object ...)方法适用于所有类型的参数.将来可以通过引入新的 RecordN 子类型来解决此问题.
You can still construct a record with more than 22 fields using DSLContext.newRecord(Field...). Now, there is no values(Object...) method on the Record type, because the Record type is the super type of all the Record1 - Record22 types. If such an overload were present, then the type safety on the sub types would be lost, because the values(Object...) method is applicable for all types of arguments. This might be fixed in the future by introducing a new RecordN subtype.
But you can load data into your record with other means, e.g. by calling Record.fromArray(Object...):
Record record = create.newRecord(COL_1, ...);
record.fromArray("val1", ...);
result.add(record);
values()方法仅为 fromArray()的方便(增加类型安全性).
The values() method being mere convenience (adding type safety) for fromArray().
我假设您已阅读链接的文档页面上的免责声明.无论如何，我还是将它发布在这里，供其他可能尚未阅读免责声明的读者阅读:
I'm assuming you read the disclaimer on the documentation page you've linked. I'm posting it here anyway for other readers of this question, who might not have read the disclaimer:
免责声明::使用此jOOQ API模拟JDBC连接的一般想法是使用非常简单的JDBC抽象提供快速解决方法，注入点等.建议不要使用此模拟API来模拟整个数据库(包括复杂的状态转换，事务，锁定等).满足此要求后，请考虑使用实际数据库代替集成测试，而不是在MockDataProvider内部实现测试数据库.
Disclaimer: The general idea of mocking a JDBC connection with this jOOQ API is to provide quick workarounds, injection points, etc. using a very simple JDBC abstraction. It is NOT RECOMMENDED to emulate an entire database (including complex state transitions, transactions, locking, etc.) using this mock API. Once you have this requirement, please consider using an actual database instead for integration testing, rather than implementing your test database inside of a MockDataProvider.
似乎您将要重新实现一个可以运行"数据库的数据库.任何类型的查询，包括具有23列以上的查询，并且每次更改被测查询时，您也都将在此处更改此测试.我仍然建议您改为使用 testcontainers 甚至使用H2进行集成测试，这将有助于覆盖查询比任何此类单元测试方法都要多.
It seems you're about to re-implement a database which can "run" any type of query, including a query with 23+ columns, and every time you change the query under test, you will also change this test here. I still recommend you do integration testing instead, using testcontainers or even with H2, which will help cover many more queries than any such unit test approach.
此外，集成测试将有助于测试查询的正确性.像这样的单元测试将只提供虚拟结果，而不管实际的查询如何.这样的模拟很可能比SQL级别更容易在更高的级别上实现，即通过模拟DAO，存储库或任何其他方法来实现.
Also, integration tests will help test query correctness. Unit tests like these will only provide dummy results, irrespective of the actual query. It is likely that such mocks can be implemented much more easily on a higher level than the SQL level, i.e. by mocking the DAO, or repository, or whatever methods, instead.
这篇关于使用jOOQ对大型SELECT查询进行单元测试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
