在Spring Boot@Query中使用一组实体查询实体时出现问题

Question

我想在JpaRepository中有一个Spring Boot@Query，它会返回一组其他实体。我知道我可以使用findAll()方法，然后只取那些我感兴趣的行，但我认为这要慢得多。问题是，我的实体Booster包含其他实体Boost的Set，当我尝试查询它时，我得到了一个错误。这是我的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;

这就是控制台显示的内容：

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

我知道我可以在没有Boost的Set的情况下进行查询，然后在另一个查询中获得特定的Boosts，但在我的应用程序中会有很多SQL，我的代码会变成意大利面条。有没有一种简单的方法来解决我的问题？

Answer 1

您是否可以在没有任何@Query注释的情况下将以下内容添加到您的存储库中并查看？

List<Booster> findByGamesLikeAndBoostsFinishedIsTrue(String game);

Answer 2

这里的问题是，您使用的是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);