The main research in this article is the relevant content of Hibernate hql query, as follows.
Hibernate Query Language (HQL) is a completely object-oriented query statement with very powerful query functions; it has polymorphism, association and other characteristics. HQL query is also the query method officially recommended by Hibernate.
Next, we analyze the relevant query methods through a case study
Classes.java:
public class Classes {/*Class ID*/private int id;/*Class name*/private String name;/*Relationship between class and students*/private Set<Student> students;//Omit setter and getter methods}Student.java:
public class Student {/*Student ID*/private int id;/*Student name*/private String name;/*Relationship between students and class*/private Classes classes;//Omit setter and getter methods}Classes.hbm.xml:
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping package="com.lixue.bean"> <!-- Set lazy to false --> <class name="Classes" table="t_classes" lazy="false"> <id name="id"> <generator/generator/> </id> <property name="name"/> <!-- One-to-many mapping, inverse="true" means to hand over the relationship to the peer--> <set name="students" inverse="true"> <key column="classesid"/> <one-to-many/> </set> </class> </hibernate-mapping>
Student.hbm.xml:
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping package="com.lixue.bean"> <class name="Student" table="t_student"> <id name="id"> <generator//id> <!-- Map normal properties--> <property name="name"/> <!-- Many-to-one mapping, add a foreign key to the multiple end--> <many-to-one name="classes" column="classesid"/> </class> </hibernate-mapping>
/*Return the result set attribute list, the element type and the attribute types in the entity class are the same*/ List<String> students = session.createQuery("select name from Student").list(); /*Travel*/ for (Iterator<String> iter=students.iterator(); iter.hasNext();) { String name = (String)iter.next(); System.out.println(name); }Note: When querying a single attribute, the returned set is a collection, and the type of the collection element is the type of the attribute.
/*Query multiple properties, returning an object array*/ List<Object[]> students = session.createQuery("select id, name from Student").list(); /*Transip*/ 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 type objects arrays. This is easy to understand. When querying a single attribute is the returned collection element type, it is the type of the attribute, but what about multiple types? That must be an object array to process, that is, Object[].
/*We set the corresponding constructor for the entity object, and then we can return a collection of entity object types by querying the object*/ List students = session.createQuery("select new Student(id, name) from Student").list(); /*Travel*/ 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 we return an object array, we can also set the corresponding constructor for the entity object, and then query the object by querying the object, and then return a collection of entity types.
/*Aliases can be used*/ List<Object[]> students = session.createQuery("select s.id, s.name from Student s").list(); /*Transip*/ for (Iterator<Object[]> iter=students.iterator(); iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } /*Return is a collection of entity object types*/ List<Student> students = session.createQuery("from Student").list(); /*Transip*/ for (Iterator<Student> iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); }Note: Query entities can directly use the form of the from class name.
/*Use select to use alias*/ List<Student> students = session.createQuery("select s from Student s").list(); /*Transip*/ 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. Another point must be noted: hql does not support the form of select *.
/** * If you use list to query the entity object, a query statement will be issued to obtain the entity 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(); /*Travel*/ for (Iterator<Student> iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); }Note: When using .list() method to query objects, only one statement will be issued, that is, a statement that obtains the physical object data.
/** * The N+1 problem will occur. The so-called N+1 refers to issuing N+1 sql statements* * 1: Issuing a statement that querys the id list* Hibernate: select student0_.id as col_0_0_ from t_student student0_ * * N: Issuing N sql statements based on the id to load the relevant object* Hibernate: select student0_.id as id0_0_, student0_.name as name0_0_, * student0_.createTime as createTime0_0_, student0_.classesid as classisid0_0_ * from t_student student0_ where student0_.id=? * */ Iterator<Student> iter = session.createQuery("from Student").iterate(); /*Travel*/ while (iter.hasNext()) { Student student = (Student)iter.next(); System.out.println(student.getName()); }Note: When performing object query through iterator(), N+1 statements will be issued. First, a statement will be issued to query the ID of the entity object, and then N statements will be issued based on their respective IDs to query N objects. The formal performance is relatively poor.
/*Storing the queryed collection in the first-level cache, i.e. session-level cache*/ List<Student> students = session.createQuery("from Student").list(); /*Transip*/ for (Iterator<Student> iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } System.out.println("-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Avoid N+1 problem* * Because after performing the list operation, the data will be placed in the session cache (first-level cache), when using iterate, * First, a statement querying the id list will be issued, and then the corresponding data will be loaded in the cache according to the id. If there is data matching it in the cache*, no SQL statements querying based on the id will be issued, and the data in the cache will be directly used * * The Iterate method If there is data in the cache, it can improve performance, otherwise N+1 problem will occur* */ Iterator<Student> iter = session.createQuery("from Student").iterate(); /*Travel*/ while (iter.hasNext()) { Student student = (Student)iter.next(); System.out.println(student.getName()); }Note: In fact, Hibernate provides iterator() query to improve performance, so why does it help too much? The reason is that iterator() fetches data from the first-level cache. If there is data in the cache, its efficiency will undoubtedly be quite powerful. However, when I query the first time, how could there be data in the cache? This leads to the so-called N+1 problem. The above code can avoid the N+1 problem. Its idea is to use list() first to query, because after list() is queryed, data exists in the first-level cache summary, and when using iterator(), the efficiency will be very high.
/*Query according to the conditions (alias are usually used here, which is more convenient)*/ List<Object[]> students = session.createQuery("select s.id, s.name from Student s where s.name like '%0%'").list(); /*Travel*/ for (Iterator<Object[]> iter=students.iterator(); iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); }Note: Conditional query is the same as native SQL, both of which are where keywords. In addition, it is usually more convenient to use alias. The above program is to query multiple attributes, so it returns a collection of object array types, and the elements in the object array are the corresponding attributes.
/*Chenged Programming*/ List<Object[]> students = session.createQuery("select s.id, s.name from Student s where s.name like ?") .setParameter(0, "%0%") .list(); /*Travel*/ 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 through placeholders, which can prevent SQL injection.
/*Chip 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 : there is no space after the myname colon, otherwise an error will occur.
[java] view plain copy/* adopts the in method, only one formal parameter can be used */ 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(); /*Transip*/ 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 formal parameter in the brackets after in. When we set the parameter value, we can pass the value through the object array.
/* Query students in 2009-08, you can call mysql's 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(); /*Travel*/ 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"); /*Students from 2009-08-01 to 2009-08-20 can call mysql's date formatting function*/ List<Object[]> students = session.createQuery("select s.id, s.name from Student s where s.createTime between ? and ?") .setParameter(0, sdf.parse("2009-08-01 00:00:00")) .setParameter(1, sdf.parse("2009-08-20 23:59:59")) .list(); /*Transip*/ for (Iterator<Object[]> iter=students.iterator(); iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } /*Use select * You must use original SQL statements, and it is similar to hql querying multiple properties, so it returns a collection of object array types*/ List<Object[]> students = session.createSQLQuery("select * from t_student").list(); /*Transip*/ 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 the query form of select *, but Hibernate supports original SQL statements. We can use SQL statements to query. In addition, it is similar to HQL's query multiple attributes, so it returns a collection of object array types.
/*Page query, setFirstResult(1) means starting from the first data; setMaxResult(2) means 2 pieces of data are displayed per page*/ List students = session.createQuery("from Student") .setFirstResult(1) .setMaxResults(2) .list(); /*Travel*/ for (Iterator iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } /*Navigation query, s.classes.name Navigate from student to class in class (this is navigating from more end to less end, which is also possible)*/ List<Student> students = session.createQuery("from Student s where s.classes.name like '%2%'") .list(); /*Travel*/ 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 above query statement is to obtain the name of the class from the student navigation to the class classes. You can also navigate in reverse: Navigate from class to student in getting a certain attribute. In addition, the query statement in the program means to query all students with 2 in the class name.
/*Inner connection, just use the join keyword */ List<Object[]> students = session.createQuery("select c.name, s.name from Student s join s.classes c") .list(); /*Transaction*/ for (Iterator<Object[]> iter=students.iterator(); iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); }Note: The keyword in the inner connection is join, and the connection is still done using alias and navigation. The above query statement means: query the class name and student name from the student table and class table (inner connection means that there must be worthy attributes in querying, such as no class or no students or students cannot query without classes).
/*Left join uses keyword left join*/ List<Object[]> students = session.createQuery("select c.name, s.name from Student s left join s.classes c") .list(); /*Transip*/ for (Iterator<Object[]> iter=students.iterator(); iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); }Note: The keyword used for left join is left join. The above query statement means: from the student and class table, query the class name and student name. Because it is a left-connected, students without a class will also be queryed.
[java] view plain copy/*right join keyword is right join*/ List<Object[]> students = session.createQuery("select c.name, s.name from Student s right join s.classes c") .list(); /*Transip*/ for (Iterator<Object[]> iter=students.iterator(); iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); }Note: The keyword using the right join is right join. The above query statement means: from the student and class table, query the class name and student name. Because it is a right-connected, classes without students will be queried.
Long count = (Long)session.createQuery("select count(*) from Student").uniqueResult();Note: Only statistical queries can be used in hql with *. uniqueResult() means that there is only one result set, and the returned type Long.
/*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(); /*Travel*/ 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, sorting, etc. The above statement means: query the name of each class and query the number of students in each class, group by class name, sort by class name
The above is all about the Hibernate hql query code example in this article, I hope it will be helpful to everyone. Interested friends can continue to refer to other related topics on this site. If there are any shortcomings, please leave a message to point it out. Thank you friends for your support for this site!