在Spring Boot @Query中查询具有一组实体的问题。

huangapple go评论49阅读模式
英文:

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( &quot;select new app.model.Booster(&quot; +
&quot;   B.id, &quot; +
&quot;   B.active, &quot; +
&quot;   B.games, &quot; +
&quot;   B.ownerAccount, &quot; +
&quot;   B.boosts, &quot; +
&quot;   B.boosterNickname, &quot; +
&quot;   B.additionalInformation) &quot; +
&quot;from &quot; +
&quot;   Booster B &quot; +
&quot;   left join Boost Bo on B.id = Bo.boostingAccount.id &quot; +
&quot;where &quot; +
&quot;   B.games like %?1% &quot; +
&quot;   and Bo.finished = true &quot; +
&quot;group by &quot; +
&quot;   B.id&quot;)
List&lt;Booster&gt; findBoostersForOverview(String game);

These are my entity classes:

@Data
@Entity(name = &quot;Booster&quot;)
public class Booster {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
private boolean active;
private String games;
@OneToOne(mappedBy = &quot;boosterAccount&quot;, cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Account ownerAccount;
@OneToMany(mappedBy = &quot;boostingAccount&quot;, cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set&lt;Boost&gt; boosts;
private String boosterNickname;
private String additionalInformation;
@Data
@Entity(name = &quot;Boost&quot;)
public class Boost {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name=&quot;id&quot;,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 = &quot;boosted_account_id&quot;, referencedColumnName = &quot;id&quot;)
private Account boostedAccount;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = &quot;boosting_account_id&quot;, referencedColumnName = &quot;id&quot;)
private Booster boostingAccount;
@OneToMany(mappedBy = &quot;boost&quot;, cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set&lt;Game&gt; 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 &#39;. as col_4_0_, booster0_.booster_nickname as col_5_0_, booster0_.additional_info&#39; at line 1<br><br>
I know that I can query without Set of Boost, and then in another query get specific Boosts, 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&lt;Booster&gt; 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( &quot;select new app.model.Booster(&quot; +
&quot;   B.id, &quot; +
&quot;   B.active, &quot; +
&quot;   B.games, &quot; +
&quot;   B.ownerAccount, &quot; +
&quot;   B.boosts, &quot; +
&quot;   B.boosterNickname, &quot; +
&quot;   B.additionalInformation) &quot; +
&quot;from &quot; +
&quot;   Booster B &quot; +
&quot;   left join Boost Bo on B.id = Bo.boostingAccount.id &quot; +
&quot;where &quot; +
&quot;   B.games like %?1% &quot; +
&quot;   and Bo.finished = true&quot;)
List&lt;Booster&gt; findBoostersForOverview(String game);

huangapple
  • 本文由 发表于 2020年8月1日 11:40:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/63201515.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定