Exceptions when annotated named query is applied and executed

[#1]

Hello Forum,

I have some problems in creating and using named queries declared via

class level annotations.

I declared the following named query:

[code]@NamedQueries({

@NamedQuery(name = SapUserDataContext.NAMED_QUERY_NAME_COUNT_QUERY,

query = "SELECT COUNT(obj.databaseId) FROM SapUserData AS obj WHERE "

+ "obj.sapCustomerId"

+ "= :"

+ SapResultDataContext.COLUMN_NAME_SAP_CUSTOMER_ID

+ " AND "

+ "obj.sapSystemId"

+ "= :"

+ SapResultDataContext.COLUMN_NAME_SAP_SYSTEM_ID

+ " AND "

+ "obj.sapServerId"

+ "= :"

+ SapResultDataContext.COLUMN_NAME_SAP_SERVER_ID

+ " AND "

+ "obj.dayOfAggregation"

+ "= :"

+ DayContext.COLUMN_NAME_DAY_OF_AGGREGATION

+ " AND "

+ "obj.timeSlice"

+ "= :"

+ TimeSliceContext.COLUMN_NAME_TIME_SLICE),

})

public class SapUserData extends AbstractSapUserData {

...

}[/code]

When executing that query I encounter the following errors:

[code][EXCEPTION]

#1#java.lang.IllegalArgumentException: line 1: Comparison '=' not defined for dependent objects

SELECT COUNT(obj.databaseId) FROM SapUserData AS obj WHERE obj.sapCustomerId= :sap_customer_id AND obj.sapSystemId= :sap_system_id AND obj.sapServerId= :sap_server_id AND obj.dayOfAggregation= :day_of_aggregation AND obj.timeSlice= :time_slice

^

line 1: Path 'obj' (SapUserData) has no component 'timeSlice'

SELECT COUNT(obj.databaseId) FROM SapUserData AS obj WHERE obj.sapCustomerId= :sap_customer_id AND obj.sapSystemId= :sap_system_id AND obj.sapServerId= :sap_server_id AND obj.dayOfAggregation= :day_of_aggregation AND obj.timeSlice= :time_slice

^

at com.sap.engine.lib.orpersistence.ejbqlparser.JPQLParserFacade.parseJQPL(JPQLParserFacade.java:30)

at com.sap.engine.services.orpersistence.query.QueryImpl.calculateJPQLQueryData(QueryImpl.java:102)

at com.sap.engine.services.orpersistence.query.QueryImpl.precompileQueries(QueryImpl.java:83)

at com.sap.engine.services.orpersistence.entitymanager.EntityManagerFactoryImpl$MetaDataImpl.<init>(EntityManagerFactoryImpl.java:153)

at com.sap.engine.services.orpersistence.entitymanager.EntityManagerFactoryImpl.getPersistenceUnitData(EntityManagerFactoryImpl.java:124)

at com.sap.engine.services.orpersistence.core.PersistenceContextImpl.<init>(PersistenceContextImpl.java:69)

at com.sap.engine.services.orpersistence.entitymanager.EntityManagerImpl.<init>(EntityManagerImpl.java:71)

at com.sap.engine.services.orpersistence.entitymanager.JtaEntityManagerFactoryImpl.createEntityManagerInternal(JtaEntityManagerFactoryImpl.java:57)

at com.sap.engine.services.orpersistence.entitymanager.JtaEntityManagerFactoryImpl.createEntityManager(JtaEntityManagerFactoryImpl.java:49)

at com.sap.engine.services.orpersistence.container.EMManager.createEntityManager(EMManager.java:202)

at com.sap.engine.services.orpersistence.container.EMManager.getTransactionEntityManager(EMManager.java:60)

at com.sap.engine.services.orpersistence.container.EntityManagerProxy.getEntityManager(EntityManagerProxy.java:241)

at com.sap.engine.services.orpersistence.container.EntityManagerProxy.createNamedQuery(EntityManagerProxy.java:153)

at de.collogia.sapmon.worker.beans.service.user.SapCurrentUsersServiceBean.checkRecordExistence(SapCurrentUsersServiceBean.java:233)

at de.collogia.sapmon.worker.beans.service.user.SapCurrentUsersServiceBean.storeCurrentUserList(SapCurrentUsersServiceBean.java:109)

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:585)

