NHibernate Part 2 Natalie Vegerina Software engineer Infostroy

NHibernate. Part 2 Natalie Vegerina Software engineer Infostroy Ltd, Kharkov, Ukraine

Методы загрузки данных • • • SQL query HQL query Criteria query Query. Over LINQ to NHibernate

SQL

Scalar queries ISession session = Db. Session. Factory. Instance. Open. Session(); ISQLQuery query = session. Create. SQLQuery("SELECT * FROM CATS"). Add. Scalar("ID", NHibernate. Util. Int 64). Add. Scalar("NAME", NHibernate. Util. String). Add. Scalar("BIRTHDATE", NHibernate. Util. Date); IList results = query. List(); Output: object[] result = new object[3]; result = (object[])results[0]; long id = (long)result[0]; string name = (string)result[1]; Date. Time birthdate = (Date. Time)result[2];

Entity queries ISQLQuery query = session. Create. SQLQuery("SELECT * FROM CATS"). Add. Entity(typeof(Cat)); ISQLQuery query = session. Create. SQLQuery(@"SELECT ID, NAME, BIRTHDATE FROM CATS"). Add. Entity(typeof(Cat)); IList results = query. List(); Output: Cat cat = results[0] as Cat;

Returning non-managed entities session. Create. SQLQuery("SELECT NAME, BIRTHDATE FROM CATS"). Set. Result. Transformer(Transformers. Alias. To. Bean(typeof (Cat. DTO)));

Named SQL queries <sql-query name="persons"> <return alias="person" class="eg. Person"/> SELECT person. NAME AS {person. Name}, person. AGE AS {person. Age}, person. SEX AS {person. Sex} FROM PERSON person WHERE person. NAME LIKE : name. Pattern </sql-query> IList people = session. Get. Named. Query("persons"). Set. String("name. Pattern", "smith"). Set. Max. Results(50). List();

Named SQL queries <sql-query name="my. Sql. Query"> <return-scalar column="name" type="String"/> <return-scalar column="age" type="Int 64"/> SELECT p. NAME AS name, p. AGE AS age, FROM PERSON p WHERE p. NAME LIKE 'Hiber%' </sql-query>

Named SQL queries <sql-query name="my. Sql. Query"> <return alias="person" class="eg. Person"> <return-property name="Name" column="my. Name"/> <return-property name="Age" column="my. Age"/> <return-property name="Sex" column="my. Sex"/> </return> SELECT person. NAME AS my. Name, person. AGE AS my. Age, person. SEX AS my. Sex, FROM PERSON person WHERE person. NAME LIKE : name </sql-query>

Using stored procedures for querying CREATE PROCEDURE select. All. Employments AS SELECT EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE, REGIONCODE, EMPID, VALUE FROM EMPLOYMENT

Using stored procedures for querying <sql-query name="select. All. Employments_SP"> <return alias="emp" class="Employment"> <return-property name="employee" column="EMPLOYEE"/> <return-property name="employer" column="EMPLOYER"/> <return-property name="start. Date" column="STARTDATE"/> <return-property name="end. Date" column="ENDDATE"/> <return-property name="region. Code" column="REGIONCODE"/> <return-property name="id" column="EID"/> </return> exec select. All. Employments </sql-query>

Custom SQL for create/update/delete <class name="Person"> <id name="id"> <generator class="increment"/> </id> <property name="name" not-null="true"/> <sql-insert>INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(? ), ? )</sql-insert> <sql-update>UPDATE PERSON SET NAME=UPPER(? ) WHERE ID=? </sql-update> <sql-delete>DELETE FROM PERSON WHERE ID=? </sql-delete> </class>

Stored procedures for create/update/delete <class name="Person"> <id name="id"> <generator class="increment"/> </id> <property name="name" not-null="true"/> <sql-insert>exec create. Person ? , ? </sql-insert> <sql-delete>exec delete. Person ? </sql-delete> <sql-update>exec update. Person ? , ? </sql-update> </class>

Custom SQL for loading <class name="Person"> <id name="Id"> <generator class="increment"/> </id> <property name="Name" not-null="true"/> <loader query-ref="person"/> </class> <sql-query name="person"> <return alias="pers" class="Person" lock-mode="upgrade"/> SELECT NAME AS {pers. Name}, ID AS {pers. Id} FROM PERSON WHERE ID=? FOR UPDATE </sql-query>

