Hibernate QBC 语言

最新推荐文章于 2024-05-25 12:01:51 发布

weixin_33828101

最新推荐文章于 2024-05-25 12:01:51 发布

阅读量55

点赞数

文章标签： java

原文链接：https://my.oschina.net/boonya/blog/391617

版权

为什么80%的码农都做不了架构师？>>>

基本查询

以下是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", "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属性大小

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

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

转载于:https://my.oschina.net/boonya/blog/391617