Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Using CUBRID with NHibernate - Advanced tutorial (Part I)


June, 2013

Introduction

This tutorial is the continuation of the CUBRID NHibernate Beginner's guide tutorial.

If you have not read yet the Beginner's Guide, we strongly suggest doing it, before proceeding further with this tutorial.

The scope of this next tutorial is to show you some more advanced topics in regard to suing CUBRID and NHibernate together:

  • Tables relationships
  • Dealing with CUBRID special data types

Let’s start!

 

Recap

In the previous NHibernate tutorials we showed you where to get:

  • The NHibernate CUBRID extensions library
  • The CUBRID ADO.NET library
    ...And how to setup a MS Visual Studio project

To recap:

NHibernatedll.jpg

CUBRIDdll.jpg

 

Table relationships

When dealing with databases, one of the most important parts is handling the relationships between database tables. NHibernate makes this task easier because the relationships are mapped only once at the beginning of the development phase of a project, and then NHibernate handles automatically all the operations for you. In the following example we will demonstrate how to work with 1:N and M:N relationships.

 

1:N relationship

To exemplify a 1:N relationship we will use two demodb tables:

  • athlete
  • nation

 

AthleteTable.jpg

NationTable.jpg

 

The mapping files

 

In the Nation.hbm.xml mapping file the relationship is mapped as bag element that has two child elements: akey element which specifies the column in the foreign key table that references the Nation table, and a one-to-many element which specifies the name of the class that references Nation. In addition, notice that in the bag element we have set the cascade attribute. The all-delete-orphan cascade style means that when an object is saved/updated/deleted, NHibernate checks the associations and saves/updates/deletes all the objects found. In additional to that, when an object is removed from the association and not associated with another object (orphaned), NHibernate also deletes it.

More information on NHibernate cascade styles can be found here.

 

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="OneNRelationship"
namespace="OneNRelationship">
<class name="Nation">
<id name="code" >
<column name="code"/>
</id>
<property name="name" not-null="true"/>
<property name="continent"/>
<property name="capital"/>
<bag name="Athletes" cascade="all-delete-orphan" inverse="true">
<key column="nation_code" />
<one-to-many class="Athlete"/>
</bag>
</class>
</hibernate-mapping>

 

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="OneNRelationship"
namespace="OneNRelationship">
<class name="Athlete" table="athlete">
<id name="code">
<generator class="identity" />
</id>
<property name="name"/>
<property name="gender"/>
<property name="nation_code"/>
<property name="athlete_event" column="event"/>
</class>
</hibernate-mapping>

 

The Persistent Classes

In the Nation persistent class, the 1:N relationship is represented as an IList of Athletes. This list will be populated with the corresponding entries from the Athlete table when a nation is queried:

namespace OneNRelationship
{
  class Nation
  {
    virtual public string code { get; set; }
    virtual public string name { get; set; }
    virtual public string continent { get; set; }
    virtual public string capital { get; set; }
    public virtual IList<Athlete> Athletes { get; set; }
  }
}

namespace OneNRelationship
{
  class Athlete
  {
     virtual public int code { get; set; }
     virtual public string name { get; set; }
     virtual public string gender { get; set; }
     virtual public string nation_code { get; set; }
     virtual public string athlete_event { get; set; }
  }
}

Next, we will see how to perform the basic operations (INSERT, SELECT, UPDATE, DELETE) on the relationship we just mapped.

 

INSERT

To perform an INSERT operation, we create a Nation and an Athlete object as usual. What is different here is that the athlete is added to the Athletes list of the nation object. After doing this, when we save a nation, NHibernate will also insert the corresponding athlete because in the mapping file we set the cascade style of the relationship to cascade-all-delete-orphan. 

