Common HQL Statements

1     //hql:hibernate Query Language. 2     //Features:3     //>> 1, similar to SQL, the syntax in SQL is basically straightforward to use. 4     //>> 2,sql queries are columns in tables and tables, and HQL queries are objects and properties in objects. 5     //>> 3,hql Keywords are case-insensitive, and the class name and property name are case-sensitive. 6     //>> 4,select can be omitted. 7               8     //1, simple query, employee is entity name instead of table name in database (Object oriented property)9HQL = "from Employee"; TenHQL = "from Employee as E";//using aliases OneHQL = "from Employee e";//using aliases, the AS keyword can be omitted A        -     //2, with filter conditions (aliases can be used): Where -HQL = "from Employee WHERE id<10";  theHQL = "from Employee e WHERE e.id<10";  -HQL = "from Employee e WHERE e.id<10 and E.id>5";  -        -     //3, with sorting criteria: ORDER by +HQL = "from Employee e WHERE e.id<10 ORDER by E.name";  -HQL = "from Employee e WHERE e.id<10 ORDER by E.name DESC";  +HQL = "from Employee e WHERE e.id<10 ORDER by e.name DESC, id ASC";  A        at     //4, specify the SELECT clause (You cannot use SELECT *) -HQL = "Select E from Employee e";//equivalent to "from Employee E" -HQL = "Select E.name from Employee e";//query only one column, the element type of the returned collection is the type of this property -HQL = "Select E.id,e.name from Employee e";//querying multiple columns, the element type of the returned collection is an object array -HQL = "Select New Employee (e.id,e.name) from employee E";//You can use the new syntax to specify that some of the properties of a query are encapsulated in an object -        in     //5, execute query, get results (list, uniqueresult, paging) -Query query = Session.createquery ("from Employee e WHERE id<3");  toQuery.setfirstresult (0);  +Query.setmaxresults (10);//equal to limit 0,10 -     //Two query results list, Uniqueresult the     //List List = Query.list ();//The result of the query is a list collection *     //Employee employee = (employee) query.uniqueresult ();//The result of a query is the only result that, when there are multiple results, throws an exception $       Panax Notoginseng     //6, Method chain -List List = Session.createquery (//   the"From Employee E")//   +. Setfirstresult (0)//   A. Setmaxresults (10)//   the . List ();  +        -     //7, aggregate function: Count (), Max (), Min (), AVG (), SUM () $HQL = "Select COUNT (*) from Employee";//The returned result is a long type. $HQL = "Select min (id) from Employee";//The returned result is the type of the id attribute -        -     //8, group: GROUP By ... having theHQL = "Select E.name,count (e.id) from Employee e GROUP by E.name";  -HQL = "Select E.name,count (e.id) from Employee e GROUP by E.name have COUNT (e.id) >1"; WuyiHQL = "Select E.name,count (e.id) from the Employee e WHERE id<9 GROUP by E.name have COUNT (e.id) >1";  theHQL = "Select E.name,count (e.id)" +//   -"From Employee E" +//   Wu"WHERE id<9" +//   -"GROUP by E.name" +//   About"Having count (e.id) >1" +//   $"ORDER by Count (e.id) ASC";  -HQL = "Select E.name,count (e.id) as C" +//   -"From Employee E" +//   -"WHERE id<9" +//   A"GROUP by E.name" +//   +"Having count (e.id) >1" +//column aliases cannot be used in a HAVING clause the"ORDER by C ASC";//column aliases can be used in the By clause -        $     //9, Connection query/HQL is an object-oriented query the      //>> Internal connection (inner keyword can be omitted) theHQL = "Select E.id,e.name,d.name from Employee e JOIN e.department D";  theHQL = "Select E.id,e.name,d.name from Employee e INNER JOIN e.department D";  the      //>> LEFT OUTER join (outer keyword can be omitted) -HQL = "Select E.id,e.name,d.name from Employee e left OUTER JOIN e.department D";  in      //>> right outer join (outer keyword can be omitted) theHQL = "Select E.id,e.name,d.name from Employee e right JOIN e.department d";  the      //You can use the more convenient method AboutHQL = "Select E.id,e.name,e.department.name from Employee e";  the        the     //10, using parameters when querying the     //>> method One: use '? ' Occupy position +HQL = "from Employee e WHERE id between?" and? ";  -List List2 = session.createquery (HQL)//   the. Setparameter (0, 5)//set the parameter, and the index of the 1th parameter is 0. Bayi. Setparameter (1, 15)//   the . List ();  the        -     //>> Method Two: Use variable name -HQL = "from Employee e WHERE ID between:idmin and:idmax";  theList list3 = session.createquery (HQL)//   the. Setparameter ("Idmax", 15)//   the. Setparameter ("Idmin", 5)//   the . List ();  -        the     //When the parameter is a collection, be sure to use Setparameterlist () to set the parameter value theHQL = "from Employee e WHERE ID in (: IDs)";  theList list4 = session.createquery (HQL)//  94. Setparameterlist ("IDs",NewObject[] {1, 2, 3, 5, 8, 100})//   the . List ();  the        the     //11,update with Delete, does not notify session cache98     //>> Update About     intresult = Session.createquery (//   -"UPDATE Employee e SET e.name=?" WHERE id>15 ")//  101. Setparameter (0, "anonymous")//  102. executeupdate ();//returns the result of the int type, indicating how many rows were affected. 103     //>> Delete104     intRESULT1 = Session.createquery (//   the"DELETE from Employee e WHERE id>15")//  106. executeupdate ();//returns the result of the int type, indicating how many rows were affected. 

Common HQL Statements