英文:
Problem with querying entities with a set of entities in Spring Boot @Query
问题
我想在JpaRepository中使用Spring Boot的@Query
,以返回具有一组其他实体的实体。我知道我可以使用findAll()
方法,然后只获取我感兴趣的行,但我认为这会慢得多。问题是,我的实体Booster
包含一组其他实体Boost
,当我尝试查询它时,我会收到错误。所以,这是我的查询:
@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_
// 这里有一些不必要的日志
我收到以下错误:
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
集合的情况下进行查询,然后在另一个查询中获取特定的Boost
,但在我的应用程序中将有大量的SQL,我的代码会变得混乱。是否有一种简单的方法来解决我的问题?
英文:
I want to have a Spring Boot @Query
in JpaRepository which returns me entities with a set of other entities. I know that I can use findAll()
method, and then take only those rows that I'm interested in, but I think that this is much slower. The problem is that my entity Booster
contains a Set
of other entities Boost
, and when i try to query it, I get an error. So, this is my 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);
These are my entity classes:
@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;
This is what console shows:
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
And this is error i get:<br>
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
<br><br>
I know that I can query without Set
of Boost
, and then in another query get specific Boost
s, but in my app there will be a lot of SQL and my code will turn to spaghetti. Is there a simple way to solve my problem?
答案1
得分: 1
Sure, here's the translation:
你能否将以下内容添加到你的代码库中,不使用任何 @Query
注解并测试一下?
List<Booster> findByGamesLikeAndBoostsFinishedIsTrue(String game);
英文:
Can you add the following to your repository without any @Query
annotation and see?
List<Booster> findByGamesLikeAndBoostsFinishedIsTrue(String game);
答案2
得分: 0
The problem here, you are using GROUP BY
. When you are using Group By
you have to use aggregate functions (COUNT, MAX, MIN, SUM, AVG)
to group the result-set by one or more columns in the select column. Aggregate functions are mandatory when using GROUP BY
function.
In your case, I hope Group By
is not required. Modify your code like below.
@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);
英文:
The problem here, you are using GROUP BY
. When you are using Group By
you have use aggregate functions (COUNT, MAX, MIN, SUM, AVG)
to group the result-set by one or more columns in the select column. Aggregate functions are mandatory when using GROUP BY
function.
In your case, I hope Group By
is not required. Modify your code like below.
@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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论