Common HQL Statements

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; HQL queries are properties in objects and objects.
>> 3,hql keywords are case-insensitive, and class names and property names are case-sensitive.		
		
>> 4,select can be omitted.
1, simple query, Employee is the entity name rather than the table name in the database (object-oriented characteristics) hql = "from Employee"; HQL = "from Employee as E"; Use alias hql = "from Employee e";
Using aliases, as keywords can be omitted//2, with filter conditions (can use aliases): Where hql = "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 the sort condition: order by hql = "from Employee e WHERE e.id<10 orders 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"; Querying 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) fromEmployee e "; You can use the new syntax to specify that you want to encapsulate some of the properties of the query into the object//5, execute the query, get the results (list, uniqueresult, paging) query query = Session.createquery ("from Employee e W
Here Id<3 ");
Query.setfirstresult (0); Query.setmaxresults (10); Equivalent to limit 0,10//Two query results list, Uniqueresult//List List = Query.list ();  The result of the query is a list set//Employee employee = (employee) query.uniqueresult ();//The result of the query is the only result, and when there are more than one, it throws an exception//6, Method chain list

= Session.createquery (//"from Employee E")//. Setfirstresult (0)//. Setmaxresults (a)//. List (); 7, aggregate function: Count (), Max (), Min (), AVG (), sum () hql = "SELECT count (*) from Employee"; The result returned is a long hql = "Select min (id) from Employee"; The result returned is the type//8 of the id attribute, grouped: GROUP by ...
Has hql = "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";
HQL = "Select E.name,count (e.id) from Employee e WHERE id<9 GROUP by E.name having COUNT (e.id) >1"; HQL = "Select E.name,count (e.id)" +//"from EMployee e "+//" WHERE id<9 "+//" GROUP by E.name "+//" have count (e.id) >1 "+//" ORDER by Count (E.I
d) 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" +//cannot use the column alias "ORDER by C ASC" in the HAVING clause; Column Aliases//9 can be used in the By clause, and the connection query/HQL is an object-oriented query//>> inner JOIN (inner keyword can be omitted) hql = "Select E.id,e.name,d.name from employ
 EE 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 leave 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-hand join e.department D";

You can use a more convenient method hql = "Select E.id,e.name,e.department.name from Employee e"; 10, query using the parameters//>> mode one: use '? ' Placeholder hql = "from Employee e WHERE ID BETWEEN?"
 and? "; List List2 = session.createquery (HQL)//. Setparameter (0, 5/Set parameters, the index of the 1th parameter is 0. setparameter (1)//. List ();
 >> mode two: Use variable name hql = "from Employee e WHERE id between:idmin and:idmax";

List list3 = Session.createquery (HQL)//. Setparameter ("Idmax",)//Setparameter ("Idmin", 5)//. List ();
 When the argument is a collection, be sure to set the parameter value using Setparameterlist () hql = "from Employee e WHERE ID in (: IDs)";

List list4 = Session.createquery (HQL)//. Setparameterlist ("IDs", new object[] {1, 2, 3, 5, 8, MB})//. List ();  11,update and Delete, will not notify session cache//>> update int result = Session.createquery (//"Update Employee e SET e.name=?) WHERE id>15 ")//. Setparameter (0," John Doe ")//. Executeupdate ();
Returns the result of the int type, indicating how many rows were affected. >> Delete int result1 = Session.createquery (//"Delete from Employee e WHERE id>15")//. Executeupdate (); Returns the result of the int type, indicating how many rows were affected.

————— End —————