java - 按集合大小排名前5位的记录

我有以下实体：

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股票实体。到目前为止，我在sql中编写本机查询，它看起来像：

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

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

最佳答案

假设您的实体映射如下。

@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是您的朋友。

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();
}

关于java - 按集合大小排名前5位的记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30817772/