如果输入和输出数据库位于两个不同的数据库服务器中，是否可以使用渲染映射 [英] Is it possible to use Render Mapping if the input and output db is in two different database servers

问题描述

我正在一个迁移项目中，从中读取一个表并将某些字段移到另一个表.它的实现方式是Java客户端应用程序自行读取源表，然后将结果存放在Pojo中，并使用该Pojo作为请求正文向REST发出POST请求服务器然后处理写入另一个表的任务.当前，我在客户端应用程序中使用JOOQ来从源表读取数据，而没有生成任何代码.我正在使用String文字映射到表列和标识符构建API，如下所示(选择示例):

I'm working on a migration project where a table is read from and certain fields are moved to another table. It's implemented in such a manner that a Java client app reads the source table on its own, the results are then lodged into a Pojo and a POST request is made using that Pojo as a request body, to a REST server which then handles the task of writing to another table. Currently, I'm using JOOQ in my client app to read from the source table, without any code generation. I'm using String literals to map to the table columns and identifier building API, like this (select example):

return ctx.select(field(name(ID)),
    field(name(CUSTOMER_ID)),
    field(name(SIZE)),
    field(name(NAME)),
    field(name(CONTENT)))
    .from(table(TABLE))
    .where((field(name(UPLOAD)).eq((byte) 0)));

这种情况使得我需要从多个数据库中读取同一张表，并且还可以访问artifactory，其中包含所有表的JOOQ生成的类，包括我需要从中读取的类.现在，问题是，JOOQ生成的类来自特定的数据库服务器A，而我需要从中读取的表位于数据库服务器B中的数据库内部.当我尝试使用artifactory中的生成类读取表时，收到一条错误消息，提示表不存在".在这种情况下，表"指的是从另一台数据库服务器用于JOOQ生成的数据库表.有什么方法可以配置RenderMapping来将input设置为服务器A上的数据库，并且将output设置为我当前正在读取的服务器?我已经在同一服务器上的两个数据库之间配置了RenderMapping，但是不知道它是否适用于两个不同的数据库服务器.我在这里要做的主要事情是使用artifactory中的预生成的JOOQ类从单个表中读取而不生成自己的表.

The situation is so that I need to read the same table from multiple databases and I also have access to an artifactory that contains the JOOQ generated classes of all the tables, including the one that I need to read from. Now, the problem is, the JOOQ generated classes come from a specific database server A and the table I need to read from is inside a database in database server B. When I tried to read the table using the generated class from the artifactory, I get an error message that said "Table doesn't exist". "Table", in this context, refers to the database table which was used for JOOQ generation, from another database server. Is there any way I can configure a RenderMapping that sets the input to the database on server A and output to the server I'm currently reading from? I have configured RenderMapping between two databases on the same server, but no idea if it works for two different database servers. The main thing I'm trying to do here is to use pre-generated JOOQ classes from an artifactory to read from a single table without generating my own.

推荐答案

在我的主管提出以下建议后，设法解决了该问题.除了Settings之外，我还必须使用Configuration.像这样:

Managed to fix the issue after the following suggestion from my supervisor. Apart from Settings, I also had to use Configuration. Something like this:

Configuration jooqConfig = new DefaultConfiguration();

然后使用渲染映射创建一个新的Settings.

Then Create a new Settings with render mapping.

Settings jooqSettings = new Settings()
    .withRenderMapping(new RenderMapping().withSchemata(
        new MappedSchema().withInput(sourceDb).withOutput(outputDb)))
    .withRenderFormatted(true);

分配设置并将SQLDialect分配给jooqConfig

jooqConfig.set(jooqSettings);
jooqConfig.set(SQLDialect.MARIADB);

最后，在try-with-resources中，

and finally, inside try-with-resources,

try (DSLContext ctx = DSL.using(jooqConfig.derive(getDataSource()))) {
  /* getDataSource() returns a HikariDataSource with output db credentials */
   do something, for ex., ctx.select()
}

显然，我只需要告诉jOOQ对所有数据库使用sourceDB模式，因为它是用于生成JOOQ的源数据库，并且不需要在pom.xml内添加任何jooq插件.我唯一需要的就是JOOQ和artifact相关的依赖项.

Apparently, I only needed to tell jOOQ to use the sourceDB schema for all databases as it's the source database for JOOQ generation and also didn't need to add any jooq plugin inside pom.xml. The only things I needed were JOOQ and artifact related dependencies.

这篇关于如果输入和输出数据库位于两个不同的数据库服务器中，是否可以使用渲染映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！