Multiple Database Connection using Spring Data JPA

It is very common to have a monolithic application that connects and perform operations on multiple databases. Most organisations adopt this approach to enhance security by isolating different pieces of their data in different database systems such that in event of any of the database servers being compromised, the compromised data will not necessary be of any use to the hacker. For example, a banking application, governments ID databases, patient management systems etc.

In this post, we will develop a simple patient management system using Spring Boot (spring-data-jpa), where the patient information is stored in a MySQL database and the medical record stored in a PostgreSQL database. The developed application should interact with both databases in real time and perform various database operations.

Typically, Spring boot provides lots of auto configuration, but in a multi database driven application, we would need to add additional configuration in order to establish multiple database connections.

Maven Dependencies

In addition to related spring data dependencies, we need to include MySQL and PostgreSQL database connector dependencies.

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>

Application Properties

This is where we configure different database connection settings. Please update these settings to suit your needs.

# ===============================
# MYSQL DATA SOURCE
# ===============================
app.mysql.datasource.url= jdbc:mysql://localhost:3306/pms
app.mysql.datasource.username = root
app.mysql.datasource.password = password

# ===============================
# MYSQL HIKARI CONNECTION POOL 
# ===============================

app.mysql.datasource.hikari.connection-timeout=10000
app.mysql.datasource.hikari.maximum-pool-size=10
app.mysql.datasource.hikari.minimum-idle= 1
app.mysql.datasource.hikari.pool-name= mysql-HikariPool

# ===============================
# PGSQL DATA SOURCE
# ===============================

app.pgsql.datasource.url= jdbc:postgresql://localhost:5432/computingfacts
app.pgsql.datasource.username = postgres
app.pgsql.datasource.password = password


# ===============================
# PGSQL HIKARI CONNECTION POOL 
# ===============================
app.pgsql.datasource.connection-timeout=10000
app.pgsql.datasource.maximum-pool-size=10
app.pgsql.datasource.minimum-idle= 1
app.pgsql.datasource.pool-name= pgsql-HikariPool

# ===============================
# JPA / HIBERNATE
# ===============================
spring.jpa.database=default
spring.jpa.hibernate.ddl-auto = update
spring.jpa.show-sql = true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Database Schema

To create the two tables that will be used in this application. we run these SQL scripts.

MySQL

