java—在spring boot@query中使用一组实体查询实体时出现问题

aamkag61  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(348)

我想要一双Spring Boot @Query 在jparepository中,它返回带有一组其他实体的实体。我知道我可以用 findAll() 方法,然后只取我感兴趣的那些行,但我认为这要慢得多。问题是我的实体 Booster 包含 Set 其他实体 Boost ,当我尝试查询它时,得到一个错误。所以,这是我的 Query :

@Query( "select new app.model.Booster(" +
            "   B.id, " +
            "   B.active, " +
            "   B.games, " +
            "   B.ownerAccount, " +
            "   B.boosts, " +
            "   B.boosterNickname, " +
            "   B.additionalInformation) " +
            "from " +
            "   Booster B " +
            "   left join Boost Bo on B.id = Bo.boostingAccount.id " +
            "where " +
            "   B.games like %?1% " +
            "   and Bo.finished = true " +
            "group by " +
            "   B.id")
    List<Booster> findBoostersForOverview(String game);

以下是我的实体类:

@Data
@Entity(name = "Booster")
public class Booster {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    private boolean active;
    private String games;
    @OneToOne(mappedBy = "boosterAccount", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    private Account ownerAccount;
    @OneToMany(mappedBy = "boostingAccount", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    private Set<Boost> boosts;
    private String boosterNickname;
    private String additionalInformation;
@Data
@Entity(name = "Boost")
public class Boost {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id",nullable=false,unique=true)
    private long id;
    private Date dateCreated;
    private Date dateFinished;
    private Date dateLastModification;
    private boolean finished;
    private boolean paused;
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "boosted_account_id", referencedColumnName = "id")
    private Account boostedAccount;
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "boosting_account_id", referencedColumnName = "id")
    private Booster boostingAccount;
    @OneToMany(mappedBy = "boost", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    private Set<Game> games;
    private String game;
    private String additionalInformation;
    private String server;

以下是console显示的内容:

Hibernate: 
    select
        booster0_.id as col_0_0_,
        booster0_.active as col_1_0_,
        booster0_.games as col_2_0_,
        booster0_.id as col_3_0_,
        . as col_4_0_,
        booster0_.booster_nickname as col_5_0_,
        booster0_.additional_information as col_6_0_ 
    from
        booster booster0_ 
    //HERE IS SOME MORE LOGS THAT ARE UNNECESSARY HERE

这是我得到的错误: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. as col_4_0_, booster0_.booster_nickname as col_5_0_, booster0_.additional_info' at line 1 我知道我可以不经你的允许 SetBoost ,然后在另一个查询中 Boost s、 但在我的应用程序中会有很多sql,我的代码会变成意大利面条。有什么简单的方法可以解决我的问题吗?

6tr1vspr

6tr1vspr1#

你能将以下内容添加到你的存储库中吗 @Query 你看呢?

List<Booster> findByGamesLikeAndBoostsFinishedIsTrue(String game);
afdcj2ne

afdcj2ne2#

这里的问题,你用的是 GROUP BY . 当你使用 Group By 你必须使用聚合函数 (COUNT, MAX, MIN, SUM, AVG) 按“选择”列中的一列或多列对结果集进行分组。使用时必须使用聚合函数 GROUP BY 功能。
对你来说,我希望 Group By 不需要。如下所示修改代码。

@Query( "select new app.model.Booster(" +
        "   B.id, " +
        "   B.active, " +
        "   B.games, " +
        "   B.ownerAccount, " +
        "   B.boosts, " +
        "   B.boosterNickname, " +
        "   B.additionalInformation) " +
        "from " +
        "   Booster B " +
        "   left join Boost Bo on B.id = Bo.boostingAccount.id " +
        "where " +
        "   B.games like %?1% " +
        "   and Bo.finished = true")
List<Booster> findBoostersForOverview(String game);

相关问题