multiple datasources (querying multiple databases)

[andym...@gmail.com]

Hello,

I am following the guidance documented here:

https://stackoverflow.com/questions/44030012/build-left-join-query-with-jooq-using-two-tables-from-two-different-databases

The example given (how to query over multiple databases) is something like this:

DSL.using(configuration)
   .select()
   .from(LIBRARY.BOOKS)
   .leftJoin(LIBRARY2.AUTHOR2)
   .on(LIBRARY.BOOKS.AUTHOR_ID.eq(LIBRARY2.AUTHOR2.ID))
   .fetch();

The question I have is how do I configure the datasources such that the above will work.  I am not as familiar with the data source config, so I don't know if I need just one (which doesn't specify a single database) or multiple, in which case the jooq configuration class would somehow combine them together.

Thank you.

Andy Tarpley

[Lukas Eder]

Hi Andy,

Thanks for your message.

I think there's a misunderstanding here. The question you linked talks about "databases" in the sense of a MySQL "database", which is really a schema. You probably have a "database" in mind in the sense of an Oracle "instance" or "server", which is really a catalog.

Few databases allow you to write queries across catalogs. SQL Server is the only one I'm aware of that can do it out-of-the-box, whereas in Oracle, you would be specifying a database link.

If your setup requires that you have two separate data sources, there's no reasonable way to join across "databases" over the two data sources, apart from doing that in Java memory (which is a pity, performance wise). However, maybe you can configure a data source to have the necessary grants required to access both "databases" in your server.

I hope this helped more than it contributed to confusion :)

If you have any further question, please feel free to ask, providing some more details about your actual environment.

Thanks,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.