I have an application that needs to connect to two databases but the entities are repeated for both databases.

I managed to get the application to connect and persist the entity in both databases but I had to create two repositories, one for each database and I didn't find this approach correct.

There is a better way to do this management where I create a repository and define for it which connection it should use based on a flag for example.

First database configuration

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
basePackages = ["xxx.xxx.domains.portadapter.repository.firstRepository"],
entityManagerFactoryRef = "firstEntityManegerFactory",
transactionManagerRef = "firstTransactionManager"
)
class FirstDatabaseConfiguration {

@Bean(name = ["firstDatasource"])
@ConfigurationProperties(prefix = "first.datasource")
fun dataSource(): DataSource? {
    return DataSourceBuilder.create().build()
}

@Bean(name = ["firstEntityManegerFactory"])
fun firstEntityManegerFactory(
    builder: EntityManagerFactoryBuilder, @Qualifier("firstDatasource") dataSource: DataSource?
): LocalContainerEntityManagerFactoryBean? {
    return builder.dataSource(dataSource).packages("xxx").persistenceUnit("xxx")
        .build()
}

@Bean(name = ["firstTransactionManager"])
fun firstTransactionManager(
    @Qualifier("firstEntityManegerFactory") barEntityManagerFactory: EntityManagerFactory?
): PlatformTransactionManager? {
    return JpaTransactionManager(barEntityManagerFactory!!)
 }
}

Second Database Configuration

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
basePackages = ["xxx.xxx.domains.portadapter.repository.secondRepository"],
entityManagerFactoryRef = "secondEntityManegerFactory",
transactionManagerRef = "secondTransactionManeger"
)
class secondDatabaseConfiguration {

@Primary
@Bean(name = ["secondDatasource"])
@ConfigurationProperties(prefix = "spring.datasource")
fun dataSource(): DataSource? {
    return DataSourceBuilder.create().build()
}

@Primary
@Bean(name = ["secondEntityManegerFactory"])
fun secondEntityManegerFactory(
    builder: EntityManagerFactoryBuilder, @Qualifier("secondDatasource") dataSource: DataSource?
): LocalContainerEntityManagerFactoryBean? {
    return builder.dataSource(dataSource).packages("xxx").persistenceUnit("xxx")
        .build()
}

@Primary
@Bean(name = ["secondTransactionManager"])
fun secondTransactionManager(
    @Qualifier("secondEntityManegerFactory") barEntityManagerFactory: EntityManagerFactory?
): PlatformTransactionManager? {
     return JpaTransactionManager(barEntityManagerFactory!!)
 }
}

Repository for agegroup that persist the entity on the first database

interface FirstAgeGroupsRepository : JpaRepository<AgeGroups, Int> {
fun findBySispacId(sispacId : String): Optional<AgeGroups>
}

Repository for agegroup that persist the entity on the second database

interface SecondAgeGroupsRepository : JpaRepository<AgeGroups, Int> {
fun findBySispacId(sispacId : String): Optional<AgeGroups>
}

In this controller for example I would have to instantiate each of the repositories and define through a conditional which one I should use

@Service
class AgeGroupsController(
 @Autowired val scoobyAgeGroupRepository: ScoobyAgeGroupsRepository,
 @Autowired val fisiaAgeGroupRepository: FisiaAgeGroupsRepository
) {

fun saveAgeGroup(body: AgeGroupDto): ResponseDto {
    return try {
        val ageGroupFound: Optional<AgeGroups> = fisiaAgeGroupRepository.findBySispacId(body.sispacId)

        if (!ageGroupFound.isEmpty) {
            return ResponseDto(HttpStatus.FOUND, "Faixa etária ja existe, persistencia não realizada.")
        }

        val formatter = SimpleDateFormat("yyyy-MM-dd")
        val ageGroup = AgeGroups(
            body.ageGroupName, formatter.parse(body.createdAt), body.sispacId,
            formatter.parse(body.updatedAt)
        )
        body.ageGroupIndexDescription?.let { ageGroup.setAgeGroupIndexDescription(it) }

        fisiaAgeGroupRepository.save(ageGroup)

        ResponseDto(HttpStatus.OK, "Faixa etária criada com sucesso!")
    } catch (erro: Exception) {
        print(erro.message)
        ResponseDto(HttpStatus.BAD_REQUEST, erro.message)
    }
 }
}

If anyone knows any tutorial or something related that can help me improve this code because if one day I want to add another database I will have to duplicate the repositories then add one more conditional in each of my controllers.

I'm open to suggestions

CodePudding user response：

Adding this as an answer because I don't have enough reputation to comment. Here are a few ways to improve this code:

• Spring uses a Controller/Service/Repository pattern. To make this code more readable/maintainable, cut the controller logic into a separate @Service class. Use Controllers for strictly front-facing logic. As a general rule, if you need to access repositories in your code, chances are it belongs in a service.
• Nitpick: the @Autowired dependencies can be private

Edit: removed microservices reference

CodePudding user response：

You can cut a little bit on the code if you create a single repository interface with all your extra methods and then extend it in the dedicated packages so that different JPA configurations could take them from there.

Say, in the package where JPA doesn't search for repositories:

interface AgeGroupsRepository : JpaRepository<AgeGroups, Int> {
    fun findBySispacId(sispacId : String): Optional<AgeGroups>
}

In the package for the first database repositories:

interface FirstAgeGroupsRepository : AgeGroupsRepository {
}

In the package for the second database repositories:

interface SecondAgeGroupsRepository : AgeGroupsRepository {
}

However, I think you are missing something important in your description. You will in fact need not only separate repositories, but also separate services. This is because you'll need to annotate service methods with either @Transactional("firstTransactionManager") or @Transactional("secondTransactionManager") depending on the used repository. So you either give up on the Spring transaction management and roll your own or duplicate the service too.

As you see, Spring Boot architecture is optimized for the single datasource use case. Since your use case is different, you might fare better with a technology that is lower level than Spring Data JPA: jOOQ, Spring JdbcTemplate or plain JDBC. You will have more possibilities for removing code duplication for sure.