NHibernate. Part 2 Natalie Vegerina Software engineer Infostroy

Скачать презентацию NHibernate. Part 2 Natalie Vegerina Software engineer Infostroy Скачать презентацию NHibernate. Part 2 Natalie Vegerina Software engineer Infostroy

my_nhibernate_p2.ppt

  • Размер: 1.9 Mегабайта
  • Количество слайдов: 47

Описание презентации NHibernate. Part 2 Natalie Vegerina Software engineer Infostroy по слайдам

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

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

SQL SQL

Scalar queries ISession session = Db. Session. Factory. Instance. Open. Session(); ISQLQuery query = session. Create.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( typeofEntity 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. 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  aliasNamed 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  sql-query  name =  my. Sql. Query    return-scalarNamed SQL queries SELECT p. NAME AS name, p. AGE AS age, FROM PERSON p WHERE p. NAME LIKE ‘Hiber%’

Named SQL queries  sql-query  name =  my. Sql. Query    returnNamed 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 , 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  Using stored procedures for querying exec select. All. Employments

Custom SQL for create/update/delete  class  name =  Person    id 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  class  name =  Person    id Stored procedures for create/update/delete exec create. Person ? , ? exec delete. Person ? exec update. Person ? , ?

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

HQL HQL

Scalar and entity queries string hql = from Product p ; var products = session. Create.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. CategoryFiltering, 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 =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 asResult 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(*)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,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.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)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  query  name =  Count. All. Products   ![CDATA[ select count(p.Named queries

Named queries & futures public  void Get. Page. Of. Products( int page. Number,  intNamed 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. QueryDetached 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.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 Criteria

Restrictions List  Product  products = session. Create. Criteria Product () . Add( Restrictions. Eq(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.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 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  Category  categories = session. Create. Criteria Category () . Create. Criteria( ProductsJoin IList categories = session. Create. Criteria() . Create. Criteria( «Products» , Join. Type. Inner. Join) . Add( Restrictions. Eq( «Discount» , 0)) . List();

Paging List  Product  products = session. Create. Criteria Product () . Add( Restrictions. Eq(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 Product (). Add( Restrictions. Eq( Name , product. Name)).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 Product () . Add( Restrictions. Eq( NameAggregate 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)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 Product () . Add( Restrictions. Like(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

Query. Over IList  Product  results = session. Query. Over Product () . Where(x =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  Product  results = session. Query. Over Product () . Where. Restriction.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  Name. Price  results = session. Query. Over Product () . Select(mQuery. 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 Product () . Select( Projections. Avg Product (m =Projections var result = session. Query. Over() . Select( Projections. Avg(m => m. Unit. Price)) . Single. Or. Default();

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

Session methods Session methods

Session actions using ( ISession session = Db. Session. Factory. Instance. Open. Session()) { using (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 • GetSession 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.Литература • 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#