HQL is Hibernate query language that is hibernate querying language1. The steps to execute the HQL statement:(1), Get Session objectConfiguration cfg = new configuration ();sessionfactory sessionfactory = Cfg.buildsessionfactory ();Session session = Sessionfactory.getcurrentsession ();(2), write HQL statement(3), create querysession.createquery (HQL);(4), execute the query, get the query results. Execute HQL statement 2. Binding parameters in the HQL statementmethod One: Bind by parameter positionQuery q = hibernatesessionfactory.getsession (). CreateQuery ("from User u where u.name like =?");q.setparameter (0, "");method Two: Binding by parameter nameQuery q = hibernatesessionfactory.getsession (). CreateQuery ("from User u where u.name like:name");q.setparameter ("name", "");SetProperties (can be an object and a collection) 3. Dynamic QueryString hql= "from Users u where u.name=? and u.password=? "; Session.createquery (HQL). Setparameter (0," Xiaoming "). Setparameter (1," 123456 "); Public list<user> findUser2 (object[] obj) {Query q = hibernatesessionfactory.getsession (). CreateQuery ("from User U where u.name like? "); for (int i = 0; i < obj.length; i++) {Q.setparameter (I, Obj[i]);} return Q.list ();}4, projection query hql projection query is one or more property values that query a persisted classencapsulate each query result as an objectString sql= "Select U.name from User u";encapsulates each query result into an object arrayString sql= "Select u.name,u.id from User u";encapsulates each query result into an object through a constructor functionString sql= "Select New Login (U.name,u.passwoord) from User u";5, paging queryUniqueresult () method gets a unique objectPublic long Findcount (String hql) {Object o = (Object) session.createquery (HQL). Uniqueresult (); return Long.parselong ( O.tostring ());}Setfirstresult () method setting starts with the first fewSetfirstresult ((page-1) * size)Setmaxresults () method sets the maximum number of records read. Setmaxresults (size) public list<user> findbypage (String hql, map<string, object> user,int page, int size) { Return Hibernatesessionfactory.getsession (). CreateQuery (HQL). SetProperties (user). Setfirstresult ((page-1) * size). Setmaxresults (size). List ();} Get total number of Bars select COUNT (*) count from ... public Long findcountofuser (String hql, map<string, object> use R) {return (Long) hibernatesessionfactory.getsession (). CreateQuery ("SELECT count (*) from User"). SetProperties (user). Uniqueresult ();}6, Hibernate hql Query SummarySpecial 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 The hostel, ...} ---------------------------------------------(1). The simplest queryList<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 placed in a list (2).parametric form based on?/** * Query use?, by Setparameter Way can 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?"). Setparameter (0, "% Liu"). List () ;In HQL also support based on? The parameterized form of the query,Note: in JDBC, the subscript for Setparameter is starting at 1, and the subscript for Hibernate Setparameter starts at 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>) SES Sion.createquery ("Select Stu from Student Stu where name like:name and sex Like:sex"). Setparameter ("name", "% king"). Setpa Rameter ("Sex", "% male%"). List ();(4).If there is only one value returned, 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<object[]> Stus = (list<object[]>) session.creat Equery ("Select Stu.name, Stu.sex from Student Stu where name is like: Name and Sex Like:sex "). Setparameter (" name ","%% "). Setparameter (" Sex ","% man "). 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 St U from Student Stu where stu.room.name like:room and Sex Like:sex "). Setparameter (" 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).querying a list using in/** * 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 (: Guest))//. 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 is passed through an alias, and one is passed by, then the HQL statement of the alias and the parameter setting statement should be placed behind, or hibernate will 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<s tudent>) session.createquery ("Select Stu from Student Stu where stu.room.name like:room and Sex Like:sex"). Setparamet ER ("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), the efficiency is not high, it is recommended to use Join to query */list<student> Stus = (list<studen t>) 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 hostel SQL: Select t.* from Student t join classroom C on t.c Id=c.id (10).left outer and right outer connection query/** * LEFT outer and right outer joins are actually relative, the left join is based on the table on the right, and then join is the reference to 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 To store the data we have queried, through new XXX () * The premise is that 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 = (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 words/** * 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 professionalFind 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 ( ); ------------Reprint
Hibernate Query Language (HQL)
