Hibernate retuns list with null values when querying Oracle views

Question

when querying Oracle views with Hibernate 4 I get back a list with a size bigger than 0, representing the count of elements I get when running the same query in SQL Developer for example. When I loop through the list, I however only get null values.

My hibernate.cfg.xml looks like:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
    <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>

    <property name="hibernate.connection.url">jdbc:oracle:thin:@127.0.0.1:1521:OUT</property>
    <property name="hibernate.connection.username">username</property>
    <property name="hibernate.connection.password">password</property>
    <property name="hibernate.default_schema">schema</property>

    <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

    <property name="hibernate.connection.pool_size">1</property>

    <property name="hibernate.current_session_context_class">thread</property>

    <property name="hibernate.cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
    <property name="hibernate.hbm2ddl.auto">update</property>

    <mapping resource="be/comp/model/db/VwPersoneelslid.hbm.xml"/>
</session-factory>

The VwPersoneelslid.hbm.xml looks like:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated 9-aug-2013 10:13:31 by Hibernate Tools 4.0.0 -->
<hibernate-mapping>
<class name="be.comp.model.db.VwPersoneelslid" table="VW_PERSONEELSLID">
    <composite-id name="id" class="be.comp.model.db.VwPersoneelslidId">
        <key-property name="pkVwPersoneelslid" type="double">
            <column name="PK_VW_PERSONEELSLID" precision="126" scale="0" />
        </key-property>
        <key-property name="fkVwFunctie" type="double">
            <column name="FK_VW_FUNCTIE" precision="126" scale="0" />
        </key-property>
        <key-property name="familienaam" type="string">
            <column name="FAMILIENAAM" length="80" />
        </key-property>
        <key-property name="voornaam" type="string">
            <column name="VOORNAAM" length="80" />
        </key-property>
        <key-property name="code" type="string">
            <column name="CODE" length="10" />
        </key-property>

    </composite-id>
</class>

The function with the query is:

public List<VwPersoneelslid> findByCode(String code)
{
    Session session = HibernateUtil.getSessionFactoryNeptunus().getCurrentSession();
    session.beginTransaction();

    List<VwPersoneelslid> list = new ArrayList();

    Query query = session.createQuery("FROM VwPersoneelslid WHERE code = :code"); 
    query.setParameter("code", code);
    list = query.list();

    session.getTransaction().commit();

    return list;
}

The function transformed to native SQL:

public List<VwPersoneelslid> findByCode(String code)
{
    Session session = HibernateUtil.getSessionFactoryNeptunus().getCurrentSession();
    session.beginTransaction();

    List<VwPersoneelslid> list = new ArrayList();

    Query query = session.createSQLQuery("SELECT * FROM CIPALII.VW_PERSONEELSLID WHERE code = :code").addEntity(VwPersoneelslid.class)
            .setParameter("code", code);

    list = query.list();

    session.getTransaction().commit();

    return list;
}

If you guys need more information in order to know what might be the problem, please let me know and I add more code. The VwPersoneelslid.hbm.xml file is auto generated by Eclipse. Thanks.

@bellabox I edited my post and included the code field. I had accidently removed it to reduce the size of the VwPersoneelslid.hbm.xml file. — Snels Nick, Aug 09 '13 at 17:54

score 4 · Accepted Answer · answered Aug 19 '13 at 13:16

I managed to create a workaround. The problem, for me at least, lies in the fact that the Hibernate Tools create composite-id 's. Which I can not get to work correctly, without jumping through several hoops. So I managed to get rid of the composite-id . In my reveng.xml file I added:

<table name="VW_PERSONEELSLID">
    <primary-key>
        <key-column name="PK_VW_PERSONEELSLID" />
    </primary-key>
</table>

This generates a single id (PK_VW_PERSONEELSLID) and it allows me to continue to use the Hibernate Tools. I do not know if this is a dirty fix. Will probably find that out in a couple of days.

score 2 · Answer 2 · answered Aug 13 '13 at 06:49

2

Hibernate HQL Queries deals with Entities . Views are the concept of Database or SQL Schema. When we query data using HQL we mention the Entity name and Entity Properties. So if you want to query from Views , Use Core SQL Queries with Hibernate instead Of HQL Queries.

answered Aug 13 '13 at 06:49

Harmeet Singh Taara

6,483
20
73
126

