Category Archives: ORM

Hibernate annotation to the rescue when mapping files cannot be located

Forget hibernate mapping files, seriously unless you really need to. Use annotations instead. Then you need not worry about running into class path issues.

This is the story:

I wanted to create a simple RESTFul service which exposes simple CRUD functionality on data within a PostgreSQL database. In a previous post,
I briefly covered using Hibernate to connect to a PostgreSQL database to retrieve entities within the context of a RESTful webservice. This all worked flawlessly since the deployment target was IntelliJ’s embedded http server. When it came time to deploy the resulting .war to a local Tomcat instance, all hell broke loose.

I used the the Jersey archetype for Maven which gave provided a directory structure like this:

|- src
   |- main
      |- java
      |- resources
          - hibnernate.cfg.xml
          - user.hbm.xml

As stated earlier, everything worked as expected when deployed to IntellJ’s local http server. However, when .war file was deployed to a local Tomcat instance on the same box, hibernate was not able to find a mapping for the user entity. The error was

User is not mapped [from User where id > :id]

for a class defined as follows:

public class User {
    private int id;
    private String _firstName;
    private String _lastName;

    public String getFirstName(){
        return _firstName;
    public void setFirstName(String name){
        _firstName = name;

    public int getId() {
        return id;

    public void setId(int id) { = id;

    public String getLastName() {
        return _lastName;

    public void setLastName(String _lastName) {
        this._lastName = _lastName;

    @Override public String toString(){
        return String.format("%s %s", _firstName, _lastName);

The hibernate files were all in place and mappings were all correct. After spending a couple of days and not seemingly getting anywhere, questioning my sanity, I decided on another approach: annotations. After all, this is the common pattern when using the Entity Framework in .NET.

The User class was modified as follows:

import javax.persistence.*;

 * Created by Klaus on 31/08/2015.
@Table( name = "users" )
public class User {

    private int id;

    @Column (name = "firstname")
    private String _firstName;

    @Column (name = "lastname")
    private String _lastName;

    /* rest of code ignored for brevity */

HibernateUtils was also modified:

public class HibernateUtils {
    private static SessionFactory sessionFactory;

    public static SessionFactory getSessionFactory() {

        if (sessionFactory == null) {
            // loads configuration and mappings
            Configuration configuration =
                    new Configuration()

            ServiceRegistry serviceRegistry
                    = new StandardServiceRegistryBuilder()

            // builds a session factory from the service registry
            sessionFactory = configuration.buildSessionFactory(serviceRegistry);

        return sessionFactory;

Repacking, deploying to IntelliJ’s local http server ensured functionality was not broken. Next, deploying to a local instance of Tomcat on the local machine, also proved successful. This left me pondering for a while…. but there are things to get done…

What happened here remains a mystery which begs for an explanation. Could a unit test have saved me endless hours deploying to Tomcat?


Quick start on using Hibernate with Postgres 9.4 in IntelliJ

1. Install IntelliJ Ultimate. You can get a 30 day trial license.
2. Install PostgresSQL 9.4 or whatever is the latest version today.

What do do:
1. Create a new Maven Project in HIbernate. User the simplest maven archetype to get a good folder structure. I chose the maven-archetype-webapp archetype.
2. Right click on the newly created project and enable Hibernate support through the “Add Framework Support” menu.
3. Add Postgres 9.4 dependencies via Maven.
4. Launch PgAdmin and connect to your PostgreSQL database.
5. Create a users table in PostgresSQL using the following script:

create table Users (
   id SERIAL,
   firstName VARCHAR(100) default NULL,
   lastName  VARCHAR(100) default NULL,
   salary     INT  default NULL,

6. Under the resources folder, add a new hibernate.cfg.xml file with the following content:


    <property name="dialect">org.hibernate.dialect.PostgreSQL82Dialect</property>
    <property name="connection.driver_class">org.postgresql.Driver</property>
    <property name="connection.url">jdbc:postgresql://localhost:5432/database</property>
    <property name="connection.username">username</property>
    <property name="connection.password">password</property>

    <property name="cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>

    <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <property name="current_session_context_class">thread</property>
    <property name="hibernate.show_sql">false</property>

7. In the same folder, add a users.hbm.xml file with the following content:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD//EN"

    <class name="tut.User" table="Users">
        <meta attribute="class-description">
            This class contains the employee detail.
        <id name="id" type="int" column="id">
            <generator class="increment"/>
        <property name="firstName" column="firstname" type="string"/>
        <property name="lastName" column="lastname" type="string"/>
        <property name="age" column="age" type="int"/>

8. Create a new folder under src/main/java/myco/utils and create a new file HibernateUtils class to host your Session Factory as follows:

public class HibernateUtils {
    private static final SessionFactory ourSessionFactory;
    private static final ServiceRegistry serviceRegistry;

    static {
        try {
            Configuration configuration = new Configuration();

            serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry();
            ourSessionFactory = configuration.buildSessionFactory(serviceRegistry);
        } catch (Throwable ex) {
            throw new ExceptionInInitializerError(ex);

    public static Session getSession() throws HibernateException {
        return ourSessionFactory.openSession();

9. Create a new folder under src/main/java/myco/models and a new User class as follows:

public class User {
    private String _firstName;
    private String _lastName;
    private int _age;
    private boolean _isEmployed;
    private int _id;

    public String getFirstName(){
        return _firstName;
    public void setFirstName(String name){
        _firstName = name;

    public int getAge(){
        return _age;
    public void setAge(int age){
        _age = age;

    public boolean getIsEmployed(){
        return _isEmployed;
    public void setIsEmployed(boolean isEmployed){
        _isEmployed = isEmployed;
    public int getId() {
        return _id;

    public void setId(int _id) {
        this._id = _id;

    public String getLastName() {
        return _lastName;

    public void setLastName(String _lastName) {
        this._lastName = _lastName;

    @Override public String toString(){
        return String.format("%s Ss", _firstName, _age);


10. Create a UserResource file under src/main/java/myco/resources as follows:

public class UserResource { 
    public User[] getAll(){

        Session session = HibernateUtils.getSession();

        Query query=  session.createQuery("from User where  id > :id");
        query.setParameter("id", 0);

        Object[] users = query.list().toArray();

        User[] toReturn = new User[users.length];
        for (int i = 0; i < users.length; i++){
            toReturn[i] = (User)users[i];

        return toReturn;
    public User[] createUsers(User[] users){

        Session session = HibernateUtils.getSession();

        for (User user : users){
            System.out.println("Adding user to session " + user.toString());


      return users;

11. Create a UserResourceTest class under src/text/java/myco inheriting from TestCase as follows:

public class UserResourceTest extends TestCase {

    public void testGetAll() throws Exception {

        UserResource userResource = new UserResource();
        User[] users = userResource.getAll();

        Assert.assertTrue(users.length > 0);


    public void testCreateUsers() throws Exception {

        User user = new User();

        UserResource userResource = new UserResource();
        userResource.createUsers(new User[]{ user});

        Assert.assertTrue(user.getId() > 0);

12. Go to View | Tools Windows | Maven Project to launch the Maven tools window.
13. Expand the project within the Maven tools window, right click on the clean life cycle and run it.
14. Unless I missed a step, you should see BUILD SUCCESS in your output window.
15. Right click on UserResourceTest and select Run UserResourceTest. Test should all run with green lights.

Happy Coding.

A Gentle Introduction to Entity Framework 6 from an NHibernate Perspective

If you have been using Hibernate/NHibernate for a while before the introduction of its fluent API, you are used to a certain workflow. You define your entity classes. Then you create a hibernate configuration file that defines mappings between the database tables and your entity classes. And that was about it.

With the Entity Framework 6, henceforth refereed to as EF6, you can several approaches: Database first, Model First or Code First. While each of these approaches cater to a different type of audience, they also present an immediate decision point of entry to the beginner, possibly hindering adoption and setting the stage ripe for a rough ride of perceptions whereby every aspect of the technology seems to appear overly engineered or complicated.

This blog attempts a gentle introduction to the Entity Framework. It is especially catered towards those developers who are used to the Hibernate workflow or similar. Such a workflow corresponds to the Entity Framework Code First approach. Along the way, commonalities and differences between both frameworks, within the context of what we are trying to achieve, will be highlighted.

Our Objective:
Create, read and update entities in a database using the Entity Framework.

If you like a hands-on approach and will like to code along, you will need the following tools:

1. Visual Studio 2012 (any edition)
2. SQL Server Management Studio Express 2012 available here:
3. Adventure Works database, available here:
4. Entity Framework 6, available via NuGet.

The overall procedure can be summarized as follows:

1. Define entity classes that are representative of the database tables.
2. Create an Entity Framework database context that derives from System.Data.Entity.DBContext add a DbSet for every entity definition you want to query from the database.
3. Creating mapping classes that correlate properties in your entity model to columns in your database tables.
3. Create your Entity Framework configuration in app.config/web.config.
4. Add required configuration to your application’s configuration file
for the Entity Framework.

Please note that we will not be using any automatic code generation features of the Entity Framework. We choose to do every thing manually, fully acknowledging that certain aspects of the workflow such as mapping each column type to a respective CLR type, are faster done automated. However, just like making the 24 hour drive versus the 5 hour flight between Ottawa, Ontario, Canada to a place lie Sandestin, Florida, the former option gives you an opportunity to experience America’s landscape in its wonder. Whereas, the latter does not, quickly taking you from one comfort station to another, with no thrills or nothing to write home about. Doing things manually also gives us an opportunity to gain a better understanding of the framework by solving each potential problem as they arise.

Entity Framework 6 Default Behavior
Before proceeding it is worth noting certain default behaviors and assumptions exhibited by EF6, unless it is told otherwise:

  • Behavior: Pluralize a model to find corresponding table in database.
  • Behavior: Map a property in the model against a table column using the property name.
  • Behavior: Create a database to represent your Entity if one does not exist.
  • Assumption: Each entity has a primary key called Id or name of Entity’s type concatenated with “Id” defined in the corresponding table

As far as I know, Hibernate does not make any such assumptions, forcing you to explicitly create the context it will use to connect to the database, retrieve and map these entities.

Step 1: The Employee class:
This class is represented in HumanResources.Employee table of AdventureWorks2012.

Look at the table, schema, I manually created the following class:

public class Employee
        public int Id { get; set; }
        public string NationalIDNumber { get; set; }
        public string LoginID { get; set; }
        public short OrganizationLevel { get; set; }
        public string JobTitle { get; set; }
        public DateTime BirthDate { get; set; }
        public char MaritalStatus { get; set; }
        public char Gender { get; set; }
        public DateTime HireDate { get; set; }
        public bool IsSalaried { get; set; }
        public short VacationHours { get; set; }
        public short SickLeaveHours { get; set; }
        public bool IsCurrent { get; set; }
        public DateTime ModifiedDate { get; set; }

        public override string ToString()
            return string.Format("Employee Id {0} Login Id: {1}", Id, LoginID);

To keep things simple, columns OrganizationalMode and rowguid are not represented in the model as I could not readily figure out a corresponding CLR data type.

Step 2: Creating mappings to deal with model-table discepancies:
Since there is typically a discrepancy between our model and its associated table in the database, we need to create a type mapping.

For example the three default behaviors exhibited by EF6 will fail with the Employee model defined above because:
a: Our corresponding table is not called Employees. Instead it is HumanResources.Employee.
b: Properties IsSalaried and IsCurrent do not exist in the table. Corresponding table fields are CurrentFlag and SalariedFlag.
c: The primary key in HumanResources.Employee is BusinessEntityID and neither Id not EmployeeId.

In Hibernate you ALWAYs creating some sort of mapping either through configuration or recently via the Fluent API. In EF6, however, we only create a mapping if the default assumptions are false. I personally found this to be another decision point that could lead to some frustration but is not well documented. For the Employee model the default assumptions are false and therefore we will need to creating a mapping.

In EF6, we will create these mappings using a fluent API. This involves involves creating a class that derives from System.Data.Entity.ModelConfiguration.EntityTypeConfiguration
and defining mappings within the class constructor. For the Employee class, we need to specify the proper primary fields and create the correct mapping between fields IsCurrent and IsSalaried and their corresponding databae columns CurrentFlag and SalariedFlag respectively. The resulting code C# class looks like this:

   public class EmployeeMapping : EntityTypeConfiguration<Employee>
        public EmployeeMapping()
            // resolve discrepancy between default assumption on existence of Employees table

            // to resolve discrepancy between primary key BusinessEntityID defined in database 
            HasKey(a => a.Id);

            // map Emplyee.Id to table column BusinessEntityID
            Property(e => e.Id).HasColumnName("BusinessEntityID");

            // map Employee.IsCurrent to table column CurrentFlag
            Property(a => a.IsCurrent).HasColumnName("CurrentFlag");

            // map Employee.IsSalaried to table column SalariedFlag
            Property(a => a.IsSalaried).HasColumnName("SalariedFlag");

Step 3: Registering type mappings with framework

In other to use these mappings, they must be registered with the framework. In NHibernate, we register type mappings in hibernate.cfg.xml or embed them into the assembly and let the framework resolve them automatically during runtime.

In EF6, the only way, unless someone can prove otherwise, to register these type mappings is by manually instantiating these classes in the appropriate EF database context. An EF database context is what your application uses to interact with the database. It is an instance of this class needs to be made aware of your type mappings and other configuration information that influences the framework’s behavior. In some ways, this class is similar in to the Hibernate Session object.

To connect to our AdventureWorks2012 database, we implement the following EF6 DbContext:

 public class AdventureWorksDbContext: DbContext
        // register repository to Employees here
        public DbSet<Employee>  Employees { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

            // register Employee type mapping here
            modelBuilder.Configurations.Add(new EmployeeMapping());

As commented in the above file, we have registered our type mapping and created a repository to query for Employee types. Either of those lines are required to get EF6 communicating to the database.

Step 5: The Configuration:
Adding EF6 via NuGet adds an app.config file with the following:

    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>

This is a standard snippet required to initialize EF6 in the your app domain. Nothing interesting here. However, we have one last EF6 default behavior to override. We want the framework to use our existing AdventureWorks2012 database instead of creating a new one. To do this, we need to add a connection string entry in our app.config, ensuring its name is exactly the same as the class name of our DbContext implementation as follows:

    <add name="AdventureWorksDbContext" providerName="System.Data.SqlClient" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI" />  

This was another tripping point during my learning experience. And that is it folks. You have all the required bits to perform a simple CRUD operation to AdventureWorks2012 via the Entity Framework 6.

If you have followed so far, you should be able to write a simple console application to retrieve some employees:

   class Program
        static void Main(string[] args)
            IEnumerable<Employee> employees;
            using (var context = new AdventureWorksDbContext())
                employees = context.Employees.Where(e => e.IsCurrent).ToArray();

            foreach (var employee in employees)


In a future blog post, I will look at relationships and stored procedures.

Configuring NHibernate to execute a Stored Procedure that returns a result set

I recently spent about 5 hours trying to get NHibernate to successfully execute a stored procedure that returns a result set in SQL Server 2012. Given that it took me less than an hour to put all the pieces together for NHibernate to execute a similar stored procedure but one that returns a scalar, I thought this would be easy. Surely did not turn out that way.

This blog post explains exactly how you need to configure NHibernate to execute a stored procedure that returns a result set.

 The tools I used were Visual Studio 2010, SQL Studio Management Express 2012 and Express Profiler v2.0.   NHibernate version is pulled into the project via NuGet.

Say, you have the following stored procedure:

if OBJECT_ID ( '[dbo].[sp_get_deactivation_list]', 'P' ) is not null
    drop procedure [dbo].[sp_get_deactivation_list];
create procedure [dbo].[sp_get_deactivation_list]
	@companyId int,
	@startDate DateTime,
	@endDate DateTime
   	        tblRadioinfo.ID as Id,
		tblRadioinfo.Mobile_ID as MobileId,
		tblRadioinfo.Radio_Address as RadioAddress,
		tblRadioinfo.Deactivation_Date as DeactivationDate
	from tblRadioinfo
	where tblRadioinfo.Radio_Type_ID in (2, 4, 7)
	and tblRadioinfo.Company_ID = @companyId
	and tblRadioinfo.Deactivation_Date <= @endDate
	and tblRadioinfo.Deactivation_Date >= @startDate
	and tblRadioinfo.Radio_Address in (select IMEI from [airtime_cdrs] where  Effective_Date > @startDate and Effective_Date < @endDate)
<p>	ORDER BY tblRadioinfo.Deactivation_Date

which when executed using the following simple test in SQL Studio Management Express 2012.

declare @route_id_param as varchar(10), @start_time as datetime, @start_date as datetime, @end_date as datetime
set @start_time = GETDATE()
set @start_date = CONVERT(DATETIME,'10/26/2013',101)
set @end_date = CONVERT(DATETIME,'12/26/2020',101) 
<p>exec dbo.sp_get_deactivation_list @companyId=1, @startDate = @start_date, @endDate = @end_date;
select execution_time_in_ms = DATEDIFF(millisecond, @start_time, getdate())

returns two results as follows:

Id            MobileId              RadioAddress    DeactivationDate
3              MobileID_2        300034013417890             2020-12-26 00:00:00.000
4              MobileID_3        300034012356790             2020-12-26 00:00:00.000

Now, say you will like to use NHibernate to execute this stored procedure and return the same two records.

An Approach
Assume you have the following class and interface definitions:

 public class HibernateStoredProcedureExecutor : IExecuteStoredProcedure
        private readonly ISessionFactory _sessionFactory;

        public HibernateStoredProcedureExecutor(ISessionFactory sessionFactory)
            _sessionFactory = sessionFactory;

        public IEnumerable<TOut> ExecuteStoredProcedure<TOut>(string procedureName, IList<SqlParameter> parameters)
            IEnumerable<TOut> result;

            using (var session = _sessionFactory.OpenSession())
                var query = session.GetNamedQuery(procedureName);
                AddStoredProcedureParameters(query, parameters);
                result = query.List<TOut>(); 

            return result;
        public TOut ExecuteScalarStoredProcedure<TOut>(string procedureName, IList<SqlParameter> parameters)
            TOut result;

            using (var session = _sessionFactory.OpenSession())
                var query = session.GetNamedQuery(procedureName);
                AddStoredProcedureParameters(query, parameters);
                result = query.SetResultTransformer(Transformers.AliasToBean(typeof(TOut))).UniqueResult<TOut>();

            return result;

        public static IQuery AddStoredProcedureParameters(IQuery query, IEnumerable<SqlParameter> parameters)
            foreach (var parameter in parameters)
                query.SetParameter(parameter.ParameterName, parameter.Value);

            return query;

  public interface IExecuteStoredProcedure
        TOut ExecuteScalarStoredProcedure<TOut>(string procedureName, IList<SqlParameter> sqlParameters);
        IEnumerable<TOut> ExecuteStoredProcedure<TOut>(string procedureName, IList<SqlParameter> sqlParameters);

and somewhere within your project, you are invoking an instance of this class like this;

  public IEnumerable<Activation> GetDeactivationList(int companyId, DateTime startDate, DateTime endDate)
             var sessionFactory = BuildSessionFactory();
            var executor = new HibernateStoredProcedureExecutor(sessionFactory);
            var deactivations = executor.ExecuteStoredProcedure<Activation>(
                    new SqlParameter("companyId", companyId), 
                    new SqlParameter("startDate", startDate), 
                    new SqlParameter("endDate", endDate), 

            return deactivations;

To get this all working, you will need a mapping for your stored procedure and one for the return type of your stored procedure, if your stored procedure is returning a result set of complex types.  The mapping file for the stored procedure looks like this:

<?xml version="1.0" encoding="utf-8" ?>
  <sql-query name="GetDeactivationList" callable="true">
    <query-param name="companyId" type="int"/>
    <query-param name="startDate" type="DateTime"/>
    <query-param name="endDate" type="DateTime"/>
    <return class="Activation">
      <return-property column="Id" name="Id" />
      <return-property column="MobileId" name="MobileId" />
      <return-property column="RadioAddress" name="RadioAddress" />
      <return-property column="DeactivationDate" name="DeactivationDate" />
    exec [sp_get_deactivation_list] @companyId=:companyId, @startDate=:startDate, @endDate=:endDate

and for the return type of our stored procedure, which is not really associated with any table in the database, is listed below.

<?xml version="1.0" encoding="utf-8" ?>
  <class name="Activation" >
    <id name="Id">
      <generator class="identity"/>
    <property column="MobileId" name="MobileId" />
    <property column="RadioAddress" name="RadioAddress" />
    <property column="DeactivationDate" name="DeactivationDate" />

Once you have all of the aforementioned in place, you should be able to write and execute successfully a unit test against the GetDeactivationList method. If you use the same input parameters as those used earlier you should get the same two records.

The Gotchas
Bold lines in the aforementioned code sections were stumbling points and are worth mentioning. These were missing in my original iteration and were arrived at after numerous consultations against the document and StackOverflow

Since one cannot define a Hibernate mapping file without the Id column, you must add one, even if it means nothing to your entity.

 <id name="Id">
      <generator class="identity"/>

An Id column defined by the above Hibernate mapping mandates its inclusion in the return type definition of your stored procedure, hence the reason of this entry in the stored procedure’s mapping file:

    <return-property column="Id" name="Id" />

Otherwise, you will get an exception similar to this:

NHibernate.Exceptions.GenericADOException: could not execute query
[ exec [sp_get_deactivation_list] @companyId=@p0, @startDate=@p1, @endDate=@p2 ]
Name:companyId – Value:1 Name:startDate – Value:10/26/2013 12:00:00 AM Name:endDate – Value:12/26/2020 12:00:00 AM
[SQL: exec [sp_get_deactivation_list] @companyId=@p0, @startDate=@p1, @endDate=@p2] —> System.IndexOutOfRangeException: Id2_0_

An Id column defined in a Hibernate mapping file also mandates a corresponding public virtual property defined on the class. So we need this:

public virtual int Id { get; set; }

in the Activation class.

Finally, since you mapping has instructed NHibernate to expect an Id column in the result set, you must ensure one is returned by the stored procedure, hence the reason for this line:

assMobileRadio.ID as Id

In Conclusion
Relatively painful getting here but it now works. Hopefully this helps out others running into a similar problem. If any of my statements are technically inaccurate, please let me know. Also, much thanks to some good folks at StackOverflow for their help.