Criteria query for Hibernate

Project to use the criteria of the query method, feel very good, after work to find a bit of information, while testing, while on the blog record down

1. Initial Solution

A quick tour of the information, a general understanding of the following:

1. Hibernate defines the Criteriaspecification interface specification used to accomplish object-oriented conditional queries, and the criteria and Detachedcriteria are criteriaspecification sub-interfaces.

2. The main difference between the criteria and the Detachedcriteria is that the form is different, the criteria is created online, which is created by the session, the Detachedcriteria is offline, no session is created, Detachedcriteria provides 2 static methods Forclass (Class) or Forentityname (Name) for Detachedcriteria instance creation

3. Restrictions is a tool class that can create instances of criterion

Also swallowed looked under, the following directly into the test:

2. How to use

1. Check All

/*** Function: Check all * Description: Through list (), return the Entity object collection*/@Test Public voidtest1 () {//Create a connectionSession session =hibernateutil.getcurrentsession (); //Open TransactionTransaction tx =session.begintransaction (); //Create CriteriaCriteria = Session.createcriteria (User.class, "U"); //Businesslist<user> list =criteria.list ();  for(user user:list) {System.out.println (user); }        //Close TransactionSession.close (); }

2. Conditional query

1) One condition

/**      * Function: or () Usage     * Description: Returns a collection of entity objects by or ()     * Add      (Criterion Criterion) for adding a query condition     *      Restrictions can     create criterion*//criteria by using the built-in static method        = Session.createcriteria (User.  Class, "U");         // Business: Query for users        with ID 1 or 2 list<user> list = Criteria.add (Restrictions.or (RESTRICTIONS.EQ ("id", 1),                                     restrictions.eq ("id ", 2))                                    . List ();

2) Multiple conditions

/**      * Function: Multiple conditions     : the use of like () and GT () *//Criteria        = Session.createcriteria (User.  Class, "U");         // Business: Query out ID greater than 1, name of the user        with Ji list<user> list = Criteria.add (Restrictions.like ("name", "%ji%"))                                  . Add (restrictions.gt ("id", 1)                                  . List ();

Other conditions will not be tested, the following list of common restrictions methods

<  less than equals <  corresponds to SQL between clause restrictions.like a LIKE clause corresponding to SQL restrictions.in The IN clause of the corresponding SQL Restrictions.and and relationship restrictions.or or relationship

3. Sorting

/**      * Function: Sort     * Description: Add sorting criteria by AddOrder ()     *         order set collation, desc is descending, ASC is ascending      */// criteria = Session.createcriteria (User.          Class, "U");         // business: For query results, sort        by ID list<user> list = Criteria.addorder (ORDER.DESC ("id"))                                   . List ();

4. Association

The user entity class has a relation property of orders,

The data in the database is as follows: The foreign key in the order table is user_id

User table:

Order table:

The code is as follows:

/*** Function: Association * Description: Set relationship properties by CreateAlias () * For defined correlation, we can use CreateAlias () to create an alias for the property, and then reference the alias for conditional query, such as:            In this case, the result set of the parent object that is associated with the query can be obtained, in this example, the user is the parent class * * Hibernate print SQL statement as follows: * Select This_.id as id0_1_, this_.age as age0_1_, This_.birth as birth0_1_, this_.name as Name0_1            _, O1_.id as id1_0_, o1_.note as note1_0_, O1_.price as price1_0_ from  User50 this_ INNER JOIN Order50 o1_ on this_.id=o1_.user_id where o1_.id =                 ? * The console is printed as follows: * User [Userid=1, Name=zhangjifeng, Age=18, birthday=2016-07-26]*/@Test Public voidTest5 () {//Create a connectionSession session =hibernateutil.getcurrentsession (); //Open TransactionTransaction tx =session.begintransaction (); //Create CriteriaCriteria = Session.createcriteria (User.class, "U"); //Business:list<user> list = Criteria.createalias ("Orders", "O"). Add (Restrictions.eq ("O.id", 1) . List ();  for(user user:list) {System.out.println (user); }                //Close TransactionSession.close (); }

Association 2:createcriteria ()

The test code is as follows

/*** Second Createcriteria (), returns a new instance * print out user and order*/@Test Public voidTest6 () {//Create a connectionSession session =hibernateutil.getcurrentsession (); //Open TransactionTransaction tx =session.begintransaction (); //Create CriteriaCriteria = Session.createcriteria (User.class, "U"); //Business:List List = Criteria.createcriteria ("Orders", "O"). Add (Restrictions.eq ("O.id", 1) . Setresulttransformer (Criteria.alias_to_entity_map)        . List (); Iterator Iterator=List.iterator ();  while(Iterator.hasnext ()) {map map=(MAP) iterator.next (); //Set KeySet = Map.keyset ();Set EntrySet=Map.entryset (); Iterator Iterator2=Entryset.iterator ();  while(Iterator2.hasnext ()) {Map.entry<string, object> map1 = (entry<string, object>) Iterator2.next ();            System.out.println (Map1.getvalue ()); }        }        //Close TransactionSession.close (); }

5.Projections Projection, counting rows, not repeating results

1) Use the results in the result set as rows and column sets, similar to how the data obtained by executing a select query through JDBC is used. Therefore, hibernate also supports queries such as attributes, statistical functions, and GROUP by.

2) to use Hibernate's projection statistics function, first obtain the Org.hibernate.criterion.Projection object from the Org.hibernate.criterion.Projections factory class

3) Hibernate's projections factory class contains several commonly used statistical functions:

①avg (String PropertyName): Calculates the average of an attribute field.

②count (String PropertyName): Counts the number of occurrences of an attribute in the result.

③countdistinct (String PropertyName): The Count property contains the number of distinct values.

④max (String PropertyName): Calculates the maximum value of a property value.

⑤min (String PropertyName): Calculates the minimum value of a property value.

⑥sum (String PropertyName): Calculates the sum of the property values.

The code is as follows:

/*** Projection: Projections * statistics, repetition*/@Test Public voidTest8 () {//Create a connectionSession session =hibernateutil.getcurrentsession (); //Open TransactionTransaction tx =session.begintransaction (); //Create CriteriaCriteria = Session.createcriteria (Order.class, "O"); //Business:list<object[]> list =criteria.setprojection (Projections.projectionlist (). Add (Projections.max ( c2>"Price"). Add (Projections.min ("Price"). Add (Projections.avg ("Price"). Add (Projections.countdistinct ("Note")) . List ();  for(object[] objects:list) { for(Object object:objects) {System.out.println (object); }        }        //Close TransactionSession.close ();

The result is a separate property rather than an entity class. For example, a product table contains many fields, and we want to get the names and descriptions in the product table without having to load the entire entity into memory

/*** Projection: Projections * Get partial fields*/@Test Public voidTest9 () {//Create a connectionSession session =hibernateutil.getcurrentsession (); //Open TransactionTransaction tx =session.begintransaction (); //Create CriteriaCriteria = Session.createcriteria (Order.class, "O"); //Business:Projectionlist projlist =projections.projectionlist (); Projlist.add (Projections.property ("Name")); Projlist.add (Projections.property ("Price"));         Criteria.setprojection (projlist); List result=criteria.list ();  for(Object Object:result) {System.out.println (object); }        //Close TransactionSession.close (); }

Just record here, hands shaking in ~~~~~~

Criteria query for Hibernate