HQL

Scalar and entity queries string hql = "from Product p"; var products = session. Create. Query(hql). List(); var products = session. Create. Query(hql). List<Product>();

Filtering, sorting and paging string hql = @"from Product p where p. Discontinued and p. Category = : category and p. Unit. Price <= : unit. Price order by p. Name"; var cheap. Fruits = session. Create. Query(hql). Set. String("category", "Fruits"). Set. Decimal("unit. Price", 1. 0 m). Set. First. Result(10). Set. Max. Results(10). List<Product>();

Unique result IQuery query = session. Create. Query("select count(*) from Product"); int count = Convert. To. Int 32(query. Unique. Result()); int count = query. Unique. Result<int>();

Result transformers var products. Lookup = session. Create. Query("select Id as Id, Name as Name from Product"). Set. Result. Transformer(Transformers. Alias. To. Bean<Name. ID>()). List<Name. ID>();

Grouping var products. Grouped = session. Create. Query(@"select p. Category as Category, count(*) as Count, avg(p. Unit. Price) as Average. Price from Product p group by p. Category"). List();

Grouping with transformers var products. Grouped = session. Create. Query(@"select p. Category as Category, count(*) as Count, avg(p. Unit. Price) as Average. Price from Product p group by p. Category"). Set. Result. Transformer(Transformers. Alias. To. Entity. Map). List<IDictionary>(). Select(r => new { Category = r["Category"], Count = r["Count"], Average. Price = r["Average. Price"], });

Join var products = session. Create. Query(@"select p from Product p left join p. Category as c left join c. Type t where t. Name = 'Fruits'"). List<Product>(); var hql = @"select p from Person as p left join fetch p. Hobbies as h"; var list. Of. Persons = session. Create. Query(hql). List<Person>();

Multi query public void Get. Page. Of. Products(int page. Number, int page. Size) { ISession session = Db. Session. Factory. Instance. Open. Session(); int skip = (page. Number - 1)*page. Size; string count. Hql = @"select count(p. Id) from Product p"; IQuery count. Query = session. Create. Query(count. Hql); var product. Hql = @"from Product p order by p. Unit. Price asc"; IQuery result. Query = session. Create. Query(product. Hql). Set. First. Result(skip). Set. Max. Results(page. Size); IMulti. Query multi. Query = session. Create. Multi. Query(). Add<long>("count", count. Query). Add<Product>("page", result. Query); long product. Count = ((IList<long>) multi. Query. Get. Result("count")). Single(); IList<Product> products = (IList<Product>) multi. Query. Get. Result("page"); }

Named queries <query name="Count. All. Products"> <![CDATA[ select count(p. Id) from Product p ]]> </query> <query name="Get. All. Products"> <![CDATA[ from Product p order by p. Unit. Price asc ]]> </query>

Named queries & futures public void Get. Page. Of. Products(int page. Number, int page. Size) { ISession session = Db. Session. Factory. Instance. Open. Session(); var skip = (page. Number - 1)*page. Size; var product. Count = session. Get. Named. Query("Count. All. Products"). Future. Value<long>(); var products = session. Get. Named. Query("Get. All. Products"). Set. First. Result(skip). Set. Max. Results(page. Size). Future<Product>(); var page. Count = (int) Math. Ceiling( product. Count. Value/(double) page. Size); }

Detached query string hql = @"from Product p where p. Discontinued"; IDetached. Query detached. Query = new Detached. Query(hql); IQuery executable. Query = detached. Query. Get. Executable. Query(session); IList result = executable. Query. List();

Bulk data changes var update. Hql = "update Product p set p. Unit. Price = 1. 1 * p. Unit. Price"; session. Create. Query(update. Hql). Execute. Update(); var delete. Hql = "delete Product p where p. Discontinued = true"; session. Create. Query(delete. Hql). Execute. Update(); var insert. Hql = @"insert into Product(Id, Name, Category, Unit. Price) select t. Id, t. Name, t. Category, t. Unit. Price from Product. Temp t"; session. Create. Query(insert. Hql). Execute. Update();

Criteria

