Hibernate (15): QBC Retrieval and local SQL retrieval

• QBC Search

QBC queries are querying objects by using the query by Criteria API provided by Hibernate, which encapsulates the dynamic assembly of SQL statements and provides a more object-oriented interface to queries.

1) Implement conditional queries with critera

1 @Test2      Public voidtestCriteria00 () {3         //1. Create Criteria Object4Criteria = Session.createcriteria (Employee.class);5 6         //2, add filter condition can use criterion table, criterion can be returned by restrictions static method. 7Criteria.add (Restrictions.eq ("email", "[email protected]"));8Criteria.add (restrictions.gt ("salary", 1000F));9 Ten System.out.println (Criteria.uniqueresult ()); One}

Execute SQL and Result:

Hibernate:Selectthis_.id asid1_1_0_, This_.name asname2_1_0_, This_. SALARY assalary3_1_0_, This_. EMAIL asemail4_1_0_, This_. department_id asdepartme5_1_0_ fromDx_employee This_whereThis_. EMAIL=?  andThis_. SALARY>? Employee[id=11, Name=tommy10, salary=10000.0, [email protected]]

2) Implement queries with and or conditions through critera

1 @Test2      Public voidTestcriteraandor () {3Criteria = Session.createcriteria (Employee.class);4 5         //and : Using conjunction, conjunction is itself a criterion object, and it can also add criterion objects6conjunction conjunction =restrictions.conjunction ();7Conjunction.add (Restrictions.like ("name", "2", Matchmode.anywhere));8Department depart =NewDepartment ();9Depart.setid (5);TenConjunction.add (Restrictions.eq ("department", Depart)); One  A         //OR -Disjunction disjunction =restrictions.disjunction (); -Disjunction.add (restrictions.gt ("salary", 10000F)); theDisjunction.add (restrictions.isnull ("email")); -  - Criteria.add (disjunction); - Criteria.add (conjunction); +  -list<employee> items = (list<employee>) criteria.list (); + System.out.println (Items.size ()); A}

Execute SQL and Result:

1 Hibernate:2     Select3This_.id asid1_1_0_,4This_.name asname2_1_0_,5This_. SALARY assalary3_1_0_,6This_. EMAIL asemail4_1_0_,7This_. department_id asdepartme5_1_0_8      from9 Dx_employee This_Ten     where One         ( AThis_. SALARY>?  -             orThis_. EMAIL is NULL -         )  the          and ( -This_.name like ?  -              andThis_. department_id=? -         ) + 2

3) Statistical query via critera: Use projection to represent

1 @Test2      Public voidTeststatistics () {3Criteria Criteria=session.createcriteria (Employee.class);4         5         //statistical query: Use projection to represent6Criteria.setprojection (Projections.max ("salary"));7         8 System.out.println (Criteria.uniqueresult ()); 9}

Execute SQL and Result:

12     Select3         Maxas 4      from 5         dx_employee this_679000.0

4) Sorting and paging through critera

1 @Test2      Public voidTestorderbyandpager () {3Criteria = Session.createcriteria (Employee.class);4 5         //1) Order by6Criteria.addorder (Order.desc ("salary"));7Criteria.addorder (Order.desc ("name"));8 9         //2) PagerTen         intPageSize = 5; One         intPagenum = 2; AList<employee> employees = (list<employee>) criteria.setfirstresult ((pageNum-1) *pageSize). Setmaxresults (pageSize). List (); -          - System.out.println (Employees.size ()); the}

Execute SQL and Result:

1 Hibernate:2     Select3This_.id asid1_1_0_,4This_.name asname2_1_0_,5This_. SALARY assalary3_1_0_,6This_. EMAIL asemail4_1_0_,7This_. department_id asdepartme5_1_0_8      from9 Dx_employee This_Ten     Order  by OneThis_. SALARYdesc, AThis_.namedesclimit?, -         ? - 5

For more information on how to use QBC, refer to the Hibernate official website example.

• Local SQL query

Local SQL queries to refine HQL cannot cover all of the query features.

Hibernate (15): QBC Retrieval and local SQL retrieval