-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 20 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: NHibernate with Multiple Databases
PostPosted: Tue Jul 18, 2006 2:04 am 
Beginner
Beginner

Joined: Fri Sep 02, 2005 12:13 pm
Posts: 44
Location: Denver, CO
I've uploaded a new article on CodeProject.com concerning the use of NHibernate with multiple databases. As I post articles like this more as a learning experience than anything else, I welcome any and all feedback from the NHibernate community concerning my approach. The article can be found at http://www.codeproject.com/useritems/NHibernateMultipleDBs.asp.

Billy McCafferty


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jul 18, 2006 7:16 am 
Senior
Senior

Joined: Sat May 14, 2005 8:40 am
Posts: 130
Have you considered using the NHibernate facility of Castle? It also allows using multiple databases in an easy way.

_________________
Cuyahoga


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jul 18, 2006 12:34 pm 
Beginner
Beginner

Joined: Fri Sep 02, 2005 12:13 pm
Posts: 44
Location: Denver, CO
I've tried the Castle NHibernate facility and thought it was a pretty good setup but had a lot of difficulties getting Open-Session-in-View working with it - i.e. I couldn't get lazy loading working in a web env't. Perhaps they've addressed this issue since it's been about six months since I tried it.

It also didn't work with .NET 2.0 generics which was a major drawback to me. I expect that they'll address this once NHibernate supports generics natively...I'll be sure to check it out again at that time.

Billy


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jul 18, 2006 1:18 pm 
Pro
Pro

Joined: Fri Nov 19, 2004 5:52 pm
Posts: 232
Location: Chicago, IL
I always thought it should be possible to use a single session factory as long as all the databases were on the same database server. So far as I know, this isn't possible though, at least not with Hibernate. I would assume that NHibernate is the same. I guess maybe transactions wouldn't work across databases?

The reason I was thinking about this is that I have several applications that all have a Person table. I was thinking that maybe I would put that table into a Common database and then have other databases for the individual applications that had their specific data. As far as I know you can't really do this with N/Hibernate unless as you pointed out, you use multiple sessions/factories.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jul 18, 2006 1:31 pm 
Pro
Pro

Joined: Fri Nov 19, 2004 5:52 pm
Posts: 232
Location: Chicago, IL
Accidentally resubmitted this, please ignore...


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jul 19, 2006 12:37 am 
Beginner
Beginner

Joined: Fri Sep 02, 2005 12:13 pm
Posts: 44
Location: Denver, CO
Yeah, with NHibernate, you can only apply a transaction to a session factory - so a single transaction can't encapsulate multiple session factories.

Billy


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 20, 2006 4:20 pm 
Newbie

Joined: Fri Mar 24, 2006 3:45 pm
Posts: 18
jemiller wrote:
I always thought it should be possible to use a single session factory as long as all the databases were on the same database server. So far as I know, this isn't possible though, at least not with Hibernate. I would assume that NHibernate is the same. I guess maybe transactions wouldn't work across databases?

The reason I was thinking about this is that I have several applications that all have a Person table. I was thinking that maybe I would put that table into a Common database and then have other databases for the individual applications that had their specific data. As far as I know you can't really do this with N/Hibernate unless as you pointed out, you use multiple sessions/factories.


BUT:

You CAN use a single session factory for two databases in the same server (and maybe in a separate server - see final note), and you CAN refactor these common classes in its own assembly.

SOLUTION:
You just specify the schema in each class mapping file including the Database name in it.

EXAMPLE:

Using your example, you can put Persons table in CommonDB (I am asumming a SQL Server 2000 or 2005 instance) and then create a dll project for Common things. Here goes Person code (in VB):

Code:
Public Class Person
    Private _id As Guid
    Private _name As String

    Public Overridable ReadOnly Property Id() As Guid
        Get
            Return _id
        End Get
    End Property

    Public Overridable Property Name() As String
        Get
            Return _name
        End Get
        Set(ByVal value As String)
            _name = value
        End Set
    End Property
End Class


Here's Person mapping:

Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0"
   schema="CommonDB.dbo"
   assembly="Common"
   namespace="Common"
   default-access="field.camelcase-underscore" >
    <class name="Person" table="Persons" >
        <id name="Id" column="PersonId" >
            <generator class="guid.comb" />
        </id>
        <property name="Name" />
    </class>
</hibernate-mapping>


The KEY thing here is to specify schema="CommonDB.dbo".

Then suposing you have a ClientClasses table in a ClientDB database, you create a Client dll that uses the person class:

Code:
Imports Common

Public Class ClientClass
    Private _id As Guid
    Private _person As Person

    Public Overridable ReadOnly Property Id() As Guid
        Get
            Return _id
        End Get
    End Property

    Public Overridable Property Person() As Person
        Get
            Return _person
        End Get
        Set(ByVal value As Person)
            _person = value
        End Set
    End Property
End Class


And you use the mapping:

Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0"
   schema="ClientDB.dbo"
   assembly="Client"
   namespace="Client"
   default-access="field.camelcase-underscore" >
    <class name="ClientClass" table="ClientClasses" >
        <id name="Id" column="ClientClassId" >
            <generator class="guid.comb" />
        </id>
        <many-to-one name="Person" column="PersonId" class="Common.Person, Common" />
    </class>
</hibernate-mapping>


Note the schema attribute! and check that full qualified name has been used in the many-to-one reference.

