Thursday, March 3, 2016

HQL and Criteria

HQL: Hibernate Query Language


  • Case sensitive for Java class and field
  • HQL query is to object, not table
  • support polymorphism
Query q = session.createQuery(hql);

  • from Person
  • from User user where
  • from User user where and user.birthday <:birthday
Table primary key: better not to be business logic

Generating domain file and *.hbm.xml file: if we have primary key and foreign key, first generate for primary table, then generate foreign table



1. select all field from table
The result can be as List<Student>
//hql: get all field from table

List<Student> list1 = session.createQuery("from Student where id<10").list();
//"Student" is class name, not table name
//"from Student" default as "select *"

2. select part of fields from table(more than one column)
The result can NOT be List<Student>, it is List<Object[]>
 //hql: get part of fields from table
 List<Object[]> list1 = session.createQuery("select name,department from Student").list();
 //"Student" is class name
 //only returns List<Object[]>
//Also can do query like distinct, between and, in(), count,avg, having,max,min,sum etc..
List<Object[]> list2 = session.createQuery("select distinct age,gender from Student where age between 10 and 30").list();

List<Object[]> list3 = session.createQuery("from Student where name like '%Cat%'").list();

from Student where name like "%Cat%"

3. select only one column from table
The result is List<Object>, NOT List<Object[]>

4. Foreign key: foreign key is already transformed to class on the field, so calling id name directly in hql will cause exception, we have to check class field to match hql columns

List<Object[]> list1 = session.createQuery("select id,,grade from Stucourse").list();

5. uniqueResult(): select unique record from table
Must make sure it has only one or no record
returns null if no record
Throws exception if multiple record found, so setMaxResults(1) can make sure it has only one record
Student s = (Student)session.createQuery("from Student where id<10").setMaxResults(1).uniqueResult();

6. setFirstResult(int): starting record from query, default start from 0

7. setMaxResults(int): retrieve only certain amount of records or less
Pagination will be used by setFirstResult(int) and setMaxResults(int)

8. Parameters in Query
Two styles

  • "?", starts from 0
    Query query = session.createQuery("from Student where department=? and age>?");query.setString(0, "计算机系");
    query.setInteger(1, 20);
    List<Student> list1 = query.list();
  • ":"
    Query query = session.createQuery("from Student where department=:dept and age>:age"); query.setString("dept", "计算机系");
    query.setInteger("age", 20);
    List<Student> list1 = query.list();


Criteria cri = session.createCriteria(Student.class);
cri.add("age", 10));

No comments:

Post a Comment