common configuration for multiple databases with spring boot

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/