Spring数据投影和错误:“在结果元组中找不到别名！确保您的查询定义了别名！"

问题描述

以下从JPA查询中获取Spring数据投影的方法对我不起作用:

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

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

我的桌子:

LOOKUP_T

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

ACTIVITIES_T(activity_category_id映射到LOOKUP_T.id)

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

Spring Data DAO接口从此联接中获取某些字段:

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数据投影模型接口:

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)

我不想在查询中使用select new Obj(..)，它很脏，并且依赖于我们正在抽象到JPA的Hibernate.

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.

我想让这种Projection方法工作.

I want to get this Projection approach to work.

具有我提到的(无效)答案的相关问题， Spring数据JPA:获取否在结果元组中找到别名！执行自定义查询时出现错误

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

推荐答案

我遇到了同样的问题.在尝试了几次更改之后，我发现我们只需要在NativeQuery中为每列添加"as"(即使列名未更改).对于您而言，在您的SQL上进行如下更改:

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 ")

这篇关于Spring数据投影和错误:“在结果元组中找不到别名！确保您的查询定义了别名！"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！