Summary of HQL statements commonly used in hibernate

2. Hql:hibernate Query Language.
4. Characteristics:
6. >> 1, similar to SQL, the syntax in SQL is basically straightforward to use.
8. >> 2,sql queries are columns in tables and tables, and HQL queries are objects and properties in objects.
10. >> 3,hql keywords are case-insensitive, and the class name and property name are case-sensitive.
12. >> 4,select can be omitted.
16. 1, simple query, employee is entity name instead of table name in database (Object oriented property)
18. HQL = "from Employee";
20. HQL = "from Employee as E"; //Use aliases
22. HQL = "from Employee e"; //Use aliases, as keywords can be omitted
26. 2, with filter conditions (aliases can be used): Where
28. HQL = "from Employee WHERE id<10";
30. HQL = "from Employee e WHERE e.id<10";
32. HQL = "from Employee e WHERE e.id<10 and e.id>5";
36. 3, with sorting criteria: order BY
38. HQL = "from the Employee e WHERE e.id<10 ORDER by E.name";
40. HQL = "from Employee e WHERE e.id<10 ORDER by E.name DESC";
42. HQL = "from Employee e WHERE e.id<10 ORDER by e.name DESC, id ASC";
46. 4, specify the SELECT clause (You cannot use SELECT *)
48. HQL = "Select e from Employee e"; //equivalent to "from Employee E"
50. HQL = "Select e.name from Employee e"; //Query only one column, the element type of the returned collection is the type of this property
52. HQL = "Select E.id,e.name from Employee e"; //Query multiple columns, the element type of the returned collection is an object array
54. 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 the query are encapsulated in the object
58. 5, execute query, get results (list, uniqueresult, paging)
60. Query query = session.createquery ("from Employee e WHERE id<3");
62. Query.setfirstresult (0);
64. Query.setmaxresults (10); //equal to limit 0,10
66. List of two query results, Uniqueresult
68. List List = Query.list (); The result of the query is a list collection
70. Employee employee = (employee) query.uniqueresult ();//The result of a query is the only result, and when there are multiple results, it throws an exception
74. 6, Method chain
76. List List = Session.createquery (//
78. "from Employee E") //  
80. . Setfirstresult (0)//
82. . Setmaxresults (Ten)//
84. . List ();
88. 7, aggregate function: Count (), Max (), Min (), AVG (), SUM ()
90. HQL = "Select COUNT (*) from Employee"; //The result returned is a long type
92. HQL = "select min (id) from Employee"; //The result returned is the type of the id attribute
96. 8, group: GROUP By ... Having
98. HQL = "Select E.name,count (e.id) from the Employee e GROUP by E.name";
100. HQL = "Select E.name,count (e.id) from the Employee e GROUP by E.name have COUNT (e.id) >1";
102. HQL = "Select E.name,count (e.id) from the Employee e WHERE id<9 GROUP by E.name have COUNT (e.id) >1";
104. HQL = "Select E.name,count (e.id)" + //
106. "from Employee E" + //
108. "WHERE id<9" + //
110. "GROUP by e.name" + //
112. "having count (e.id) >1" + //
114. "ORDER by Count (e.id) ASC";
116. HQL = "Select E.name,count (e.id) as C" + //
118. "from Employee E" + //
120. "WHERE id<9" + //
122. "GROUP by e.name" + //
124. "having count (e.id) >1" + //cannot use column aliases in a HAVING clause
126. "ORDER by C ASC"; //You can use column aliases in the BY clause
130. 9, Connection query/HQL is an object-oriented query
132. //>> Inner Connection (inner keyword can be omitted)
134. HQL = "Select E.id,e.name,d.name from Employee e JOIN e.department D";
136. HQL = "Select E.id,e.name,d.name from Employee e INNER JOIN e.department d";
138. //>> LEFT OUTER join (outer keyword can be omitted)
140. HQL = "Select E.id,e.name,d.name from Employee e left OUTER JOIN e.department D";
142. //>> right outer join (outer keyword can be omitted)
144. HQL = "Select E.id,e.name,d.name from Employee e right JOIN e.department d";
146. //Can be used in a more convenient way
148. HQL = "Select E.id,e.name,e.department.name from Employee e";
152. 10, using Parameters when querying
154. >> method One: use '? ' Occupy position
156. HQL = "from Employee e WHERE id between?" and? ";
158. List List2 = session.createquery (HQL)//
160. . Setparameter (0, 5)//Set parameter, index of 1th parameter is 0.
162. . Setparameter (1, up )//
164. . List ();
168. >> method Two: Use variable name
170. HQL = "from Employee e WHERE id between:idmin and:idmax";
172. List list3 = session.createquery (HQL)//
174. . Setparameter ("Idmax", + )//
176. . Setparameter ("Idmin", 5)//
178. . List ();
182. When the parameter is a collection, be sure to use Setparameterlist () to set the parameter value
184. HQL = "from the Employee e WHERE ID in (: IDs)";
186. List list4 = session.createquery (HQL)//
188. . Setparameterlist ("IDs", new object[] { 1, 2, 3, 5, 8, + })// /c4>
190. . List ();
194. 11,update with Delete, does not notify session cache
196. >> Update
198. int result = Session.createquery (//
200. "UPDATE Employee e SET e.name=?" WHERE id>15 ")//
202. . Setparameter (0, "anonymous")//
204. . executeupdate ();  //Returns the result of the int type, indicating how many rows were affected.
206. >> Delete
208. int result1 = Session.createquery (//
210. "DELETE from Employee e WHERE id>15") //  
212. . executeupdate (); //Returns the result of the int type, indicating how many rows were affected.

Summary of HQL statements commonly used in hibernate