Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Using CUBRID with NHibernate - Advanced tutorial (Part II)


June, 2013

 

Introduction

This is the 2nd part of the Using CUBRID with NHibernate Advanced tutorial.

In this part, we will discuss about dealing with CUBRID special data types in NHibernate.

 

CUBRID special data types

Out-of-the-box, NHibernate Core does not support the following CUBRID special data types:

  • CLOB
  • BLOB
  • SET
  • MULTISET
  • LIST/SEQUENCE
  • ENUM

We have submitted a code patch to the NHibernate Core project – we will let you know when CUBRID support will be available in the official NHibernate Core libraries.

In the meantime, you will have to use the precompiled libraries we have already published (or apply the CUBRID NHibernate patch and compile the NHibernate Core library yourself) – see the 1st tutorial.

So, in this part we will show you how to deal in NHibernate with some of these CUBRID special data types.

 

CUBRID LOB

CUBRID CLOB is a data type used to store large character string data.The maximum length of CLOB data is the maximum file size which can be created on the external storage, which depends on the OS specifics.

In this section we will use NHibernate to store and retrieve a text file in the demodb database. As usual, the first thing we need to do is to create a table with a CLOB column:

CREATE TABLE testcubridclob(
   c_integer integer AUTO_INCREMENT(1,1) NOT NULL,
   c_clob clob,
   CONSTRAINT pk_testcubridclob_c_integer PRIMARY KEY(c_integer)
) 

 

The Mapping File

Because NHibernate does not support CUBRID CLOB out-of-the-box, we have to add a new attribute to the <property> tag corresponding to the CUBRID CLOB column: type = "CUBRIDClob". This will tell NHibernate that it is dealing with a CUBRIDClob object at it will handle it approprietly.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernateTestCases"
namespace="CUBRID.Data.TestNHibernate">
<class name="TestCUBRIDClobType" table="TestCUBRIDClob">
<id name="c_integer">
<generator class="identity" />
</id>
<property name="c_clob" type="CUBRIDClob">
<column name="c_clob" sql-type="CLOB" />
</property>
</class>
</hibernate-mapping>

 

The Persistent Class

In the persistent class, the property corresponding to the CUBRIDClob Column will be of type CUBRID.Data.CUBRIDClinet.CUBRIDClob:

namespace CUBRID.Data.TestNHibernate
{
    public class TestCUBRIDClobType
    {
        virtual public int c_integer { get; set; }
        virtual public CUBRID.Data.CUBRIDClient.CUBRIDClobc_clob { get; set; }
    }
}

 

INSERT

To insert a CLOB object in our table we will perform the following operations: read a file from the disk using a StreamReader, create a CUBRIDClob object using the CUBRID ADO.NET Driver, fill our CUBRIDClob object with the content of our file through the SetString() method and then use NHibernate to perform the insert operation just like we would with a normal data type.

The CLOB data type is a special data types also in the CUBRID ADO.NET Driver, so in order to handle it some dedicated specific operations are implemened. You can get more information on handling LOB objects with the CUBRID ADO.NET Driver here.

 

Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(TestCUBRIDClobType).Assembly);
TestCUBRIDClobType test = new TestCUBRIDClobType {
    c_clob = new CUBRIDClob(conn)
};
StreamReader originalFileReader = new StreamReader("../../BSD License.txt");
string clobStringToInsert = originalFileReader.ReadToEnd();
originalFileReader.Close();
test.c_clob.SetString(1, clobStringToInsert);

//Insert
ISessionFactorysessionFactory = cfg.BuildSessionFactory();
using (var session = sessionFactory.OpenSession())
{
    using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Save(test);
        trans.Commit();
    }
}

 

SELECT

Retrieving the CLOB from the database is done the other way around: use NHibernate to get a CUBRIDClob object from the ADO.NET Driver, use that object to get the CLOB content through the GetString() method and save the content to a new file on the disk: 

ISessionFactorysessionFactory = cfg.BuildSessionFactory();
using (var session = sessionFactory.OpenSession())
{
    //Retrieve the inserted information
    IQuery query = session.CreateQuery("FROM TestCUBRIDClobType");
    IList<TestCUBRIDClobType>testQuery = query.List<TestCUBRIDClobType>();
    CUBRIDClob bImage = testQuery0.c_clob;
    string clobInserted = bImage.GetString(1, (int)testQuery0.c_clob.ClobLength);
    System.IO.StreamWriter file = new System.IO.StreamWriter("../../Retrieved.txt");
    file.WriteLine(clobInserted);
    file.Close();
}

 

CUBRID SET