Nation nation = new Nation
{
   code = "ROM",
   name = "Romania",
   capital = "Bucharest",
   continent = "Europe",
   Athletes = new List<Athlete>()
};
Athlete athlete = new Athlete
{
   name = "Lucian Bute",
   gender = "M",
   nation_code = "ROM",
   athlete_event = "Boxing",
};
nation.Athletes.Add(athlete);
using (ISession session = sessionFactory.OpenSession())
{
    using (ITransactiontx = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Save(nation);
        tx.Commit();
    }
}

 

 

SELECT

A SELECT operation can be performed in the standard way to retrieve both the nations and the athletes.

But what do we do if we want to retrieve a nation together with its corresponding athletes?

To do this, when we will use the Fetch() method, which is an extension of the ISession class.That additional method says: "Hey, NHibernate! Go and get the nation, but also the athletes too!"

using (ISession session = sessionFactory.OpenSession())
{
    foreach (Nation currentNation in session.Query<Nation>().Fetch(b =>b.Athletes))
    {
        System.Console.WriteLine(string.Format("Nation {0} has athletes:", currentNation.name));
        foreach (Athlete currentAthlete in currentNation.Athletes)
        {
            System.Console.WriteLine(string.Format("\t{0}", currentAthlete.name));
        }
    }
}

 

Note: In order to use the Query<T>() method you have to import the NHibernate.Linq namespace.

using NHibernate.Linq;

 

UPDATE

An UPDATE operation is straight forward. We just update the properties of our Nation and Athlete objects and call the Update() method from ISession on our nation. Again, NHibernate will also update the corresponding athlete because of the cascade style we've set in the mapping:

using (ISession session = sessionFactory.OpenSession())
{
    using (ITransaction tx = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        nation.capital = "Bucuresti";
        athlete.name = "Leonard Doroftei";
        session.Update(nation);
        tx.Commit();
    }
}

 

DELETE

Just like the UPDATE operation, DELETE is done in a standard way on a Nation object and the delete-orphan cascade style will delete the orphan athletes:

using (ISession session = sessionFactory.OpenSession())
{
    //Delete an inserted information
    using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Delete(nation);
        trans.Commit();
    }
}

 

 

M:N relationships

In this section, we will go a step further and see how to handle M:N relationships using the Athlete and the Event tables from the demodb database:

AthleteTable.jpg 

EventTable.jpg 

Let's say that we want to know in what events does an athlete take part, but also, we want to know who the athletes that take part in an event are. It is obvious that we are dealing with a M:N relationship so let's see how we go about mapping it.

By design, NHibernate only supports the implicit many-to-many mapping if there is absolutely nothing other than the pair of FKs represented in the intermediate (“middle”) table that holds the many-to-many relationship information.

So, because we don't have such a table already in the database, let's create one (in the following we will refer to this table as the reference table):

 

CREATE TABLE athleteevent(
event_code integer NOT NULL,
athlete_code integer NOT NULL,
CONSTRAINT pk_athleteevent_event_code_athlete_code PRIMARY KEY(event_code,athlete_code)
)

Now let's add a couple of records to the table so we can link athletes to events:

INSERT INTO AthleteEvent VALUES (20038, 10011), (20038, 14313)

 

The mapping files

To map the M:N relationship, we will again use a bag element in both our mapping files, but this time with some differences . First of all, we add the table attribute to tell NHibernate that it is dealing with a reference table. Next, instead of the <one-to-many/> tag, we use the <many-to-many> tag. This specifies that the relationship is M:N with the table given by the class attribute, and the reference column given by the column attribute. Note that, the key column and the many-to-many column are columns from the AthleteEvent table. Notice that we used the cascade attribute again, in the same way we did for the 1:N relationship:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NMRelationship"
namespace="NMRelationship">
<class name="Athlete" table="Athlete">
<id name="code">
<generator class="identity" />
</id>
<property name="name"/>
<property name="gender"/>
<property name="nation_code"/>
<property name="athlete_event" column="event"/>
<bag name="Events" table="AthleteEvent" cascade="all-delete-orphan" lazy="true">
<key column ="athlete_code" />
<many-to-many class="Event" column="event_code" />
</bag>
</class>
</hibernate-mapping>

 

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NMRelationship"
namespace="NMRelationship">
<class name="Event" table="Event">
<id name="code">
<column name="code" />
</id>
<property name="sports"/>
<property name="name"/>
<property name="gender"/>
<property name="players"/>
<bag name="Athletes" table="AthleteEvent" cascade="all-delete-orphan" inverse="true" lazy="true">
<key column ="event_code" />
<many-to-many class="Athlete" column="athlete_code" />
</bag>
</class>
</hibernate-mapping>


