java - 按馆藏规模排名前 5 的记录

Question

我有以下实体:

public final class Stock implements Serializable {

    @JsonIgnore
    @ManyToMany(mappedBy = "stocks", fetch = FetchType.LAZY)
    private Set<User> users = new HashSet<>();

    [Other fileds]

    [getters/setters]
}

我想在 jpql 中编写查询，以根据设置用户的大小获取 top5 Stock 实体。到目前为止，我在 sql 中编写 native 查询，它看起来像:

SELECT s.ticker, COUNT(s.ticker)
FROM t_stock s INNER JOIN t_user_stocks us ON s.id = us.stock_id 
INNER JOIN t_user u on us.user_id = u.id GROUP BY s.ticker ORDER BY count DESC

我想要一个返回前 5 名股票实体的 jqpl 查询。有人可以帮助我吗？

Answer 1

假设您的实体映射如下。

@Entity
public class Stock {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column
    private String ticker;

    @JsonIgnore
    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinTable(name = "stock_user", joinColumns = { @JoinColumn(name = "STOCK_ID", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "USER_ID", nullable = false, updatable = false) })
    private Set<User> users = new HashSet<User>();  
}

我使用原生 SQL 执行了以下操作来获取结果。如果您坚持使用 JPQL，答案 here是你的 friend 。

public interface StockRepository extends JpaRepository<Stock, Integer> {

    @Query(value = "SELECT  s.ticker, COUNT(s.ticker) FROM stock s INNER JOIN "
            + "stock_user us ON s.id = us.stock_id  INNER JOIN user u on us.user_id = u.id GROUP BY s.ticker order by count(*) desc limit 1", nativeQuery = true)
    public List<Object[]> findStock();
}