Specify which datasource to use in service/dao when multiple datasources

Question

I need to use 2 different mysql database in my project. To do this, I added a second datasource in my JDBC config file. I would like to specify in my services or DAO (I read it can be done on both) which datasource it has to access.

I tried to specify in @Transactional on top of my services which DataSourceTransactionManager it should use, but it does not work. The first datasource is working well, and when I switch the @Primary annotation on the second datasource, it works well too, so it's not a problem specific to each database configuration.

JDBCConfig.java

@Configuration
@PropertySource("classpath:jdbc.properties")
public class JDBCConfig {

    @Value("${bonecp.url}")
    private String jdbcUrlPrm;

    @Value("${bonecp.username}")
    private String jdbcUsernamePrm;

    @Value("${bonecp.password}")
    private String jdbcPasswordPrm;

    @Value("${bonecp.driverClass}")
    private String driverClassPrm;

    @Value("${bonecp.moteurFormulaire.url}")
    private String jdbcUrlMoteurFormulaire;

    @Value("${bonecp.moteurFormulaire.username}")
    private String jdbcUsernameMoteurFormulaire;

    @Value("${bonecp.moteurFormulaire.password}")
    private String jdbcPasswordMoteurFormulaire;

    @Value("${bonecp.moteurFormulaire.driverClass}")
    private String driverClassMoteurFormulaire;

    @Value("${bonecp.idleMaxAgeInMinutes}")
    private Integer idleMaxAgeInMinutes;

    @Value("${bonecp.idleConnectionTestPeriodInMinutes}")
    private Integer idleConnectionTestPeriodInMinutes;

    @Value("${bonecp.maxConnectionsPerPartition}")
    private Integer maxConnectionsPerPartition;

    @Value("${bonecp.minConnectionsPerPartition}")
    private Integer minConnectionsPerPartition;

    @Value("${bonecp.partitionCount}")
    private Integer partitionCount;

    @Value("${bonecp.acquireIncrement}")
    private Integer acquireIncrement;

    @Value("${bonecp.statementsCacheSize}")
    private Integer statementsCacheSize;

    @Primary
    @Bean(name = "prmDataSource", destroyMethod = "close")
    public DataSource prmDataSource() {
        BoneCPDataSource dataSource = new BoneCPDataSource();
        dataSource.setDriverClass(driverClassPrm);
        dataSource.setJdbcUrl(jdbcUrlPrm);
        dataSource.setUsername(jdbcUsernamePrm);
        dataSource.setPassword(jdbcPasswordPrm);
        dataSource.setIdleConnectionTestPeriodInMinutes(idleConnectionTestPeriodInMinutes);
        dataSource.setIdleMaxAgeInMinutes(idleMaxAgeInMinutes);
        dataSource.setMaxConnectionsPerPartition(maxConnectionsPerPartition);
        dataSource.setMinConnectionsPerPartition(minConnectionsPerPartition);
        dataSource.setPartitionCount(partitionCount);
        dataSource.setAcquireIncrement(acquireIncrement);
        dataSource.setStatementsCacheSize(statementsCacheSize);
        return dataSource;
    }

    @Bean(name = "moteurFormulaireDataSource", destroyMethod = "close")
    public DataSource moteurFormulaireDataSource() {
        BoneCPDataSource dataSource = new BoneCPDataSource();
        dataSource.setDriverClass(driverClassMoteurFormulaire);
        dataSource.setJdbcUrl(jdbcUrlMoteurFormulaire);
        dataSource.setUsername(jdbcUsernameMoteurFormulaire);
        dataSource.setPassword(jdbcPasswordMoteurFormulaire);
        dataSource.setIdleConnectionTestPeriodInMinutes(idleConnectionTestPeriodInMinutes);
        dataSource.setIdleMaxAgeInMinutes(idleMaxAgeInMinutes);
        dataSource.setMaxConnectionsPerPartition(maxConnectionsPerPartition);
        dataSource.setMinConnectionsPerPartition(minConnectionsPerPartition);
        dataSource.setPartitionCount(partitionCount);
        dataSource.setAcquireIncrement(acquireIncrement);
        dataSource.setStatementsCacheSize(statementsCacheSize);
        return dataSource;
    }

