CODEWITHSUNDEEP
javahibernatehibernate-criteria
themaster
themaster

Reputation: 473

Writing distinct in hibernate criteria

I want to write the below query using criteria .

I need to find the distinct rows and also use the current date in where clause .How can I achieve this in Criteria. 

SELECT DISTINCT *
FROM EMAIL_PROGRAM
WHERE CURRENT_DATE >=PGM_START_DT
AND CURRENT_DATE   <= PGM_END_DT
AND EMAIL_PGM_FLG     ='Y'
AND EMAIL_PGM_DESC  IS NOT NULL
and RGN_CD = 'US';

Below is my code in which I need to apply .

SessionFactory factory = null;
    Session session = null;
    try {
        factory = getSessionFactory();
        session = factory.openSession();

        final Criteria criteria = session
                .createCriteria(EmailDataBean.class);
        returnList = criteria.list();
    } catch (Exception e) {
        logger.error(e.getMessage());
        throw new DAOException(e);
    } finally {
        DBUtil.close(factory, session);
    }
    if (logger.isInfoEnabled()) {
        logger.info(LOG_METHOD_EXIT);
    }
    return returnList;
}

Upvotes: 1

Views: 3371

Answers (2)

dsp_user
dsp_user

Reputation: 2119

You should do something like

//first add conditions in the where clause (you should use property names as defined in your Hbernate entities , not column names in your DB)
criteria.add(Restrictions.ge("PGM_START_DT", startDt));
criteria.add(Restrictions.le("PGM_END_DT", endDt));
criteria.add(Restrictions.eq("EMAIL_PGM_FLG", "Y"));
criteria.add(Restrictions.isNotNull("EMAIL_PGM_DESC"));
criteria.add(Restrictions.eq("RGN_CD", "US"));

Now, add every column (i.e. a Hibernate entity property/field) to a Projection list (this is needed to support distinct in the query)

ProjectionList pList = Projections.projectionList();
pList.add(Projections.property("PGM_START_DT"), "PGM_START_DT");
pList.add(Projections.property("PGM_END_DT"), "PGM_END_DT");
// add all the other properties (columns) and then have the Projections.distinct method act on the entire row (all the columns)
criteria.setProjection(Projections.distinct(pList));

By default, using Projections does return the result as a List<Object[]> rather than List<EmailDataBean>, which is usually not convenient. To remedy that, you should set a ResultTransformer

crit.setResultTransformer(Transformers.aliasToBean(EmailDataBean.class));

Alternatively, instead of using Projections, you can use 

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

but this will not fetch distinct rows from the database but rather have Hibernate filter the results (removing the duplicates).

Upvotes: 0

Pushpendra Pal
Pushpendra Pal

Reputation: 640

you can use below on your criteria object.

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Upvotes: 1

Related Questions