【郭林专刊】HQL语法汇总

最新推荐文章于 2022-10-15 13:31:02 发布

iteye_10189

最新推荐文章于 2022-10-15 13:31:02 发布

阅读量98

点赞数

文章标签： java

HQL语法结构(update/delete子句为Hibernate3引入的新特性)
[select/update/delete]
[from...]
[where...]
[group by...[having...]]
[order by...]

hql = "SELECT DISTINCT user.admin FROM User as user";
DISTINCT表示去掉重复值。
在testCase的增加表中，增加product和module字段时可用此关键字。
hql = "SELECT COUNT(*) FROM User";
hql = "SELECT AVG(user.age) FROM User as user";
hql = "SELECT UPPER(user.name) FROM User as user";
hql = "FROM User as user WHERE user.name is not null";

UPDATE User SET name="vivian" WHERE name="peggy";//没有给User类起别名
DELETE User WHERE name="peggy";

Criteria Query和Query接口提供了两个方法，用于完成分页
setFirstResult(0); //从第0条取数据
setMaxResult(10); //共取10条记录

HQL中的时间比较

String hql = "from TradeRecord as tr where tr.TradeTime>= :startTime "
+ "and tr.TradeTime <= :endTime and tr.CustomerId =:cid";
String[] params = { "startTime", "endTime", "cid" };
Object[] args = { startTime, endTime, new Long(cid) };
List list = this.getHibernateTemplate().findByNamedParam(hql, params, args);

startTime,endTime,cid是这个方法的参数

Spring下常用的HQL

1.hql更新
String hql = "update PhUser set realName=?";
int row=this.getSession().createQuery(hql).setString(0, "小李想").executeUpdate();
PhUser 类名
2.hql删除
String hql = "delete PhUser a where a.userId=2";
int row=this.getSession().createQuery(hql).executeUpdate();
还有个这种的格式:
final String hql = "delete PhRoleFunction as a where a.roleId = "
+ roleId;
this.getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
return session.createQuery(hql).executeUpdate();
}
});更新也可以写成这样的格式
3.hql单表查询
String hql = "from PhUser a where a.userId=" + userId;
List list = this.getHibernateTemplate().find(hql);
4.hql多表查询
(1)String hql = "select new map(a.CUId as CUId,a.unitName as unitName,b.CUFId as CUFId,b.UFName as UFName) from PhCorrelativeUnit a,PhCorrelativeUnitFunction b where a.CUId=b.CUId";
List list = this.getHibernateTemplate().find(hql);
多个表的字段放到map中,map的键值就是as后面的别名,如果没有as就是字段名
(2) String hql = "select new com.phantom.appeal.action.bean.DealPaper(a.id as id,a.billId as billId,a.state as state,a.creator as creator,a.createtime as createtime ,b.eventContent as eventContent ,c.realName as realName,b.billCode as billCode,b.citName as citName ) from PhDealBill a,PhAcceptBill b,PhUser c where a.departmentId="+ billid+ " and a.state=0 and a.billId=b.billId and a.creator =c.userId order by a.billId";
return this.getHibernateTemplate().find(hql);
另外就是写一个类,对应你要查询的字段,这里的类名是new com.phantom.appeal.action.bean.DealPaper,里面对应查询的字段名
5.得到记录数
String hql = "select count(*) from PhUser";
List list = this.getHibernateTemplate().find(hql);
return ((Long) list.get(0)).intValue();

hql 的like 写法

String hql = "from tableA a ";

List values = new LinkedList();

if (name != null && name.length() > 0) {

hql = hql + " where a.name like ? ";

values.add("%" + name + "%");

}

替代sql的in,not in操作

select * from tableb b where exists(select 1 from tablea a where a.cola=b.col)
用EXISTS方式会比IN的方式查询速度快

使用外部连接代替NOT IN

原SQL

Select Title from Bookshelf where Tiltle not in(select Title from bookSelf_checkout) order by Title

外部连接的Sql语句

Select distinct b.title from bookshelf_checkout bc right outer join bookshelf b on bc.title=b.title where bc.title is Null order by b.title

使用Not Exists 来代替Not in

Select b.title from bookshelf b where not exists(select ‘x’ from bookshelf_checkout bc where bc.title=b.tiltle) order by b.title

hibernate+spring 取记录条数方法

//第一种方法：
Stringhql="selectcount(*)fromUserasuser"
Integercount=(Integer)getHibernateTemplate().find(hql).listIterator().next();
returncount.intValue();

//第二种方法：
Stringhql="selectcount(*)fromUserasuser";
return((Integer)getHibernateTemplate().iterate(hql).next()).intValue();

//第三种方法：
Stringhql="selectcount(*)fromUserasuser";
Queryquery=getHibernateTemplate().createQuery(getSession(),hql);
return((Integer)query.uniqueResult()).intValue();