1

UPDATED

I'm getting an issue with Spring framework while I try to connect to 2 identical databases (with same tables) and then choose one of them to make requests. I'm using for this, 3 config files DbConfig, AbstractRoutingDataSource and an application.properties file. I'm currently not getting any ERRORS, while starting my application, but any time I use my restful webservice I get empty data, check the controller file below.

NOTE: I've followed all these links and still no results:

  1. Baeldung AbstractRoutingDataSource guide
  2. Alexander guide
  3. Dynamic DataSource Routing on Spring website

I'm working on Spring boot v2.0.5.

NOTE: please do NOT answer me if you don't have any ideas about AbstractRoutingDataSource class and how it works.

My application.properties file

spring.jpa.database=mysql
spring.jpa.open-in-view=false

# Main database DEV_database1 
first.datasource.url = jdbc:mysql://localhost:3306/DEV_database1 
first.datasource.username = root 
first.datasource.password =  
first.datasource.driver-class-name = com.mysql.jdbc.Driver

# second database DEV_database2 
second.datasource.url = jdbc:mysql://localhost:3306/DEV_database2 
second.datasource.username = root 
second.datasource.password =  
second.datasource.driver-class-name = com.mysql.jdbc.Driver

spring.jpa.database-platform = org.hibernate.dialect.H2Dialect
spring.jpa.show-sql = true
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
#...

[UPDATE] My DbConfig file

@Configuration
public class DbConfig {

    @Bean
    @Primary
    @ConfigurationProperties("first.datasource")
    public DataSourceProperties firstDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public HikariDataSource firstDataSource() {
        return firstDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("second.datasource")
    public DataSourceProperties secondDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    public HikariDataSource secondDataSource() {
        return secondDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }   

    @Bean
    DataSource dataSources() {

        AbstractRoutingDataSource dataSource = new CustomerRoutingDataSource();

        Map<Object, Object> resolvedDataSources = new HashMap<>();    
        resolvedDataSources.put(DbType.DATASOURCE1, firstDataSource());
        resolvedDataSources.put(DbType.DATASOURCE2, secondDataSource());

        dataSource.setDefaultTargetDataSource(firstDataSource()); // << default
        dataSource.setTargetDataSources(resolvedDataSources);
        return dataSource;
    }   
}

My CustomerRoutingDataSource.java

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
 * AbstractRoutingDatasource can be used in place of standard DataSource implementations and enables a mechanism to
 * determine which concrete DataSource to use for each operation at runtime.
 * 
 * @author fre
 */
public class CustomerRoutingDataSource extends AbstractRoutingDataSource {

    private static final Logger log = LoggerFactory.getLogger(CustomerRoutingDataSource.class);

    @Override
    protected Object determineCurrentLookupKey() {
        log.info(">>> determineCurrentLookupKey thread: {}", Thread.currentThread().getName() );
        log.info(">>> RoutingDataSource: {}", DbContextHolder.getDbType());
        return DbContextHolder.getDbType();
    }
}

My DbContextHolder.java

public class DbContextHolder {

       private static final ThreadLocal<DbType> contextHolder =
                new ThreadLocal<DbType>();
       // set the datasource
       public static void setDbType(DbType dbType) {
           if(dbType == null){
               throw new NullPointerException();
           }
          contextHolder.set(dbType);
       }
       // get the current datasource in use
       public static DbType getDbType() {
          return (DbType) contextHolder.get();
       }
       // clear datasource
       public static void clearDbType() {
          contextHolder.remove();
       }
}

DbType.java

public enum DbType {
    DATASOURCE1, 
    DATASOURCE2,
}

This is how I'm using it in Controller:

My Controller

@RestController
@RequestMapping("/api/user")
public class MembreController extends MainController {

    @Autowired
    MembreRepository membreRepository;

