hibernate - spring boot 多个数据库的通用配置

我mulit tenancy通过使用 Spring Boot 实现找到了解决方案AbstractRoutingDatasource。

我已经定义了一个国家数据库（也作为默认数据库）和两个州数据库。

1）我在下面的课程中定义了所有的数据库数据源。

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）然后，在定义数据源bean时，我将这些数据源绑定到自定义AbstractRoutingDatasource类

@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）然后，下面是我们自定义AbstractRoutingDatasource类的定义

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;
    }


}

这是设置多租户应用程序所需的所有配置

最后，我们定义了我们的 Controller、Service 和 Entity 类

@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（最初，我只使用 JpaRepository 来获取记录。后来，我发现我也可以EntityManagerFactory用于执行 HQL 查询而不是使用 JpaRepository。所以，无论他们觉得方便，这都取决于开发人员）

@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");

        }*/


    }
}

最后，实体类

    @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

}

可以通过将 db 配置放在属性文件上来优化代码。

我希望你会发现答案有帮助

参考：

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

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

hibernate - spring boot 多个数据库的通用配置

问题描述

解决方案

推荐阅读