This discussion is archived
2 Replies Latest reply: Dec 7, 2012 1:04 PM by orafad RSS

Hibernate return empty values on persisted null values

576227 Newbie
Currently Being Moderated
Hi,

I have an Hibernate based DAO layer with lots of methods in there. The integration test suites work fine against MySql, H2 and HSQLDB but on Oracle I have to work around the fact the Hibernate returns null instead of empty "" for a non set value in the persistence store of Oracle.

I tried with specifying a not-null to true in the Hibernate mapping as in:
<property name="languageCode" type="string" not-null="true">
<column name="language_code" length="2" />
</property>
but it did not change anything and the issue remained.

The way I found to work laboriously around the issue is to explicitly set the values to empty if any are nulls.
     public List<NavmenuLanguage> findWithNavmenu(Navmenu navmenu) {
          String statement = "select id, version, language_code as languageCode from navmenu_language where navmenu_id = :navmenuId order by language_code nulls first";
          Query query = getSession().createSQLQuery(statement)
               .addScalar("id", StandardBasicTypes.INTEGER)
               .addScalar("version", StandardBasicTypes.INTEGER)
               .addScalar("languageCode");
          query.setInteger("navmenuId", navmenu.getId());
          List<NavmenuLanguage> navmenuLanguages = query.setResultTransformer(Transformers.aliasToBean(NavmenuLanguage.class)).list();
          for (NavmenuLanguage navmenuLanguage : navmenuLanguages) {
               if (navmenuLanguage.getLanguageCode() == null) {
                    navmenuLanguage.setLanguageCode(new String());
               }
          }
          return navmenuLanguages;
     }

Is there any way to tell Hibernate to return an empty value if a null value is present in the persistence store of Oracle ?

Kind Regards,

Edited by: user573224 on Dec 7, 2012 1:40 AM

Edited by: user573224 on Dec 7, 2012 1:41 AM
  • 1. Re: Hibernate return empty values on persisted null values
    576227 Newbie
    Currently Being Moderated
    I solved the issue by using a coalesce function as it made the DAO method cross database servers compatible.

         public List<NavmenuLanguage> findWithNavmenu(Navmenu navmenu) {
              Query query = getSession().createQuery("from NavmenuLanguage where navmenu.id = :navmenuId order by coalesce(languageCode, '0')");
              query.setLong("navmenuId", navmenu.getId());
              return query.list();
         }

    Note that both the single quotes and the zero '0' are required to have it work on all database servers.
  • 2. Re: Hibernate return empty values on persisted null values
    orafad Oracle ACE
    Currently Being Moderated
    Since you already have found a solution, I'll add this post at least for reference by other readers.

    >
    ... the Hibernate returns null instead of empty "" for a non set value in the persistence store of Oracle.
    You may have seen consequence of a difference in database concepts and implementation. Oracle handles the "empty string" with NULL.
    See http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements005.htm

    Note that this is a fundamental thing, it applies to all Oracle editions (not XE specifically).

    There are numerous theoretical or technical discussions out there. Just search for e.g. "Oracle empty string".

    So, first face the fundamental fact, then consider how (and where) to work with it, to suit your application requirements. E.g. either in some software framework/layer such as Hibernate or in the application code.

    Is there any way to tell Hibernate to return an empty value if a null value is present in the persistence store of Oracle ?
    Afaik, Hibernate is not an Oracle product so these types of questions does probably belong in Hibernate forums.

    Focusing on Oracle SQL instead, describing the issue from that viewpoint, regardless of external software layer, maybe one could have put a topic for discussion in the SQL and PL/SQL forum.
    (SQL and PL/SQL

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points