所以我有四张桌子
雇主
@Entity
@EqualsAndHashCode(callSuper = false)
@Table(name = "employers")
@NoArgsConstructor
@AllArgsConstructor
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler", "jobPostings"})
@PrimaryKeyJoinColumn(name="employer_id", referencedColumnName = "id")
public class Employer extends User {
@Column(name = "company_name")
private String companyName;
@Column(name = "website")
private String website;
@Column(name = "phone_number")
private String phoneNumber;
@OneToMany(mappedBy="employer")
private List<JobPosting> jobPostings;
}城市
@Data
@Entity
@Table(name="cities")
@AllArgsConstructor
@NoArgsConstructor
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler", "jobPostings"})
public class City {
@Id
@Column(name="id")
private int id;
@Column(name="city_name")
private String cityName;
@OneToMany(mappedBy="city")
private List<JobPosting> jobPostings;
}工作岗位
@AllArgsConstructor
@Data
@Entity
@Table(name="job_positions")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler", "jobPostings"})
public class JobPosition {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="job_position_id")
private int id;
@Column(name="title")
private String title;
@OneToMany(mappedBy="jobPosition")
private List<JobPosting> jobPostings;
}和JobPosting(就像招聘广告)
@Entity
@Table(name="job_postings")
@Data
@NoArgsConstructor
@AllArgsConstructor
//@JsonIgnoreProperties({"hibernateLazyInitializer", "handler", "city", "jobPosition","employer"})
public class JobPosting {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id")
private int id;
@Column(name="job_requirements")
private String jobRequirements;
@Column(name="salary_min")
private int salaryMin;
@Column(name="salary_max")
private int salaryMax;
@Column(name="application_deadline")
private LocalDate applicationDeadline;
@Column(name="number_of_openings")
private int numberOfOpenings;
@Column(name="stream_date")
private LocalDate streamDate;
@ManyToOne()
@JoinColumn(name="city_id")
private City city;
@ManyToOne()
@JoinColumn(name="job_position_id")
private JobPosition jobPosition;
@ManyToOne()
@JoinColumn(name= "employer_id")
private Employer employer;
}我已经为所有这些人实现了必要的映射,而且它运行得很好。不过,我想加入他们的DTO,例如:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class JobPostingWithJobPositionCityEmployerDto {
private int id;
private String jobRequirements;
private int salaryMin;
private int salaryMax;
private LocalDate applicationDeadline;
private int numberOfOpenings;
private LocalDate streamDate;
private String cityName;
private String title;
private String companyName;
}为了以更清晰的方式获得我想要的字段,我尝试使用springframework.jpa的@Query注释,但是我无法完全管理它,因为我刚刚了解到这一点,我使用的查询是:
@Query(value ="Select new kodlamaio.hrms.entities.dtos.JobPostingWithJobPositionCityEmployerDto"
+ "(j.id, j.jobRequirements, j.salaryMin, j.salaryMax, j.numberOfOpenings, j.streamDate, j.applicationDeadline, c.cityName, p.title, e.companyName)"
+ " From Employer e Inner Join e.jobPostings j, "
+ "From City c Inner Join c.jobPostings j, "
+ "From JobPosition p Inner Join p.jobPostings j", nativeQuery = true)
List<JobPostingWithJobPositionCityEmployerDto> getJobPostings();我甚至不知道这是否是正确的方法,我不断得到语法错误,我查找答案,但不能完全理解他们说了什么,有很多不同的情况。所以,如果有人能帮我完成这个查询,并推荐一些来源来了解不同的命令,我会非常感激的,谢谢。
发布于 2021-06-10 05:46:24
在查询中使用new关键字将查询结果与DTO进行映射,仅适用于JPQL,它将不适用于SQL (这是您正在使用的)。
在我看来,如果您试图编写一个过于复杂的查询,可以通过JobPosting类实现/访问所有内容,这将在使用JPQL时隐式地执行连接。
因此,与其编写原生SQL,不如编写一个JPQL来修复它。
有点像
@Query(value ="Select new kodlamaio.hrms.entities.dtos.JobPostingWithJobPositionCityEmployerDto"
+ "(jp.id, jp.jobRequirements, jp.salaryMin, jp.salaryMax, jp.numberOfOpenings, jp.streamDate, jp.applicationDeadline, jp.city.cityName, jp.jobPosition.title, jp. employer.companyName)"
+ " From JobPosting jp)这应该能起作用。您的JPA提供程序应该足够聪明,能够找到要加入和检索的内容。
发布于 2021-06-10 06:05:03
一般说来,这就是所谓的投影,我相信您已经创建了一个包含10个args的构造函数的JobPostingWithJobPositionCityEmployerDto,当然还有它们各自的数据类型。而且,由于jpa是自定义的,所以不能使用nativeQuery = true。这些修改应该没问题。
@vlad呼叫救援https://vladmihalcea.com/the-best-way-to-map-a-projection-query-to-a-dto-with-jpa-and-hibernate/
https://stackoverflow.com/questions/67912813
复制相似问题