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
– kostja, Commented Jan 16, 2014 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
– kumar, Commented Jan 16, 2014 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
– Amogh, Commented Jan 21, 2014 at 8:23

Community · Accepted Answer · 2017-05-23 12:23:18Z

2

+50

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.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Jan 22, 2014 at 0:10

Angular University

43.2k15 gold badges79 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kumar Over a year ago

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.

Angular University Over a year ago

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.

Collectives™ on Stack Overflow

Hibernate Table has different columns in different databases

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related