Hibernate simple select query doesn't return anything

Question

I got this simple Hibernate query set up but it returns nothing, here is my code:

      EntityManagerFactory emf = javax.persistence.Persistence.createEntityManagerFactory("timereg");
      EntityManager em = emf.createEntityManager();

     int id = em.createQuery("SELECT emp.id FROM Employee as emp WHERE emp.bsn = '398723916'").getFirstResult();
     object.getEmployee().setId(id);
     System.out.println("query returns employee id: " + id);

The stupid thing is that id stays zero but when i execute this query in PostgreSQL it returns 37.

I think hibernate does not like my way of implementing a select query, does anyone know what is wrong with my select query ?

THE ANSWER: There was nothing wrong with the select query i just had to use getSingeResult() instead of getFirstResult(); Change the code into:

EntityManagerFactory emf = javax.persistence.Persistence.createEntityManagerFactory("timereg");
      EntityManager em = emf.createEntityManager();

     Object ob = em.createQuery("select id from Employee where bsn = '398723917'").getSingleResult();

        object.getEmployee().setId(Integer.parseInt(ob.toString()));
        System.out.println(ob);

This is the total solution for my problem, but i got inspired by Yanflea so he deserves all the credits.

Maybe [this question](http://stackoverflow.com/questions/2110809/use-of-entitymanager-createnativequeryquery-foo-class) can help. i.e. try to use `BigDecimal` instead of `long` — hage, Feb 24 '12 at 09:17
thanks but i have already dropped the table and let it generate again with normal integers instead of long(Java)/bigint(SQL language). So the bigint in the table changed to int and the id in the employee class also changed to int. ps. i updated my code — Ben, Feb 24 '12 at 09:48
can you turn "Hibernate.SQL" in log4j on to see which sql is executed? — Firo, Feb 24 '12 at 09:59
Hi @Firo and how do i do that ? (I'm new to Hibernate), when i do a local machine search on the term log4j i get loads of results... which logfile/settingfile should i pick ? — Ben, Feb 24 '12 at 10:11
I'm not familiar with JPA, maybe these can help: https://forum.hibernate.org/viewtopic.php?f=1&t=984480 http://publib.boulder.ibm.com/infocenter/wasinfo/v7r0/index.jsp?topic=%2Fcom.ibm.websphere.express.doc%2Finfo%2Fexp%2Fae%2Ftejb_loggingwjpa.html — Firo, Feb 24 '12 at 10:31
Thanks i found a way to check the database logs and it these select queries do get executed due to the fact that this will come into the logfile of the database: 2012-02-24 12:04:37 CET LOG: unexpected EOF on client connection — Ben, Feb 24 '12 at 11:06
If you replace getFirstResult() by list() does it return anything useful? — Andre, Feb 24 '12 at 11:34
I already tried that and it did not work :( thanks for you feedback though. — Ben, Feb 24 '12 at 11:45
How can such a simple problem be such a pain in the ass..... :( — Ben, Feb 24 '12 at 11:48
Have you tried using plain sql instead of hql? em.createSqlQuery(...) See if that works so we can try to isolate the problem — Andre, Feb 24 '12 at 12:36
FYI - to turn logging on in log4j just add this: log4j.logger.org.hibernate.SQL=DEBUG — ndtreviv, Feb 24 '12 at 12:56
@Andre ye i have tried to use createNativeQuery("select id from tbl_employee where bsn Like 'bsnnumber'") but that also did not work — Ben, Feb 24 '12 at 13:01
@ndtreviv i have never heard of it so i dont think so, but i have already found the solution thanks. — Ben, Feb 24 '12 at 13:48

Yanflea · Accepted Answer · 2012-02-24T14:03:51.513

3

You are using the method getFirstResult(), which gives you the position of the record in the table. You should use getSingleResult() instead. See http://docs.oracle.com/javaee/6/api/javax/persistence/Query.html.

EDIT

Here it is :

Object ob = em.createQuery("select id from Employee where bsn = '398723917'").getSingleResult();

edited Feb 24 '12 at 14:03

answered Feb 24 '12 at 13:09

Yanflea

3,876
1
14
14

getSingeResult() returns an object not a value. – Ben Feb 24 '12 at 13:11
You will have to cast it into the object you're expecting (BigInt or Integer, depending on your setup). Did you inspect it at all to see what object it returned? – Andre Feb 24 '12 at 13:15
In your other post -where you give your code-, id for the Employee class is a Long... So just cast the result into a Long... – Yanflea Feb 24 '12 at 13:24
ye that was old, i changed it to int. thanks for the notification iĺl edit the old post – Ben Feb 24 '12 at 13:25
@Yanflea Object test = em.createQuery("From Employee where bsn = '398723917'").getSingleResult(); System.out.print(test.toString()); returns: com.jmonks.timereg.domain.Employee@670eab4 – Ben Feb 24 '12 at 13:33
You must redefine .toString() in the Employee class if you want it to return your id property value. Otherwise it will just return the reference of the object. – Yanflea Feb 24 '12 at 13:39
Change your answer to: Object ob = em.createQuery("select id from Employee where bsn = '398723917'").getSingleResult(); then i will confirm it :D – Ben Feb 24 '12 at 13:41
Not sure to understand what you mean, but I guess you managed to make it work like you wanted to... :) – Yanflea Feb 24 '12 at 13:48
@Yanflea i will post the total answer in about 3 hours when i am allowed to answer on my own questions, then you have to copy that answer and edit it into yours then i will apply your answer so you get the credits.... I have also put the total answer on the bottom of the question. – Ben Feb 24 '12 at 13:58
Moved your final -and working- attempt in my answer, as you asked... :) The customer is king :). – Yanflea Feb 24 '12 at 14:06

Hibernate simple select query doesn't return anything

1 Answers1