    @GetMapping("/login")
    public ResponseString login(String userName, String password) {

        // setting the datasouce to DATASOURCE1
        DbContextHolder.setDbType(DbType.DATASOURCE1);

        // finding the list of user in DataSource1
        List<Membres> list = membreRepository.findAll();

        // clearing the current datasource
        DbContextHolder.clearDbType();

        for (Iterator<Membres> membreIter = list.iterator(); membreIter.hasNext();) {
            Membres membre = membreIter.next();

            if (membre.getUserName().equals(userName) && membre.getPassword().equals(password)) {
                return new ResponseString("Welcome" + membre.getFirstName() + " " + membre.getLastName());
            }
        }
        return new ResponseString("User not found");
    }

}

my Application.java file

@ComponentScan
@SpringBootApplication
public class Application extends SpringBootServletInitializer {

    /**
     * main method
     * 
     * @param args
     */
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }


    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
        return application.sources(Application.class);
    }

}
10
  • What isn't working? What error do you get? Also you are using Spring Boot then how are you loading that XML configuration file. Looks like you are mixing things you shouldn't be mixing (or at least mixing them in the wrong way). Commented Dec 5, 2018 at 11:24
  • I'm not getting any error actually, do you propose me to change my config in the xml file into a java file? Commented Dec 5, 2018 at 11:28
  • 1
    No I'm wondering why you are using XML and Spring Boot. I also suspect that that is part of your problem by (re)defining things instead of using Spring Boot. Also I would suggest to add spring.jpa.open-in-view=false to your properties, if you don't an Entitymanager and Connection are opened very early before you have time to set the correct value for the AbstractRoutingDataSource. also remove the loading of the application.properties from your xml as that should be handled by Spring Boot already. Also your spring.jpa properties are of no use as you are configuring JPA yourself. Commented Dec 5, 2018 at 11:31
  • ok what about the appContext.xml file? do I have to delete it ? Commented Dec 5, 2018 at 11:34
  • I did what you told me and nothing has been changed :( Commented Dec 5, 2018 at 11:38

1 Answer 1

2

Well I found the solution! The problem was simply with my .properties and my DbConfig files. I just omitted databases parameters from .properties file and added them manually to my DbConfig file. and it works fine:

My Properties file

spring.jpa.database=mysql
spring.jpa.open-in-view=false

spring.jpa.database-platform = org.hibernate.dialect.H2Dialect
spring.jpa.show-sql = true
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
#...

My DbConfig

   @Configuration
public class DbConfig{

    @Autowired
    private Environment env;

    @Bean(name = "dataSource1")
    DataSource dataSource1() {
        return DataSourceBuilder.create()
                .url("jdbc:mysql://localhost:3306/DEV_database1?zeroDateTimeBehavior=convertToNull")
                .driverClassName("com.mysql.jdbc.Driver").username("root").password("").build();
    }

    @Bean(name = "dataSource2")
    DataSource dataSource2() {
        return DataSourceBuilder.create()
                .url("jdbc:mysql://localhost:3306/database2")
                .driverClassName("com.mysql.jdbc.Driver").username("root").password("").build();
    }

    @Primary    
    @Bean(name = "mainDataSource")
    DataSource dataSources() {

        AbstractRoutingDataSource dataSource = new CustomerRoutingDataSource();
        DataSource dataSource1= dataSource1();
        DataSource dataSource2 = dataSource2();
        Map<Object, Object> resolvedDataSources = new HashMap<>();
        resolvedDataSources.put(DbType.DATASOURCE1, dataSource1);
        resolvedDataSources.put(DbType.DATASOURCE2, dataSource2 );
        dataSource.setTargetDataSources(resolvedDataSources);
        dataSource.setDefaultTargetDataSource(dataSource1); // << default
        return dataSource;
    }
}

THATS NOT ALL! I got a new error after this! it says:

***************************
APPLICATION FAILED TO START
***************************

Description:

The dependencies of some of the beans in the application context form a cycle:

   org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration
┌─────┐
|  dataSource defined in class path resource [tn/fre/gestdoc/DbConfig.class]
↑     ↓
|  dataSource1 defined in class path resource [tn/fre/gestdoc/DbConfig.class]
↑     ↓
|  org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker
└─────┘

I followed this: Answer on GitHub and the error has gone, the application works fine now!! I can choose which datasource from my login controller, thanks @M. Deinum

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.