Hibernate QBC查询
HibernateQBC语言
文章分类:Java编程
节8.01基本查询
以下是HQL/QBC/NativeSQL三种查询策略
HQL策略：
Java代码
session.createQuery("FROMCategorycwherec.namelike'Laptop%'");
session.createQuery("FROMCategorycwherec.namelike'Laptop%'");
QBC策略：
Java代码
session.createCriteria(Category.class).add(Restrictions.like("name","Laptop%"));
session.createCriteria(Category.class).add(Restrictions.like("name","Laptop%"));
NativeSQL策略
Java代码
session.createSQLQuery("select{c.*}fromCATEGORY{c}whereNAMElike'Laptop%'").
addEntity("c",Category.class);
session.createSQLQuery("select{c.*}fromCATEGORY{c}whereNAMElike'Laptop%'").
addEntity("c",Category.class);
节8.02分页查询
Java代码
Criteriacriteria=session.createCriteria(Category.class)
.add(Restrictions.like("name","Laptop%"));
criteria.addOrder(Order.asc("name"));
criteria.setFirstResult(0);//初始行数
criteria.setMaxResults(20);//每页显示行数
Criteriacriteria=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.betweenBETWEEN
Restrictions.likeLIKE
Restrictions.inin
Restrictions.andand
Restrictions.oror
Restrictions.sqlRestriction用SQL限定查询
(a)应用限制
Java代码
CriterionemailEq=Restrictions.eq("email","[email protected]");
Criteriacriteria=session.createCriteria(User.class);
criteria.add(emailEq);
Useruser=(User)criteria.uniqueResult();
CriterionemailEq=Restrictions.eq("email","[email protected]");
Criteriacriteria=session.createCriteria(User.class);
criteria.add(emailEq);
Useruser=(User)criteria.uniqueResult();
(b)比较表达式
Java代码
Restrictions.between("amount",newBigDecimal(100),newBigDecimal(200));
Restrictions.gt("amount",newBigDecimal(100));
Restrictions.in("email",emails);//注：emails为集合
Restrictions.isNull("email");
Restrictions.isNotNull("email");
Restrictions.isEmpty("bids");
Restrictions.sizeGe("bids",3);//bids属性大小
Restrictions.between("amount",newBigDecimal(100),newBigDecimal(200));
Restrictions.gt("amount",newBigDecimal(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(selectb.AMOUNTFROMBIDb"+
"WHEREb.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(selectb.AMOUNTFROMBIDb"+
"WHEREb.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",newBigDecimal(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",newBigDecimal(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",newBigDecimal(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",newBigDecimal(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",newDate()))
.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",newDate()))
.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代码
StringsqlFragment="(selectcount(*)fromItemiwherei.item_id=item_id)"
+"asnumofitems";
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,
newString[]{"numofitems"},
newType[]{Hibernate.LONG}))
);