DetachCriteria paginated query returns duplicate values

Question

I've got a paginated detached criteria execution which return rows of an entity. The entity class is as following.

@Entity
@Table(name="POS_T_HTL_ITEM_GROUP")
public class HotelItemGroup extends Versioned {

private static final long serialVersionUID = 1734461562543376947L;

@Id
@SequenceGenerator(name = "s_hotel_item_group", sequenceName = "POS_S_HTL_ITEM_GROUP")
@GeneratedValue(strategy = GenerationType.AUTO, generator = "s_hotel_item_group")
@Column(name = "HTL_ITEM_GROUP_ID")
private Long id;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "HOTEL_ID")
private Hotel hotel;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "ITM_GRP_ID")
private ItemGroup itemGroup;

@Transient
private String action;

/**
 * @return the id
 */
public Long getId() {
    return id;
}

/**
 * @param id the id to set
 */
public void setId(Long id) {
    this.id = id;
}

/**
 * @return the hotel
 */
public Hotel getHotel() {
    return hotel;
}

/**
 * @param hotel the hotel to set
 */
public void setHotel(Hotel hotel) {
    this.hotel = hotel;
}

/**
 * @return the itemGroup
 */
public ItemGroup getItemGroup() {
    return itemGroup;
}

/**
 * @param itemGroup the itemGroup to set
 */
public void setItemGroup(ItemGroup itemGroup) {
    this.itemGroup = itemGroup;
}

/**
 * @return the action
 */
public String getAction() {
    return action;
}

/**
 * @param action the action to set
 */
public void setAction(String action) {
    this.action = action;
}
}

This is the dao implementation which the detach criteria is executed and following is the method implementation.

@Override
public Page<HotelItemGroup> findHotelItemGroupsByCriteria(HotelItemGroupDTO searchCriteria,Page<HotelItemGroup> pg) {

    DetachedCriteria dc = DetachedCriteria.forClass(HotelItemGroup.class);

    dc.createAlias("hotel", "htl");
    dc.createAlias("itemGroup", "itm");
    dc.createAlias("itm.majorGroup", "majGrp");
    dc.addOrder(Order.asc("majGrp.majorGroupName"));


    if (searchCriteria.getHotelId() != null) {
        dc.add(Restrictions.eq("htl.id", searchCriteria.getHotelId()));
    }

    if (searchCriteria.getMajorGroupId() != null) {
        dc.add(Restrictions.eq("majGrp.id", searchCriteria.getMajorGroupId()));
    }

    if (searchCriteria.getItemGroupId() != null) {
        dc.add(Restrictions.eq("itm.id", searchCriteria.getItemGroupId()));
    }

     return executeCriteria(dc, pg);
}

The following is the abstract method implementation of executeCriteria method.

@SuppressWarnings("unchecked")
@Transactional(readOnly = true)
public <R extends Serializable> Page<R> executeCriteria(final DetachedCriteria dc, final Page<R> page) {

    return (Page<R>) getJpaTemplate().execute(new JpaCallback() {

        @Override
        public Object doInJpa(EntityManager em) throws PersistenceException {

            // Obtain Hibernate Session from EM
            Session session = PersistenceUtil.getSession(em);

            // Find Total & Update Page
            dc.setProjection(Projections.rowCount());
            Criteria ctrTotal = dc.getExecutableCriteria(session);
            page.setTotalRecords(((Number) ctrTotal.uniqueResult()).longValue());

            // Reset Criteria
            dc.setProjection(null);
            dc.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);

            // Perform Search & Update Page
            Criteria ctrQuery = dc.getExecutableCriteria(session);
            ctrQuery.setFirstResult(page.getStartPosition());
            if (page.getPageSize() > 0) {
                ctrQuery.setMaxResults(page.getPageSize());
            }
            page.setPageData(ctrQuery.list());

            return page;
        }
    });
}

The problem is, I paginate the result set for example 15 objects per query. in the initial loading I request the first page, and if user requests the second page, it returns a set of results which contains duplicate records which were there in the first page. what am I doing wrong here? Page is a class which basically consists of the following attributes.

private long totalRecords = 0;
private int startPosition = 0;
private int pageSize = DEFAULT_PAGE_SIZE;
private Collection<T> pageData = null;

could any one please help me out with this issue.

Answer 1

I am not 100% familiar with the Criteria API, but changing dc.setResultTransformer(CriteriaSpecification.ROOT_ENTITY); to dc.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

might be sufficient for your results to be condensed.

I expect the issue here is that you have a relationship with multiplicity for which you are creating an alias. I expect this will result in an explicit join being performed in the executed query.

If the suggestion above has no effect the best self-debugging method is to enable the logging of the executed queries and run them against the DB manually. The cause should quickly become obvious.