Spring Data Projection and Error: “No aliases foun

Question

The below approach to get a Spring Data Projection from a JPA Query doesn't work for me:

https://stackoverflow.com/a/45443776/1005607

My table:

LOOKUP_T

id   description    display_order_num
------------------------------------
1    Category #1    1
2    Category #2    2

ACTIVITIES_T (activity_category_id maps to LOOKUP_T.id)

id  activity_category_id  activity_title
---------------------------------------
1      2                  Sleeping
2      2                  Eating
3      2                  Travel

Spring Data DAO Interface to get certain fields from this join:

@Repository
public interface ActivitiesDAO extends JpaRepository<ActivitiesT, Integer> {

    @Query("select a.activityTitle, l.description as category, " + 
           "l.displayOrderNum as categoryDisplayOrderNum " + 
           "from ActivitiesT a, LookupT l " + 
           "where a.lookupT.id = l.id order by l.displayOrderNum asc ")
    public List<MySpringDataProjection> findCustom();

}

Spring Data Projection Model Interface:

public interface MySpringDataProjection {

    public String getActivityTitle();

    public String getCategory();

    public Integer getCategoryDisplayOrderNum();

}

Everything is the same as in that accepted answer. But getting this error:

org.springframework.dao.InvalidDataAccessApiUsageException: No aliases found in result tuple! Make sure your query defines aliases!; nested exception is java.lang.IllegalStateException: No aliases found in result tuple! Make sure your query defines aliases!
    org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:381)
    org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:489)
    org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)

I don't want to use select new Obj(..) in the Query, it's dirty and relies on Hibernate which we're abstracting out to JPA.

I want to get this Projection approach to work.

Related question which had the (non-working) answer I referenced, Spring data JPA: getting No aliases found in result tuple! error when executing custom query

Answer 1

I encounter the same problem. After try several changes, I found we just need to add "as" for each column(even the column name is not changed) in NativeQuery. For you here, change your sql like :

    @Query("select a.activityTitle **as activityTitle**, l.description as category, " + 
       "l.displayOrderNum as categoryDisplayOrderNum " + 
       "from ActivitiesT a, LookupT l " + 
       "where a.lookupT.id = l.id order by l.displayOrderNum asc ")

Answer 2

I had the same problem and i think i solve it. What i did is to use alias in all field, even if they have the same name. Use alias in activityTitle too. Like this:

@Repository
public interface ActivitiesDAO extends JpaRepository<ActivitiesT, Integer> {

    @Query("select a.activityTitle as activityTitle, l.description as category, " + 
           "l.displayOrderNum as categoryDisplayOrderNum " + 
           "from ActivitiesT a, LookupT l " + 
           "where a.lookupT.id = l.id order by l.displayOrderNum asc ")
    public List<MySpringDataProjection> findCustom();

}

Answer 3

I think you must define exact name methods in the interface, but i'm not sure this approach apply to your case.

The important bit here is that the properties defined here exactly match properties in the aggregate root. This allows a query method to be added like this

In your example, you could try an Open Projection