Hibernate QBC查询

Hibernate QBC语言
文章分类:Java编程
节 8.01 基本查询
以下是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 }))

);