common configuration for multiple databases with spring boot

Question

My Project has multiple databases having one master database and 35 other databases (having same schema and same configuration)

Now currently we are using c3p0 connection pool and using jdbc for getting connections from pool.

I had tried different ways for moving existing implementation into hibernate/JPA but it leads to heavy boilerplate code where i need to declare datasources and dao for every db schema. (https://o7planning.org/en/11653/using-multiple-datasources-with-spring-boot-and-jpa)

I want to design the db flow in such a way that when a request for connection has been asked my db config class should be able to return the JPA connection and I could do respective functionality on my service class

Let me know if i can make my query understandable or any flaw in my approach

Answer 1

I found mulit tenancy solution by implementing using Spring Boot AbstractRoutingDatasource.

I had defined one national database (also as default db) and two state databases.

1) I had defined the all db datasources in following class.

public class DatabaseLookupMap {

    public static Map<Object,Object> getDataSourceHashMap() {
        Map<Object,Object> dbMap = new HashMap<Object, Object>();

        DriverManagerDataSource dnational = new DriverManagerDataSource();
        dnational.setDriverClassName("org.postgresql.Driver");
        dnational.setUrl("jdbc:postgresql://127.0.0.1:5432/master");
        dnational.setUsername("postgres");
        dnational.setPassword("root");

        DriverManagerDataSource dstate1 = new DriverManagerDataSource();
        dstate1.setDriverClassName("org.postgresql.Driver");
        dstate1.setUrl("jdbc:postgresql://127.0.0.1:5432/b_22");
        dstate1.setUsername("postgres");
        dstate1.setPassword("root");

        DriverManagerDataSource dstate2 = new DriverManagerDataSource();
        dstate2.setDriverClassName("org.postgresql.Driver");
        dstate2.setUrl("jdbc:postgresql://127.0.0.1:5432/b_18");
        dstate2.setUsername("postgres");
        dstate2.setPassword("root");

        //dbnational will be marked as default state when application starts up
        dbMap.put(0, dnational);
        dbMap.put(22, dstate1);
        dbMap.put(18, dstate2);

        return dbMap;
    }
}

2) Then, While defining datasource bean, I had bind these datasources to custom AbstractRoutingDatasource class

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@ComponentScan(basePackages = "org.nic")
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "org.nic")
public class PrintcardjobApplication {

    public static void main(String[] args) {
        SpringApplication.run(PrintcardjobApplication.class, args);
    }

    //this is used to define entityManagerFactory to the application
    @Bean
    public DataSource dataSource() {
        ClientDataSourceRouter router = new ClientDataSourceRouter();

        router.setTargetDataSources(DatabaseLookupMap.getDataSourceHashMap());
        return  router;
    }
}

3) Then, following is the definition of our custom AbstractRoutingDatasource class

public class ClientDataSourceRouter extends AbstractRoutingDataSource{

    public static Integer currentState;


    @Override
    protected Object determineCurrentLookupKey() {

        //At application startup, current state is null
        if(currentState != null) {
            return currentState;
        }

        //in this scenario, nhps schema should be returned to application 
        return 0;
    }


}

This is the all configuration is required to setup multtenant application

Lastly, we define our Controller, Service and Entity class

@RequestMapping("/testjpa/{statecode}")
    public @ResponseBody String testJPAController(@PathVariable("statecode") String state) {
        System.out.println("statecode=>>>"+state);

        try {
            ClientDataSourceRouter.currentState = Integer.parseInt(state);

            testService.testjpa(state);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return "checking";
    }

Service Class (Initially, I was using only JpaRepository for getting the records. Later, I found that i can also use EntityManagerFactory for executing HQL Queries instead of using JpaRepository. So, its upto to the developer whatever they find it convenient)

@Service
@Transactional
public class TestService {

    @Autowired
    private RecordMasterDao dao;

    @Autowired
    private EntityManagerFactory factory;

    public void testjpa(String statecode) throws Exception {

        EntityManager em = factory.createEntityManager();

        Query query = em.createQuery("select a from RecordMasterEntity a where a.nhaid = :pmrssmid");
        query.setParameter("pmrssmid", "PLSNZ26M");

        RecordMasterEntity result = (RecordMasterEntity) query.getSingleResult();

        System.out.println(result);

        em.close();
        /*Optional<RecordMasterEntity> entity = dao.findById("PLSNZ26M");

        if(entity.isPresent()) {
            System.out.println(entity.get());   
        }
        else {
            System.err.println("no record found");

        }*/


    }
}

Finally, the Entity Class

    @Entity
    @Table(name = "tablename")
    public class RecordMasterEntity {

        @Id
        private String myid;

        private String scode;

        private String sname;

        private String name_eng;

        private String yearofbirth;

        private String gender;

        private String photo;

        private String hhidtype;

        getters and setters

}

Code can be optimized by placing the db configuration on properties file.

I hope you will find the answer helpful

References:

https://www.baeldung.com/spring-abstract-routing-data-source

https://javadeveloperzone.com/spring-boot/spring-boot-jpa-multi-tenancy-example/