在Spring Boot中从本机SQL转换为JPA以返回列表

Question

您好，我们正在尝试返回包含技能列表的工作列表。这是我们的代码：

@Query(nativeQuery =true, value="select * from jobs j inner join( select * from job_skills js  where skill_id IN (?1.skill_id)) on j.job_id = js.job_id")
    List<Job> findBySkills(List<Skill> skills);

然而，当我们使用Postman进行测试并发送POST请求时，我们会得到以下错误：

组织。冬眠QueryException:JPA风格的位置参数不是整数序号；

我们将Spring Boot与PostgreSQL一起使用。我们假设问题出在这里（？1.skill_id）我们如何修复此问题以使用查询的ID从作业表中返回所有技能的列表？

这是我们的工作模型和JPA映射

@Entity
@Table(name = "jobs")
public class Job {

    @Id
    @Column(name = "job_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    /** An integer that uniquely identifies this job. */
    private int id;

    @Column(name = "job_title")
    /** The name of this job. */
    private String title;

    @Column(name = "job_description", length=10_000)
    /** A description of this job, with a maximum length of 10,000 characters. */
    private String description;

    @Column(name = "job_location")
    /** A string identifying where the job takes place. */
    private String location;

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(name = "job_skills", joinColumns = { @JoinColumn(name = "job_id") }, inverseJoinColumns = {
            @JoinColumn(name = "skill_id") })
    /** A set of skills that candidates applying to this job are expected to have. */
    private Set<Skill> skills = new HashSet<>();

    @Column(name = "job_isFilled")
    /** Returns true if the job opening is currently filled, and false otherwise. */
    private boolean isFilled;

    @OneToMany(mappedBy="job")
    private Set<Interview> interviews = new HashSet<>();

    @OneToOne
    @JoinColumn(name = "filled_by_profile_id")
    /** The employee that currently holds this job. Returns null if it is not held by any employee. */
    private Profile profile;

    /** Creates a new job with all properties set to their default values. */
    public Job() {
        super();
    }

这是我们的技能模型（仅显示相关映射）

@Entity
@Table(name = "skills")
public class Skill {

    @Id
    @Column(name = "skill_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    /** An integer that uniquely identifies this skill. */
    private int id;

    @Column(name = "skill_title")
    /** The name of this skill. */
    private String title;

    @ManyToMany(fetch = FetchType.EAGER, cascade = { CascadeType.MERGE }, mappedBy = "skills")
    @Column(name = "profiles")
    /** A set of candidates who claim proficiency in this skill. */

    @JsonIgnore
    private Set<Profile> profiles = new HashSet<>();

    @ManyToMany(fetch = FetchType.EAGER, cascade = { CascadeType.MERGE }, mappedBy = "skills")
    @Column(name = "jobs")
    /** A set of jobs that this skill is necessary for. */
    @JsonIgnore
    private Set<Job> jobs = new HashSet<>();

    /** Creates a new skill with all properties set to their default values. */
    public Skill() {
        super();
    }

Answer 1

skills实体中的属性名称是id，而不是skill\u id

选择*从作业j内连接（选择*从job_skillsjsskill_id在哪里？1.skill_id））j.job_id=js.job_id

@Query(nativeQuery =true, value="select * from jobs j inner join( select * from job_skills js  where skill_id IN (?1.id)) on j.job_id = js.job_id")
    List<Job> findBySkills(List<Skill> skills);