Next you define in your UI project that uses both Client.dll and Common.dll, an app.config or web.config including something like:

Code:
<configSections>
   <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler,NHibernate" />
</configSections>

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.0">
   <session-factory>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
      <property name="connection.connection_string">Server=SERVERNAME; Database=ClientDB; Integrated Security=SSPI</property>
      <property name="connection.isolation">ReadCommitted</property>
      <property name="dialect">NHibernate.Dialect.MsSql2000Dialect</property>
      <property name="show_sql">false</property>
      <property name="use_reflection_optimizer">true</property>
      <property name="bytecode.provider">lcg</property>
      <mapping assembly="Common"/>
      <mapping assembly="Client"/>
   </session-factory>
</hibernate-configuration>


The important thing here are to include the assemblies in the right order.

Then your client code goes like this:

Code:
Dim cfg As New Cfg.Configuration()
cfg.Configure()
Dim mySessionFactory as ISessionFactory = cfg.BuildSessionFactory()

' It works without transaction
Dim session1 As ISession = mySessionFactory.OpenSession

Dim person1 As New Common.Person
person1.Name = "Hector"
session1.Save(person1)
Dim client1 As New Client.ClientClass
client1.Name = "Client 1"
client1.Person = person1
session1.Save(client1)

session1.Flush()
session1.Close()

' And using transactions (distributed?)
Dim session2 As ISession = mySessionFactory.OpenSession
Dim myTransaction As ITransaction = session2.BeginTransaction

Dim person2 As New Common.Person
person2.Name = "Esther"
session2.Save(person2)
Dim client2 As New ClientSystem.ClientClass
client2.Name = "Client class 2"
client2.Person = person2
session2.Save(client2)

myTransaction.Commit()
session2.Close()


This works because when making SQL statements NHibernate uses the schema in each table to create things like:

Code:
INSERT INTO CommonDB.dbo.Persons Values('(guid1)', 'Hector')
INSERT INTO ClientDB.dbo.ClientClasses Values('(guid2)', '(guid1)')


the SQL above puts each thing in its place.

Final note: I'm not sure but maybe this could be extended to databases in separate servers appending [SERVERNAME] to the schema like:

schema="[anotherDbServer].CommonDb.dbo"

Hector Cruz


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 20, 2006 5:03 pm 
Pro
Pro

Joined: Fri Nov 19, 2004 5:52 pm
Posts: 232
Location: Chicago, IL
Thanks for the info. I didn't realize that you could prefix the schema with the name of the database like that. I wonder if that's SQL server specific? I've been doing something very similar to what you described as far as how I'm organizing things into different assemblies.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jul 24, 2006 2:46 pm 
Beginner
Beginner

Joined: Fri Sep 02, 2005 12:13 pm
Posts: 44
Location: Denver, CO
Very useful info Hector...thanks for the post!

Billy


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 30, 2006 12:21 pm 
Newbie

Joined: Thu Mar 18, 2004 1:48 pm
Posts: 6
My case is a little diferent.

I have a application on the web with 15 clients, each client have a database in sql server 2000 that have exactly the same tables, when the client login a I create a session variable with the database name that I need to connect and use this variable to create the string connection .
In this case I need one session factory for each database ?

thanks in advance


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 30, 2006 12:25 pm 
Beginner
Beginner

Joined: Fri Sep 02, 2005 12:13 pm
Posts: 44
Location: Denver, CO
If they're on different SQL server instances, then yes, you'd need a different session for each one. Otherwise, you should be able to use hectorjcruz' suggestion and talk to each one via one session but with different DB schema's defined in the HBM mapping files. I haven't tried this myself and would be interested in seeing how it works out for you.

Billy


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 30, 2006 1:32 pm 
Newbie

Joined: Thu Mar 18, 2004 1:48 pm
Posts: 6
Thanks for the info.
But, I have hundreds of hbm files.
What you are saying is that i have to duplicate this hbm files for each client ?

thanks


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 30, 2006 1:44 pm 
Beginner
Beginner

Joined: Fri Sep 02, 2005 12:13 pm
Posts: 44
Location: Denver, CO
Ah, I now see what you're saying. The exact same HBM files need to work with different databases. In that case, how about programmatically set the DB schema before the session is created for the given HTTP request? In this way, you'll only need one session per HTTP request and it'll be "tuned" to the proper database.

(In my multi-database applications, each HBM talks to the same database; albeit, some HBM's talk to one database while others talk to another database.)

Billy


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 30, 2006 1:57 pm 
Newbie

Joined: Thu Mar 18, 2004 1:48 pm
Posts: 6
Thanks for your attention, I will try this solucion.

Marco Polo


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 05, 2007 2:53 am 
Newbie

Joined: Tue Jun 05, 2007 2:48 am
Posts: 1
[quote="wmccafferty"]Ah, I now see what you're saying. The exact same HBM files need to work with different databases. In that case, how about programmatically set the DB schema [i]before[/i] the session is created for the given HTTP request? In this way, you'll only need one session per HTTP request and it'll be "tuned" to the proper database.

(In my multi-database applications, each HBM talks to the same database; albeit, some HBM's talk to one database while others talk to another database.)

Billy[/quote]

WAIT, DON'T program. If you set up Linked-servers on the DB, then you could access even 2 different DB's via 1 connection string only by setting different schema. I think, it can be solved this way, but haven't done anything like that by now.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 20 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.