[转]hibernate使用DatachedCriteria分页关联查询以及去除重复

近来项目中用到DatachedCriteria做分页，涉及到关联查询产生的重复记录问题。

主要有三张表相关联，Commodity，OrderCommodityLink，Order 这里采用注解的方式进行对象与表的映射配置：

Commodity类：

Java代码  
@Entity    
@Table (name= "COMMODITY" )    
public   class  Commodity  implements  java.io.Serializable {    
        
     /**   
     * 商品ID   
     */    
     @Id   @GeneratedValue (strategy=GenerationType.IDENTITY)    
     @Column (name =  "COMMODITY_ID" , unique =  true , nullable =  false )    
     private  Integer commodityId;    
     /**   
     * 商品名称   
     */    
     @Column (name =  "COMMODITY_NAME" , length= 50 , nullable =  false )    
     private  String commodityName;    
   
     public  Integer getCommodityId() {    
         return   this .commodityId;    
    }    
   
     public   void  setCommodityId(Integer commodityId) {    
         this .commodityId = commodityId;    
    }    
     public  String getCommodityName() {    
         return   this .commodityName;    
    }    
   
     public   void  setCommodityName(String commodityName) {    
         this .commodityName = commodityName;    
    }    
}   
@Entity
@Table(name="COMMODITY")
public class Commodity implements java.io.Serializable {
 
 /**
  * 商品ID
  */
 @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
 @Column(name = "COMMODITY_ID", unique = true, nullable = false)
 private Integer commodityId;
 /**
  * 商品名称
  */
 @Column(name = "COMMODITY_NAME", length=50, nullable = false)
 private String commodityName;

 public Integer getCommodityId() {
  return this.commodityId;
 }

 public void setCommodityId(Integer commodityId) {
  this.commodityId = commodityId;
 }
 public String getCommodityName() {
  return this.commodityName;
 }

 public void setCommodityName(String commodityName) {
  this.commodityName = commodityName;
 }
}OrderCommodityLink 类：

Java代码  
@Entity    
     @Table (name= "ORDER_COMMODITY_LINK" )    
     public   class  OrderCommodityLink  implements  java.io.Serializable {    
         @ManyToOne (fetch=FetchType.EAGER)    
         @JoinColumn (name= "ORDER_ID" , nullable= false )    
         private  Order order;    
            
         @ManyToOne (fetch=FetchType.EAGER)    
         @JoinColumn (name= "COMMODITY_ID" , nullable= false )    
         private  Commodity commodity;    
            
         public  Order getOrder() {    
             return  order;    
        }    
   
         public   void  setOrder(Order order) {    
             this .order = order;    
        }    
            
         public  Commodity getCommodity() {    
             return  commodity;        
        }        
            
         public   void  setCommodity(Commodity commodity) {    
             this .commodity = commodity;         
        }    
    }   
@Entity
 @Table(name="ORDER_COMMODITY_LINK")
 public class OrderCommodityLink implements java.io.Serializable {
  @ManyToOne(fetch=FetchType.EAGER)
  @JoinColumn(name="ORDER_ID", nullable=false)
  private Order order;
  
  @ManyToOne(fetch=FetchType.EAGER)
  @JoinColumn(name="COMMODITY_ID", nullable=false)
  private Commodity commodity;
  
  public Order getOrder() {
   return order;
  }

  public void setOrder(Order order) {
   this.order = order;
  }
  
  public Commodity getCommodity() {
   return commodity;  
  }  
  
  public void setCommodity(Commodity commodity) {
   this.commodity = commodity;  
  }
 }Order类：

Java代码  
@Entity    
@Table (name= "ORDERINFO" )    
public   class  Order  implements  java.io.Serializable {    
     /**   
     * 主键ID   
     */    
     @Id   @GeneratedValue (strategy=GenerationType.IDENTITY)    
     @Column (name= "ORDER_ID" , nullable= false )    
     private  Integer orderId;    
     /**   
     * 订单创建时间   
     */    
     @Column (name= "ORDER_CREATE_TIME" )    
     private  Date orderCreateTime;    
     @OneToMany (mappedBy= "order" ,cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)    
     @JoinTable (name= "order_commodity_link" ,joinColumns={ @JoinColumn (name= "order_id" )},inverseJoinColumns= @JoinColumn (name= "order_id" ))    
     private  Set<OrderCommodityLink> orderCommoditys;    
     public  Integer getOrderId() {    
         return   this .orderId;    
    }    
   
     public   void  setOrderId(Integer orderId) {    
         this .orderId = orderId;    
    }    
     public  Date getOrderCreateTime() {    
         return   this .orderCreateTime;    
    }    
   
     public   void  setOrderCreateTime(Date orderCreateTime) {    
         this .orderCreateTime = orderCreateTime;    
    }    
     public  Set<OrderCommodityLink> getOrderCommoditys() {    
         return  orderCommoditys;    
    }    
   