Restrictions List<Product> products = session. Create. Criteria<Product>(). Add(Restrictions. Eq("Name", product. Name)). Add. Order(Order. Asc("Unit. Price")). List<Product>();

Restrictions • • • Eq, Eq. Property Ge, Gt, Ge. Property, Gt. Property Le, Lt, Le. Property, Lt. Property Like In Between Not Is. Null Is. Not. Null Where And Or

Restrictions List<Product> products = session. Create. Criteria<Product>(). Add(Restrictions. And( Restrictions. Ge("Unit. Price", min. Price), Restrictions. Le("Unit. Price", max. Price) )). Add. Order(Order. Asc("Unit. Price")). List<Product>();

Join IList<Category> categories = session. Create. Criteria<Category>(). Create. Criteria("Products", Join. Type. Inner. Join). Add(Restrictions. Eq("Discount", 0)). List<Category>();

Paging List<Product> products = session. Create. Criteria<Product>(). Add(Restrictions. Eq("Name", product. Name)). Set. First. Result(10). Set. Max. Results(10). List<Product>();

Projections IList products = session. Create. Criteria<Product>(). Add(Restrictions. Eq("Name", product. Name)). Set. Projection(Projections. Projection. List(). Add(Projections. Property("Id")). Add(Projections. Property("Name"))). Set. Result. Transformer(Transformers. Alias. To. Bean(typeof(Name. ID))). List();

Aggregate functions var product. Count = session. Create. Criteria<Product>(). Add(Restrictions. Eq("Name", product. Name)). Set. Projection(Projections. Row. Count()). Unique. Result();

Multi criteria public void Get. Page. Of. Products(int page. Number, int page. Size) { ISession session = Db. Session. Factory. Instance. Open. Session(); int skip = (page. Number - 1) * page. Size; ICriteria row. Count = session. Create. Criteria(typeof(Product)). Set. Projection(Projections. Count(Projections. Id())); ICriteria criteria = session. Create. Criteria(typeof(Product)). Add(Restrictions. Gt("Unit. Price", 0)). Add. Order(Order. Asc("Unit. Price")). Set. First. Result(skip). Set. Max. Results(page. Size); IMulti. Criteria multi. Criteria = session. Create. Multi. Criteria(). Add<long>("count", row. Count). Add<Product>("page", criteria); long product. Count = ((IList<long>)multi. Criteria. Get. Result("count")). Single(); IList<Product> products = (IList<Product>)multi. Criteria. Get. Result("page"); }

Detached criteria Detached. Criteria detached. Criteria = Detached. Criteria. For<Product>(). Add(Restrictions. Like("Name", product. Name)); IList results = detached. Criteria. Get. Executable. Criteria(session). List();

Query. Over

Query. Over IList<Product> results = session. Query. Over<Product>(). Where(x => x. Product. Type == Product. Types. Product. Type. A). Order. By(x => x. Name). Desc. List();

Query. Over IList<Product> results = session. Query. Over<Product>(). Where. Restriction. On(x => x. Unit. Price). Is. Between(20). And(50). Order. By(x => x. Name). Desc. List();

Query. Over IList<Name. Price> results = session. Query. Over<Product>(). Select(m => m. Name, m => m. Unit. Price). List<object[]>(). Select(props => new Name. Price() { Name = (string) props[0], Price = (decimal) props[1] });

Projections var result = session. Query. Over<Product>(). Select(Projections. Avg<Product>(m => m. Unit. Price)). Single. Or. Default<double>();

Join, paging IList<Category> categories = session. Query. Over<Category>(). Inner. Join. Query. Over(x => x. Products). Skip(20). Take(10). List<Category>();

Session methods

Session actions using (ISession session = Db. Session. Factory. Instance. Open. Session()) { using (ITransaction transaction = session. Begin. Transaction()) { // create, update, delete, or read data transaction. Commit(); } }

Session methods • • • • Insert Save Update Save. Or. Update Delete Get Load Merge Flush Refresh Evict Clear Close

Литература • • Benjamin Perkins - Working with Nhibernate 3. 0 Jason Dentler – Nhibernate 3. 0. Cookbook Dr. Gabriel Nicolas Schenker, Aaron Cure – Nhibernate 3. Beginners guide SQL in Nhibernate - http: //knol. google. com/k/nhibernate-chapter-14 native-sql#