[转]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条
