Hibernate Query Language (HQL)

Topics What is HQL? "FROM" clause Associations and join "SELECT " clause Polymorphic query "WHERE " clause

What is HQL?

Hibernate Query Language (HQL) Very similar to SQL but less verbose Understands OO – inheritance, polymorphism,

association, aggregation, composition Selection: from, as Associations and joins: inner join, left outer join,

right outer join, full join Projection: select, elements Constraints: where Other constructs: aggregate functions, expressions,

order by clauses, group by clauses, polymorphic selections, sub-queries

Differences from SQL HQL is fully object-oriented, understanding

notions like inheritance, polymorphism and association

Queries are case-insensitive, except for names of Java classes and properties. So SeLeCT is the same as sELEct is the same as

SELECT but org.hibernate.eg.FOO is not org.hibernate.eg.Foo

and foo.barSet is not foo.BARSET

"FROM" clause

"FROM" clause Return all instances of the class,

example.CustomerFROM example.Customer

Usually don't need to qualify the class name, since auto-import is the default.FROM Customer is same as FROM example.Customer

Most of the time, you will want to assign an alias, since you will want to refer to the Customer in other parts of the queryFROM Customer AS customer

Or

FROM Customer customer

"FROM" clause for multiple classes Multiple classes may appear, resulting in a

Cartesian product or "cross" join.FROM Formula, Parameter

FROM Formula AS form, Parameter AS param

Local variable naming recommendation Name query aliases using an initial lowercase,

consistent with Java naming standards for local variables

Associations and Joins

JOIN We may also assign aliases to associated

entities, or even to elements of a collection of values, using a join FROM Cat AS cat

INNER JOIN cat.mate AS mate

LEFT OUTER JOIN cat.kittens AS kitten FROM Cat AS cat LEFT JOIN cat.mate.kittens AS kittens

FROM Formula form FULL JOIN form.parameter param

JOIN types supported inner join left outer join right outer join full join (not usually useful)

"SELECT" clause

SELECT clause The select clause picks which objects and

properties to return in the query result setSELECT courseFROM CourseOffering AS offering INNER JOIN offering.course AS course

Compact formSELECT offering.course FROM CourseOffering offering

SELECT clause Queries may return properties of any value type

including properties of component typeSELECT cat.name FROM DomesticCat cat WHERE cat.name LIKE 'fri%'SELECT cust.name.firstName FROM Customer AS cust

Queries may return multiple objects and/or properties as an array of type Object[]SELECT mother, offspr, mate.name FROM DomesticCat AS mother INNER JOIN mother.mate AS mate LEFT OUTER JOIN mother.kittens AS offspr

SELECT clause Queries may return multiple objects and/or

properties as a ListSELECT new List(mother, offspr, mate.name)

FROM DomesticCat AS mother

INNER JOIN mother.mate AS mate

LEFT OUTER JOIN mother.kittens AS offspr

Or as an actual typesafe Java objectSELECT new Family(mother, mate, offspr)

FROM DomesticCat AS mother

JOIN mother.mate AS mate

LEFT JOIN mother.kittens AS offspr

Polymorphic Query

Polymorphic queries The query below returns instances not only of Employee, but also of subclasses like HourlyEmployeeFROM Employee AS employee

Hibernate queries may name any Java class or interface in the from clause. The query will return instances of all persistent

classes that extend that class or implement the interface.

The following query would return all persistent objectsFROM java.lang.Object o

"WHERE" clause

WHERE clause The where clause allows you to narrow the list of

instances returned. If no alias exists, you may refer to properties by

nameFROM Cat WHERE name='Fritz'

If there is an alias, use a qualified property name:FROM Cat AS cat WHERE cat.name='Fritz'

where clause Return all instances of Foo for which there exists

an instance of bar with a date property equal to the startDate property of the FooSELECT fooFROM Foo foo, Bar barWHERE foo.startDate = bar.date

Compound path expressions make the where clause extremely powerful. FROM Cat cat WHERE cat.mate.name IS NOT NULL

Expresstions Mathematical operators : + - * / Binary comparison operators = >= <= <> != like

logical operations and, or, not Parentheses(), indicating grouping: in, not in, between, is null, is not null, is empty, is not empty, member of and not member of

"Simple" case, case ... when ... then ... else ... end, and "searched" case, case when ... then ... else ... end

Binary comparison operators: Các phép toán này sẽ giúp cho câu Query tìm

kiếm dễ dàng bằng cách so sánh: = , >= , <= , <> , != , like

