hibernate使用DatachedCriteria分页关联查询以及去除重复记录

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

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

Commodity类：

Java代码

1. @Entity   
2. @Table (name= "COMMODITY" )   
3. public   class  Commodity  implements  java.io.Serializable {   
4.        
5.      /**  
6.      * 商品ID  
7.      */   
8.      @Id   @GeneratedValue (strategy=GenerationType.IDENTITY)   
9.      @Column (name =  "COMMODITY_ID" , unique =  true , nullable =  false )   
10.      private  Integer commodityId;   
11.      /**  
12.      * 商品名称  
13.      */   
14.      @Column (name =  "COMMODITY_NAME" , length= 50 , nullable =  false )   
15.      private  String commodityName;   
16.   
17.      public  Integer getCommodityId() {   
18.          return   this .commodityId;   
19.     }   
20.   
21.      public   void  setCommodityId(Integer commodityId) {   
22.          this .commodityId = commodityId;   
23.     }   
24.      public  String getCommodityName() {   
25.          return   this .commodityName;   
26.     }   
27.   
28.      public   void  setCommodityName(String commodityName) {   
29.          this .commodityName = commodityName;   
30.     }   
31. }  

@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代码

1. @Entity   
2.      @Table (name= "ORDER_COMMODITY_LINK" )   
3.      public   class  OrderCommodityLink  implements  java.io.Serializable {   
4.          @ManyToOne (fetch=FetchType.EAGER)   
5.          @JoinColumn (name= "ORDER_ID" , nullable= false )   
6.          private  Order order;   
7.            
8.          @ManyToOne (fetch=FetchType.EAGER)   
9.          @JoinColumn (name= "COMMODITY_ID" , nullable= false )   
10.          private  Commodity commodity;   
11.            
12.          public  Order getOrder() {   
13.              return  order;   
14.         }   
15.   
16.          public   void  setOrder(Order order) {   
17.              this .order = order;   
18.         }   
19.            
20.          public  Commodity getCommodity() {   
21.              return  commodity;       
22.         }       
23.            
24.          public   void  setCommodity(Commodity commodity) {   
25.              this .commodity = commodity;        
26.         }   
27.     }  

@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代码

1. @Entity   
2. @Table (name= "ORDERINFO" )   
3. public   class  Order  implements  java.io.Serializable {   
4.      /**  
5.      * 主键ID  
6.      */   
7.      @Id   @GeneratedValue (strategy=GenerationType.IDENTITY)   
8.      @Column (name= "ORDER_ID" , nullable= false )   
9.      private  Integer orderId;   
10.      /**  
11.      * 订单创建时间  
12.      */   
13.      @Column (name= "ORDER_CREATE_TIME" )   
14.      private  Date orderCreateTime;   
15.      @OneToMany (mappedBy= "order" ,cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)   
16.      @JoinTable (name= "order_commodity_link" ,joinColumns={ @JoinColumn (name= "order_id" )},inverseJoinColumns= @JoinColumn (name= "order_id" ))   
17.      private  Set<OrderCommodityLink> orderCommoditys;   
18.      public  Integer getOrderId() {   
19.          return   this .orderId;   
20.     }   
21.   
22.      public   void  setOrderId(Integer orderId) {   
23.          this .orderId = orderId;   
24.     }   
25.      public  Date getOrderCreateTime() {   
26.          return   this .orderCreateTime;   
27.     }   
28.   
29.      public   void  setOrderCreateTime(Date orderCreateTime) {   
30.          this .orderCreateTime = orderCreateTime;   
31.     }   
32.      public  Set<OrderCommodityLink> getOrderCommoditys() {   
33.          return  orderCommoditys;   
34.     }   
35.   
36.      public   void  setOrderCommoditys(Set<OrderCommodityLink> orderCommoditys) {   
37.          this .orderCommoditys = orderCommoditys;   
38.     }   
39. }  

@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代码

1. DetachedCriteria dc = DetachedCriteria.forClass(Order. class );   
2. dc.createAlias( "orderCommoditys" ,  "ocl" );   
3.                 dc.createAlias( "ocl.commodity" ,  "com" );   
4.                 dc.add(Restrictions.like( "com.commodityName" ,commodityName,MatchMode.ANYWHERE)); //关联查询；   
5.   
6. dc.add(Restrictions.ge( "orderCreateTime" ,startTime));   
7. dc.add(Restrictions.le( "orderCreateTime" ,endTime));   
8.   
9. 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代码

1. public  List findPage( final  DetachedCriteria dc,  final  Page page) {   
2.         Criteria criteria = dc.getExecutableCriteria(getSession());   
3.         Integer totalCount = (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult(); //记录总数   
4.         page.setRowCount(totalCount);   
5.         criteria.setProjection( null );   
6.            
7.         dc.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);  //只返回根对象，不加此限制结果将返回数组，包含根对象与关联对象   
8.         criteria.setFirstResult(page.getFirst() -  1 );   
9.         criteria.setMaxResults(page.getPageSize());   
10.          return  criteria.list();   
11.     }  

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的查询语句。