Hibernate's HQL query statement summary

<div class="post">    

Here by defining three classes, special, classroom, student to do the test, special and classroom is a one-to-many, classroom and student is a one-to-many relationship, here just paste the property code of these three beans:

Special class:

 Public class special{    privateint  ID;     Private String name;     Private String type;     Private Set<classroom> Rooms;    ..........}

Classroom class:

 Public class classroom{    privateint  ID;     Private String name;     Private special special;     Private set<student> students;  ............}

Student class:

 Public class student{    privateint  ID;     Private String name;     Private String sex;     Private Classroom;    ..........}

1. The simplest query

List<special> specials = (list<special>) session.createquery ("Select SPE from Special SPE"). List ();

This is the HQL most basic query statement, the function is to find out all the special objects put into a list

2. Based on? The parameterized form

/**              * Query in use?, by Setparameter to prevent SQL injection             * JDBC Setparameter subscript starting from 1, hql subscript starting from             0 * /            List<Student> students = (list<student>) session.createquery ("Select Stu from Student Stu where Name like? " )                                                . Setparameter (0, "% Liu")                                                . List ();

In HQL also support based on? Parametric form query, Note : in JDBC, the subscript for Setparameter is starting from 1, and Hibernate's setparameter subscript is starting from 0 .

3. Setting parameters based on the alias of: XX

/**              * In HQL can be used to query the way alias, the format is: XXX through setparameter to set             the alias */             List<Student> Students = (list<student>) session.createquery ("Select Stu from Student Stu where name like:name and sex Like:sex" c7>).                                                setparameter ("name", "% king"). Setparameter ("Sex", "% male%")                                                . List ();

4. If the returned value is only one, you can use the Uniqueresult method

/*** If you get only one value, you can use the Uniqueresult method*/Long Stu= (Long) session.createquery ("SELECT count (*) from Student Stu where name like:name and sex Like:sex"). Setparameter ("Name", "% king"). Setparameter ("Sex", "% male%")). Uniqueresult (); /*** If you get only one value, you can use the Uniqueresult method*/Student Stu= (Student) session.createquery ("Select Stu from Student stu where id =?"). Setparameter (0, 1). Uniqueresult ();

5. Projection-based queries

/**              * Projection-based query, if multiple values are returned, these values are stored in a object[] array              */            List
: Name and Sex Like:sex ").                                                setparameter (" name ","% sheet% "). Setparameter (" Sex ","% male ").                                                list ();

6. Query based on navigation object

/**              * If there are navigation objects in the object, you can query             directly through the object navigation */            List<Student> stus = (list<student >) session.createquery ("Select Stu from Student Stu where stu.room.name like:room and sex like:sex")                                                . SetParam Eter ("Guest box", "% computer application%"). Setparameter ("Sex", "% female%")                                                . List ();

Note : If you are querying directly from a navigation object, it is actually using cross join (Cartesian product) to make a connection query, which is poor performance and does not recommend using

7. Using in to make a list query

/*** You can use in to set up a list-based query, use an in query to use an alias to set parameters, * can be set by the Setparameterlist method, when using the alias and the HQL statement query, the form of the query must be put In front of the alias*///list<student> stus = (list<student>) session.createquery ("Select Stu from Student stu where sex Like? and Stu.room.id in (: Guest) ")//. Setparameter (0, "% female%"). Setparameterlist ("Guest", New Integer[]{1, 2}) /c5>//. List ();List<student> Stus = (list<student>) session.createquery ("Select Stu from Student Stu where Stu.room.id in (: R Oom) and Stu.sex like:sex "). Setparameterlist ("The Hostel",NewInteger[]{1, 2}). Setparameter ("Sex", "% female%"). List ();

When using in for the list query, this time to use the Setparameterlist () method to set our parameters, Note : If a parameter is passed through an alias, a way to pass through, What about the HQL statement with the alias and the parameter setting statement? Back, or hibernate will make an error. If you are using aliases to set parameters, there is no precedence

8. Paging Query

/**              * through Setfirstresult (0). Setmaxresults (10) You can set up a paging query equivalent to offset             and pagesize*/            List <Student> stus = (list<student>) session.createquery ("Select Stu from Student Stu where stu.room.name Like:room and Sex Like:sex ").                                                Setparameter (" Guest ","% computer "). Setparameter (" Sex ","% female% "). Setfirstresult (0). Setmaxresults (                                                ). List ();

9. Internal Connection Query

/**              *    using the object's navigation query can complete the connection query, but using the cross Join (cartesian product), inefficient, it is recommended to use Join to query              */            List <Student> stus = (list<student>) session.createquery ("Select Stu from Student stu join Stu.room hostel wher E room.id=2 ")                                                . List ();

There is a difference between statements that use connection queries in HQL and our SQL for connection queries:

hql:    Select Stu from Student stu join Stu.room hostelSQL:    select t.* from Student t join Cla Ssroom C on T.cid=c.id

10. Left Outer and right outer connection query

/**              * The    left and right outer joins are relative, and the left join is based on the table on the right side, and the join is based on the table             at the top */            list< Object[]> Stus = (list<object[]>) session.createquery ("Select Room.name, Count (stu.room.id) from Student Stu Right join Stu.room Guest GROUP by Room.id ")                                                . List ();

11. Create a DTO class to store multiple queried fields in a Dto object

/*** When we query out multiple fields, we typically create a Dto object that stores the data we query, via new XXX () * If xx X This class must have a construction method that accepts these fields, and must use the full name of the class *///list<object[]> stus = (list<object[]>) session.createquery ("Select Stu.id,stu.name,stu.sex,room . Name,special.name from Student Stu left join Stu.room the left join Room.special special ")//. List ();//For (object[] obj:stus)//            {//System.out.println (obj[0] + "," + obj[1] + "," + obj[2] + "," + obj[3] + "," + obj[4]);//            }

List<studentdto> Stus = (list<studentdto>) session.createquery ("Select New Com.xiaoluo.bean.StudentDTO ( Stu.id, Stu.name, Stu.sex, Room.name, special.name) from Student Stu left join Stu.room Hostel left join Room.special specia L ")
. List ();

12.group having a clause

/**              * Aliases cannot be set in HQL by the fields that are queried, aliases can only be set from             the back */            list<Object[]> stus = (list <Object[]>) session.createquery ("Select Special.name, Count (stu.room.special.id) from Student Stu right Join　　Stu.room.special Special GROUP BY Special.id have Count (stu.room.special.id) >150 ")                                                . List (); Find out more than 150 people in the professional

　　　　　　　
Find out the number of boys and girls in each major
List<object[]> Stus = (list<object[]>) session.createquery ("Select Special.name, Stu.sex, COUNT ( stu.room.special.id) from Student Stu Right join Stu.room.special special GROUP by Special.id,stu.sex ")                                                . List () ;

Basically use the HQL query statement is these, later if met to add.

If Count (1) is used in Tip:hsql, it can be executed with count (*) or count (ID).

Hibernate's HQL query statement summary