HQL and Criteria

HQL: Hibernate Query Language

HQL:

• 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 user.name=:name
• from User user where user.name=name 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

session.delete
session.save
session.persist
session.update
session.load
session.get

Examples


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();

like

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,course.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

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