Search code examples
javajooq

Getting error while implementing joins on multiple DSL contexts from different databases in jooq

I want to execute one query having joins with the tables related to different DSLCONTEXT which are related to different databases.Below is the example of what I am trying to do :

DSLContext ctx1;
DSLContext ctx2;
Val findDetails= ctx1.select(table1(**from ctx1**). innerjoin(table2)(**from ctx2**).on(table2.column.eq(table1.column)).asTable();

While executing the above query getting table2 doesn't exist.

I am expecting to use something like ctx2. Table2 inside the query.

Solution

  • I'm assuming that the reason for you to have two distinct DSLContext instances is because you have separate JDBC Connection instances, and you're connecting to separate database instances. To my knowledge, it isn't possible to use Oracle style database links or PostgreSQL style foreign data wrappers in MySQL, so you cannot join between the two tables in a single query. You'll have to run both queries separately, and join the data in the client, e.g.:

    Result<?> r1 = ctx1.select(..).from(..).where(..).fetch();
Result<?> r2 = ctx2.select(..).from(..).where(
    CORRELATION.ID.in(r1.getValues(...))).fetch();

    Of course, it may well be that there isn't really a need for two separate DSLContext instances, if the two databases are on the same server, but you didn't specify those details. In any case, jOOQ can't do that for you.