快捷搜索:

HQL查询及语法

HQL:Hibernate Query Language

HQL是完周全向工具的查询说话,是以可以支持承袭和多态等特性。

HQL查询依附于Query类,每个Query实例对应一个查询工具,应用HQL查询按

如下步骤进行:

(1)获取Hibernate Session工具;

(2)编写HQL语句;

(3)以HQL语句作为参数,调用Session的createQuery措施创建查询工具;

(4)假如HQL语句包孕参数,调用Query的setXxx措施为参数赋值;

(5)调用Query工具的list等措施遍历查询结果。

查询示例:

public class HqlQuery

{

public static void main(String[] args) throws Exception {

HqlQuery mgr = new HqlQuery();

//调用查询措施

mgr.findPersons();

//调用第二个查询措施

mgr.findPersonByHappenDate();

HibernateUtil.sessionFactory.close();

}

//第一个查询措施

private void findPersons() {

//得到Hibernate Session

Session sess = HibernateUtil.currentSession();

//开始事务

Transaction tx = sess.beginTransaction();

//以HQL语句创建Query工具

//履行setString措施为HQL语句的参数赋值

//Query调用list措施造访查询的整个实例

List p1 = sess.createQuery("from Person p where o.myEvents.title = :

eventTitle").setString("eventTitle", "很通俗工作").list();

//遍历查询的整个结果

for (Iterator pit = p1.iterator(); pit.haxNext(); )

{

Person p = (Person)pit.next();

System.out.println(p.getName());

}

//提交事务

tx.commit();

HibernateUtil.closeSession();

}

//第二个查询措施

private void findPersonByHappenDate() throws Exception {

Session sess = HibernateUtil.currentSession();

Transaction tx = sess.beginTransaction();

//解析出Date工具

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

Date start = sdf.parse("2007-11-27");

System.out.println("系统开始经由过程日期查找人" + start);

//经由过程Session的createQuery措施创建Query工具

//设置参数

//返回结果集

List pl = sess.createQuery(

"from Person p where p.myEvents.happenDate between :firstDate

and :endDate")

.setDate("firstDate", start)

.setDate("endDate", new Date())

.list();

//遍历结果集

for (Iterator pit = pl.iterator(); pit.hasNext(); )

{

Person p = (Person)pit.next();

System.out.println(p.getName());

}

tx.commit();

HibernateUtil.closeSession();

}

}

$下面先容HQL语句的语法

1.from子句

from Person

注解从Person持久化类中选出整个的实例。

保举:from Person as p

2.select子句

select p.name from Person as p

select p.name.firstName from Person as p

select new list(p.name, p.address) from Person as p

select new ClassTest(p.name, p.address) from Person as p (有条件)

select p.name as personName from Person as p

select new map(p.name as personName) from Person as p (与new map()结合更普遍)

3.凑集函数

avg,count,max,min,sum

select count(*) from Person

select max(p.age) from Person as p

select p.name || "" || p.address from Person as p

4.多态查询

from Person as p

from java.lang.Object o

from Named as n

5.where子句

from Person where name like "tom%"

from Person as p where p.name like "tom%"

from Cat cat where cat.mate.name like "kit%"

select * from cat_table as table1 cat_table as table2 where table1.mate =

table2.id and table1.name like "kit%"

from Foo foo where foo.bar.baz.customer.address.city like "fuzhou%"

from Cat cat, Cat rival where cat.mate = rival.mate

select cat, mate

from Cat cat, Cat mate

where cat.mate = mate

from Cat as cat where cat.id = 123

from Cat as cat where cat.mate.id = 69

from Person as person

where person.id.country = 'AU'

and person.id.medicareNumber = 123456

from Account as account

where account.owner.id.country = 'AU'

and account.owner.id.medicareNumber = 123456

from Cat cat where cat.class = DomesticCat

from Account as a where a.person.name.firstName like "dd%" // 精确

from Account as a where a.person.name like "dd%" // 差错

6.表达式

from DomesticCat cat where cat.name between 'A' and 'B'

from DomesticCat cat where cat.name in ('Foo', 'Bar', 'Baz')

from DomesticCat cat where cat.name not between 'A' and 'B'

from DomesticCat cat where cat.name not in ('Foo', 'Bar', 'Baz')

from DomesticCat cat where cat.name is null

from Person as p where p.address is not null

true 1, false 0

from Cat cat where cat.alive = true

from Cat cat where cat.kittens.size > 0

from Cat cat where size(cat.kittens) > 0

from Calendar cal where maxelement(cal.holidays) > current date

from Order order where maxindex(order.items) > 100

from Order order where minelement(order.items) > 10000

//操作聚拢元素

select mother from Cat as mother, Cat as kit

where kit in elements(foo.kittens)

//p的name属性即是聚拢中某个元素的name属性

select p from NameList list, Person p

where p.name = some elements(list.names)

//操作聚拢元素

from Cat cat where exists elements(cat.kittens)

from Player p where 3 > all elements(p.scores)

from Show show where 'fizard' in indices(show.acts)

//items是有序聚拢属性,items[0]代表第一个元素

from Order order where order.items[0].id = 1234

//holidays是map聚拢属性,holidays[national day]是代表此中第一个元素

select person from Person person, Calendar calendar

where calendar.holidays['national day'] = person.birthDay

and person.nationality.calendar = calendar

//下面同时应用list聚拢和map聚拢属性

select item from Item item, Order order

where order.items[order.deliveredItemIndices[0]] = item and order.id = 11

select item from Item item, Order order

where order.items[maxindex(order.items)] = item and order.id = 11

select item from Item item, Order order

where order.items[size(order.items) - 1] = item

select cust

from Product prod,

Store store

inner join store.customers cust

where prod.name = 'widget'

and store.location.name in ['Melbourne', 'Sydney']

and prod = all elements(cust.currentOrder.lineItems)

SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order

FROM customers cust,

stores store,

locations loc,

store_customers sc,

product prod

WHERE prod.name = 'widget'

AND store.loc_id = loc.id

AND loc.name IN ('Melbourne', 'Sydney')

AND sc.store_id = store.id

AND sc.cust_id = cust.id

AND prod.id = ALL(

SELECT item.prod_id

FROM line_items item, orders o

WHERE item.order_id = o.id

AND cust.current_order = o.id

)

7.order by子句

from Person as p

order by p.name, p.age

from Person as p

order by p.name asc, p.age desc

8.group by子句

select cat.color, sum(cat.weight), count(cat)

from Cat cat

group by cat.color

//select后呈现的id处呈现在group by之后,而name属性则呈现在凑集函数中

select foo.id, avg(name), max(name)

from Foo foo join foo.names name

group by foo.id

select cat.color, sum(cat.weight), count(cat)

from Cat cat

group by cat.color

having cat.color in (eg.Color.TABBY, eg.Color.BLACK)

select cat

from Cat cat

join cat.kittens kitten

group by cat

having avg(kitten.weight) > 100

order by count(kitten) asc, sum(kitten.weight) desc

9.子查询

from Cat as fatcat

where fatcat.weight > (select avg(cat.weight) from DomesticCat cat)

from Cat as cat

where not (cat.name, cat.color) in (

select cat.name, cat.color from DomesticCat cat

)

10.fetch关键字

from Person as p join p.scores

from Document fetch all properties order by name

from Document doc fetch all properties where lower(doc.name) like '%cat%'

11.命名查询

from Person as p where p.age > ?

调用命名查询的示例代码如下:

private void findByNamedQuery() throws Exception {

Session session = HibernateUtil.currentSession();

Transaction tx = session.beginTransaction();

System.out.println("履行命名查询");

//调用命名查询

List pl = sess.getNamedQuery("myNamedQuery")

//为参数赋值

.setInteger(0, 20)

//返回整个结果

.list();

//遍历结果集

for (Integer pit = pl.iterator(); pit.hasNext(); )

{

Person p = (Person)pit.next();

System.out.println(p.getName());

}

tx.commit();

HibernateUtil.closeSession();

}

您可能还会对下面的文章感兴趣: