Hibernate reconfigures a list with null values ​​when querying Oracle views

Question

when querying Oracle views with Hibernate 4, I return a list with a size greater than 0 representing the number of elements that I get when I run the same query in SQL Developer, for example. When I scroll through the list, I get only null values.

My hibernate.cfg.xml looks like this:

<?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> 

VwPersoneelslid.hbm.xml looks like this:

 <?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> 

Function with request:

 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; } 

Function converted 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 to find out what might be the problem, let me know and I will add more code. The VwPersoneelslid.hbm.xml file is automatically generated by Eclipse. Thanks.

Answer 1

I managed to create a workaround. The problem, at least for me, is that Hibernate Tools creates compound identifiers. Which I can't work normally without jumping over a few hoops. Therefore, I managed to get rid of the composite identifier. 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 using Hibernate Tools. I do not know if this is a dirty decision. This is likely to happen in a couple of days.

Answer 2

Hibernate HQL queries are handled by entities. Views are the concept of a database or SQL schema. When we request data using HQL, we specify the name Entity and Entity Properties. Therefore, if you want to get a query from views, use key SQL queries with Hibernate instead of HQL queries.

Answer 3

First of all: sorry for the poor format, but on my 3-inch screen, mobile recording e is a pain ...)
IMO the problem is that your organization consists only of an identifier. Hibernate probably gets the identifier at the time of the request and detects the actual number of objects (the size of your list), but when it comes time to convert the identifier to the associated object during dehydration, it encounters the problem of getting real data and creating your entity (maybe hibernation gets other properties excluding them are part of the identifier and are launched as a null value from the table, where id-fields = prefetched id is just an idea, so you get a list filled with zeros). If you can revise your composite identifier or try to add additional properties to the object. Hope can help decide or point to a decision. Hibernate tools sometimes generate strange mappings ... Else, working with its own query, addScalar () (instead of addEntity) and aliastobean resulttransformer can be workaround (if you display the view in readonly mode, this can be convenient)

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 an empty constructor and setter / getter; for any issues related to checking this out . Answer.

Answer 4

If you know that your column you are requesting may contain null values, I suggest you add an NVL (ColumnName, value) query.

For example,

 session.createSQLQuery("select NVL(size,0) as size from randomTable") .addScalar("size",StandardBasicType.INTEGER) .setResultTransformer(Transformers.aliasToBean(randomTableClass.class)) .list(); 

You can also use COALESCE

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

This solved my problem.

Correct me if I am wrong.