how to use Hibernate for two different schemas in a single database

Question

I want to use two different schema in database, each schema has same set of Tables but data differs. How to use hibernate and point to two different schema.I am new to the hibernate.Please provide me the links.

Thanks in advance

Answer 1

You can specify it by schema element while defining table for your entity.

@Table(name="TABLE_NAME", schema="SCHEMA_NAME")

Else, you can use separate EntityManager pointing to respective schema & then use the same entity, as their structure is similar.

---

Edit : You can have separate configuration files for each schema & then build SessionFactory from it, below is some pseudo-code for it.

SessionFactory sf_1 = new  Configuration().configure("schema1config.cfg.xml").buildSessionFactory();
SessionFactory sf_2 = new Configuration().configure("schema2config.cfg.xml").buildSessionFactory();

session_1 = sf_1.openSession();  //-- Similarly for other

You can refer this link for further details to map multiple schema, but it isn't hibernate specific.

Answer 2

In your config file :

<hibernate-configuration>
       <session-factory>
        <property name="hibernate.connection.driver_class">com.ibm.db2.jcc.DB2Driver</property>
        <property    name="hibernate.connection.url">jdbc:db2://localhost:50000/TEST</property>
        <property name="hibernate.connection.username">user</property>
        <property name="hibernate.connection.password">pwd</property>
        <property name="hibernate.dialect">org.hibernate.dialect.DB2Dialect</property>
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
        <property name="hbm2ddl.auto">auto </property>
        <mapping class="com.test.db2procedure.User"/>
        <mapping class="com.test.db2procedure.User1"/>
      </session-factory>
     </hibernate-configuration>

In your Entity Class :

@Entity
@Table(name="SCHEMA.USER") ////Here you can specify your schema name. Here my     schema name is schema
public class User implements Serializable {

private String city;

private String firstname;
enter code here
@Id
@Column(name="ID")
private String id;

private String lastname;

public User() {
}

public String getCity() {
    return this.city;
}

public void setCity(String city) {
    this.city = city;
}
}

For the second class, you have to do this :

@Entity
@Table(name="SCHEMA1.USER") //Here you can specify your schema name. Here my schema name is schema1
 public class User1 implements Serializable {
    private String city;

    private String firstname;

    @Id
    @Column(name="ID")
    private String id;

    private String lastname;

    public User1() {
    }

    public String getCity() {
        return this.city;
    }

    public void setCity(String city) {
        this.city = city;
    }
}

To test this :

public class Test{
    public static void main(String args[]){
         SessionFactory factory ;
         Configuration cfg = new Configuration();
         cfg.configure("hibernate.cfg.xml");          
         List<User> user=new ArrayList<User>();
         factory = cfg.buildSessionFactory();
         Session session = factory.openSession();        
         String hql = "select u2.city from User u1,User1 u2 where u1.id=u2.id";
         Query query = session.createQuery(hql);

         List results = query.list();
         System.out.println("User City: "+results.get(0).toString());
    }
}

In the above Test.class execute result set combined from both schema and schema1

Answer 3

Thanks for all your response, I would like to add one more point to the findings

Scenario: Table with same name in two schema

1. The name we give in table property of @Column annotation should match with the name we give in @Table and @SecondaryTable annotation.
2. Hence when we prepend the schema name in @Table, make sure we do it in @Column

---

@Table(name="schema1.table")
@SecondaryTables({
    @SecondaryTable(name = "schema2.table")
})

---

@Column( name = "col1", table = "schema2.table")

---

Note: You don't have to specify table property for the primary table column