CODEWITHSUNDEEP
sqljpasubqueryaliasquerydsl
Genzotto
Genzotto

Reputation: 1974

Reference subquery fields in QueryDSL

I have to build a query in QueryDSL with a subquery, like this:

Expression<String> caseExpression = new CaseBuilder()
    ...;

Expression<?>[] queryProjection={
    table.parameter1,
    caseExpression
};


Expression<?>[] subqueryProjection={
    table.parameter1.as("alias1"),
    table.parameter2.as("alias2"),
    table.parameter3.as("alias3"),
    table.parameter4.as("alias4")
};


SQLSubQuery subQuery = new SQLSubQuery()
      .from(table)
      .where(...);

JPASQLQuery query = new JPASQLQuery(entityManager, ORACLE_TEMPLATE)
    .from(subQuery.list(subqueryProjection));

query.list(queryProjection);

I am getting the following exception:

9/10/2014 09:45:55 AM org.apache.catalina.core.StandardWrapperValve invoke
GRAVE: Servlet.service() para servlet rtve-rest lanzó excepción
java.sql.SQLException: ORA-00904: "TABLE"."PARAMETER1": invalid identifier

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at sun.reflect.GeneratedMethodAccessor54.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at $Proxy78.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:829)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
at org.hibernate.loader.Loader.doList(Loader.java:2438)
at org.hibernate.loader.Loader.doList(Loader.java:2424)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2254)
at org.hibernate.loader.Loader.list(Loader.java:2249)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:331)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1784)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:229)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:156)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:257)
at com.mysema.query.jpa.sql.AbstractJPASQLQuery.list(AbstractJPASQLQuery.java:145)

This is caused because the field in my queryProjection is not the same as in my subqueryProjection. This field has to be the same as in the subquery ("alias1").

How could I reference a field by its alias? Or how could I reference a field in a subquery from outside it?

Thank you very much in advance

Upvotes: 2

Views: 2732

Answers (2)

Genzotto
Genzotto

Reputation: 1974

I managed to do it with Expressions.stringTemplate:

Expression<?>[] queryProjection={
    Expressions.stringTemplate("alias1"),
    caseExpression
};

Upvotes: 1

Timo Westk&#228;mper
Timo Westk&#228;mper

Reputation: 22180

You need to use a Path instance with alias1 as the name or get rid of the aliasing. The simplest way to create that Path is

Expressions.path(Object.class, "alias1")

Replace Object with a more specific type if needed.

Upvotes: 1

Related Questions