[FIXED] Spring saveAndFlush() not working with secondary data source

Issue

I set up a small Spring Boot project that I use for certain monitorings. It has various endpoints to receive data via webservice calls and is supposed to store them into two different databases.

While everything works fine forthe primary data base, I cannot save data to the secondary database. Reading the existing data is working fine, but I cannot save anything. I'm always getting an exception:

javax.persistence.TransactionRequiredException: no transaction is in progress

This is the coding:

Everything related to Light, Motion and Temperature (LMT) is in one database. The information for Power (P) is stored in a second database (where I have the issues with).

My package set up is as follows:

- [Configuration]
- [Controller]
--- [Light]
--- [Motion]
--- [Temperature]
--- [Power]
- [Model]
--- [LMT-Models]
--- [P-Models]
- [Repository]
--- [lmt]
--- [PowerMonitoring]

In my Configuration package I have two persistence classes, one for LMT and one for Power, both handling the database connections for the respective repositories. The LMT one is the primary one, the Power one the secondary (or rather the non-primary):

Primary data source:

@Configuration
@EnableJpaRepositories(
        basePackages = "com.delta.Monitoring.Repository.lmt",
        entityManagerFactoryRef = "entityManagerFactory",
        transactionManagerRef = "lmtTransactionManager"
)
public class LmtPersistenceConfiguration {
    @Bean( name = "lmtDataSourceProperties")
    @ConfigurationProperties("spring.datasource")
    @Primary
    public DataSourceProperties lmtDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean( name = "lmtDataSource")
    @ConfigurationProperties("spring.datasource.configuration")
    @Primary
    public DataSource lmtDataSource() {
        return lmtDataSourceProperties().initializeDataSourceBuilder()
                .type(BasicDataSource.class).build();
    }

    @Bean(name = "entityManagerFactory")
    @Primary
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(lmtDataSource())
                .packages(new String[]{"com.delta.Monitoring.Model.lmt"})
                .build();
    }

    @Bean (name = "lmtTransactionManager")
    @Primary
    public PlatformTransactionManager lmtTransactionManager(
            final @Qualifier("entityManagerFactory") LocalContainerEntityManagerFactoryBean lmtEntityManagerFactory) {
        return new JpaTransactionManager(lmtEntityManagerFactory.getObject());
    }

}

Secondary data source:

@Configuration
@EnableJpaRepositories(
        basePackages = "com.delta.Monitoring.Repository.PowerMonitoring",
        entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "powerTransactionManager"
)
public class PowerPersistenceConfiguration {

    @Bean( name = "powerDataSourceProperties")
    @ConfigurationProperties("spring.powerdatasource")
    public DataSourceProperties powerDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean( name = "powerDataSource")
    @ConfigurationProperties("spring.powerdatasource.configuration")
    public DataSource powerDataSource() {
        return powerDataSourceProperties().initializeDataSourceBuilder()
                .type(BasicDataSource.class).build();
    }

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(powerDataSource())
                .packages(new String[]{"com.delta.Monitoring.Model.PowerMonitoring"})
                .build();
    }

    @Bean( name = "powerTransactionManager")
    public PlatformTransactionManager powerTransactionManager(
            final @Qualifier("entityManagerFactory") LocalContainerEntityManagerFactoryBean powerEntityManagerFactory) {
        return new JpaTransactionManager(powerEntityManagerFactory.getObject());
    }
}

This is what one of the Power-Repositories looks like. Since I'm not doing anything fancy with it (yet), I just extended the JpaRepository:

@Transactional("powerTransactionManager")
public interface PowerDaysRepository extends JpaRepository<PowerDay, PowerDayId> {


}

And finally, the controller, where I always get the exception:

@RestController
@RequestMapping("power")
public class PowerController {

    private static final Logger myLog = LogManager.getLogger(PowerController.class);

    @Autowired
    PowerDayDetailsRepository powerDayDetailsRepository;

    @PostMapping("/powerdaydetails")
    public boolean insertPowerDayDetails(@RequestBody List<PowerDayDetails> powerDayDetails){
        myLog.info("POST /power/powerdaydetails");
        powerDayDetailsRepository.deleteAll();
        //List<PowerDayDetails> lines = powerDayDetailsRepository.saveAllAndFlush(powerDayDetails);
        List<PowerDayDetails> lines = powerDayDetailsRepository.saveAll(powerDayDetails);

        myLog.info("Update size: " + lines.size());
        myLog.info("Received data: " + powerDayDetails.size());

        return lines.size() == powerDayDetails.size();
    }
}

When I call the /powerdaydetails-endpoint I wanted to save the respective data in the database. First delete all and then save the newly received data. When I use the saveAll() method, I am not getting the exception, however also nothing in stored in the database. This is the log:

2022-10-20 10:00:12.067 INFO 22842 --- [http-nio-8321-exec-88] c.m.H.Controller.Power.PowerController : POST /power/powerdaydetails

2022-10-20 10:00:12.639 INFO 22842 --- [http-nio-8321-exec-88] c.m.H.Controller.Power.PowerController : Update size: 582

2022-10-20 10:00:12.639 INFO 22842 --- [http-nio-8321-exec-88] c.m.H.Controller.Power.PowerController : Received data: 582

When I use the saveAllAndFlush() method, the above-mentioned exception occurs and - needless to say - also nothing gets stored in the database.

I've read a lot about the @Transactional and also the @Modifying topic, but that also never solved my problem. The primary data source and the respective repositories and controllers work perfectly and save all the data directly. It just seems that I have made a mistake with the second data source or haven't told Spring yet where/how to find a transaction for this.

Solution

It's quite simple and was in front of my eyes the whole time: In the secondary data source configuration I named the entity manager factory bean "entityManagerFactorySecondary" but in the transaction manager method I only referred to "entityManagerFactory".

This is how they must look like:

return builder
            .dataSource(powerDataSource())
            .packages(new String[]{"com.delta.Monitoring.Model.PowerMonitoring"})
            .build();
}

@Bean( name = "powerTransactionManager")
public PlatformTransactionManager powerTransactionManager(
        final @Qualifier("entityManagerFactorySecondary") LocalContainerEntityManagerFactoryBean powerEntityManagerFactory) {
    return new JpaTransactionManager(powerEntityManagerFactory.getObject());
}

Answered By - Rick
Answer Checked By - Timothy Miller (JavaFixing Admin)