Exceptions when annotated named query is applied and executed

[Former Member]

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!

> [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),

> })

> ractSapUserData {

As far as I understood you use the physical column names (the DB columns) in your named query. It is absolutely illegal to use DB column names in your object-oriented JPQL query. In my opinion this would totally undermine the spirit of JPA to mix up DB- and OO-stuff inside your queries. If you want to refer to DB columns please use named-native queries. This query might run on JBoss because the underlying JPA-implementation is Hibernate which might acceppt non-compliant JPQL queries.

Best Regards,

Robin

[Former Member]

Hello Robin,

thank you for that fast response!

>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.´

Thanks for that hint - I think I messed up HQL and EJBQL definitions.

So will use the following query:

@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 {

    ...

}

But what about the following exception when using that query:

[EXCEPTION]
 {0}#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
                                                                                ^

The field dayOfAggregation is defined as @Embedded object in the hierarchy

of that SapUserData class.

The hierarchy is as follows:

         @MappedSuperclass
         AbstractSapResultData (contains dayOfAggregation field)
                 ^
                 |
        @MappedSuperclass
        AbstractSapUserData (contains the timeSlice field)
                 ^
                 |
          @Entity
          SapUserData

The get method for the field dayOfAggregation contained in class

AbstractSapResultData is as follows:

    /**
     * Get method for the member "<code>dayOfAggregation</code>".
     * 
     * @return Returns the dayOfAggregation.
     */
    @Embedded
    @AttributeOverrides({@AttributeOverride(name = "calendar", /* Not possible to use interface constant here - field name must be used for reference */
                                            column = @Column(name = DayContext.COLUMN_NAME_DAY_OF_AGGREGATION, 
                                                             nullable = false)),
                         @AttributeOverride(name = "weekday", 
                                            column = @Column(name = DayContext.COLUMN_NAME_WEEKDAY, 
                                                             nullable = false)),
                       })
    public Day getDayOfAggregation() {
        return this.dayOfAggregation;
    }

The code of the get method for the timeSlice field contained in class

AbstractSapUserData is as follows:

    /**
     * Get method for the member "<code>timeSlice</code>".
     * 
     * @return Returns the timeSlice.
     */
    @Embedded
    public TimeSlice getTimeslice() {
        return this.timeSlice;
    }

Using the obj.timeSlice field in the named query leads to the following exception:

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
                                                                                ^

So the problems are related to embedded fields declared in the base classes

that are annotated with @MappedSuperclass.

Sorry - Unfortunately I'm not allowed to post more code...

Henning

[Former Member]

Hi Henning!

The problem here is that you compare an input-parameter with an embeddable field of your entity. Regarding to the JPQL-grammer (spec 4.14) you are not allowed to compare with the non-terminal embeddable field but with the terminal fields of your embeddable. Stating that your embedded class might have the fields startTime and endTime your JPQL query might look like this:

Query q = em.createQuery("SELECT count(obj.databaseId) FROM SapUserData obj WHERE obj.timeSlice.startTime = :startTime AND obj.timeSlice.endTime = :endTime");

q.setParameter("startTime", foo.getStartTime());
q.setParameter("endTime", foo.getEndTime());
q.getResultList();

This limitation in the JPQL grammar is rather uncomfortable. An automatic mapping of the parameter's fields to the terminal fields of your embedded field would be more convenient. The same can be said for lists as parameter-values which is possible in HQL, too. I think we have to wait patiently for JPA 2.0 and hope for improvements there.

Best Regards,

Robin