how to retrieve distinct root entity row count in hiberntae?

Question

Hi,

I have to show Employee and his Project in dropdown to employee in the right ride of tabular format. In the top i need to show number of records. So, I am doing two queries, one for retrieving count and another for retrieving employee and his project.finally, the total count comes employee*project count.

If an employee has 3 projects then it counts to 3. But I need only employee count. And I am retriving distinct Employee object, that employee object has list of Project.

I used .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY) to get like this.

Please help me to get only employee count instead of employee*project, I am struggling with this.

My COde is,

public Collection fetchEmployeeWithProject(final List condition,
            final PaginationCriteria pageCriteria)
            throws DataAccessLayerException {
                 Session session = this.getPersManager().getCurrentSession();

        Criteria criteria = session.createCriteria(Employee.class, "employee")
                .createAlias(
                        "employee.empProject", "empProject",
                        CriteriaSpecification.LEFT_JOIN).createAlias(
                        "empProject.Project", "project",
                        CriteriaSpecification.LEFT_JOIN);
        criteria = this.addMultipleSeachCriteria(criteria, condition);
        this.buildPaginatedCriteria(criteria, pageCriteria);
        List lst = criteria.list();
        session.clear();
    return lst;
    }

protected Criteria buildPaginatedCriteria(Criteria criteria,
            PaginationCriteria pageCriteria) throws DataAccessLayerException {

        logger.debug(LOG_PREFIX + "buildPaginatedCriteria::Begin");

        if (pageCriteria != null) {

            if (!pageCriteria.isFetchAll()) {

                if (pageCriteria.getTotalRecords() == 0)
                    pageCriteria.setTotalRecords(((Integer) criteria
                            .setProjection(Projections.rowCount())
                            .uniqueResult()).intValue());
                criteria.setProjection(null);
                criteria.setFirstResult(
                        pageCriteria.getFirstRecordOfCurrentPage())
                        .setMaxResults(pageCriteria.getRecordsPerPage());
            }

            if (StringUtils.isNotBlank(pageCriteria.getSortBy()))
                criteria.addOrder(pageCriteria.isSortDescending() ? Order
                        .desc(pageCriteria.getSortBy()) : Order
                        .asc(pageCriteria.getSortBy()));

            if (StringUtils.isNotBlank(pageCriteria.getSecondarySortBy())) {
                criteria.addOrder(Order.asc(pageCriteria.getSecondarySortBy()));
            }

            if (pageCriteria.isCached()) {
                criteria.setCacheable(true).setCacheMode(CacheMode.NORMAL);
            }
        }
        criteria
                .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

        logger.debug(LOG_PREFIX + "buildPaginatedCriteria::End");

        return criteria;
    }

These are the methods that i am using.

Answer 1

There is a problem in Hibernate when doing joins and trying to count. The problem with DISTINCT_ROOT_ENTITY it's that it removes equal rows, but since you did a join, some rows have different fields although it is the same entity.

If you want to count and do joins at the same time, put all the objects retrieved into a Set. This way the duplicates will go away. Then you can do Set#size to count.

Or you can use HQL and write your query manually.

Maybe if you edit your question and add the actual queries that you are creating we can help you further.

Update

Add this after: List lst = criteria.list();

Set<Employee> employeeSet = new HashSet<Employee>();
employeeSet.addAll(lst);
return employeeSet;