Hibernate in HQL query

Transferred from: http://www.cnblogs.com/AnswerTheQuestion/p/6512701.html

This essay will document Hql's frequently used query statements to facilitate future viewing.

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{    private int id;    Private String name;    Private String type;    Private set<classroom> rooms;    ..........}

Classroom Class:

public class classroom{    private int id;    Private String name;    Private Special special;    Private Set<student> students;  ............}

Student Class:

public class student{    private int 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 Way 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 is 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 the 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")                                                . setparameter ("name", "% wang"). 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% ").                                                un　　　　　　　Iqueresult (); /**             * 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             */            
: Name and sex like:sex ").                                                setparameter (" name ","% sheet% "). setparameter (" sex ","% male ").                                                list ();

6. Query based on navigation object

/**             * If there is a navigation object 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 ")                                                . setparameter (" Guest box ","% computer application% "). Setpar Ameter ("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 placed in front of the alias             *///            list<student> stus = (list<student>) session.createquery ("select stu from Student Stu where is sex Like?") and stu.room.id in (:)//                                                . setparameter (0, "% female%"). setparameterlist ("guest", new integer[]{1, 2})/                                                . 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 (" guest ", new integer[]{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 passed through an alias, one is passed? The way to pass in the words, then the HQL statement through the alias and parameter settings statements to be placed in? back, or Hibernate will make an Error. If you are using aliases to set parameters, there is no precedence

8. Paging Query

/**             * via Setfirstresult (0). setmaxresults (10) You can set 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", "% of computer application"). setparameter ("sex", "% female%"). setfirstresult (0). setmaxresults (                                                ). list ();

9. Internal Connection Query

/**             *    Use the Object's navigation query to complete the connection query, but using the cross Join (cartesian product), The efficiency is not high, it is recommended to use Join to query             *            /list<student> Stus = ( list<student>) session.createquery ("select Stu from Student Stu join stu.room, where 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 classroom C on T.cid=c.id

10. Left Outer and right outer connection query

/**             *    left outer and right outer joins are actually relative, the LEFT join is the reference to the table on the right, and it is based on the table on the starboard *            /list<object[]> stus = (List <Object[]>) session.createquery ("select room.name, count (stu.room.id) from Student Stu right join Stu.room GROUP BY room.id ")                                                . list ();

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

/** *  When we query multiple fields, we typically create a Dto object that stores the data we have queried, through new XXX ()  *  as long as XXX This class must have to accept the construction method of these fields, and must use the full name of the class  *///list<object[]> stus = (L ist<object[]>) session.createquery ("select stu.id,stu.name,stu.sex,room.name,special.name from Student Stu            Left join Stu.room Box 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>) s Ession.createquery ("select New Com.xiaoluo.bean.StudentDTO (stu.id, stu.name, stu.sex, room.name, Special.name) from Student Stu left Join Stu.room Box left join room.special special ") 
. list (); 

12. Sub-query Statements

Sto sto= (sto) session.createquery ("from sto where crtadte= (select Max (s.crtadte) from sto s where S.storno Like?)")                        . setparameter (0, "%" +date+ "%")                        . getsingleresult ();

13.group having a clause

/**             * You cannot set aliases 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 has 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 ();

(go) Hibernate in HQL query