Hibernate computed property and SQL-Queries

Question

Hi,

I've implemented a computed property to my configuration which works fine if I'm using HQL only. Sadly there are places where SQL-Queries are executed which I can't build into HQL. Am I facing a bug or just doing something wrong?

<property name="customerNr" type="int" insert="false" update="false" lazy="false">
<formula>
(SELECT DISTINCT p.CUSTOMER FROM P.P03 p WHERE p.COUNTRY = land)
</formula>
</property>

When a SQL Quey is executed, which is done by this way:

session.createSQLQuery(queryString).addEntity(P11.class).list()

I recieve a NullPointerException.

java.lang.NullPointerException
    at org.hibernate.loader.DefaultEntityAliases.intern(DefaultEntityAliases.java:157)
    at org.hibernate.loader.DefaultEntityAliases.getSuffixedPropertyAliases(DefaultEntityAliases.java:130)
    at org.hibernate.loader.DefaultEntityAliases.<init>(DefaultEntityAliases.java:76)
    at org.hibernate.loader.ColumnEntityAliases.<init>(ColumnEntityAliases.java:40)
    at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.generateCustomReturns(SQLQueryReturnProcessor.java:197)
    at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:152)
    at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:67)
    at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:136)
    at org.hibernate.impl.AbstractSessionImpl.getNativeSQLQueryPlan(AbstractSessionImpl.java:160)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
    at de.acocon.mis.dao.AmpelDaoImpl.getAmpelOMK(AmpelDaoImpl.java:134)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307

Answer 1

You must copy the formula into your native SQL query. Hibernate doesn't try to manipulate or understand native SQL, when you use it, you're on your own.

Answer 2

Did you solve your problem? I have the same Problem with:

Hello,

i got a NPE when trying to load an Entity that has a Formula definied. For loading the Entity i use a native Query. something like this:

Query query = this.em.createNativeQuery(sql.toString(), Customer.class);

The Query:

[#|2010-09-15T11:41:51.946+0200|INFO|glassfishv3.0|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=31;_ThreadName=Thread-1;|QUERY: SELECT G.* FROM CUSTOMER G, CUSTOMER_RELATION R WHERE G.ID = R.REF_GP2   AND R.RELTYP IN ('ZI1', 'ZI2', 'ZI3')   AND R.REF_GP = ? |#]

The Customer has a property:

@Formula("(SELECT CASE WHEN (COUNT(*) > 0) THEN 'true' WHEN (COUNT(*) = 0) THEN 'false' END FROM VW_CUSTOMER S WHERE S.CUSTOMER_ID = ID)")
private String premium;