I have transformed the query to a core SQL query (see my original post at the bottom). But I am still getting null's. – Snels Nick Aug 13 '13 at 09:16
Try to use `SQLQuery` interface instead of `Query `. – Harmeet Singh Taara Aug 13 '13 at 09:27
Could you give me an example? – Snels Nick Aug 14 '13 at 06:21
SQLQuery query = session.createSQLQuery("SELECT * FROM CIPALII.VW_PERSONEELSLID WHERE code = :code"); query.setParameter("code", code); – Harmeet Singh Taara Aug 14 '13 at 09:58
I tried your code. Now when I use an Iterator to loop through the object and call the next() method, I get the error: Exception in thread "main" java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to be.ocmwturnhout.model.neptunus.VwPersoneelslid at be.ocmwturnhout.permanenties.Main.main(Main.java:73) – Snels Nick Aug 14 '13 at 11:32
I think the native Queries return the data in List – Harmeet Singh Taara Aug 14 '13 at 17:38

score 2 · Answer 3 · edited May 23 '17 at 11:59

2

Before all: sorry for poor format, but on my 3" screen mobile write e is a pain...)
IMO the problem is that your entity is composed only by the id. Hibernate probably fetch id during query and found a valid numbers of objects (your list size) but when is time to convert id to mapped entity during dehydrating, it run into problem fetching real data and building up your entity (maybe hibernate is getting other properties excluding those are part of id and run into a sort of select null from table where id-fields = prefetched id - just an idea; this is why you get a list filled of nulls). If you can reconsider your composite-id or try to add more properties to entity. Hope can help to solve or point to solution. Hibernate tools sometimes generate weird mappings... Else working with native query, addScalar() (instead of addEntity) and an aliastobean resulttransformer can be a work-around (If you are mapping a readonly view this can be resonable)

Using SQLQuery:

session.createSQLQuery("select pkVwPersoneelslid, fkVwFunctie, familienaam, voornaam, code from VW_PERSONEELSLID where code = :code") .setParameter("code", code) .addScalar("pkVwPersoneelslid",DoubleType.INSTANCE) .addScalar("fkVwFunctie",DoubleType.INSTANCE) .addScalar("familienaam",StringType.INSTANCE) .addScalar("voornaam".StringType.INSTANCE) .addScalar("code".StringType.INSTANCE) .setResultTransformer(new AliasToBeanResultTransformer(VwPersoneelslid.class)) .list();

You need empty constructor and setter/getter; for any issues about check this SO answer.

edited May 23 '17 at 11:59

Community

1
1

answered Aug 13 '13 at 10:10

Luca Basso Ricci

17,829
2
47
69

Could you give me an example? – Snels Nick Aug 14 '13 at 06:23
I'm not an home to write an example but [here] ( http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html) you can find example for addScalar() and point 16.1.5 for resultTransformer; – Luca Basso Ricci Aug 14 '13 at 07:15
When I add addScalar() to the query, I get the following error: Exception in thread "main" java.lang.ClassCastException: java.lang.String cannot be cast to be.comp.model.neptunus.VwPersoneelslid at be.comp.permanenties.Main.main(Main.java:73) – Snels Nick Aug 14 '13 at 10:10
you need `addScalar()` for all properties and a `ResultTransformer` as edited – Luca Basso Ricci Aug 18 '13 at 16:26
I used your code with the addScalar() and the ResultTransformer. I simplified it a bit for testing purposes. I only used the code field. Now however, I get the error: Exception in thread "main" org.hibernate.PropertyNotFoundException: Could not find setter for code on class be.comp.model.neptunus.VwPersoneelslid - I should probably also mention that the Hibernate code generator, generated a VwPersoneelslid.java, VwPersoneelslidHome.java and VwPersoneelslidId.java file along with the VwPersoneelslid.hbm.xml file – Snels Nick Aug 19 '13 at 07:47
you need setter/getter for property in class VwPersoneelslid else it doesn't work; it's mandatory! But this looks the right way to proceed! Check http://stackoverflow.com/questions/9605821/aliastobeanresulttransformermydto-class-fails-to-instantiate-mydto Don't get all code generated by Hibernate Tools as "correct" because sometimes this tools generate weird mapping that need to be modified a bit to get them works – Luca Basso Ricci Aug 19 '13 at 10:56

score 0 · Answer 4 · answered May 03 '16 at 02:46

If you know your column that you are querying could contain null values, then I suggest you to add NVL(ColumnName,value) to the query.

For eg:

session.createSQLQuery("select NVL(size,0) as size from randomTable")

  .addScalar("size",StandardBasicType.INTEGER)
  .setResultTransformer(Transformers.aliasToBean(randomTableClass.class))
  .list();

You may also use COALESCE

session.createSQLQuery("select COALESCE(size,0) as size from randomTable")

This solved my issue.

Correct me if I am wrong.

Hibernate retuns list with null values when querying Oracle views

4 Answers4