CriteriaQuery JPA 2 issue with results

Question

I am using JPA 2 with Hibernate to construct a query that for the life of me wont run correctly. Query below:

public Integer getCountForDAR(Date _SD, Date _ED, Integer _PostId, String _Filter) {

        CriteriaBuilder cb = getEm().getCriteriaBuilder();
        CriteriaQuery cq = cb.createQuery(TcDarActivities.class);        
        Root fromTcDarActivities = cq.from(TcDarActivities.class);

        Path typePath = fromTcDarActivities.get(BaseDao.TC_DAR_ACTIVITY_TYPE).get(BaseDao.TYPE_NAME);
        Path postPath = fromTcDarActivities.get(BaseDao.TC_POST).get(BaseDao.POST_ID);

        cq.select(fromTcDarActivities).where(
                cb.and(cb.between(fromTcDarActivities.get(BaseDao.DARACTIVITY_TIME), _SD, _ED),
                cb.equal(postPath, _PostId),
                cb.or(
                cb.like(fromTcDarActivities.get(BaseDao.COMMENTS), _Filter),
                cb.like(typePath, _Filter)
                )));

        TypedQuery tq = getEm().createQuery(cq);
        LOGGER.info(tq.unwrap(org.hibernate.Query.class).getQueryString());

        try
        {
            List<TcDarActivities> list = getEm().createQuery(cq).setHint("javax.persistence.cache.retrieveMode", CacheRetrieveMode.BYPASS).getResultList();
            return list.size();
        }
        catch(Exception e) { return 0; }
    }

The TC_DAR_ACTIVITY_TYPE field could be null in the database. The problem is that the query only returns results if TC_DAR_ACTIVITY_TYPE and COMMENTS have values within the date range. Can someone please help here. I'm cracking my head on this because I'm fairly certain I'm doing it right...

Thanks Wessel

Perhaps describing what your query should do, and what your query is instead doing, might help? Can you show us the SQL it produces? When you run the SQL in a tool, what conditions can you remove/correct to give the correct results? — Thomas W
When you say "query only returns results if TC_DAR_ACTIVITY_TYPE and COMMENTS have values within the date range", how does ACTIVITY_TYPE have a date range? — Thomas W
Your code would be readable if you didn't have all the ugly prefixes on everything. ACTIVITY, COMMENT, ACTIVITY_TYPE would make readable code that you & other people could understand. Format your code -- criteria especially -- better, too. If it can't be read clearly it can't be understood clearly. First step in debugging, capiche? — Thomas W
Sorry guys I am new to stackoverflow and I thought I would be getting notified when anybody replies. I will go over this now.. — Wessel Oosthuizen
@Thomas, the first part of that query has a date range. So only if records within that date range have both values, will any records be returned. If no records have both values nothing returns, make sense? — Wessel Oosthuizen

Chris Chris · Accepted Answer · 2013-07-27T11:58:05

Type and post path are using inner joins required by the specification. You must specify the join and joinType.LEFT for any joins that you want to use left outer joins for.

FromTcDarActivities.join(BaseDao.TC_DAR_ACTIVITY_TYPE, JoinType.LEFT).get(BaseDao.TYPE_NAME);

CriteriaQuery JPA 2 issue with results

1 Answers