NHibernate Custom Queries Code Review

Mapping the Class and Database Objects Using XML Mapping File

Code Review

Code Walkthrough

The following XML elements are used to define the key objects in the database model:

  • auto-import : whether or not unqualified class names (of classes in this mapping) can be used.
  • namespace : namespace to assume for unqualified class names in the mapping document.
  • assembly : assembly to assume for unqualified class names in the mapping document.
  • class : used to declare a persistent class.
  • class name : the .NET class name of the persistent class.
  • class table : the name of the database table being mapped to.
  • class lazy : whether or not lazy fetching is used to load the class.
  • id : used to declare the primary key column of the database table.
  • id name : name of the class identifier property.
  • id column name : name of the database table primary key column.
  • id generator : name of the .NET class used to generate unique identifiers for instances of the persistent class.
  • many-to-one : used to define a many-to-one association between persistent classes.
  • many-to-one name : name of the association.
  • many-to-one class : name of the associated class.
  • many-to-one column : name of the column to be used in the association.
  • many-to-one cascade : specifies which operations should be cascaded from the parent object to the associated object.
  • bag : used to define a persistent collection.
  • bag name : the collection property name.
  • bag cascade : specifies which operations should be cascaded to the child entities.
  • bag lazy : whether or not lazy fetching is used. If disabled, association is eagerly fetched.
  • bag inverse : mark this collection as the inverse end of a bidirectional association.
  • bag key column : name of the foreign key column.
  • bag one-to-many : indicates a one to many association.
  • bag loader : indicates that a named query will be used to load the collection.
  • bag loader query-ref : indicates the named query to use when loading the collection.
  • property : declares a persistent property of the class.
  • property name : name of the property of the persistent class.
  • property column : name of the mapped database table column.
  • sql-insert : specify the custom stored procedure to invoke when performing an insert action on the database.
  • sql-update : specify the custom stored procedure to invoke when performing an update action on the database. The check property specifies whether or not the number of rows affected is verified.
  • sql-delete : specify the custom stored procedure to invoke when performing an delete action on the database. The check property specifies whether or not the number of rows affected is verified.
  • sql-query : declares a named SQL query and specifies the custom query to execute.
  • sql-query name : name of the named query.
  • sql-query return : specifies the persistent class type returned by the named query.
  • sql-query load-collection : specifies the persistent collection to be loaded by by the named query.

The below XML is stored in the Person.hbm.xml file and is used to specify mapping between the Person data model of the database and the Person application object model.

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" namespace="PersonNHibernateStoredProc_2_0" assembly="PersonNHibernateStoredProc_2_0">
<class name="Person" table="Person" lazy="true"> <id name="ID"> <column name="ID" /> <generator class="increment" /> </id>
<many-to-one name="Role" class="Role" column="IDRole" cascade="save-update"/>
<bag name="Salaries" cascade="all" lazy="true" inverse="true"> <key column="IDPerson" /> <one-to-many class="Salary" /> <loader query-ref="AllSalariesForPerson"/> </bag>
<property name="LastName" column="LastName" /> <property name="FirstName" column="FirstName" />
<sql-insert>exec [nhibInsertPerson] ?,?,?,?</sql-insert>
<sql-update check="none">exec [nhibUpdatePerson] ?,?,?,?</sql-update>
<sql-delete check="none">exec [nhibDeletePerson] ?</sql-delete> </class>
<sql-query name ="AllPeopleData"> <return class="Person" /> exec [nhibGetAllPeople] </sql-query> <sql-query name="AllSalariesForPerson"> <load-collection alias="Salaries" role="Person.Salaries" /> exec [nhibGetSalariesByPersonId] ? </sql-query> <sql-query name ="GetPersonByLastName"> <return class="Person" /> exec [nhibGetPersonByLastName] @LastName=:LastName </sql-query> <sql-query name ="GetPersonByIDPerson"> <return class="Person" /> exec [nhibGetPersonByID] @IDPerson=:IDPerson </sql-query> <sql-query name="ApplyDatabaseReset"> exec [hibResetData] </sql-query> </hibernate-mapping>

The below XML is stored in the Role.hbm.xml file and is used to specify mapping between the Role data model of the database and the Role application object model.

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" namespace="PersonNHibernateStoredProc_2_0" assembly="PersonNHibernateStoredProc_2_0">
<class name="Role" table="Role" lazy="true">
<id name="ID"> <column name="id"/> <generator class="increment" /> </id>
<bag name="People" table="Person" cascade="all" inverse="true"> <key column="IDRole" /> <one-to-many class="Person" /> <loader query-ref="AllPeopleForRole"/> </bag>
<property name="RoleDescription" column="RoleDescription" />
<loader query-ref="GetRoleByRoleID"/>
<sql-insert>exec [nhibInsertRole] ?,?</sql-insert>
<sql-delete check="none">exec [nhibDeleteRole] ?</sql-delete> </class>
<sql-query name="GetRoleByRoleID"> <return class="Role"/> exec [nhibGetRoleByRoleId] ? </sql-query> <sql-query name="AllRoleData"> <return class="Role"/> exec [nhibGetAllRoles] </sql-query> <sql-query name="AllPeopleForRole"> <load-collection alias="People" role="Role.People" /> exec [nhibGetPeopleByRoleId] ? </sql-query> <sql-query name="GetRoleByRoleDescription"> <return class="Role"/> exec [nhibGetRoleByDescription] @RoleDescription=:RoleDescription </sql-query> <sql-query name="GetRoleByRoleDescriptionAndID"> <return class="Role"/> exec [nhibGetRoleByDescriptionAndByID] @RoleDescription=:RoleDescription, @IDRole=:IDRole </sql-query> </hibernate-mapping>

The below XML is stored in the Salary.hbm.xml file and is used to specify mapping between the Salary data model of the database and the Salary application object model.

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" namespace="PersonNHibernateStoredProc_2_0" assembly="PersonNHibernateStoredProc_2_0">
<class name="Salary" table="Salary" lazy="true">
<id name="ID"> <column name="ID"/> <generator class="increment" /> </id>
<many-to-one name="Person" class="Person" column="IDPerson" cascade="save-update" />
<property name="Year" column="Year" /> <property name="SalaryYear" column="SalaryYear" />
<sql-insert>exec [nhibInsertSalary] ?,?,?,?</sql-insert>
<sql-delete check="none">exec [nhibDeleteSalary] ?</sql-delete> </class>
<sql-query name ="GetSalaryByValueAndByID"> <return class="Salary" /> exec [nhibGetSalaryByValueAndID] @SalaryYear=:SalaryYear, @ID=:SalaryID </sql-query> </hibernate-mapping>