Hibernate使用DatachedCriteria分页关联查询以及去除重复

cdh111007 贡献于2010-09-08

作者 jing  创建于2009-12-13 14:42:00   修改者jing  修改于2010-04-26 14:55:00字数6784

文档摘要:Hibernate使用DatachedCriteria分页关联查询以及去除重复,近来项目中用到DatachedCriteria做分页,涉及到关联查询产生的重复记录问题。
关键词:

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 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 getOrderCommoditys() {             return  orderCommoditys;        }            public   void  setOrderCommoditys(Set 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",casca de = {CascadeType.ALL}, fetch = FetchType.EAGER)  @JoinTable(name="order_commodity_link",joinColumns={@JoinColumn(name="order_id")},inverseJoinColumns=@JoinColumn(name="order_id"))  private Set 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 getOrderCommoditys() {   return orderCommoditys;  }  public void setOrderCommoditys(Set orderCommoditys) {   this.orderCommoditys = orderCommoditys;  } }根据订单中商品条目名称和订单创建时间进行查询每页显示5条订单,每条订单下嵌套显示其包含的商品条目。 service层封装了查询条件: Java代码  DetachedCriteria dc = DetachedCriteria.forClass(Order. class );    dc.createAlias( "orderCommoditys" ,  "ocl" );                    dc.createAlias( "ocl.commodity" ,  "com" );                    dc.add(Restric tions.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条对orderCommoditys的查询语句。

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 20 金币 [ 分享文档获得金币 ] 1 人已下载

下载文档