My goal is to periodically sync data between two databases. One is a "new" database which runs MySQL 8, the other is an "old" database that hasn't been fully decommissioned yet which runs MySQL 5. I want to sync in both directions, meaning for some tables I'm syncing from old to new, and others I'm syncing from new to old.
My goal is to do this from a single Java project, using JDBC. I'm using MySQL Connector/J. The latest version 8.4.0 which only supports MySQL 8 and up. Conversely, connector version 5.1.49 does not work with MySQL 8.
I have tried using multiple modules, but this feels hacky. Assuming module A depends on module B:
I have tried manually setting the driver class name to the MySQL 5 one, but that doesn't work.
The problem you have is that you need two versions of the same library, and you do not know how to embedd them both on the classpath. Only the first of the two will be effective.
What you can do however is to arrange your application in three parts. One of them is common, one of them is MySQL 5 related, and one of them is MySQL 8 related. The common part allows to control the other two and exchange data. The other two parts take over the communication, which mainly happens via the JDBC driver.
The trick is to have these different parts on separate classloaders, which will allow you to run both JDBC drivers simulaneously within one JVM. It works the same way as several webapps in one servlet container - and each of them come with their own version of some library.
There are a number of resources out there how to deal with your own classloader. Here is one of them: https://docs.oracle.com/javase/tutorial/ext/basics/index.html
Likely you do not even have to care about creating the jar. The JDBC drivers are jar files already, and all you need is the classloader and some controlling logic (the common part I mentioned earlier).