spring-data-jpa - 附加表上的 JPA 存储库查询 @ManytoMany
问题描述
我想在我的 jpa spring 存储库中做这样的选择
SELECT sicknes_id, count(symptomp_id) as ilosc FROM symptomp_sicknes where symptomp_id IN (1,2) group by sicknes_id Order by ilosc DESC;
我的实体
@Entity
@Table(name = "symptomp")
public class Symptomp {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "symptomp_id")
private Long symptomp_id;
@Column(name = "name")
private String name;
@Column(name = "description")
private String description;
@ManyToMany(cascade = {CascadeType.DETACH,CascadeType.MERGE,CascadeType.PERSIST,CascadeType.REFRESH}, fetch = FetchType.LAZY)
@JoinTable(name = "symptomp_sicknes",joinColumns = @JoinColumn(name = "symptomp_id"),inverseJoinColumns = @JoinColumn(name = "sicknes_id"))
private Set<Sicknes> sicknes = new HashSet<>();
@Entity
@Table(name = "sicknes")
public class Sicknes {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "sicknes_id")
private Long sicknes_id;
@Column(name = "name")
private String name;
@Column(name = "description")
private String description;
@ManyToOne(cascade = {CascadeType.DETACH,CascadeType.MERGE,CascadeType.PERSIST,CascadeType.REFRESH}, fetch = FetchType.LAZY)
@JoinColumn(name = "speciesId")
private Species species;
我的症状存储库:
public interface SymptompRepository extends JpaRepository<Symptomp, Long> {
@Query("select p from Symptomp p where name like ?1%")
public List<Symptomp> findAllBySymptompName(String symptomp);
public Symptomp findByName(String symptomp);
public List<Symptomp> findByNameIn(List<String> list);
Integer countDistinctSymptompByName(String id);
}
如何在我的 JPA 存储库中创建此选择?我尝试像在 select 中一样获取值,但我得到了错误映射 bean。
解决方案
您可以List<Object[]>
使用nativeQuery=true
参数获取查询结果
@Query("SELECT sicknes_id, count(symptomp_id) as ilosc FROM symptomp_sicknes where symptomp_id IN (1,2) group by sicknes_id Order by ilosc DESC", nativeQuery=true)
List<Object[]> getQueryResult();
其他选项是使用适当的构造函数创建dto
类
public class QueryResultDto {
Long sicknesId;
Long count;
public QueryResultDto(Long sicknesId, Long count) {
this.sicknesId = sicknesId;
this.count = count;
}
}
然后使用 JPQL
@Query("select new yourproject.dtopath.QueryResultDto(...")
List<QueryResultDto> getQueryResult(@Param("symptompIds") List<Long> symptompIds);
推荐阅读
- javascript - Chart.js 如何制作垂直虚线
- python - Bool 没有属性 fetchone,即使它是一个游标
- azure - Kubernetes Dashboard 右上角没有用户图标
- apache-flink - flink 中的自定义指标组
- javascript - React Firebase 应用程序身份验证服务在生产中不起作用
- python - 使用 CSV 文件和 python 的多图像下载器
- java - 上下文类加载器和在依赖项之一中加载资源的问题
- python - 如何在 Python 中对 GENIA 语料库进行 XML 解析
- php - WordPress 使用工作缩略图重命名附件文件
- graphql - 主键与 Amplify Schema 文件的关系