Hibernate学习笔记-HQL(Hibernate Query Language)
来源:互联网 发布:百度软件开发平台 编辑:程序博客网 时间:2024/04/30 12:00
Hibernate Gossip: 基本查詢
使用HQL查詢最簡單的例子,就是查詢指定?別對應表格的所有資料,例如:Session session = sessionFactory.openSession();
Query query = session.createQuery("from User");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
User user = (User) iterator.next();
System.out.println(user.getId() + "/t" +
user.getAge() + "/t" +
user.getName());
}
Query query = session.createQuery("from User");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
User user = (User) iterator.next();
System.out.println(user.getId() + "/t" +
user.getAge() + "/t" +
user.getName());
}
也可以指定?別的全名,例如:
Query query = session.createQuery("from onlyfun.caterpillar.User");
HQL本身不區分大小寫,不過要注意?別的名稱必須區分大小寫。
在查詢?別對應的表格時,需注意到繼承的問題,Hibernate會自動判定繼承關係,如果查詢的?別是某?別的父?別,則會返回與父?別、子?別對應的所有表格資料,例如如果查詢java.lang.Object,由於Object在Java中是所有?別的父?別,所以下面這個查詢會返回資料庫中所有表格的資料:
Query query = session.createQuery("from java.lang.Object");
如果要針對某個屬性作查詢,則可以如下:
Session session = sessionFactory.openSession();
Query query = session.createQuery("select user.name from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
Query query = session.createQuery("select user.name from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
如果要查詢兩個以上的屬性,則如下,查詢的結果會以陣列的方式傳回:
Session session = sessionFactory.openSession();
Query query = session.createQuery("select user.age, user.name from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
Object[] obj = (Object[]) iterator.next();
System.out.println(obj[0] + "/t" + obj[1]);
}
Query query = session.createQuery("select user.age, user.name from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
Object[] obj = (Object[]) iterator.next();
System.out.println(obj[0] + "/t" + obj[1]);
}
如果User?別提供有適當的建構方法,則可以在使用HQL時直接指定新建一個物件傳回,例如若User如下設計:
package onlyfun.caterpillar;
public class User {
private Integer id;
private String name;
private Integer age;
public User() {
}
public User(String name, Integer age) {
this.name = name;
this.age = age;
}
....
}
public class User {
private Integer id;
private String name;
private Integer age;
public User() {
}
public User(String name, Integer age) {
this.name = name;
this.age = age;
}
....
}
則在使用HQL查詢時可以如下:
Session session = sessionFactory.openSession();
Query query = session.createQuery("select new User(user.name, user.age) from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
User user= (User) iterator.next();
System.out.println(user.getAge() + "/t" + user.getName());
}
Query query = session.createQuery("select new User(user.name, user.age) from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
User user= (User) iterator.next();
System.out.println(user.getAge() + "/t" + user.getName());
}
要注意的是,這個返回的User?例?K未與資料庫有任何關聯,可以試著取得id屬性,可以發現它的值是nul,如果試圖使用Session的saveOrupdate()方法,則會新增一筆資料而不是更新原有的資料。
可以使用distinct去除資料重複的記錄:
Query query = session.createQuery("select distinct user.age from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
您也可以在HQL中使用函式,例如取得資料的筆數:
Query query = session.createQuery("select count(*) from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
使用avg()取得屬性的平均值:
Query query = session.createQuery("select avg(user.age) from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
使用upper()函式將字串轉為大寫:
Query query = session.createQuery("select upper(user.name) from User as user");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
System.out.println(iterator.next());
}
可以一?銋⒖?Query 的使用。
Hibernate Gossip: where、group by、order by 子句
可以使用where子句?硐薅ú樵兊臈l件,除了 = 運算之外,還有 >、>=、<、<=、!= 或 <>等比較運算,例如:Session session = sessionFactory.openSession();
Query query = session.createQuery("from User user where user.name='caterpillar'");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
User user = (User) iterator.next();
System.out.println(user.getAge() + "/t" + user.getName());
}
Query query = session.createQuery("from User user where user.name='caterpillar'");
List names = query.list();
Iterator iterator = names.iterator();
while(iterator.hasNext()) {
User user = (User) iterator.next();
System.out.println(user.getAge() + "/t" + user.getName());
}
也可以在where子句上進行運算式,例如:
Query query = session.createQuery("from User user where (user.age / 10 = 3)");
也可以在where子句上使用and、or,例如:
Query query = session.createQuery("from User user where (user.age > 20) and (user.name = 'caterpillar')");
is not nullL與is null則可以測試欄位值是否為空值,例如:
Query query = session.createQuery("from User user where user.name is not null");
between可以測試欄位值是否在指定的範圍之內,例如:
Query query = session.createQuery("from User user where user.age between 20 and 30");
可以使用in或not in?頊y試欄位值是否在您指定的集合中,例如:
Query query = session.createQuery("from User user where user.name in('caterpillar', 'momor')");
like或not like可以讓您進行模糊條件搜尋,例如想搜尋名稱中含有cater開頭的資料:
Query query = session.createQuery("from User user where user.name like 'cater%'");
可以對查詢結果使用order by進行排序:
Query query = session.createQuery("from User user order by user.age");
可使用desc反排序:
Query query = session.createQuery("from User user order by user.age desc");
可同時指定兩個以上的排序方式,例如先按照"age"反序排列,如果"age"相同,則按照"name"順序排列:
Query query = session.createQuery("from User user order by user.age desc, user.name");
可以配合GROUP BY子句,自動將指定的欄位依相同的內容群組,例如依欄位"sex"分組?K作平均:
Query query = session.createQuery("select user.sex, avg(user.age) from User user group by user.sex");
一個執行的結果如下:
+-------------------------------+
| sex | avg(age) |
+-------------------------------+
| male | 30 |
+-------------------------------+
| female | 25 |
+-------------------------------+
還可以結合having子句,例如只將平均大於20的資料分組顯示出?恚?BR>
Query query = session.createQuery("select user.sex, avg(user.age) from User user group by user.sex having avg(user.age) > 20");
Hibernate Gossip: 更新、刪除
在Hibernate 2時,HQL只用於查詢資料,要更新或刪除資料,則是依賴於Session的update()、saveOrUpdate()、delete()等方法,在Hibernate 3中,HQL新增了update與delete語句,可以直接使用HQL指定更新或刪除,例如使用update子句進行更新:Session session = sessionFactory.openSession();
Transaction tx= session.beginTransaction();
Query query = session.createQuery("update User set name='momor' where name='bbb'");
query.executeUpdate();
tx.commit();
session.close();
Transaction tx= session.beginTransaction();
Query query = session.createQuery("update User set name='momor' where name='bbb'");
query.executeUpdate();
tx.commit();
session.close();
使用delete子句進行資料刪除:
Session session = sessionFactory.openSession();
Transaction tx= session.beginTransaction();
Query query = session.createQuery("delete User where name='bush'");
query.executeUpdate();
tx.commit();
session.close();
Transaction tx= session.beginTransaction();
Query query = session.createQuery("delete User where name='bush'");
query.executeUpdate();
tx.commit();
session.close();
- Hibernate学习笔记-HQL(Hibernate Query Language)
- Hibernate学习笔记-HQL(Hibernate Query Language)
- Hibernate Query Language(HQL)
- HQL(Hibernate Query Language)
- HQL(Hibernate Query Language)
- Hibernate Query Language (HQL)
- HQL(Hibernate Query Language)
- HQL(Hibernate Query Language)使用详解
- hibernate-HQL(query language)-16
- About Hibernate Query Language(HQL)
- Hibernate查询(Query Language,hql,ejbql,nativeql,qbc,qbe)
- Hibernate查询语言(Query Language), 即HQL
- hibernate参考手册之---Hibernate查询语言(Query Language), 即HQL
- Hibernate检索方式之HQL(Hibernate Query Language)
- 【Hibernate框架开发之八】Hibernate 查询语言Query Language(HQL)
- Hibernate中使用Session操作数据以及部分简单HQL(hibernate query language)
- Hibernate学习笔记:HQL
- hibernate HQL学习笔记
- 福布斯:微软浏览器捆绑大战仍将持续
- 考试
- 解决DNN安装时不执行SQL的问题
- 小玩意:切换SVN代码库地址的脚本
- 六十星系之54廉贞破军坐卯酉
- Hibernate学习笔记-HQL(Hibernate Query Language)
- ASP.NET常见问题
- 通往测试架构师之路:那些家伙在干什么?
- sql技巧
- Query Cache 的使用
- ASP.NETweb编程常用到的27个函数集
- C# 操作文件夹及文件
- ExtJS入门-概述
- Hibernate 中聚合函数的使用