So i am running a spring boot server which i use to query a MySQL database. So far i have been using the auto-configured HikariCP connection pool with JOOQ so i had almost nothing to do with the connection pool. But now i need to query two different schemas (on the same server) and it seems like i can't auto-configure two connection pools so i have to tinker with the DataSource myself. I would like to conserve the native behavior of the connection, i.e have a set of persistent connections so that the server can dispatch the queries and once the query is resolved, the connection is still there and free to use again. I have found multiple implementations of connection pools allowing to have multiple DataSource to query multiple servers but i don't know if each of them is using the behavior that i just described.
Implementation #1 : https://www.ru-rocker.com/2018/01/28/configure-multiple-data-source-spring-boot/
Implementation #2 : https://www.stubbornjava.com/posts/database-connection-pooling-in-java-with-hikaricp
I feel like #2 is the most straight forward solution but i am sceptical to the idea of creating a new DataSource everytime i want to query. If i don't close it, am i just opening now connections over and over again? So obviously i would have to close them once finished but then it's not really a connection pool anymore. (Or am i misunderstanding this?)
Meanwhile #1 seems more reliable but again, i would be calling new HikariDataSource everytime so is that what i am looking for?
(Or is there a more simple solution that i have been missing out because i need to query two different schemas but still on the same server and dialect)
Ok so it turns out i don't have to setup multiple connections in my case. As i am querying the same server with the same credentials, i don't have to setup a connection for each shema. I just removed the schema that i specified in my jdbc url config:
spring.datasource.url=jdbc:mysql://localhost:5656/db_name?useUnicode=true&serverTimezone=UTC
Becomes
spring.datasource.url=jdbc:mysql://localhost:5656/?useUnicode=true&serverTimezone=UTC
And then as i had already generated the POJO with the JOOQ generator i could reference my table from the schema object, i.e: Client.CLIENT.ID.as("idClient") becomes ClientSchema.CLIENTSCHEMA.CLIENT.ID.as("idClient"). This way i can query multiple schemas without setting up any new additional connection.
How to configure MAVEN and JOOQ to generate sources from multiple schemas:
https://www.jooq.org/doc/3.13/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-catalog-and-schema-mapping/