IT忍者神龟之Hibernate QBC语言

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

Java代码  

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

 

QBC策略：

Java代码  

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

 

Native SQL策略

Java代码  

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

 

节 8.02    分页查询

Java代码  

1. Criteria criteria = session.createCriteria(Category.class)  
2. .add(Restrictions.like("name", "Laptop%"));  
3. criteria.addOrder(Order.asc("name"));  
4. criteria.setFirstResult();//初始行数  
5. 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代码 

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

 

(b)    比较表达式

Java代码  

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

 

(c)    字符串匹配

Java代码 

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

 

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

Java代码 

1. Restrictions.or(  
2.                 Restrictions.and(  
3.                         Restrictions.like("firstname", "G%"),  
4.                         Restrictions.like("lastname", "K%")),  
5.                         Restrictions.in("email",emails));  

 

(e)    SQL表达式

Java代码 

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

 

(f)    子查询

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

Java代码  

1. session.createCriteria(Item.class)  
2. .add(Restrictions.like("description", "Foo",MatchMode.ANYWHERE))  
3. .createCriteria("bids")  
4. .add(Restrictions.gt("amount",new BigDecimal(100)));  
5.   
6. session.createCriteria(Item.class)  
7.             .createCriteria("seller")  
8.             .add(Restrictions.like("email", "%@"));  

 

2、    分配别名：

Java代码  

1. session.createCriteria(Item.class)  
2.             .createAlias("bids","b")  
3.             .add(Restrictions.like("description", "%Foo%"))  
4.             .add(Restrictions.gt("b.amount", new BigDecimal(100)));  
5.   
6. session.createCriteria(Item.class)  
7.             .createAlias("seller", "s")  
8.             .add(Restrictions.like("s.email","%@"));  

 

(b)    抓取关联

Java代码  

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

 

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

Java代码 

1. session.createCriteria(Item.class)  
2.         .add(Restrictions.gt("endDate", new Date()))  
3.         .setProjection(Projections.id());//返回单一属性  
4.   
5. session.createCriteria(Item.class).setProjection(  
6. Projections.projectionList().add(Projections.id()).  
7. add(Projections.property("description")));//返回一个Object[]  

 

(b)    统计分组

Java代码 

1. session.createCriteria(Item.class)  
2. .setProjection(Projections.rowCount());  
3.   
4. session.createCriteria(Item.class)  
5.             .setProjection(Projections.projectionList()  
6.             .add(Projections.rowCount())  
7.             .add(Projections.sum("sales"))  
8.             .add(Projections.avg("score"))  
9.             );  
10.   
11.     session.createCriteria(Bid.class)  
12.         .createAlias("bidder", "u")  
13.         .setProjection(Projections.projectionList()  
14.                 .add(Property.forName("u.id").group())  
15.                 .add(Property.forName("u.username").group())  
16.                 .add(Property.forName("id").count())  
17.                 .add(Property.forName("amount").avg())  
18.             );  

 

(c)    SQL投影

Java代码 

1. String sqlFragment = "(select count(*) from Item i where i.item_id = item_id) "  
2.                 + " as numofitems";  
3.         session.createCriteria(Bid.class).createAlias("bidder", "u")  
4.                 .setProjection(  
5.                         Projections.projectionList().add(  
6.                                 Projections.groupProperty("u.id")).add(  
7.                                 Projections.groupProperty("u.username")).add(  
8.                                 Projections.count("id")).add(  
9.                                 Projections.avg("amount")).add(  
10.                                 Projections.sqlProjection(sqlFragment,  
11.                                         new String[] { "numofitems" },  
12.                                         new Type[] { Hibernate.LONG }))  
13.   
14.                 );