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 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 IList people = session. Get. Named. Query( «persons» ) . Set. String( «name. Pattern» , «smith» ) . Set. Max. Results(50) . List();

Named SQL queries SELECT p. NAME AS name, p. AGE AS age, FROM PERSON p WHERE p. NAME LIKE ‘Hiber%’

Named SQL queries SELECT person. NAME AS my. Name, person. AGE AS my. Age, person. SEX AS my. Sex, FROM PERSON person WHERE person. NAME LIKE : name

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 exec select. All. Employments

Custom SQL for create/update/delete INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(? ), ? ) UPDATE PERSON SET NAME=UPPER(? ) WHERE ID=? DELETE FROM PERSON WHERE ID=?

Stored procedures for create/update/delete exec create. Person ? , ? exec delete. Person ? exec update. Person ? , ?

Custom SQL for loading SELECT NAME AS {pers. Name}, ID AS {pers. Id} FROM PERSON WHERE ID=? FOR UPDATE

HQL

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

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();

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();

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

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() . 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(); var hql = @»select p from Person as p left join fetch p. Hobbies as h» ; var list. Of. Persons = session. Create. Query(hql) . List();

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( «count» , count. Query) . Add( «page» , result. Query); long product. Count = (( IList ) multi. Query. Get. Result( «count» )). Single(); IList products = ( IList ) multi. Query. Get. Result( «page» ); }

Named queries

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(); var products = session. Get. Named. Query( «Get. All. Products» ) . Set. First. Result(skip) . Set. Max. Results(page. Size) . Future(); 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 products = session. Create. Criteria() . Add( Restrictions. Eq( «Name» , product. Name)) . Add. Order( Order. Asc( «Unit. Price» )) . List();

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 products = session. Create. Criteria() . Add( Restrictions. And( Restrictions. Ge( «Unit. Price» , min. Price), Restrictions. Le( «Unit. Price» , max. Price) )) . Add. Order( Order. Asc( «Unit. Price» )) . List();

Join IList categories = session. Create. Criteria() . Create. Criteria( «Products» , Join. Type. Inner. Join) . Add( Restrictions. Eq( «Discount» , 0)) . List();

Paging List products = session. Create. Criteria() . Add( Restrictions. Eq( «Name» , product. Name)) . Set. First. Result(10) . Set. Max. Results(10) . List();

Projections IList products = session. Create. Criteria(). 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() . 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( «count» , row. Count) . Add( «page» , criteria); long product. Count = (( IList )multi. Criteria. Get. Result( «count» )). Single(); IList products = ( IList )multi. Criteria. Get. Result( «page» ); }

Detached criteria Detached. Criteria detached. Criteria = Detached. Criteria. For() . Add( Restrictions. Like( «Name» , product. Name)); IList results = detached. Criteria. Get. Executable. Criteria(session). List();

Query. Over

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

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

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

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

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

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#