Spring JPA + dynamically switch the datasource between schemas in postgresql

Question

I have to modify design of the existing application according to following manner,

According to above design each project contains it's own schema and that mapping table (project_schema_table) is under main schema. All schemas are under one Postgresql database and in future users will create another new schemas (on demand) for their new projects.

Before displaying login window I have to list all existing projects and once user select his project I have to direct to that project with relevant schema. Other than main schema all other project base schemas are exactly the same.

This is a desktop application developed with latest versions of java technologies JavaFX, Spring and JPA with Postgresql database. Currently it has only one project related schema and I have to introduce main schema and all other subsequent project base schemas.

I have found similar stack overflow post but it didn't contains any solution. Whether I can accomplish this task with Spring AbstractRoutingDataSource ? or is there any other mechanism. Thanks.

Answer 1

The feature you are looking for is called Multi-Tenancy and Hibernate supports this out of the box from version 4.x onwards. Please see this post on how to use this feature.