读书人
栏目▼

Hibernate的查询形式

发布时间： 2012-09-07 10:38:15 作者： rapoo

Hibernate的查询方式

Hibernate的查询方式有：

?

1.使用主键id加载对象(load(),get());

2.通过对象导航,比如通过stu.getTeam()得到team的实例;

3.使用hql;

4.使用qbc(query by criteria)

5.直接使用sql语句取得记录集;

一般都使用后面三种方式.

注意.hql是面向对象的查询.语法和sql是基本一样的.不区分大小写的,但是注意的是对与对象.必须遵循对象的大小写.因为hql是对像查询..同时我们必须清楚.hql只能取得对象,而不支持uid(update,insert.delete)

?

?

HQL功能最强大，适合各种情况，但是动态条件查询构造起来很不方便
Criteria最适合动态条件查询，不太适合统计查询，QBE还不够强大，只适合相当简单的查询
NativeSQL可以实现特定数据库的SQL，但是可移植性就牺牲了

Hibernate2的Criteria功能不够完善，所以Hibernate2上面可用的只有HQL和NativeSQL，Hibernate3的Criteria已经非常强大了。

针对web应用来说，大部分常规查询都是动态条件查询，所以首选使用Criteria，并且Hibernate3提供了DetachedCriteria，可以在web层构造好DetachedCriteria再进入session执行查询，非常方便实用的特性。

但是涉及到统计查询和非常复杂的关联查询，Criteria就无能为力了，这种情况下我选择使用HQL。

?

?

?

以下是HQL/QBC/Native SQL三种查询策略
HQL策略：

Java代码
session.createQuery("FROM Category c where c.name like 'Laptop%'");?

session.createQuery("FROM Category c where c.name like 'Laptop%'");?

QBC策略：

Java代码
session.createCriteria(Category.class).add(Restrictions.like("name", "Laptop%"));?

session.createCriteria(Category.class).add(Restrictions.like("name", "Laptop%"));?

Native SQL策略

Java代码
session.createSQLQuery("select {c.*} from CATEGORY {c} where NAME like 'Laptop%'").??
addEntity("c",Category.class);?

session.createSQLQuery("select {c.*} from CATEGORY {c} where NAME like 'Laptop%'").
addEntity("c",Category.class);?

节 8.02??? 分页查询

Java代码
Criteria criteria = session.createCriteria(Category.class)??
.add(Restrictions.like("name", "Laptop%"));??
criteria.addOrder(Order.asc("name"));??
criteria.setFirstResult(0);//初始行数??
criteria.setMaxResults(20);//每页显示行数?

Criteria criteria = session.createCriteria(Category.class)
.add(Restrictions.like("name", "Laptop%"));
criteria.addOrder(Order.asc("name"));
criteria.setFirstResult(0);//初始行数
criteria.setMaxResults(20);//每页显示行数?

节 8.03??? 数据过滤
方法????????????????????? 说明???
Restrictions.eq???????? =??
Restrictions.allEq????? 利用Map来进行多个等于的限制???
Restrictions.gt???????? >???
Restrictions.ge???????? >=???
Restrictions.lt???????? <?
Restrictions.le???????? <=???
Restrictions.between??? BETWEEN??
Restrictions.like?????? LIKE??
Restrictions.in???????? in??
Restrictions.and??????? and??
Restrictions.or???????? or??
Restrictions.sqlRestriction???? 用SQL限定查询
(a)??? 应用限制

Java代码
Criterion emailEq = Restrictions.eq("email", "w@163.com");??
Criteria criteria = session.createCriteria(User.class);??
criteria.add(emailEq);??
User user = (User)criteria.uniqueResult();?

Criterion emailEq = Restrictions.eq("email", "w@163.com");
Criteria criteria = session.createCriteria(User.class);
criteria.add(emailEq);
User user = (User)criteria.uniqueResult();?

(b)??? 比较表达式