The Persistent Classes

The persistent classes have nothing special. All we need to do is to add IList properties that correspond to the other class:

namespace NMRelationship
{
    class Athlete
    {
        virtual public int code { get; set; }
        virtual public string name { get; set; }
        virtual public string gender { get; set; }
        virtual public string nation_code { get; set; }
        virtual public string athlete_event { get; set; }
        virtual public IList<Event> Events { get; set; }
  }
}
 
namespace NMRelationship
{
    class Event
    {
        virtual public int code { get; set; }
        virtual public string sports { get; set; }
        virtual public string name { get; set; }
        virtual public string gender { get; set; }
        virtual public int players { get; set; }
        virtual public IList<Athlete> Athletes { get; set; }
    }
}

Next, we will see how to perform the basic operations (INSERT, SELECT, UPDATE, DELETE) on the M:Nrelationship we mapped.

 

INSERT

We perform an INSERT operation in the same way we've done before. Just add an Event to the Athlete's list of events and NHibernate will do the rest: it will insert the athlete, the event and it will add a corresponding entry in the AthleteEvent table too:

Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof (Athlete).Assembly);
ISessionFactory sessionFactory = cfg.BuildSessionFactory();
Athlete athlete = new Athlete {
    name = "Lucian Bute",
    gender = "M",
    nation_code = "ROM",
    athlete_event = "Boxing",
    Events = new List<Event>()
};
athlete.Events.Add(new Event { code = 20422, sports = "Boxing", name = "70 Kg", gender = "M", players = 2 });
using (ISession session = sessionFactory.OpenSession())
{
    using (ITransaction tx = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Save(athlete);
        tx.Commit();
    }
}

 

SELECT

Again, no difference from the 1:N relationship in the previous section. The only thing worth noting is that NHibernate will create a circular reference between an athlete and its events. That means that the elements in the athlete's list of events will contain a list of their corresponding athletes and so on:

using (ISession session = sessionFactory.OpenSession())
{
    foreach (Athlete currentAthlete in session.Query<Athlete>().Fetch(b =>b.Events))
    {
        if (currentAthlete.Events.Count != 0)
        {
            Console.WriteLine(string.Format(
                "Athlete {0} takes part in {1} along with:",
                currentAthlete.name, currentAthlete.Events0.name));
            foreach (Athlete currentEventAthletes in currentAthlete.Events0.Athletes)
            {
                Console.WriteLine(string.Format("\t{0}", currentEventAthletes.name));
            }
        }
    }
}

 

UPDATE

The update operation is performed just like before:

athlete.name = "Leonard Doroftei";
athlete.Events0.name = "65 Kg";
using (ISession session = sessionFactory.OpenSession())
{
    using (ITransaction tx = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Update(athlete);
        tx.Commit();
    }}

 

DELETE

The delete-orphan cascade style is the important thing here. If we delete an athlete with this cascade style, NHibernate will also delete the corresponding entry in the reference table, but more important, it will also delete the corresponding events even though they might correspond to other athletes, so you have to be careful with your configuration. In our case, this is not a problem since we are sure that our event corresponds to just one athlete:

using (ISession session = sessionFactory.OpenSession())
{
    using (ITransaction tx = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Delete(athlete);
        tx.Commit();
    }
}

 

This concludes the 1st part of this tutorial. You can find the 2nd part here.

comments powered by Disqus
Page info
viewed 1365 times
translations en
Author
posted last year by
CUBRID
Contributors
updated last year by
View revisions
Share this article