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, Jul 27 '13 at 03:10
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, Jul 27 '13 at 03:12
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, Jul 27 '13 at 03:14
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, Aug 09 '13 at 14:01
@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, Aug 09 '13 at 16:06
@Thomas, the query sql it produces looks fine, and actually gives me the correct results. I will post the output shortly... — Wessel Oosthuizen, Aug 09 '13 at 16:08

score 0 · Answer 1 · answered Jul 27 '13 at 11:58

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 Answers1