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 }))   
  
                );