Spring Boot JPA“查询验证失败" JPQL错误 [英] Spring Boot JPA "Validation failed for query" error for JPQL

问题描述

我正在尝试将自定义查询与jpa一起使用(而不是nativeQuery，因为我想将结果映射到自定义对象)，但我不知道出了什么问题.

I'm trying use a custom query with jpa (not a nativeQuery since I want to map the result to a custom object) and I can't figure out whats wrong.

存储库类如下:

@Repository
public interface ChallengeCompletionRepository extends JpaRepository<ChallengeCompletion, Integer>{
    List<ChallengeCompletion> findByUser(int uid);
    List<ChallengeCompletion> findByChallenge(int cid);
    List<ChallengeCompletion> findByUserAndChallenge(int uid, int cid);

    @Query(value = "SELECT new com.some.rly.long.package.name.ChallengeScore(user_id, count(id)) " +
        "FROM ChallengeCompletion " +
        "WHERE challenge_id = :cid " +
        "GROUP BY user_id " +
        "ORDER BY count(id) DESC")
   List<ChallengeScore> fetchUserScoreForChallenge(@Param("cid") int cid);
}

我想要列出结果的模型类如下:

And the model class I want the resultl listed in looks like this:

@Data
@NoArgsConstructor
public class ChallengeScore {

    public ChallengeScore(UUID userId, int score){
        this.score = score;
        this.userId = userId;
    }

    private int score;
    private User user;

    @JsonIgnore
    private UUID userId;

}

这是ChallengeCompletion的模型:

This is the model for ChallengeCompletion:

@Entity
@Data
@Table(name = "challenge_completions")
public class ChallengeCompletion extends BaseModel{

    @ManyToOne
    @JsonBackReference
    private User user;

    @ManyToOne
    private Challenge challenge;

    @ManyToOne
    private Project project;

}

基本模型是:

@MappedSuperclass
@Data
public abstract class BaseModel {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @CreationTimestamp
    private Timestamp createdAt;

    @UpdateTimestamp
    private Timestamp updatedAt;
}

错误很简单:验证失败，查询...".我也感到有点奇怪，我需要为要构造的类使用完全限定的名称，..但这可能是由于该类不是真正的@Entity并且不会自动加载.

The error is simply: "Validation failed for query...". I also feel it is a bit strange that I need to use the fully qualified name for the class I want to construct,.. but this is probably due to the fact the class is not a real @Entity and doesn't get autoloaded.

可以在此处找到完整的堆栈跟踪: https://pastebin.com/MjP0Xgz4

A full stacktrace can be found here: https://pastebin.com/MjP0Xgz4

对于上下文，此查询应做的是:用户可以多次完成挑战.每个完成都在ChallengeCompletion中记录为一行.我想获得一个不同用户的列表，以及他们完成某项挑战的频率.

For context, what this Query should do is: A user can complete challanges multiple times. Every completion is logged as a row in ChallengeCompletion. I want to get a list of distinct user(ids) and how often they completed a certain challenge.

欢呼

感谢@ DN1的评论，如果您不想给一个Challenge对象而只给一个id:

Thanks to the comment by @DN1 I could get it working after finding out that you can indeed do something like cc.challenge.id if you do not want to give a Challenge object but rather only an id:

@Query(value = "SELECT new com.energiedienst.smartcity.middleware.module.challenge.model.ChallengeScore(cc.user, count(cc.id)) " +
        "FROM ChallengeCompletion cc " +
        "WHERE cc.challenge.id = :cid " +
        "GROUP BY cc.user " +
        "ORDER BY count(cc.id) DESC")
List<ChallengeScore> fetchUserScoreForChallenge(@Param("cid") int cid);

ChallengeScore类现在看起来像:

And the ChallengeScore Class now looks like:

@Data
@NoArgsConstructor
public class ChallengeScore {

    public ChallengeScore(User user, long score){
        this.score = score;
        this.user = user;
    }

    private long score;
    private User user;

}

推荐答案

出现异常的原因是您正在使用JPQL，而JPQL使用类/字段名称而不是表/列名称(SQL使用的名称). user_id和challenge_id似乎是列名，您需要更改它们以使用字段名.有关详细信息，请参见本指南.

The reason for the exception is that you are using JPQL, and JPQL uses class/field names and not table/column names (what SQL uses). The user_id and challenge_id are column names seemingly, and you need to change these to use field names. See this guide for details.

这篇关于Spring Boot JPA“查询验证失败" JPQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！