at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceed(RequestInvocationContext.java:43)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext$InvocationContextImpl.proceed(AbstractInvocationContext.java:131)

at de.collogia.sapmon.worker.beans.interceptors.SapmonInterceptorLogServiceExecMethodStore.logServiceExecution(SapmonInterceptorLogServiceExecMethodStore.java:79)

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:585)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Application.invoke(Interceptors_Application.java:34)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:168)

at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceed(RequestInvocationContext.java:85)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_StatesTransition.invoke(Interceptors_StatesTransition.java:19)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:168)

at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceed(RequestInvocationContext.java:85)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Resource.invoke(Interceptors_Resource.java:23)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:168)

at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceed(RequestInvocationContext.java:85)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Transaction.doWorkWithAttribute(Interceptors_Transaction.java:38)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Transaction.invoke(Interceptors_Transaction.java:22)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:168)

at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceed(RequestInvocationContext.java:85)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:179)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_StatelessInstanceGetter.invoke(Interceptors_StatelessInstanceGetter.java:16)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:168)

at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceed(RequestInvocationContext.java:85)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_SecurityCheck.invoke(Interceptors_SecurityCheck.java:21)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:168)

at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceed(RequestInvocationContext.java:85)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_ExceptionTracer.invoke(Interceptors_ExceptionTracer.java:16)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:168)

at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceed(RequestInvocationContext.java:85)

at com.sap.engine.services.ejb3.runtime.impl.DefaultInvocationChainsManager.startChain(DefaultInvocationChainsManager.java:133)

at com.sap.engine.services.ejb3.runtime.impl.DefaultEJBProxyInvocationHandler.invoke(DefaultEJBProxyInvocationHandler.java:164)

at com.sap.engine.services.ejb3.runtime.impl.RemoteEJBProxyInvocationHandler.invoke(RemoteEJBProxyInvocationHandler.java:46)

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:585)

at com.sap.engine.services.rmi_p4.P4DynamicSkeleton.dispatch(P4DynamicSkeleton.java:237)

at com.sap.engine.services.rmi_p4.P4Message.process(P4Message.java:253)

at com.sap.engine.services.rmi_p4.P4Message.execute(P4Message.java:109)

at com.sap.engine.services.cross.fca.FCAConnectorImpl.executeRequest(FCAConnectorImpl.java:841)

at com.sap.engine.services.rmi_p4.P4Message.process(P4Message.java:125)

at com.sap.engine.services.cross.fca.MessageReader.run(MessageReader.java:59)

at com.sap.engine.core.thread.execution.Executable.run(Executable.java:108)

at com.sap.engine.core.thread.execution.CentralExecutor$SingleThread.run(CentralExecutor.java:168)[/code]

The main problem is that I'm unable to write the named query in a way that is

accepted by JBoss app. server and SAP NetWeaver app. server which

is an important fact because we are doing cross platform development.

On JBoss I'm able to write the query using the physical column names for both -

the field name I want to include in the where clause and for the named query parameter associated with the current field.

So the query working perfectly on JBoss lokks like that:

[code]@NamedQueries({

@NamedQuery(name = SapTaskDataContext.NAMED_QUERY_NAME_COUNT_QUERY,

query = "SELECT COUNT(*) FROM SapUserData AS obj WHERE "

+ SapResultDataContext.COLUMN_NAME_SAP_CUSTOMER_ID

+ "= :"

+ SapResultDataContext.COLUMN_NAME_SAP_CUSTOMER_ID

+ " AND "

+ SapResultDataContext.COLUMN_NAME_SAP_SYSTEM_ID

+ "= :"

+ SapResultDataContext.COLUMN_NAME_SAP_SYSTEM_ID

+ " AND "

+ SapResultDataContext.COLUMN_NAME_SAP_SERVER_ID

+ "= :"

+ SapResultDataContext.COLUMN_NAME_SAP_SERVER_ID

+ " AND "

+ DayContext.COLUMN_NAME_DAY_OF_AGGREGATION

+ "= :"

+ DayContext.COLUMN_NAME_DAY_OF_AGGREGATION

+ " AND "

+ TimeSliceContext.COLUMN_NAME_TIME_SLICE

+ "= :"

+ TimeSliceContext.COLUMN_NAME_TIME_SLICE),

})

