Database Routing in Spring Boot

You’ll need Spring JDBC and MySQL Connector libraries to connect to your database (e.g., MySQL) and for Spring Boot to manage data access. The provided code includes these dependencies.

A central piece is a custom DataSource class that extends Spring’s AbstractRoutingDataSource. This class determines which database (master or replica) to use for each request.

The logic for choosing the database is typically based on factors like read-only operations (reads go to replicas for better performance). In this example, a determineCurrentLookupKey method is implemented to decide the database based on the presence of the @Transactional(readOnly=true) annotation.

A Spring configuration class is created to define the different databases (master and replica) and their configurations. It uses a map to link database connection details with keys returned by the determineCurrentLookupKey method.

A default database is also specified in case the routing logic doesn’t provide a clear choice. Additionally, configurations for connection pooling and read-only behavior for replicas are set up.

This file holds the actual connection details (URLs, usernames, passwords) for both the master and replica databases.

After setting up these components, our application should function as expected. However, there’s a crucial consideration regarding the behavior of @Transactional with readOnly=true, which still directs calls to the writer instance due to Spring’s Transaction Manager implementation. To override this behavior, we need to create a custom Transaction Manager implementation and inject it into the Spring context.

To see this setup in action, you can download the repository and make the necessary adjustments according to your requirements.