Results 1 to 14 of 14
Like Tree1Likes
  • 1 Post By gimbal2

Thread: Hibernate returns list of nulls although executed SQL returns values

  1. #1
    mve
    mve is offline Member
    Join Date
    Jul 2014
    Posts
    8
    Rep Power
    0

    Question Hibernate returns list of nulls although executed SQL returns values

    Hi everyone,

    I have a problem with hibernate (I also asked this on SO). I'm using hibernate as an ORMapper and I want to execute an actually rather simple hql query:

    SQL Code:
    SELECT a 
    FROM Foo a 
    WHERE a.status = :A0status 
    ORDER BY a.bookingTypeCode ASC, 
             a.priority ASC
    This hql query is then converted into a sql query which looks something like this:

    SQL Code:
    select a.* 
    from Foo a 
    where a.status='A' 
    order by a.bookingtypecode ASC, 
             a.priority ASC
    When I execute the sql on the oracle database using the Oracle SQL Developer I get 17 rows returned. However, when I execute the hql query (using the list method of a Query I get a list of 17 elements that are all null. Although the number of elements is correct, not a single one of the elements is actually loaded.

    This is the way I create and execute my query:

    Java Code:
    // the hql query is stored in the hqlQuery variable;
    // the parameter are stored in a Map<String, Object> called params
    Query hQuery = hibSession.createQuery(hqlQuery);
    for (Entry<String, Object> param : params.entrySet()) {
        String key = param.getKey();
        Object value = param.getValue();
        hQuery.setParameter(key, value);
    }
    
    List<?> result = hQuery.list();
    The result I'm getting after executing query.list();

    I've recently upgrade from hibernate 3.2 to 4.3.5. Before the upgrade everything worked fine. After the upgrade I get this error. Also this error seems to only occur with one entity (all ~200 other entities work correctly).

    Does anyone know what might be the problem here?
    Last edited by mve; 07-10-2014 at 10:45 AM.

  2. #2
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,084
    Rep Power
    6

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    As an experiment, do an in-between upgrade; try with 4.2.14 too. Does the problem happen then too? Compare the SQL that Hibernate generates in the working version and the version where it does not work.

    Note: it is -never- a good idea to "simplify" information when you do a problem description; it is the act of turning the truth into a lie even if it is done with good intentions. Post the exact SQL that Hibernate generates.
    Tolls likes this.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  3. #3
    mve
    mve is offline Member
    Join Date
    Jul 2014
    Posts
    8
    Rep Power
    0

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    Thanks gimbal2 for your answer.

    Quote Originally Posted by gimbal2 View Post
    Compare the SQL that Hibernate generates in the working version and the version where it does not work.
    When I execute the generated sql in the Oracle SQL Developer I get the correct result. So the sql should not be the problem.

    Quote Originally Posted by gimbal2 View Post
    Note: it is -never- a good idea to "simplify" information when you do a problem description; it is the act of turning the truth into a lie even if it is done with good intentions. Post the exact SQL that Hibernate generates.
    I'm aware of that. However, I just replaced the columns of the select with a * and changed the table name and the alias because this shouldn't matter for the actual problem.

  4. #4
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,084
    Rep Power
    6

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    Oh okay then, you seem to know what you're doing. Good luck finding the issue!
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  5. #5
    mve
    mve is offline Member
    Join Date
    Jul 2014
    Posts
    8
    Rep Power
    0

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    Do you know if there were some changes in hibernate that may cause similar problems?

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,090
    Rep Power
    20

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    As gimbal suggests, try rolling back a version.
    I'm using 4.3.4 without any problems (with a number of HQL queries), so see if that one works.

    Other than that, is there anything potentially "odd" about Foo?
    Some strange id perhaps?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,084
    Rep Power
    6

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    Quote Originally Posted by mve View Post
    Do you know if there were some changes in hibernate that may cause similar problems?
    What is stopping you from checking the jira changes of the versions? I mean you don't seem to want to listen to me, so go inform yourself then.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  8. #8
    mve
    mve is offline Member
    Join Date
    Jul 2014
    Posts
    8
    Rep Power
    0

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    I'll just tried it with hibernate 4.3.0.Final and got the same result. I'll check the entity again, maybe I overlooked something. Thanks for the tips, though.

  9. #9
    mve
    mve is offline Member
    Join Date
    Jul 2014
    Posts
    8
    Rep Power
    0

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    gimbal2, sorry, I didn't want to piss you off.

    this is my hql query:
    SQL Code:
    SELECT JIFGLACCOUNTRULE 
    FROM JIFGLAccountRule JIFGLACCOUNTRULE 
    WHERE JIFGLACCOUNTRULE.status = :JIFGLACCOUNTRULE0status  
    ORDER BY JIFGLACCOUNTRULE.bookingTypeCode ASC, FIFGLACCOUNTRULE.priority ASC
    this is the generated sql query:
    SQL Code:
    SELECT jifglaccou0_.GID        AS GID1_10_,
      jifglaccou0_.treeid          AS treeid2_10_,
      jifglaccou0_.addtxt1         AS addtxt3_10_,
      jifglaccou0_.addtxt2         AS addtxt4_10_,
      jifglaccou0_.addtxt3         AS addtxt5_10_,
      jifglaccou0_.bookingtypecode AS bookingtypecode6_10_,
      jifglaccou0_.creditacid      AS creditacid7_10_,
      jifglaccou0_.creditactype    AS creditactype8_10_,
      jifglaccou0_.debitacid       AS debitacid9_10_,
      jifglaccou0_.debitactype     AS debitactype10_10_,
      jifglaccou0_.insstmp         AS insstmp11_10_,
      jifglaccou0_.insusrid        AS insusrid12_10_,
      jifglaccou0_.featid          AS featid13_10_,
      jifglaccou0_.mgid            AS mgid14_10_,
      jifglaccou0_.matid           AS matid15_10_,
      jifglaccou0_.mvtypeid        AS mvtypeid16_10_,
      jifglaccou0_.pmid            AS pmid17_10_,
      jifglaccou0_.pmtype          AS pmtype18_10_,
      jifglaccou0_.plid            AS plid19_10_,
      jifglaccou0_.pltypeid        AS pltypeid20_10_,
      jifglaccou0_.priority        AS priority21_10_,
      jifglaccou0_.rdctid          AS rdctid22_10_,
      jifglaccou0_.status          AS status23_10_,
      jifglaccou0_.tgid            AS tgid24_10_,
      jifglaccou0_.txacid          AS txacid25_10_,
      jifglaccou0_.updstmp         AS updstmp26_10_,
      jifglaccou0_.updusrid        AS updusrid27_10_,
      jifglaccou0_.wgid            AS wgid28_10_
    FROM JIF_GL_ACCOUNTRULE jifglaccou0_
    WHERE jifglaccou0_.status='A'
    ORDER BY jifglaccou0_.bookingtypecode ASC,
      jifglaccou0_.priority ASC
    When I execute the sql in the Oracle SQL Developer I get 17 restults:
    Hibernate returns list of nulls although executed SQL returns values-db.png

    This is how I create my query:
    Java Code:
    Session hibSession = ((IHibernateDataProviderSession) tmpSession).getWrappedHibernateSession();
    
    SessionImplementor sessionImpl = (SessionImplementor) hibSession;
    Dialect dialect = sessionImpl.getFactory().getDialect();
    
    try {
        Query hQuery = hibSession.createQuery(hqlQuery);
    
        hibSession.clear();
    
        for (Entry<String, Object> param : params.entrySet()) {
            String key = param.getKey();
            Object value = param.getValue();
            if (value != null && value.getClass().isArray()) {
                hQuery.setParameterList(key, (Object[]) value);
            } else if (value instanceof Collection) {
                hQuery.setParameterList(key, (Collection<?>) value);
            } else { // only this else is called
                hQuery.setParameter(key, value);
            }
        }
    
        LOGGER.info("DATABASE: FOR SESSION (aliases to lock: " + aliasesToLockStr + "): "
                          + hibSession.hashCode() + hQuery.getQueryString());
        List<?> result = hQuery.list();
        ...
    As the result of the list method I get this
    Hibernate returns list of nulls although executed SQL returns values-list.png

  10. #10
    mve
    mve is offline Member
    Join Date
    Jul 2014
    Posts
    8
    Rep Power
    0

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    I've just set the log level to TRACE and got the following console output:

    2014-07-10 13:38:56,422 <main> [TRACE] org.hibernate.loader.Loader.processResultSet(Loade r.java:946) - Processing result set
    2014-07-10 13:38:56,423 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 0
    2014-07-10 13:38:56,424 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,425 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 1
    2014-07-10 13:38:56,426 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,427 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 2
    2014-07-10 13:38:56,429 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,430 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 3
    2014-07-10 13:38:56,431 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,432 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 4
    2014-07-10 13:38:56,433 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,434 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 5
    2014-07-10 13:38:56,436 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,437 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 6
    2014-07-10 13:38:56,438 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,439 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 7
    2014-07-10 13:38:56,441 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,442 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 8
    2014-07-10 13:38:56,443 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,444 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 9
    2014-07-10 13:38:56,445 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,450 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 10
    2014-07-10 13:38:56,452 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,453 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 11
    2014-07-10 13:38:56,455 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,456 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 12
    2014-07-10 13:38:56,457 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,458 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 13
    2014-07-10 13:38:56,459 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,461 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 14
    2014-07-10 13:38:56,462 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,463 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 15
    2014-07-10 13:38:56,464 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,465 <main> [DEBUG] org.hibernate.loader.Loader.processResultSet(Loade r.java:951) - Result set row: 16
    2014-07-10 13:38:56,467 <main> [DEBUG] org.hibernate.loader.Loader.getRow(Loader.java:148 5) - Result row: null
    2014-07-10 13:38:56,468 <main> [TRACE] org.hibernate.loader.Loader.processResultSet(Loade r.java:970) - Done processing result set (17 rows)


    Any ideas why the result row is always null?

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,090
    Rep Power
    20

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    Have you changed anything else other than Hibernate?
    Oracle versions, or the JDBC driver?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  12. #12
    mve
    mve is offline Member
    Join Date
    Jul 2014
    Posts
    8
    Rep Power
    0

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    Thanks everyone for your help! I found the problem. It was actually a mapping/logic/database error. The primary key consisted of two columns (according to the entity class) and one of these columns was nullable. However a primary key can never be nullable. Therefore hibernate always returned null.

  13. #13
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,090
    Rep Power
    20

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    Which is why I asked for the entity class...

    And I possibly would then have asked for the table DDL.

    But hey ho.

    ETA: In fact -
    Quote Originally Posted by Tolls View Post
    Other than that, is there anything potentially "odd" about Foo?
    Some strange id perhaps?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  14. #14
    mve
    mve is offline Member
    Join Date
    Jul 2014
    Posts
    8
    Rep Power
    0

    Default Re: Hibernate returns list of nulls although executed SQL returns values

    Hi, I understand why you wanted to see the entity class. We use a selfmade mechanism of how entity classes are mapped (it was implemented some years ago and got larger and more complicated since then). Therefore I didn't post the implementation of the entity class.

Similar Threads

  1. My "For" loop returns empty list
    By Bojan in forum New To Java
    Replies: 17
    Last Post: 02-19-2013, 04:24 PM
  2. Hashmapping: Have values, but returns key isn't there
    By Toxicgas1 in forum Advanced Java
    Replies: 14
    Last Post: 08-15-2012, 05:48 AM
  3. Replies: 4
    Last Post: 02-16-2011, 04:31 AM
  4. Average returns 0
    By jehduncs in forum New To Java
    Replies: 2
    Last Post: 12-03-2010, 06:29 PM
  5. What's the need for co-variant returns ?
    By ajeeb in forum New To Java
    Replies: 0
    Last Post: 03-23-2009, 09:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •