Hibernate中Hql常见使用方式
完整的HQL 语句形势如下：
Select/update/delete…… from …… where …… group by …… having …… order
by …… asc/desc
其中的update/delete 为Hibernate3 中所新添加的功能
update:
String hql=”update User user set user.age=20 where user.age=18”;
Query queryupdate=session.createQuery(hql);
int ret=queryupdate.executeUpdate();
删除
Transaction trans=session.beginTransaction();
String hql=”delete from User user where user.age=18”;
Query queryupdate=session.createQuery(hql);
int ret=queryupdate.executeUpdate();
String hql=”from User user ”;
List list=session.CreateQuery(hql).list();
因为HQL 语句与标准SQL 语句相似，所以我们也可以在HQL 语句中使用where 字句，并且可
以在where 字句中使用各种表达式，比较操作符以及使用“and”,”or”连接不同的查询条
件的组合。看下面的一些简单的例子：
from User user where user.age=20;
from User user where user.age between 20 and 30;
from User user where user.age in(20,30);
from User user where user.name is null;
from User user where user.name like ‘%feng%’;
from User user where (user.sex%2)=1;
from User user where user.age=20 and user.name like ‘%feng%’;
select user.name,user.age from User user -- Object[]
List list=(List)session.createQuery("select user.name,user.age from User user").list();
for(int i=0;i<list.size();i++)
{
Object[] objs=(Ob ject[])list.get(i);
String name=(String)objs[0];
int age=(Integer)objs[1];
}
设置参数：
Query query=session.createQuery(“from User user where user.name=?
and user.sex=:sex and user.customerage=? ”);
query.setString(“name1”,name);
query.setInteger(“age”,age);
query.setInteger("sex",sex);
=================================
String hql=”from User user where user.name=:customername ”;
Query query=session.createQuery(hql);
query.setParameter(“customername”,name,Hibernate.STRING);
-----------------------------
class Customer{
private int age1000;
public void setAge(int age)
{
this.age1000=age;
}
}
Customer customer=new Customer();
customer.setName(“pansl”);
customer.setAge(80);
Query query=session.createQuery(“from Customer c where c.name=:name and c.age=:age ”);
query.setProperties(customer);
注意：属性名字与参数名一样。
from User user order by user.name asc,user.age desc;
假设我们现在有两张数据库表，分别是customer 表和order 表，它们的结构如下：
customer
ID varchar2(14)
age number(10)
name varchar2(20)
order
ID varchar2(14)
order_number number(10)
customer_ID varchar2(14)
--------------------------
from Customer c inner join c.orders o group by c.age;
select c.ID,c.name,c.age,o.ID,o.order_number,o.customer_ID
from Customer c inner join c.orders c group by c.age
Query query = session.createQuery(“select count(*) from Student”); //取得Student数量
Query query = session createQuery(“select avg(s.age) from Student as s”);
Query query = session createQuery(“select upper(s.name) from Student s”); /
Query query = session createQuery(“select distinct s.age from Student as s”); //去
//注意:hibernate3.2
session.createQuery("select distince s from Student s");
