- 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
- <property name="hibernate.query.substitutions">true 1, false 0</property>
- 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.命名查询
- <!--定义命名查询-->
- <query name="myNamedQuery">
- <!--此处确定命名查询的HQL语句-->
- from Person as p where p.age > ?
- </query>
- 调用命名查询的示例代码如下:
- 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();
- }
HQL查询及语法
最新推荐文章于 2022-07-15 14:42:32 发布