public class SapUserData extends AbstractSapUserData {

...

}[/code]

The main difference is that I'm using the physical column name for referencing

the fields:

[code] query = "SELECT COUNT(*) FROM SapUserData AS obj WHERE "

+ SapResultDataContext.COLUMN_NAME_SAP_CUSTOMER_ID <-- Physical column name used to reference the field

+ "= :"

+ SapResultDataContext.COLUMN_NAME_SAP_CUSTOMER_ID<-- Physical column name used to declare the named query parameter also[/code]

I've chosen that way because JBoss complains when using the real field names

within named queries:

[code]12:35:57,962 WARN [FromElementType] Using non-qualified column reference [sapCustomerId -> ([sap_customer_id])]

12:35:57,963 WARN [FromElementType] Using non-qualified column reference [sapSystemId -> ([sap_system_id])]

12:35:57,963 WARN [FromElementType] Using non-qualified column reference [sapServerId -> ([sap_server_id])]

12:35:57,963 WARN [FromElementType] Using non-qualified column reference [dayOfAggregation -> ([day_of_aggregation,weekday])][/code]

The main problem I have ist that I'm unsure about what is most close to the EJB 3.0/ JPA 1.0 standards.

The other problem is that both app servers are unable to reference the field

timeSlice when using the named query declaration that is accepted by

the NetWeaver app. server.

[code]line 1: Path 'obj' (SapUserData) has no component 'timeSlice'

SELECT COUNT(obj.databaseId) FROM SapUserData AS obj WHERE obj.sapCustomerId= :sap_customer_id AND obj.sapSystemId= :sap_system_id AND obj.sapServerId= :sap_server_id AND obj.dayOfAggregation= :day_of_aggregation AND obj.timeSlice= :time_slice

^[/code]

Is it correct if I assume that this error is related to the fact that the timeSlice field

is declared as private member in the base class (AbstractSapUserData) of

class SapUserData?

Another problem is the declaration of the query using the asterisk that is accepted

on JBoss:

[code] query = "SELECT COUNT(*) FROM SapUserData AS obj WHERE "[/code]

The EJBQL allows the usage of [code]COUNT(*)[/code] so far as I know.

So what's the point?

If it's standard compliant I would prefer the named query syntax that works on JBoss because I'm allowed to use the constants defined in the Interface of

the class. The constant named of that interface are used to declare the physical column names on the get methods of the fields using the @Column(..) annotation

so it would be very nice to be able to use them within the naemd query definitions.

If I have to do it the NetWeaver way I end up with hard coded strings for the field

names which is not so smart.

The fact I do not like at most is the fact that I'm unable to use base class fields in the query - I tried to change the field visibility form private to protected but I'm

still receiving the following exception:

[code]line 1: Path 'obj' (SapUserData) has no component 'timeSlice'

SELECT COUNT(obj.databaseId) FROM SapUserData AS obj WHERE obj.sapCustomerId= :sap_customer_id AND obj.sapSystemId= :sap_system_id AND obj.sapServerId= :sap_server_id AND obj.dayOfAggregation= :day_of_aggregation AND obj.timeSlice= :time_slice

^[/code]

Any ideas on that?

Thanks in advance!

Henning Malzahn

[Former Member]

Hi Henning!

I will try to answer your post in several parts..

> Another problem is the declaration of the query using

> the asterisk that is accepted

> on JBoss:

>

>

                            query = "SELECT
> COUNT(*) FROM SapUserData AS obj WHERE "

>

> The EJBQL allows the usage of

COUNT(*)

> so far as I know.

>

> So what's the point?

>

According to the JPA Spec the usage of COUNT is only allowed in conjunction with a path-expression or an identification variable. Please refer to the spec chapter 4.8.4 and 4.14 for further information. Regarding JBoss I can say that they are not that strictly bound to the standard as our implementation because the JPA implementation of JBoss is Hibernate which internally uses HQL. The HQL-grammar is a superset of JPQL. Hence you can expect that such queries will also fail with other JPA implementations.

Best Regards,

Robin