CREATE TABLE `medical_record` (
  `idmedical_record` bigint(20) NOT NULL AUTO_INCREMENT,
  `patient_id` varchar(50) NOT NULL,
  `case_report` longtext,
  `doctor_id` bigint(20) NOT NULL,
  `creation_date` datetime DEFAULT NULL,
  PRIMARY KEY (`idmedical_record`),
  UNIQUE KEY `idmedical_record_UNIQUE` (`idmedical_record`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

PostgreSQL

-- CREATE TABLE public.patient

CREATE TABLE public.patient
(
    patient_id bigint NOT NULL DEFAULT nextval('patient_patient_id_seq'::regclass),
    patient_no character varying COLLATE pg_catalog."default" NOT NULL,
    patient_name character varying COLLATE pg_catalog."default" NOT NULL,
    patient_dob date,
    patient_address character varying COLLATE pg_catalog."default",
    patient_email character varying COLLATE pg_catalog."default",
    patient_phone_number numeric,
    gp_surgery bigint,
    creation_date date,
    ni_no character varying COLLATE pg_catalog."default",
    CONSTRAINT patient_pkey PRIMARY KEY (patient_id)
)

TABLESPACE pg_default;

ALTER TABLE public.patient
    OWNER to postgres;

Application Configuration

The main application data configuration will comprise of Data source, Transaction management, spring data JPA repository and Entity manager factory bean definition.

Step 1 - DataSource Configuration

We create a dataSource in order to establish a connection to the database using the configured individual database drivers.

    @Primary
    @Bean(name = "pgsqlDataSourceProperties")
    @ConfigurationProperties("app.pgsql.datasource")
    public DataSourceProperties pgsqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean(name = "pgsqlDataSource")
    @ConfigurationProperties("app.pgsql.datasource")
    public DataSource pgsqlDataSource(@Qualifier("pgsqlDataSourceProperties") DataSourceProperties properties) {
        return (HikariDataSource) properties.initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean(name = "mysqlDataSourceProperties")
    @ConfigurationProperties("app.mysql.datasource")
    public DataSourceProperties mysqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties("app.mysql.datasource.hikari")
    public DataSource mysqlDataSource(@Qualifier("mysqlDataSourceProperties") DataSourceProperties mysqlDataSourceProperties) {
        return (HikariDataSource) mysqlDataSourceProperties.initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

Because we have created two different @Bean definition of dataSource, we need to mark one of them as the @Primary dataSource otherwise, more than one dataSource of the same type will be detected by Spring IoC and the application will fail to start.

Step 2 – Entity Manager Factory Bean definition

We need an EntityManagerFactory to create an EntityManager that will manage the Entities in our application. To let Spring container manager our EntityManagerFactory, we need to setup a LocalContainerEntityManagerFactoryBean using the EntityManagerFactoryBuilder class which has provision for us to reference the required dataSource and also the location (packages) of the entities to manage.

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean pgsqlEntityManagerFactory(EntityManagerFactoryBuilder builder, @Qualifier("pgsqlDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages(PACKAGES_TO_SCAN)
                .build();

    }

    @Bean
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder, @Qualifier("mysqlDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages(PACKAGES_TO_SCAN)
                .build();

    }

Step 3 - Transaction Management configuration

Transaction management implies how we handle the persistence of our entities or database objects (perform validation checks, to commit the operation or not to), what happens if something goes wrong (rollback) and clean ups after the database operation. The aim of Transaction management is to ensure the consistency and validity of our data.

Spring Transaction provides an abstraction over the different transaction APIs, via PlatformTransactionManager interface and for this example, we are using the JpaTransactionManager an implementation of the PlatformTransactionManager for a single JPA EntityManagerFactory.

To create a transaction manager, we will need a reference of individual entity manager factory bean and finally, add the @EnableTransactionManagement which is responsible for registering the necessary Spring components that powers annotation-driven transaction management capability.

    @Bean
    @Primary
    public PlatformTransactionManager pgsqlTransactionManager(@Qualifier("pgsqlEntityManagerFactory") EntityManagerFactory pgsqlEntityManagerFactory) {
        return new JpaTransactionManager(pgsqlEntityManagerFactory);
    }

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

Step 4 - JPA Repositories Configuration

We reference the appropriate entityManagerFactory, transactionManager and the repositories to be used.

Patient repository

@EnableJpaRepositories(
        entityManagerFactoryRef = "pgsqlEntityManagerFactory",
        transactionManagerRef = "pgsqlTransactionManager",
        basePackages = "com.computingfacts.mds.repositories.patient"
)

Record repository

@EnableJpaRepositories(entityManagerFactoryRef = "mysqlEntityManagerFactory",
        transactionManagerRef = "mysqlTransactionManager",
        basePackages = "com.computingfacts.mds.repositories.record"
)

Repository Query Methods

Let us add some service methods to test and ensure that our application configuration can successfully connect to both databases (MySQL & PostgreSQL) and also perform some database operation.

Spring data Repository

@Repository
public interface PatientRepository extends JpaRepository<Patient, Long> {

    Optional<Patient> findByPatientNo(String patientNo);
}

@Repository
public interface MedicalRecordRepository extends JpaRepository<MedicalRecord, Long> {

    List<MedicalRecord> findByPatientNo(String patientNo);
}

Service methods Implementation

@Service("patientRecordService")
class PatientRecordServiceImpl implements PatientRecordService {

    private final MedicalRecordRepository medicalRecordRepository;
    private final PatientRepository patientRepository;

    public PatientRecordServiceImpl(MedicalRecordRepository medicalRecordRepository, PatientRepository patientRepository) {
        this.medicalRecordRepository = medicalRecordRepository;
        this.patientRepository = patientRepository;
    }

    @Transactional("pgsqlTransactionManager")
    @Modifying
    @Override
    public Patient addPatient(PatientForm patientForm) {
        return patientRepository.findByPatientNo(patientForm.getPatientNo())
                .orElseGet(() -> patientRepository.save(patientForm.toPatient()));
    }

    @Transactional("mysqlTransactionManager")
    @Modifying
    @Override
    public MedicalRecord addMedicalRecord(MedicalRecordForm medicalRecordForm) {
        return medicalRecordRepository.save(medicalRecordForm.toMedicalRecord());
    }

    @Transactional(value = "mysqlTransactionManager", readOnly = true)
    @Override
    public List<MedicalRecord> findMedicalRecordsByPatientNumber(String patientNo) {
        return medicalRecordRepository.findByPatientNo(patientNo);
    }

}

Application Testing –

Now let's test the code to ensure that 1. we using the configured data sources. 2. unit test against an in-memory database (H2) and 3. Integration test on both datbases (PostgreSQL & MySQL).

Configuration

@SpringBootTest
class MultiDatasourceApplicationTests {

    @Qualifier("mysqlDataSource")
    @Autowired
    private DataSource mysqlDataSource;
    @Qualifier("pgsqlDataSource")
    @Autowired
    private DataSource pgsqlDataSource;

    @Test
    void contextLoads() {

        assertThat(mysqlDataSource).isNotNull();
        assertThat(pgsqlDataSource).isNotNull();
        assertThat(mysqlDataSource.toString()).isEqualTo("HikariDataSource (mysql-HikariPool)");
        assertThat(pgsqlDataSource.toString()).isEqualTo("HikariDataSource (pgsql-HikariPool)");
    }
}

Unit Test

@DataJpaTest
public class PatientRecordServiceImplTest {

    @Autowired
    private TestEntityManager testEntityManager;
    @Autowired
    private MedicalRecordRepository medicalRecordRepository;
    @Autowired
    private PatientRepository patientRepository;

    @Test
    void injectedComponentsAreNotNull() {
        assertThat(testEntityManager).isNotNull();
        assertThat(medicalRecordRepository).isNotNull();
        assertThat(patientRepository).isNotNull();
    }

    /**
     * Test of addPatient method, of class PatientRecordServiceImpl.
     */
    @Test
    public void testAddPatient() {
        Patient patient = constructPatient("Boris Johnson", LocalDate.of(1950, Month.MARCH, 26));
        Patient createdPatient = testEntityManager.persist(patient);
        assertThat(createdPatient).isNotNull();
        assertThat(createdPatient).hasFieldOrPropertyWithValue("patientName", patient.getPatientName());
        assertNotNull(patientRepository.findById(createdPatient.getPatientId()));
    }

    /**
     * Test of addMedicalRecord method, of class PatientRecordServiceImpl.
     */
    @Test
    public void testAddMedicalRecord() {
        Patient patient = constructPatient("Boris Johnson", LocalDate.of(1950, Month.MARCH, 26));
        Patient createdPatient = testEntityManager.persist(patient);
        MedicalRecord record = constructMedicalRecord(createdPatient);

        MedicalRecord createdPatientMedicalRecord = testEntityManager.persist(record);
        assertThat(createdPatientMedicalRecord).isNotNull();
        assertNotNull(medicalRecordRepository.findById(createdPatientMedicalRecord.getIdmedicalRecord()));
        List<MedicalRecord> records = medicalRecordRepository.findByPatientNo(createdPatient.getPatientNo());
        assertThat(records).hasSizeGreaterThan(0);

    }
}

Integration Test

@SpringBootTest
public class PatientRecordServiceImplIT {

    @Autowired
    private PatientRecordService patientRecordService;

    /**
     * Test of addPatient method, of class PatientRecordServiceImpl.
     */
    @Test
    public void testAddPatient() {
        PatientForm patientForm = constructPatientForm("Joe Biden", LocalDate.of(1930, Month.FEBRUARY, 27));
        Patient createdPatient = patientRecordService.addPatient(patientForm);
        assertThat(createdPatient).isNotNull();
        assertThat(createdPatient).hasFieldOrPropertyWithValue("patientName", patientForm.getPatientName());

    }

    /**
     * Test of addMedicalRecord method, of class PatientRecordServiceImpl.
     */
    @Test
    public void testAddMedicalRecord() {
        PatientForm patientForm = constructPatientForm("Mike Jordan", LocalDate.of(1970, Month.SEPTEMBER, 11));
        Patient createdPatient = patientRecordService.addPatient(patientForm);

        MedicalRecordForm record = constructMedicalRecord(createdPatient);

        MedicalRecord createdPatientMedicalRecord = patientRecordService.addMedicalRecord(record);
        assertThat(createdPatientMedicalRecord).isNotNull();

        List<MedicalRecord> records = patientRecordService.findMedicalRecordsByPatientNumber(createdPatient.getPatientNo());
        assertThat(records).hasSizeGreaterThan(0);
    }
}

 As usual the source code for this example application is available on GitHub. Thanks for reading and please do leave a comment.