CUBRID SET is a collection type in which each element has different values. Elements of a SET are allowed to have only one data type. It can have records of other tables.

Handling the CUBRID SET data type in NHibernateis pretty much the same as handling a normal data type, with the exception that it is not supported out-of-the-box so we have to use the type attribute of the <property> tag again to tell NHibernate how to handle this kind of object. This time the attribute will be: type = "CUBRIDCollection".

In the following example we will use a CUBRID SET column to store the ingredients of a recipe. The SQL used for the creation of our table is:

CREATE TABLE recipe(
   id integer AUTO_INCREMENT(1,1) NOT NULL,
   recipe_name character varying(1073741823),
   ingredients set_of(character varying(10)),
   CONSTRAINT pk_recipe_id PRIMARY KEY(id)
) 

 

The Mapping file

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="CUBRIDSet"
namespace="CUBRIDSet">
<class name="Recipe" table="recipe">
<id name="id">
<generator class="identity" />
</id>
<property name="name">
<column name="recipe_name"/>
</property>
<property name="ingredients" type="CUBRIDCollection">
<column name="ingredients"/>
</property>
</class>
</hibernate-mapping>

 

The persistent class

In the persistent class the CUBRID SET column is represented by an array of objects. This is because the column can contain both numeric and string values.

namespace CUBRIDSet
{
    class Recipe
    {
        virtual public int id { get; set; }
        virtual public string name { get; set; }
        virtual public object[] ingredients { get; set; }
    }
}

To insert our SET values, we just have to create an array of objects, set it with the elements of the set and perform the operation normally:

Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(Recipe).Assembly);
object[] ingredients = { "apples", "eggs", "flour", "yeast" };
Recipe recipe = new Recipe() {
    name = "Apple pie",
    ingredients = ingredients
};
ISessionFactory sessionFactory = cfg.BuildSessionFactory();
using (var session = sessionFactory.OpenSession())
{
    using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Save(recipe);
        trans.Commit();
    }
}

 

SelectFromRecipe.jpg

Now let's retrieve the inserted information. There is really no difference from handling a normal data type in NHibernate:

using (var session = sessionFactory.OpenSession())
{
    //Retrieve the inserted information
    IQuery query = session.CreateQuery(" from Recipe");
    IList<Recipe> recipies = query.List<Recipe>();
    for (var i = 0; i <recipies.Count; i++)
    {
        Console.Write("{0} is made out of: ", recipiesi.name);
        foreach (var ingredient in recipiesi.ingredients)
        {
            Console.Write("{0}, ", ingredient);
        }
    Console.WriteLine();
    }
}

 

RecipeOutput.jpg 

Note: CUBRID SET is a collection type in which each element has different values so if we try to insert duplicates like:

                object[] ingredients = { "apples", "eggs", "flour", "yeast" , "apples"};

, we will get the same result as before, because CUBRID will discard the duplicates.

 

CUBRID LIST/SEQUENCE

CUBRID LIST (= SEQUENCE) is a collection type in which the input order of elements is preserved, and duplications are allowed. Elements of a LIST are allowed to have only one data type. It can have records of other tables.

To demonstrate the use of CUBRID LIST/SEQUENCE we will create a table called student_class in which we will store in a LIST column the students that study in that class. The SQL statement used for the creation of the table is:

CREATE TABLE students_class(
    id integer AUTO_INCREMENT(1,1) NOT NULL,
    class_name character varying(1073741823),
    students sequence_of(character varying(30)),
    CONSTRAINT pk_class_id PRIMARY KEY(id)
)

 

The Mapping file

Handling CUBRID LIST/SEQUENCE columns is the same as handling the CUBRID SET columns which we discussed earlier. All we need to do apart from a normal mapping is to set the type attribute of the <property> tag like this: type = "CUBRIDCollection"

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="CUBRIDList"
namespace="CUBRIDList">
<class name="Class" table="students_class">
<id name="id">
<generator class="identity" />
</id>
<property name="name">
<column name="class_name"/>
</property>
<property name="students" type="CUBRIDCollection">
<column name="students"/>
</property>
</class>
</hibernate-mapping> 

 

The Persistent Class

As discussed in the previous CUBRID SET example, the LIST column will be persisted like an array of objects:

namespace CUBRIDList
{
    class Class
    {
        virtual public int id { get; set; }
        virtual public string name { get; set; }
        virtual public object[] students { get; set; }
    }
} 

Let's now insert something into the table:

Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(Class).Assembly);
object[] students = { "John Doe", "Amy Smith", "Mark Stevans", "Bety Ross" };
Class recipe = new Class() {
    name = "First Grade",
    students = students
};
ISessionFactory sessionFactory = cfg.BuildSessionFactory();
using (var session = sessionFactory.OpenSession())
{
    using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Save(recipe);
        trans.Commit();
    }
} 

