Original: http://www.cnblogs.com/xiaoluo501395377/p/3376256.html
---------------------------------------------------------------------
By defining three classes here, Special, Classroom, Student to test, Special is one-to-many with lassroot and Classroot is one-to-many with Student. Only the attribute codes of these three bean s are posted here:
Special class:
public class Special { private int id; private String name; private String type; private Set<Classroom> rooms; .......... }
Classroot class:
public class Classroom { private int id; private String name; private Special special; private Set<Student> students; ............ }
Student class:
public class Student { private int id; private String name; private String sex; private Classroom room; .......... }
1. The simplest query
List<Special> specials = (List<Special>)session.createQuery("select spe from Special spe").list();This is the most basic query statement for hql to find out all the Special objects and put them in a List
2. Parameterized form based on?
/** * Use in queries? sql injection can be prevented by setParameter * jdbc Subscripts for setParameter s start at 1 and for hql start at 0 */ List<Student> students = (List<Student>)session.createQuery("select stu from Student stu where name like ?") .setParameter(0, "%Liu%") .list();
Parameterized queries based on? Are also supported in hql. Note that in jdbc, the subscript of setParameter starts from 1, while the subscript of hibernate's setParameter starts from 0.
3. Setting parameters based on: xx aliases
/** * You can use aliases to query in hql in the format: xxx sets aliases through setParameter s */ List<Student> students = (List<Student>)session.createQuery("select stu from Student stu where name like :name and sex like :sex") .setParameter("name", "%king%").setParameter("sex", "%male%") .list();
4. If only one value is returned, the uniqueResult method can be used
/** * If you get only one value, you can use the uniqueResult method */ Long stu = (Long)session.createQuery("select count(*) from Student stu where name like :name and sex like :sex") .setParameter("name", "%king%").setParameter("sex", "%male%") .uniqueResult(); /** * If you get only one value, you can use the uniqueResult method */ Student stu = (Student)session.createQuery("select stu from Student stu where id = ?") .setParameter(0, 1) .uniqueResult();
5. Projection-based queries
/** * Projection-based queries that return multiple values that are stored in an object[] array */ List<Object[]> stus = (List<Object[]>)session.createQuery("select stu.name, stu.sex from Student stu where name like
:name and sex like :sex") .setParameter("name", "%Zhang%").setParameter("sex", "%male%") .list();
6. Query based on navigation object
/** * If there is a navigation object in the object, you can navigate the query directly through the object */ List<Student> stus = (List<Student>)session.createQuery("select stu from Student stu where stu.room.name like :room and sex like :sex") .setParameter("room", "%Computer application%").setParameter("sex", "%female%") .list();
Note: When querying directly through a navigation object, it is actually a join query using cross join (Cartesian product). This has poor performance and is not recommended.
7. Use in for list queries
/** * You can use in to set list-based queries, and you need to use aliases to set parameters when using in queries. * The setParameterList method allows you to set the? Form of a query that must precede the alias when querying with the hql statement for aliases and? */ // List<Student> stus = (List<Student>)session.createQuery("select stu from Student stu where sex like ? and stu.room.id in (:room)") // .setParameter(0, "%female%").setParameterList("room", new Integer[]{1, 2}) // .list(); List<Student> stus = (List<Student>)session.createQuery("select stu from Student stu where stu.room.id in (:room) and stu.sex like :sex") .setParameterList("room", new Integer[]{1, 2}).setParameter("sex", "%female%") .list();
When using in for list queries, we set our parameters by setParameterList() method at this time. Note: if a parameter is passed in by alias and one by? Then the hql statement by alias and the parameter setting statement are placed after? Otherwise hibernate will make an error.If they all use aliases to set parameters, there is no order
8. Paging Query
/** * Paging queries can be set through setFirstResult(0).setMaxResults(10), which is equivalent to offset and pagesize */ List<Student> stus = (List<Student>)session.createQuery("select stu from Student stu where stu.room.name like :room and sex like :sex") .setParameter("room", "%Computer application%").setParameter("sex", "%female%").setFirstResult(0).setMaxResults(10) .list();
9. Inner Connection Query
/** * Navigation queries using objects can complete join queries, but using Cross Join (Cartesian product) is inefficient, so join is recommended for queries */ List<Student> stus = (List<Student>)session.createQuery("select stu from Student stu join stu.room room where room.id=2") .list();
There is a difference between using a join query statement in hql and using our sql for a join query:
hql: select stu from Student stu join stu.room room
sql: select t.* from Student t join Classroom c on t.cid=c.id10. Query for left and right outer joins
/** * Left join is relative to right join, left join is based on the table on the left, right join is based on the table on the right */ List<Object[]> stus = (List<Object[]>)session.createQuery("select room.name, count(stu.room.id) from Student stu right join stu.room room group by room.id") .list();
11. Create a DTO class to store the queried fields in the DTO object
/** * When we query multiple fields, we usually create a DTO object to store the data we query, such as new XXX() * The precondition is that the XXX class must have a construction method that accepts these fields and must use the full name of the class. */ // List<Object[]> stus = (List<Object[]>)session.createQuery("select stu.id,stu.name,stu.sex,room.name,special.name from Student stu left join stu.room room left join room.special special") // .list(); // for(Object[] obj : stus) // { // System.out.println(obj[0] + ", " + obj[1] + ", " + obj[2] + ", " + obj[3] + ", " + obj[4]); // }
List<StudentDTO> stus = (List<StudentDTO>)session.createQuery("select new com.xiaoluo.bean.StudentDTO(stu.id, stu.name, stu.sex, room.name, special.name) from Student stu left join stu.room room left join room.special special")
.list();
12.group having sentence
/** * You cannot alias a queried field in hql. Aliases can only be set after from */ List<Object[]> stus = (List<Object[]>)session.createQuery("select special.name, count(stu.room.special.id) from Student stu right join stu.room.special special group by special.id having count(stu.room.special.id)>150") .list(); // Query out professions with more than 150 people
//Query the number of boys and girls in each major
List<Object[]> stus = (List<Object[]>)session.createQuery("select special.name, stu.sex, count(stu.room.special.id) from Student stu right join stu.room.special special group by special.id,stu.sex") .list();
Reprinted at: https://my.oschina.net/u/2400661/blog/596249