HQL Common Query statements

Excerpt from someone, more useful, more comprehensive.

//hql:hibernate Query Language.//Features://>> 1, similar to SQL, the syntax in SQL is basically straightforward to use. //>> 2,sql queries are columns in tables and tables, and HQL queries are objects and properties in objects. //>> 3,hql Keywords are case-insensitive, and the class name and property name are case-sensitive. //>> 4,select can be omitted. //1, simple query, employee is entity name instead of table name in database (Object oriented property)HQL = "from Employee"; HQL= "From Employee as E";//using aliasesHQL = "from Employee e";//using aliases, the AS keyword can be omitted//2, with filter conditions (aliases can be used): WhereHQL = "from Employee WHERE id<10"; HQL= "from Employee e WHERE e.id<10"; HQL= "from Employee e WHERE e.id<10 and E.id>5";//3, with sorting criteria: ORDER byHQL = "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";//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 propertyHQL = "Select E.id,e.name from Employee e";//querying multiple columns, the element type of the returned collection is an object arrayHQL = "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//5, execute query, get results (list, uniqueresult, paging)Query query = Session.createquery ("from Employee e WHERE id<3"); Query.setfirstresult (0); Query.setmaxresults (10);//equal to limit 0,10//Two query results list, Uniqueresult//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//6, Method chainList List = Session.createquery (//"From Employee E")//. Setfirstresult (0)//. Setmaxresults (10)//. 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 ... havingHQL = "Select E.name,count (e.id) from Employee e GROUP by E.name"; HQL= "Select E.name,count (e.id) from the Employee e GROUP by E.name have COUNT (e.id) >1"; HQL= "Select E.name,count (e.id) from the Employee e WHERE id<9 GROUP by E.name have COUNT (e.id) >1"; HQL= "Select E.name,count (e.id)" +//"From Employee E" +//"WHERE id<9" +//"GROUP by E.name" +//"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" +//"GROUP by E.name" +//"Having count (e.id) >1" +//column aliases cannot be used in a HAVING clause"ORDER by C ASC";//column aliases can be used in the By clause//9, Connection query/HQL is an object-oriented query//>> Internal connection (inner keyword can be omitted)HQL = "Select E.id,e.name,d.name from Employee e JOIN e.department D"; HQL= "Select E.id,e.name,d.name from Employee e INNER JOIN e.department D"; //>> 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"; //>> right outer join (outer keyword can be omitted)HQL = "Select E.id,e.name,d.name from Employee e right JOIN e.department d"; //You can use the more convenient methodHQL = "Select E.id,e.name,e.department.name from Employee e";//10, using parameters when querying//>> method One: use '? ' Occupy positionHQL = "from Employee e WHERE id between?" and? "; List List2= Session.createquery (HQL)//. Setparameter (0, 5)//set the parameter, and the index of the 1th parameter is 0. . Setparameter (1, 15)//. List ();//>> Method Two: Use variable nameHQL = "from Employee e WHERE ID between:idmin and:idmax"; List List3= Session.createquery (HQL)//. Setparameter ("Idmax", 15)//. Setparameter ("Idmin", 5)//. List ();//When the parameter is a collection, be sure to use Setparameterlist () to set the parameter valueHQL = "from Employee e WHERE ID in (: IDs)"; List List4= Session.createquery (HQL)//. Setparameterlist ("IDs",NewObject[] {1, 2, 3, 5, 8, 100})//. List ();//11,update with Delete, does not notify session cache//>> Updateintresult = Session.createquery (//"UPDATE Employee e SET e.name=?" WHERE id>15 ")//. Setparameter (0, "anonymous")//. executeupdate ();//returns the result of the int type, indicating how many rows were affected. //>> DeleteintRESULT1 = Session.createquery (//"DELETE from Employee e WHERE id>15")//. executeupdate ();//returns the result of the int type, indicating how many rows were affected. 

HQL Common Query statements