Hibernate with multiple users(databases) ???

msj4u · **Joined:** Wed Apr 25, 2007 11:44 pm **Posts:** 59

Hi,

I am a beginner user of hibernate; saw the hibernate tutorial and also implemented it; but my question is that how to connect the hibernate with multiple users

i mean there is a single session factory and which uses single username and password but in my case there are multiple users in oracle (means that there are multiple databases) so anybody guide me that what should i do or how in hibernate i can handle provided that my transactions are transparent (means a single transaction with multiple databases)

Thanks

dharmendra.pandey · **Posted:** Thu Apr 26, 2007 12:03 am

Hi msj4u,

If i am not wrong then you are talking about multiple schema in Data Base.Then You could give grant to one schema .This way you could use all schema by one user

himawan · **Joined:** Sat Aug 19, 2006 6:31 pm **Posts:** 139

In oracle, a user is considered a schema.

So for example if you connect as user "user1" and you specify hibernate.default_schema="user2" all your tables will be qualified with user2.

SELECT * from tablename... would become SELECT * from user2.tablename... for example.

I think with hibernate you can only define 1 default schema. If you want to read tables from multiple users for Oracle, you are going to have to manually qualify your queries with the user name.

msj4u · **Joined:** Wed Apr 25, 2007 11:44 pm **Posts:** 59

thanks dude that what i got from the Google related to oracle

but what to do in the hibernate

i cant access the another schema from the other one. what if those user are also not on one oracle server i mean u can imagine like there are different schemas related to different products (these might be different databases oracle, mysql, ...) and my program uses some of those tables

now what to do

himawan · **Joined:** Sat Aug 19, 2006 6:31 pm **Posts:** 139

Well if you use Hibernate's EJB3 you can specify the schema name in the @Table annotation for each of your annotated POJO.

http://www.hibernate.org/hib_docs/ejb3- ... Table.html

If you use hibernate mapping files, you can also specify a schema for each <class...> element

http://www.hibernate.org/hib_docs/v3/re ... tion-class

msj4u · **Joined:** Wed Apr 25, 2007 11:44 pm **Posts:** 59

still not got it

my hibernate xml is

Code:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

   <session-factory>
      <property name="connection.username">db1</property>
      <property name="connection.url">
         jdbc:mysql://localhost:3306/db1
      </property>
      <property name="dialect">
         org.hibernate.dialect.MySQLDialect
      </property>
      <property name="connection.password">db1</property>
      <property name="connection.driver_class">
         com.mysql.jdbc.Driver
      </property>
      <mapping resource="hibernate/mappings/Table1.hbm.xml" />
      <mapping resource="hibernate/mappings/Table2.hbm.xml" />

   </session-factory>

</hibernate-configuration>

and my mapping for table1 is

Code:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="hibernate.mappings.Table1" table="table1" >
        <id name="col1" type="java.lang.Integer">
            <column name="col1" />
            <generator class="assigned" />
        </id>
        <property name="col2" type="java.lang.String">
            <column name="col2" length="65535" />
        </property>
    </class>
</hibernate-mapping>

and for table2 which is not in db1

Code:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="hibernate.mappings.Table2" table="table2" >
        <id name="col1" type="java.lang.Integer">
            <column name="col1" />
            <generator class="assigned" />
        </id>
        <property name="col2" type="java.lang.String">
            <column name="col2" length="65535" />
        </property>
    </class>
</hibernate-mapping>

now what to do ?

msj4u · **Joined:** Wed Apr 25, 2007 11:44 pm **Posts:** 59

hellooooo anybody there

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

I don't think you can have each user have it's own Hibernate session (I could be wrong though.) You might want to look at the architecture issues you will be faced with by having just one "user" for all your DB access...

msj4u · **Joined:** Wed Apr 25, 2007 11:44 pm **Posts:** 59

lets say that the design is ok and there are some security issue like the second table is from some other company database and they only provide a separate table and user for crud operations.

so what should i do in this scenario i mean there are things like two phase commit and three phase commit or i hibernate how can i handle this.

msj4u · **Joined:** Wed Apr 25, 2007 11:44 pm **Posts:** 59

what about the thing like jta or something else is there a way to attach the jta with hibernate so that my transactions are atomic throughout all the users/databases ?

msj4u · **Joined:** Wed Apr 25, 2007 11:44 pm **Posts:** 59

so i assume that there is no solution to my problem ?