Hibernate QBC 语言
基本查询
以下是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);
分页查询
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);//每页显示行数
数据过滤
方法 说明
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", "[email protected]"); Criteria criteria = session.createCriteria(User.class); criteria.add(emailEq); User user = (User)criteria.uniqueResult(); Criterion emailEq = Restrictions.eq("email", "[email protected]"); 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) 子查询
表关联
(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%"))
投影/报表查询
(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 })) );
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。