     public   void  setOrderCommoditys(Set<OrderCommodityLink> orderCommoditys) {    
         this .orderCommoditys = orderCommoditys;    
    }    
}   
@Entity
@Table(name="ORDERINFO")
public class Order implements java.io.Serializable {
 /**
  * 主键ID
  */
 @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
 @Column(name="ORDER_ID", nullable=false)
 private Integer orderId;
 /**
  * 订单创建时间
  */
 @Column(name="ORDER_CREATE_TIME")
 private Date orderCreateTime;
 @OneToMany(mappedBy="order",cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)
 @JoinTable(name="order_commodity_link",joinColumns={@JoinColumn(name="order_id")},inverseJoinColumns=@JoinColumn(name="order_id"))
 private Set<OrderCommodityLink> orderCommoditys;
 public Integer getOrderId() {
  return this.orderId;
 }

 public void setOrderId(Integer orderId) {
  this.orderId = orderId;
 }
 public Date getOrderCreateTime() {
  return this.orderCreateTime;
 }

 public void setOrderCreateTime(Date orderCreateTime) {
  this.orderCreateTime = orderCreateTime;
 }
 public Set<OrderCommodityLink> getOrderCommoditys() {
  return orderCommoditys;
 }

 public void setOrderCommoditys(Set<OrderCommodityLink> orderCommoditys) {
  this.orderCommoditys = orderCommoditys;
 }
}根据订单中商品条目名称和订单创建时间进行查询每页显示5条订单，每条订单下嵌套显示其包含的商品条目。

service层封装了查询条件：

Java代码  
DetachedCriteria dc = DetachedCriteria.forClass(Order. class );    
dc.createAlias( "orderCommoditys" ,  "ocl" );    
                dc.createAlias( "ocl.commodity" ,  "com" );    
                dc.add(Restrictions.like( "com.commodityName" ,commodityName,MatchMode.ANYWHERE)); //关联查询；    
   
dc.add(Restrictions.ge( "orderCreateTime" ,startTime));    
dc.add(Restrictions.le( "orderCreateTime" ,endTime));    
   
List orderList = orderDAO.findPage(dc,page);                   
DetachedCriteria dc = DetachedCriteria.forClass(Order.class);
dc.createAlias("orderCommoditys", "ocl");
    dc.createAlias("ocl.commodity", "com");
    dc.add(Restrictions.like("com.commodityName",commodityName,MatchMode.ANYWHERE));//关联查询；

dc.add(Restrictions.ge("orderCreateTime",startTime));
dc.add(Restrictions.le("orderCreateTime",endTime));

List orderList = orderDAO.findPage(dc,page);    dao层：

Java代码  
public  List findPage( final  DetachedCriteria dc,  final  Page page) {    
        Criteria criteria = dc.getExecutableCriteria(getSession());    
        Integer totalCount = (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult(); //记录总数    
        page.setRowCount(totalCount);    
        criteria.setProjection( null );    
            
        dc.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);  //只返回根对象，不加此限制结果将返回数组，包含根对象与关联对象    
        criteria.setFirstResult(page.getFirst() -  1 );    
        criteria.setMaxResults(page.getPageSize());    
         return  criteria.list();    
    }   
public List findPage(final DetachedCriteria dc, final Page page) {
  Criteria criteria = dc.getExecutableCriteria(getSession());
  Integer totalCount = (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();//记录总数
  page.setRowCount(totalCount);
  criteria.setProjection(null);
  
  dc.setResultTransformer(CriteriaSpecification.ROOT_ENTITY); //只返回根对象，不加此限制结果将返回数组，包含根对象与关联对象
  criteria.setFirstResult(page.getFirst() - 1);
  criteria.setMaxResults(page.getPageSize());
  return criteria.list();
 } 初始查询结果中，页面上每个订单中有多少条商品，就会重复显示该订单多次，想到了去重，将

dc.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);改为dc.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY); 结果不重复了，但分页却有问题。本来是每页显示5条订单记录，现在第一页却显示了3条，3条订单中的商品条目记录加起来刚好是5条，原来分页对象不对，尽管去重了，但分页还是对去重前的结果集分页，CriteriaSpecification.DISTINCT_ROOT_ENTITY只是对查询后的结果进行过滤。

后来又发现建立关联时，order中的orderCommodityLink集合是级联的，也就是查order时，orderCommodityLink集合也会级联出来，所以造成的结果是left join出来的结果对于order来说肯定存在重复了。

所以在service代码中将DetachedCriteria dc = DetachedCriteria.forClass(Orderinfo.class) 改为：

DetachedCriteria dc = DetachedCriteria.forClass(Orderinfo.class).setFetchMode("orderCommoditys", FetchMode.SELECT);将关联的orderCommoditys集合对象采用查询方式抓取。

再次执行查询，每页正确显示5条订单记录，而不管订单中的商品条目数目了，只不过后台打印的sql语句多了5条