As before, we create an object array and set it with our student names. Please note that the students were not put in any particular order. Let's see what happens when we retrieve the students from the database:

using (var session = sessionFactory.OpenSession())
{
    //Retrieve the inserted information
    IQuery query = session.CreateQuery(" from Class");
    IList<Class> classes = query.List<Class>();
    for (var i = 0; i <classes.Count; i++)
    {
        Console.WriteLine("In {0} study the following students: ", classesi.name);
        foreach (var student in classesi.students)
        {
            Console.WriteLine("\t{0}", student);
        }
    }
}

ListOutput.jpg

As you can see, the students were retrieved in alphabetical order.


CUBRID ENUM

The CUBRID ENUM type is a data type consisting of an ordered set of distinct constant char literals called enum values.Only the specified string elements are allowed as the value of the column defined as ENUM and the maximum number of the ENUM elements is 65535.

In the following, we will see how to handle insert and select operations on CUBRIDENUM columns. But first, let's create a table in the demodb database with an ENUM column: 

CREATE TABLE "colors"(
    "id" integer AUTO_INCREMENT(1,1) NOT NULL,
    "color" ENUM('red', 'green', 'blue'),
    PRIMARY KEY("id")
);

NHibernate does not support the CUBRID ENUM type out-of-the-box, but in this case we can take advantage of the fact that the value of an ENUM column is just a char literal, so we will map this column in NHibernate just like we would a STRING, CHAR, or VARCHAR column. The only difference is that we will specify that the sql-type of the column is ENUM.

 

The Mapping file

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping assembly="CUBRIDEnum" namespace="CUBRIDEnum" xmlns="urn:nhibernate-mapping-2.2">
<class name="Colors" table="colors" lazy="true" >
<id name="id">
<generator class="identity" />
</id>
<property name="color">
<column name="color" sql-type="ENUM" not-null="false" />
</property>
</class>
</hibernate-mapping> 

 

The Persistent Class

namespace CUBRIDEnum
{
    class Colors
    {
        virtual public int id { get; set; }
        virtual public string color { get; set; }
    }
}

 

Once we have the persistent class and the mapping document in place, we can perform INSERT and SELECT operations. Let's insert three records in the table - one of each color in our enumeration:

Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(Colors).Assembly);
Colors red = new Colors {
    color = "red"
};
Colors green = new Colors {
    color = "green"
};
Colors blue = new Colors {
    color = "blue"
};
ISessionFactory sessionFactory = cfg.BuildSessionFactory();
using (var session = sessionFactory.OpenSession()) {
    using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Save(red);
        session.Save(green);
        session.Save(blue);
        trans.Commit();
    }
}

And now, let's retrieve the inserted records:

using (var session = sessionFactory.OpenSession())
{
    //Retrieve the inserted information
    IQuery query = session.CreateQuery("FROM Colors");
    IList<Colors> colors = query.List<Colors>();
    Console.WriteLine("The colors in the table are:");
    Console.WriteLine("id\t\tColor");
    Console.WriteLine("_________________________");
    for (var i = 0; i <colors.Count; i++)
    {
        Console.WriteLine(colorsi.id + "\t|\t" + colorsi.color);
    }
} 

The console output from running the code is:

EnumOutput.jpg

 

Note: As can be seen from the SQL statement we used to create the table, the color column can take one of the three values: 'red', 'green', 'blue'. Trying to insert a different a different value, like yellow or orange will result in the following error:

Semantic: before ' );'

Cannot coerce 'yellow' to type enum. insert into colors colors (colors.color) values ( cast('yell...

 

Important! We have presented here just some of the CUBRD special data types mappings in NHibernate.

The rest are pretty much similar to deal with – but if you need some ready-to-go code, please take a look at the CUBRID NHibernate test cases suite.

 

And this concludes our tutorial!J

 

References

 

CUBRID NHibernate Wiki page

http://www.cubrid.org/wiki_apis/entry/cubrid-nhibernate-support

NHibernate home page

http://nhforge.org/

CUBRID ADO.NET driver

http://www.cubrid.org/wiki_apis/entry/cubrid-ado-net-driver

CUBRID NHibernate source code

http://svn.cubrid.org/cubridapis/NHibernate

CUBRID-enabled NMG tool

http://nmg.codeplex.com

 

We hope you enjoyed the CUBRID NHibernate tutorials series!

As always, we kindly ask you to let us know your feedback and suggestions, so we can improve!

 

Thank you!

The CUBRID API team

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