Java代码
Restrictions.between("amount", new BigDecimal(100), new BigDecimal(200));??
Restrictions.gt("amount", new BigDecimal(100));??
Restrictions.in("email", emails);//注：emails为集合??
Restrictions.isNull("email");??
Restrictions.isNotNull("email");??
Restrictions.isEmpty("bids");??
Restrictions.sizeGe("bids", 3);//bids属性大小?

Restrictions.between("amount", new BigDecimal(100), new BigDecimal(200));
Restrictions.gt("amount", new BigDecimal(100));
Restrictions.in("email", emails);//注：emails为集合
Restrictions.isNull("email");
Restrictions.isNotNull("email");
Restrictions.isEmpty("bids");
Restrictions.sizeGe("bids", 3);//bids属性大小?

(c)??? 字符串匹配

Java代码
Restrictions.like("email", "G%");??
Restrictions.like("email", "G%",MatchMode.START);??
注：MatchMode分为START,END,ANYWHERE,EXACT四种模式??
Restrictions.like("email", "G%").ignoreCase();?

Restrictions.like("email", "G%");
Restrictions.like("email", "G%",MatchMode.START);
注：MatchMode分为START,END,ANYWHERE,EXACT四种模式
Restrictions.like("email", "G%").ignoreCase();?

(d)??? 组合表达式和逻辑操作符

Java代码
Restrictions.or(??
??????????????? Restrictions.and(??
??????????????????????? Restrictions.like("firstname", "G%"),??
??????????????????????? Restrictions.like("lastname", "K%")),??
??????????????????????? Restrictions.in("email",emails));?

Restrictions.or(
??????????????? Restrictions.and(
??????????????????????? Restrictions.like("firstname", "G%"),
??????????????????????? Restrictions.like("lastname", "K%")),
??????????????????????? Restrictions.in("email",emails));?

(e)??? SQL表达式

Java代码
Restrictions.sqlRestriction("{alias}.name='tie' and {alias}.addr='dalian'");??
Restrictions.sqlRestriction("{alias}.name=?",? "tie", Hibernate.STRING);//姓名为tie的对象??
Restrictions.sqlRestriction("length({alias}.PASSWORD) < ?",5,Hibernate.INTEGER);??
//密码小于5个字符对象??
Restrictions.sqlRestriction("'100' >all( select b.AMOUNT FROM BID b " +??
??????????????? " WHERE b.ITEM_ID = {alias}.ITEM_ID)");//返回出价不大于100?

Restrictions.sqlRestriction("{alias}.name='tie' and {alias}.addr='dalian'");
Restrictions.sqlRestriction("{alias}.name=?",? "tie", Hibernate.STRING);//姓名为tie的对象
Restrictions.sqlRestriction("length({alias}.PASSWORD) < ?",5,Hibernate.INTEGER);
//密码小于5个字符对象
Restrictions.sqlRestriction("'100' >all( select b.AMOUNT FROM BID b " +
??????????????? " WHERE b.ITEM_ID = {alias}.ITEM_ID)");//返回出价不大于100?


(f)??? 子查询

节 8.04??? 表关联
(a)??? 隐式关联
隐式关联有两种方法：
1、??? Criteria接口的createCriteria()方法：

Java代码
session.createCriteria(Item.class)??
.add(Restrictions.like("description", "Foo",MatchMode.ANYWHERE))??
.createCriteria("bids")??
.add(Restrictions.gt("amount",new BigDecimal(100)));??
?
session.createCriteria(Item.class)??
??????????? .createCriteria("seller")??
??????????? .add(Restrictions.like("email", "%@"));?

session.createCriteria(Item.class)
.add(Restrictions.like("description", "Foo",MatchMode.ANYWHERE))
.createCriteria("bids")
.add(Restrictions.gt("amount",new BigDecimal(100)));

session.createCriteria(Item.class)
??????????? .createCriteria("seller")
??????????? .add(Restrictions.like("email", "%@"));?

2、??? 分配别名：