    @Primary
    @Bean(name="prmTransactionManager")
    @Autowired
    DataSourceTransactionManager prmTransactionManager(@Qualifier("prmDataSource") DataSource prmDatasource) {
        DataSourceTransactionManager txm  = new DataSourceTransactionManager(prmDatasource);
        return txm;
    }

    @Bean(name="transactionManager")
    @Autowired
    DataSourceTransactionManager transactionManager(@Qualifier("moteurFormulaireDataSource") DataSource moteurFormulaireDatasource) {
        DataSourceTransactionManager txm  = new DataSourceTransactionManager(moteurFormulaireDatasource);
        return txm;
    }

}

CoreConfig.java that is scanned by Initializer

@Configuration
@EnableAutoConfiguration
@ComponentScan({ "san.prm.core.service",
        "san.prm.core.helper",
        "com.santeos.commons.security.password"
})
@Import({ JDBCConfig.class, MailConfiguration.class, SecurityConfig.class })
@PropertySource("classpath:app.properties")
public class CoreConfig {

}

FormulaireServiceImpl.java

@Service("formulaireService")
@Transactional("transactionManager")
public class FormulaireServiceImpl extends AbstractBusinessService<Formulaire> implements FormulaireService {

    @Autowired
    private FormulaireDAO formulaireDAO;

    @Override
    public List<Formulaire> getFormsByUniqueId(String uniqueId) {
        return formulaireDAO.findAllByUniqueId(uniqueId);
    }

FormulaireDAO.java

public interface FormulaireDAO extends GenericEntityDAO<Formulaire> {

    List<Formulaire> findAllByUniqueId(String uniqueId);

}

Has anyone any idea so I could do that? Thank you

Looks like your question has been answered here - https://stackoverflow.com/questions/48954763/spring-transactional-with-a-transaction-across-multiple-data-sources — Ajay Kumar, Jul 11 '19 at 15:41
The problem is not the same. I don't want to use transaction managers in chains, but just separately. It is written that "The methods have to be in different beans to be able to use different transaction managers.", it is my case but only the transaction manager with @Primary is use, the second one is ignored. — Amandine Watrelos, Jul 12 '19 at 09:35

score 0 · Answer 1 · answered Jul 11 '19 at 17:59

0

How are you writing to the database? What technology are you using - JDBC or JPA? You need to give qualified names to your Datasource/EntityManager/EMFactory beans and use that specified name to hook into your consuming code. If you have multiple datasources, one needs to be @Primary for sure. And give different names to both DS, and use the one you want. Hope this helps. If you can paste more code here related to DS, I can check.

answered Jul 11 '19 at 17:59

Gayatri

152
3
12

The code related to datasources is pasted in JDBCConfig file. I created a second datasource and a second transaction manager for this DS. Then I added @Transactional("nameOfTransactionManager") on my Service (and tried on functions too) but it seems like it just ignores it and use the first transaction manager with the @Primary instead of the one I specified on the service. – Amandine Watrelos Jul 12 '19 at 09:26
Can you just give it a try changing the name of "transactionManager" to something more custom like "moteurFormulaireTransactionManager"? Coz I think "transactionManager" is the default name that Spring gives. This may not work as you have named them, but it could be worth trying. Rest all of you code looks just fine. I hope all the beans are coming under component scan. – Gayatri Jul 12 '19 at 18:40
It was name like that at the beginning, but I had a spring error saying that atleast one bean had to be named "transactionManager". I guess beans are well under component scan because when I switch the default datasource (with the@Primary), I get what I am suppose to get. The problem is still that only the transactionManager by default (with @Primary) is used, and the second one is ignored even if I specify it by its name.. and I don't understand why. – Amandine Watrelos Jul 15 '19 at 13:26

Specify which datasource to use in service/dao when multiple datasources

1 Answers1