Hibernate Table has different columns in different databases

Question

I have same set of tables in two Databases. But this particular table has different columns, because of some legal constraint. So how to have same .hbm, pojo class to update with the same code in two different kinds of tables. And its not that 2 session factories will solve the problem. As my problem is not to get connected to 2 dbs. But handling CRUD operations.

I have a table in two database.

Lets say EMPLOYEE

 In DB1

NAME 
ADDRESS
DEPT
AGE
QUALIFICATION
EXPERIENCE

In DB2

NAME 
ADDRESS
DEPT

are there. Then how to handle Hibernate operations.

Note : I Can't change the database.

Question is not about connecting to two databases.

Could you please elaborate on the requirements. I do not quite understand what you are trying to achieve or prevent. Do you do have an entity mapped to either of the tables? What is the purpose of having different tables? Does it need to be a single entity? And other relevant info you can provide. — kostja, Jan 16 '14 at 13:14
I have same set of tables in two Databases. But this particular table has different columns, because of some legal constraint. So how to have same .hbm, pojo class to update with the same code in two different kinds of tables. And its not that 2 session factories will solve the problem. As my problem is not to get connected to 2 dbs. But handling CRUD operations. — kumar, Jan 16 '14 at 17:29
I don't think with the same `.hbm` it will be possible because hibernate reads the `hbm` file for table structure. Whatever in hbm hibernate will reflect in DB and DB operations. Correct me if I am wrong — Amogh, Jan 21 '14 at 08:23

score 2 · Accepted Answer · edited May 23 '17 at 12:23

In postgresql, using the schema attribute of the table annotation, this can be done with joined table inheritance:

@Entity
@Table(schema = "`DB2`", name="BASE_EMPLOYEES")
@Inheritance(strategy = InheritanceType.JOINED)
public class BaseEmployee {

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    protected Long id;

    private String name;
    private  String address;
    private  String department;

    ...
}

@Entity
@Table(schema = "`DB1`", name = "FULL_EMPLOYEES")
public class FullEmployee extends BaseEmployee {

    private Integer age;
    private String qualification;
    private String experience;

    ...

}

I have tested this in a postgresql database, and this configuration works OK, there is no need to define multiple entity manager factories and datasources.

Note: In other databases you might not need the quotes around the schema name.

In case the database support synonyms or equivalent notion:

In the case for example of Oracle, the most convenient solution is at the level of the database, by making the tables of one schema visible in another schema via public synonyms.

If there are different schema names per environment:

In that case set the schema names in different orm.xml files, and configure your build process to put only one of those files in the classpath depending on the environment, see this answer.

So in this case which pojo i should use BaseEmployee or FullEmployee what is the criteria i should use for differentiate.Because i can't depend on just schema names, as i have 3 different stages of code & DB servers. — kumar, Jan 22 '14 at 11:16
For some services you cannot return certain confidential data due to legal reasons, so you use BaseEmployee in your queries. If you need the full data then query against FullEmployee. In the service layer you always know if you need one or the other depending on the business logic of the service, so you can choose accordingly and use one or the other in the code, is this correct? If not can you provide the reasons why it's not so. — Angular University, Jan 22 '14 at 11:49

Hibernate Table has different columns in different databases

1 Answers1