Java代码
session.createCriteria(Item.class)??
??????????? .createAlias("bids","b")??
??????????? .add(Restrictions.like("description", "%Foo%"))??
??????????? .add(Restrictions.gt("b.amount", new BigDecimal(100)));??
?
session.createCriteria(Item.class)??
??????????? .createAlias("seller", "s")??
??????????? .add(Restrictions.like("s.email","%@"));?

session.createCriteria(Item.class)
??????????? .createAlias("bids","b")
??????????? .add(Restrictions.like("description", "%Foo%"))
??????????? .add(Restrictions.gt("b.amount", new BigDecimal(100)));

session.createCriteria(Item.class)
??????????? .createAlias("seller", "s")
??????????? .add(Restrictions.like("s.email","%@"));?

(b)??? 抓取关联

Java代码
session.createCriteria(Item.class)??
??????????? .setFetchMode("bids",FetchMode.JOIN)??
??????????? .add(Restrictions.like("description", "%Foo%"))?

session.createCriteria(Item.class)
??????????? .setFetchMode("bids",FetchMode.JOIN)
??????????? .add(Restrictions.like("description", "%Foo%"))?



节 8.05??? 投影/报表查询
(a)??? 简单投影

Java代码
session.createCriteria(Item.class)??
??????? .add(Restrictions.gt("endDate", new Date()))??
??????? .setProjection(Projections.id());//返回单一属性??
?
session.createCriteria(Item.class).setProjection(??
Projections.projectionList().add(Projections.id()).??
add(Projections.property("description")));//返回一个Object[]?

session.createCriteria(Item.class)
??????? .add(Restrictions.gt("endDate", new Date()))
??????? .setProjection(Projections.id());//返回单一属性

session.createCriteria(Item.class).setProjection(
Projections.projectionList().add(Projections.id()).
add(Projections.property("description")));//返回一个Object[]?


(b)??? 统计分组

Java代码
session.createCriteria(Item.class)??
.setProjection(Projections.rowCount());??
?
session.createCriteria(Item.class)??
??????????? .setProjection(Projections.projectionList()??
??????????? .add(Projections.rowCount())??
??????????? .add(Projections.sum("sales"))??
??????????? .add(Projections.avg("score"))??
??????????? );??
?
??? session.createCriteria(Bid.class)??
??????? .createAlias("bidder", "u")??
??????? .setProjection(Projections.projectionList()??
??????????????? .add(Property.forName("u.id").group())??
??????????????? .add(Property.forName("u.username").group())??
??????????????? .add(Property.forName("id").count())??
??????????????? .add(Property.forName("amount").avg())??
??????????? );?

session.createCriteria(Item.class)
.setProjection(Projections.rowCount());

session.createCriteria(Item.class)
??????????? .setProjection(Projections.projectionList()
??????????? .add(Projections.rowCount())
??????????? .add(Projections.sum("sales"))
??????????? .add(Projections.avg("score"))
??????????? );

??? session.createCriteria(Bid.class)
??????? .createAlias("bidder", "u")
??????? .setProjection(Projections.projectionList()
??????????????? .add(Property.forName("u.id").group())
??????????????? .add(Property.forName("u.username").group())
??????????????? .add(Property.forName("id").count())
??????????????? .add(Property.forName("amount").avg())
??????????? );?



(c)??? SQL投影

Java代码
String sqlFragment = "(select count(*) from Item i where i.item_id = item_id) "?
??????????????? + " as numofitems";??
??????? session.createCriteria(Bid.class).createAlias("bidder", "u")??
??????????????? .setProjection(??
??????????????????????? Projections.projectionList().add(??
??????????????????????????????? Projections.groupProperty("u.id")).add(??
??????????????????????????????? Projections.groupProperty("u.username")).add(??
??????????????????????????????? Projections.count("id")).add(??
??????????????????????????????? Projections.avg("amount")).add(??
??????????????????????????????? Projections.sqlProjection(sqlFragment,??
??????????????????????????????????????? new String[] { "numofitems" },??
??????????????????????????????????????? new Type[] { Hibernate.LONG }))??
?
??????????????? );?

读书人网 >其他数据库

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net