Mastering Spring JPA: Configuring Multiple Databases with MySQL and PostgreSQL

In today’s microservices-driven world, it’s not uncommon for applications to interact with multiple databases. Perhaps you’re migrating from MySQL to PostgreSQL and need to maintain both during the transition, or maybe different parts of your application require distinct database technologies. Whatever the reason, Spring Data JPA makes it a breeze to work with multiple databases simultaneously. In this guide, we’ll walk through setting up a Spring Boot application that communicates with both MySQL and PostgreSQL databases.

Our Example Scenario:
Imagine you’re building an e-commerce platform. Customer data is stored in a legacy MySQL database, while product information resides in a newer PostgreSQL database. Let’s set this up using Spring Data JPA.

1. Setting Up Dependencies
   First, ensure your pom.xml has the necessary dependencies:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
    </dependency>
</dependencies>

2. Defining Entities
   Let’s create our Customer and Product entities:

// in package: com.ecommerce.model.customer
@Entity
@Table(schema = "ecommerce_customers")
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    @Column(unique = true, nullable = false)
    private String email;
    private String address;
}

// in package: com.ecommerce.model.product
@Entity
@Table(schema = "ecommerce_products")
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private BigDecimal price;
    private int stockQuantity;
}

3. Creating JPA Repositories
   Next, let’s define our JPA repositories:

// in package: com.ecommerce.repository.customer
public interface CustomerRepository extends JpaRepository<Customer, Long> {
    Optional<Customer> findByEmail(String email);
}

// in package: com.ecommerce.repository.product
public interface ProductRepository extends JpaRepository<Product, Long> {
    List<Product> findByPriceLessThan(BigDecimal maxPrice);
}

4. Configuring MySQL for Customers
   Now, let’s set up the MySQL configuration for our Customer entity:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.ecommerce.repository.customer",
    entityManagerFactoryRef = "mysqlEntityManager",
    transactionManagerRef = "mysqlTransactionManager"
)
public class MySQLConfig {

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.mysql")
    public DataSourceProperties mysqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public DataSource mysqlDataSource() {
        return mysqlDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean mysqlEntityManager(
            EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(mysqlDataSource())
            .packages("com.ecommerce.model.customer")
            .persistenceUnit("mysqlPU")
            .properties(hibernateProperties())
            .build();
    }

    @Bean
    @Primary
    public PlatformTransactionManager mysqlTransactionManager(
            @Qualifier("mysqlEntityManager") EntityManagerFactory emf) {
        return new JpaTransactionManager(emf);
    }

    private Map<String, Object> hibernateProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.dialect", "org.hibernate.dialect.MySQL8Dialect");
        props.put("hibernate.hbm2ddl.auto", "update");
        return props;
    }
}

5. Configuring PostgreSQL for Products
   Similarly, let’s configure PostgreSQL for our Product entity:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.ecommerce.repository.product",
    entityManagerFactoryRef = "postgresEntityManager",
    transactionManagerRef = "postgresTransactionManager"
)
public class PostgreSQLConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.postgres")
    public DataSourceProperties postgresDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    public DataSource postgresDataSource() {
        return postgresDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean postgresEntityManager(
            EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(postgresDataSource())
            .packages("com.ecommerce.model.product")
            .persistenceUnit("postgresPU")
            .properties(hibernateProperties())
            .build();
    }

    @Bean
    public PlatformTransactionManager postgresTransactionManager(
            @Qualifier("postgresEntityManager") EntityManagerFactory emf) {
        return new JpaTransactionManager(emf);
    }

    private Map<String, Object> hibernateProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        props.put("hibernate.hbm2ddl.auto", "update");
        return props;
    }
}

6. Application Properties
   In application.properties, we’ll define our database connection details:

# MySQL (Customers)
spring.datasource.mysql.url=jdbc:mysql://localhost:3306/ecommerce
spring.datasource.mysql.username=root
spring.datasource.mysql.password=secret

# PostgreSQL (Products)
spring.datasource.postgres.url=jdbc:postgresql://localhost:5432/ecommerce
spring.datasource.postgres.username=admin
spring.datasource.postgres.password=secure123

7. Testing Our Setup
   Let’s create a simple service and test to ensure everything works:

@Service
public class EcommerceService {
    @Autowired private CustomerRepository customerRepo;
    @Autowired private ProductRepository productRepo;

    @Transactional(transactionManager = "mysqlTransactionManager")
    public Customer createCustomer(Customer customer) {
        return customerRepo.save(customer);
    }

    @Transactional(transactionManager = "postgresTransactionManager")
    public Product createProduct(Product product) {
        return productRepo.save(product);
    }
}

@RunWith(SpringRunner.class)
@SpringBootTest
public class EcommerceServiceTest {
    @Autowired private EcommerceService service;

    @Test
    public void testMultipleDatabases() {
        Customer john = new Customer();
        john.setName("John Doe");
        john.setEmail("john@example.com");
        john.setAddress("123 Main St, Anytown, USA");
        service.createCustomer(john);

        Product laptop = new Product();
        laptop.setName("Ultrabook Pro");
        laptop.setPrice(new BigDecimal("1299.99"));
        laptop.setStockQuantity(50);
        service.createProduct(laptop);

        // Assertions to verify that both entities were saved correctly
        assertNotNull(service.customerRepo.findByEmail("john@example.com").orElse(null));
        assertFalse(service.productRepo.findByPriceLessThan(new BigDecimal("1300")).isEmpty());
    }
}

Key Points to Note:

1. Package Structure: We’ve organized our code into distinct packages for each database’s models and repositories. This helps Spring identify which components belong to which database.
2. @EnableJpaRepositories: This annotation is crucial. It tells Spring where to find the repositories for each database and which EntityManagerFactory and TransactionManager to use.
3. @Primary: We’ve marked the MySQL beans as @Primary. This is because, in scenarios where a specific bean isn’t specified (e.g., autowiring a TransactionManager without a qualifier), Spring will use the primary one.
4. Hibernate Dialects: It’s essential to use the correct dialect for each database. We’ve used MySQL8Dialect and PostgreSQLDialect to ensure Hibernate generates the right SQL for each database.
5. Transaction Management: When performing operations, we explicitly specify which transaction manager to use. This ensures that transactions are managed by the correct database’s transaction manager.

Conclusion:
With Spring Data JPA, working with multiple databases isn’t just possible—it’s straightforward. By carefully configuring each database’s components and using Spring’s powerful annotations, we’ve built an e-commerce system that seamlessly interacts with both MySQL and PostgreSQL. Whether you’re migrating data, leveraging database-specific features, or just dealing with legacy systems, Spring’s flexibility has you covered. Happy coding!