Hibernate hql query code instance, hibernatehql

Hibernate hql query code instance, hibernatehql

This article focuses on the content related to Hibernate hql queries, as detailed below.

HQL Introduction

Hibernate Query Language (HQL) is a fully Object-Oriented Query statement with powerful Query functions. It has the characteristics of polymorphism and association, HQL query is also officially recommended by Hibernate.

Next we will use a case study to analyze the relevant query methods

Classes. java:

Public class Classes {/* class ID */private int id;/* class name */private String name;/* relationships between Classes and students */private Set <Student> students; // The setter and getter methods are omitted}

Student. java:

Public class Student {/* Student ID */private int id;/* Student name */private String name;/* Relationship between Student and class */private Classes classes; // The setter and getter methods are omitted}

Classes. hbm. xml:

<? Xml version = "1.0"?> <! DOCTYPE hibernate-mapping PUBLIC "-// Hibernate/Hibernate DTD ing DTD 3.0 // EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> 
Student. hbm. xml:
<? Xml version = "1.0"?> <! DOCTYPE hibernate-mapping PUBLIC "-// Hibernate/Hibernate DTD ing DTD 3.0 // EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> 1. query a single property:
/* Returned result set attribute List. The element type is the same as the attribute type in the object class */List <String> students = session. createQuery ("select name from Student "). list ();/* traverse */for (Iterator <String> iter = students. iterator (); iter. hasNext ();) {String name = (String) iter. next (); System. out. println (name );}
Note: When querying a single attribute, a set is returned, and the type of the Set element is the type of the attribute.
2. query multiple attributes and return an array of objects:
/* Query multiple attributes and return an array of objects */List <Object []> students = session. createQuery ("select id, name from Student "). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
Note: querying multiple attributes returns a collection of object arrays. It is easy to understand that when querying a single attribute, the element type of the returned collection is the type of the attribute, but what about multiple types? It must be an array of objects for processing, that is, Object [].
3. query multiple attributes and return a set of object types:
/* We set the corresponding constructor for the object, and then we can query the object to return a collection of object types */List students = session. createQuery ("select new Student (id, name) from Student "). list ();/* traverse */for (Iterator iter = students. iterator (); iter. hasNext ();) {Student student = (Student) iter. next (); System. out. println (student. getId () + "," + student. getName ());}
Note: In addition to the second method, an object array is returned. We can also set the corresponding constructor for the object and then query the object, the returned result is a set of object types.
4. query by alias:
/* Alias */List <Object []> students = session. createQuery ("select s. id, s. name from Student s "). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
5. query object:
/* Returns a collection of object types */List <Student> students = session. createQuery ("from Student "). list ();/* traverse */for (Iterator <Student> iter = students. iterator (); iter. hasNext ();) {Student student = (Student) iter. next (); System. out. println (student. getName ());}
Note: You can directly use the from class name to query objects.
/* Aliases must be used when select is used */List <Student> students = session. createQuery ("select s from Student s "). list ();/* traverse */for (Iterator <Student> iter = students. iterator (); iter. hasNext ();) {Student student = (Student) iter. next (); System. out. println (student. getName ());}
Note: If you want to use the select keyword, you must use an alias. Note that hql does not support the select * format.
6. N + 1 problem:
/*** When you use list to query object objects, a query statement is issued to obtain object data. ** Hibernate: select student0 _. id as id0 _, student0 _. name as name0 _, * student0 _. createTime as createTime0 _, student0 _. classesid as classesid0 _ * from t_student student0 _ */List <Student> students = session. createQuery ("from Student "). list ();/* traverse */for (Iterator <Student> iter = students. iterator (); iter. hasNext ();) {Student student = (Student) iter. next (); System. out. println (student. getName ());}
Note: If you use. list () for object query, only one statement is issued, that is, the statement for retrieving object data.
/*** The N + 1 problem occurs. The so-called N + 1 indicates that N + 1 SQL statement is issued ** 1: issue a statement to query the id list * Hibernate: select student0 _. id as col_0_0 _ from t_student student0 _ ** N: Issues N SQL statements based on the id and loads related objects * Hibernate: select student0 _. id as id0_0 _, student0 _. name as name0_0 _, * student0 _. createTime as createTime0_0 _, student0 _. classesid as classesid0_0 _ * from t_student student0 _ where student0 _. id =? **/Iterator <Student> iter = session. createQuery ("from Student "). iterate ();/* traverse */while (iter. hasNext () {Student student = (Student) iter. next (); System. out. println (student. getName ());}
Note: when an object is queried using iterator (), N + 1 statements are issued. First, a statement is issued to query the Object ID, then, N objects are queried by issuing N statements based on their respective IDs, which has poor formal performance.
/* Store the queried set in the first-level cache (session-level cache) through List query */List <Student> students = session. createQuery ("from Student "). list ();/* traverse */for (Iterator <Student> iter = students. iterator (); iter. hasNext ();) {Student student = (Student) iter. next (); System. out. println (student. getName ();} System. out. println ("-------------------------------------------------");/*** this avoids the N + 1 problem. ** after the list operation is executed, data is stored in the session cache (level-1 cache ), therefore, when using iterate *, a statement is first issued to query the id list, and then the corresponding data is loaded into the cache according to the id, if the cache contains matched data *, the id-based Query SQL statement is no longer issued, and the cached data is directly used ** Iterate method. If there is data in the cache, it can improve performance, otherwise, the N + 1 problem occurs **/Iterator <Student> iter = session. createQuery ("from Student "). iterate ();/* traverse */while (iter. hasNext () {Student student = (Student) iter. next (); System. out. println (student. getName ());}
Note: in fact, the iterator () query method provided by Hibernate is used to improve performance. Why does it help me? The reason is that iterator () extracts data from the first-level cache. If there is data in the cache, its efficiency will undoubtedly be quite powerful, however, when I first query the data in the cache, how can this cause the so-called N + 1 problem. The above code can avoid the N + 1 problem. Its idea is to use list () for query first, because after list () is queried, there will be data in the cache summary at the first level, when iterator () is used, the efficiency is very high.
7. Conditional query:
/* Query by condition (aliases are usually used here for convenience) */List <Object []> students = session. createQuery ("select s. id, s. name from Student s where s. name like '% 100 '"). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
Note: The condition query is the same as that of the native SQL statement and is the where keyword. In addition, it is convenient to use aliases. The preceding program queries multiple attributes. Therefore, a set of object array types is returned, and the elements in the object array are the corresponding attributes.
8. placeholder query:
/* Chained programming */List <Object []> students = session. createQuery ("select s. id, s. name from Student s where s. name like? "). SetParameter (0, "% 0% "). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
Note: parameters can be passed in the form of placeholders, which can prevent SQL injection.
9. Form of custom parameters:
/* Chained programming */List <Object []> students = session. createQuery ("select s. id, s. name from Student s where s. name like: myname "). setParameter ("myname", "% 0% "). list ();/* Object array */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
Note: like: myname has no space after the colon. Otherwise, an error will occur.
10. The query condition is in:
[Java] view plain copy/* adopts the in method, and only one form parameter is required */List <Object []> students = session. createQuery ("select s. id, s. name from Student s where s. id in (: ids )"). setParameterList ("ids", new Object [] {1, 2, 3, 4, 5 }). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
Note: There is only one parameter in the brackets behind in. When we set the parameter value, we can pass the value through the object array.
11. Use Database personalization functions:
/* For students in, you can call the mysql date Formatting Function */List <Object []> students = session. createQuery ("select s. id, s. name from Student s where date_format (s. createTime, '% Y-% m') =? "). SetParameter (0, "2009-08 "). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd HH: mm: ss");/* query students from, you can call the date Formatting Function */List <Object []> students = session. createQuery ("select s. id, s. name from Student s where s. createTime? And? "). SetParameter (0, sdf. parse ("00:00:00 ")). setParameter (1, sdf. parse ("23:59:59 ")). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
12. Use the original SQL statement:
/* To use select *, you must use the original SQL statement. In addition, it is similar to hql to query multiple attributes, therefore, the returned result is a collection of Object array types */List <Object []> students = session. createSQLQuery ("select * from t_student "). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
Note: hql does not support select * queries, but Hibernate supports original SQL statements. We can use SQL statements for queries. In addition, it is similar to querying multiple attributes of HQL, therefore, a collection of object array types is returned.
13. Paging Query
/* For paging query, setFirstResult (1) indicates that the query starts from the first data; setMaxResult (2) indicates that two pieces of data are displayed on each page */List students = session. createQuery ("from Student "). setFirstResult (1 ). setMaxResults (2 ). list ();/* traverse */for (Iterator iter = students. iterator (); iter. hasNext ();) {Student student = (Student) iter. next (); System. out. println (student. getName ());}
14. Navigation Query
/* Navigation query, s. classes. name: from the Student navigation to the class name (this is from the many end navigation to the few end, in turn) */List <Student> students = session. createQuery ("from Student s where s. classes. name like '% 100 '"). list ();/* traverse */for (Iterator <Student> iter = students. iterator (); iter. hasNext ();) {Student student = (Student) iter. next (); System. out. println (student. getName ());}
Note: The s. classes. name in the preceding query statement is used to retrieve the class name from the student navigation to the class classes. You can also navigate from class to student to get a certain attribute. In addition, the query statement in the program means to query all the students whose class name contains 2.
15. Internal Connection Query
/* Internal join, use the join keyword */List <Object []> students = session. createQuery ("select c. name, s. name from Student s join s. classes c "). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
Note: The inner join keyword is join, and the alias and navigation are used for join. The preceding query statement indicates that the class name and Student name are queried from the student table and the class table (the inner connection indicates that the query must have a value attribute, for example, no class or no student or no class can be queried ).
16. Left join
/* Left join use the keyword left join */List <Object []> students = session. createQuery ("select c. name, s. name from Student s left join s. classes c "). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
Note: The keyword of left join is left join. The preceding query statement indicates that the name of the class and the name of the student are queried from the student and class tables. Because it is left-connected, the students without the class are also queried.
17. Right join
[Java] view plain copy/* right join Keyword: right join */List <Object []> students = session. createQuery ("select c. name, s. name from Student s right join s. classes c "). list ();/* traverse */for (Iterator <Object []> iter = students. iterator (); iter. hasNext ();) {Object [] obj = (Object []) iter. next (); System. out. println (obj [0] + "," + obj [1]);}
Note: The keyword of right join is right join. The preceding query statement indicates that the class name and Student name are queried from the student and class tables. Because the right connection is used, no student's class is queried.
18. Statistics Query
Long count = (Long)session.createQuery("select count(*) from Student").uniqueResult();
Note: Only statistical queries in hql can contain the * number. UniqueResult () indicates that there is only one result set and the returned result is of the Long type.
19. Compound Query
/* Query statement */String hql = "select c. name, count (s) from Classes c join c. students s group by c. name order by c. name "; List <Object []> students = session. createQuery (hql ). list ();/* traverse */for (int I = 0; I <students. size (); I ++) {Object [] obj = (Object []) students. get (I); System. out. println (obj [0] + "," + obj [1]);}
Note: hql also supports grouping and sorting. The preceding statement queries the name of each class and the number of students in each class, groups by class name, and sorts by class name.
Summary
The above is all the content about the Hibernate hql query code instance. I hope it will be helpful to you. If you are interested, you can continue to refer to other related topics on this site. If you have any shortcomings, please leave a message. Thank you for your support!