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
– Snels Nick, Commented Aug 9, 2013 at 17:54

Snels Nick · Accepted Answer · 2013-08-19 13:16:09Z

4

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.

answered Aug 19, 2013 at 13:16

Snels Nick

9553 gold badges14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harmeet Singh Taara · Accepted Answer · 2013-08-13 06:49:41Z

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, 2013 at 6:49

Harmeet Singh Taara

6,65122 gold badges75 silver badges127 bronze badges

6 Comments

Snels Nick Over a year ago

I have transformed the query to a core SQL query (see my original post at the bottom). But I am still getting null's.

Harmeet Singh Taara Over a year ago

Try to use SQLQuery interface instead of Query .

Snels Nick Over a year ago

Could you give me an example?

Harmeet Singh Taara Over a year ago

SQLQuery query = session.createSQLQuery("SELECT * FROM CIPALII.VW_PERSONEELSLID WHERE code = :code"); query.setParameter("code", code);

Snels Nick Over a year ago

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)

|

Community · Accepted Answer · 2017-05-23 11:59:40Z

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, 2017 at 11:59

CommunityBot

11 silver badge

answered Aug 13, 2013 at 10:10

Luca Basso Ricci

18.5k2 gold badges51 silver badges75 bronze badges

6 Comments

Snels Nick Over a year ago

Could you give me an example?

Luca Basso Ricci Over a year ago

I'm not an home to write an example but [here] ( docs.jboss.org/hibernate/orm/3.3/reference/en/html/…) you can find example for addScalar() and point 16.1.5 for resultTransformer;

Snels Nick Over a year ago

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)

Luca Basso Ricci Over a year ago

you need addScalar() for all properties and a ResultTransformer as edited

Snels Nick Over a year ago

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

|

KPDS · Accepted Answer · 2016-05-03 02:46:48Z

0

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.

answered May 3, 2016 at 2:46

KPDS

213 bronze badges

Collectives™ on Stack Overflow

Hibernate retuns list with null values when querying Oracle views

4 Answers 4

Comments

6 Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

6 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related