Ví dụ FROM Student st WHERE st.name='Nguyen Chi Tien' FROM Course c WHERE c.credits>=4 FROM Course c WHERE c.credits<>4 FROM Course c WHERE c.name<>'CSDL' FROM Course c WHERE c.name != 'MMT' FROM Student st WHERE st.name LIKE 'Nguyen%'

Tìm student có tên bắt đầu bằng chuỗi Nguyen…

Logical operations Các phép toán này giúp ta có thể kết hợp nhiều

điều kiện trong mệnh đề WHERE: AND, OR, NOT

Các vd: FROM Course c WHERE c.name = 'HTTTDL' AND

c.id=8 FROM Course c WHERE c.name='CSDL' OR

c.credits=3 FROM Course c WHERE c.name IS NOT 'CSDL'

Parentheses Chỉ ra một nhóm các giá trị chứa trong ngoặc đơn:

IN, NOT IN, BETWEEN, IS NULL, IS NOT NULL, IS EMPTY, IS NOT EMPTY, MEMBER OF, NOT MEMBER OF

IN, NOT IN FROM Course c WHERE c.name IN ('HTTTDL, 'MMT') FROM Course c WHERE c.name NOT IN ('HTTTDL', 'MMT')

BETWEEN, NOT BETWEEN FROM Course c WHERE c.credits BETWEEN 1 AND 4 FROM Course c WHERE c.credits NOT BETWEEN 1 AND 4

IS NULL, IS NOT NULL FROM Student st WHERE st.emails.size IS NULL FROM Student st WHERE st.emails.size IS NOT NULL

Order By Một kết quả trả về của câu Query có thể được

sắp xếp theo các thuộc tính của một lớp hoặc Components

Chúng ta có thể sắp xếp tăng dần bằng từ khóa ASC(mặc định) hoặc giảm dần bằng DESC hoặc kết hợp ASC, DESC

Các vd: FROM Student st ORDER BY st.name FROM Course c ORDER BY c.name desc FROM Course c ORDER BY c.name, c.credits desc

Group By Một câu Query kết quả trả về được nhóm lại

theo một thuộc tính của Class hay ComponentsSELECT course.credits, COUNT(course)

FROM Course course GROUP BY course.credits

Chúng ta cũng có thể Query kèm theo điều kiện khi Group by bằng từ khóa HavingSELECT course.credits, COUNT(course)

FROM Course courseGROUP BY course.credits HAVING course.credits > 3

having and order by

SELECT course.credits, SUM(course.id)FROM Course course GROUP BY course.creditsHAVING SUM(course.credits)>3 ORDER BY course.credits desc

Chú ý: group by cũng như ORDER BY không được chứa đựng biểu thức toán học hay tính toán

Subqueries Một subquery phải được bao trong dấu ngoặc

đơn. Subquery cũng có thể sử dụng một alias bên ngoài. FROM Course course WHERE course.credits >

SELECT AVG(c.credits) FROM Course c) SELECT st.id, (SELECT MAX(email.size) FROM

st.emails email) FROM Student st

Subqueris có thể dùng nhiều biểu thức trong select FROM Cat AS cat

WHERE NOT (cat.name, cat.color) IN (SELECT cat.name, cat.color FROM DomesticCat cat)

Insert Update sử dụng phương thức executeUpdate()

String masv="0313003";Student st = (Student) ses.load(Student.class, StuId);String oldName = st.getName();String hqlUpdate = "update Student st set st.name = :newName where st.name = :oldName";int updatedEntities = ses.createQuery( hqlUpdate)

.setString("newName", newName)

.setString("oldName", oldName).executeUpdate();

Delete Sử dụng phương thức executeUpdate() để xóa:

String name = "CSDL";

String hqlDelete = "DELETE Course c WHERE c.name = :oldName";

int deletedEntities = ses.createQuery(hqlDelete)

.setString("oldName", name)

.executeUpdate();

Kết quả trả về của phương thức excuteUpdate là int cho ta biết số lượng được update ,không ảnh hưởng đến số dòng trong Database

Tips & Tricks Ta có thể đếm được số lượng của kết quả mà

không cần phải đưa ra kết quả.Integer acc = ((Integer)

(session.iterate("select count(*) from Course").next()).intValue();

Ta cũng có thể sắp xếp kết quả trả về theo size của một collectionString sqlStr = "SELECT st.mssv, st.name

FROM Student AS st JOIN st.emails AS email GROUP BY st.mssv, st.name ORDER BY COUNT(email)";

Query q = se.createQuery(sqlStr);Iterator result = q.iterate();