CODEWITHSUNDEEP
javasql-serverhibernate
Yantes
Yantes

Reputation: 251

Why is this Java query failing? returning 0 when there are results

Hi can anyone tell me why this java query is failing?

Query q = entityManager.createNativeQuery("SELECT m.* FROM MdmAudit m WHERE m.correlationID = :correlationId AND m.verb = :verb", MdmAuditDAO.class);
            //Query q = entityManager.createNamedQuery("MdmAuditDAO.GetData");
            q.setParameter("correlationId", resp.getHeader().getCorrelationID());
            q.setParameter("verb", resp.getHeader().getVerb());

            long result = (long) q.getFirstResult();

The namedQuery:

@NamedQuery( name="MdmAuditDAO.GetData", query="SELECT m FROM MdmAuditDAO m WHERE m.correlationId = :correlationId AND m.verb = :verb")
public class MdmAuditDAO implements Serializable {

I have getters and setter in my MdmAuditDAO class, and I have checked the naming of the variables, and they are identical as in the NamedQuery, so the problem does not lie there.

My problem is that I have three entries in my database, I should at least get one answer back but I get 0 in my result.

MdmAuditDAO is defined in my persistence.xml and in my ehcache.xml. So why is it that the result I get returned is 0? I have also tried to get an object returned or a list of objects, and it is the same result, nothing gets returned, but when I run my query in my mssql database I get results see picture below. It has nothing to do with the m.* I aslo get results when I use that in my SELECT statement. enter image description here

EDIT 1: This is what I get from my hibernate log, and I do not know how to read this?

Hibernate: 
    select
        mdmauditda0_.id as id1_7_,
        mdmauditda0_.correlationID as correlat2_7_,
        mdmauditda0_.messageID as messageI3_7_,
        mdmauditda0_.meter_no as meter_no4_7_,
        mdmauditda0_.noun as noun5_7_,
        mdmauditda0_.payload as payload6_7_,
        mdmauditda0_.source as source7_7_,
        mdmauditda0_.subtype as subtype8_7_,
        mdmauditda0_.time as time9_7_,
        mdmauditda0_.verb as verb10_7_ 
    from
        MdmAudit mdmauditda0_ 
    where
        mdmauditda0_.correlationID=?

Anything I have to set, to get more information? I am using the following jars enter image description here

And my java version is 1.7.0_79.

Upvotes: 0

Views: 777

Answers (1)

Yantes
Yantes

Reputation: 251

I found the solution http://www.objectdb.com/api/java/jpa/Query/getFirstResult returns the position of the first element, but I was a bit confused by the phrase

Returns 0 if setFirstResult was not applied to the query object.

Could not get my head around it to make any sense of it.

My solution now is that I just return a list of objects

Query q = entityManager.createNativeQuery("SELECT m.* FROM MdmAudit m WHERE m.correlationId = :correlationId AND verb = :verb", MdmAuditDAO.class);
            //Query q = entityManager.createNamedQuery("MdmAuditDAO.GetData");
            q.setParameter("correlationId", resp.getHeader().getCorrelationID());
            q.setParameter("verb", resp.getHeader().getVerb());
            List<MdmAuditDAO> mdmAuditList = q.getResultList();

And then it works fine and I get results. So instead of the the result == 0 check I am doing later in my code I just do a NULL and isEmpty() check instead().

Side note: I have not tried to delete entries and then see what the result would be then in the q.getFirstResult() call but that would be a possibility and see what i get returned and then check on that value, properbly null?

Upvotes: 1

Related Questions