Hibernate JPQL查询参数类型不匹配异常问题求助

数据库存在Courses和Instructor两张表，Instructor与Courses为一对多关系，Courses与Instructor为多对一关系，通过instructor_id字段维护关联。已创建对应实体类并完成映射，在DAO类中使用JPQL查询Courses表，根据指定instructor_id获取对应所有Courses对象时抛出QueryArgumentException异常，以下是相关代码及排查方案：

表关系说明 #

• Instructor（讲师）与Courses（课程）为一对多关系
• Courses与Instructor为多对一关系，通过instructor_id字段关联

实体类代码 #

Courses实体类 #

@Entity
@Table(name = "Courses")
public class Courses {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private int id;

    @Column(name = "COURSENAME")
    private String courseName;
    
    /*
     * 定义Courses到Instructor的多对一关系
     * 注意：删除课程时无需删除讲师，禁用删除级联操作
     */
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE, 
            CascadeType.REFRESH, CascadeType.DETACH } )
    @JoinColumn(name = "INSTRUCTOR_ID")
    private Instructor instructor_id;

    public Instructor getInstructor_id() {
        return instructor_id;
    }

    public void setInstructor_id(Instructor instructor_id) {
        this.instructor_id = instructor_id;
    }
    // 其他代码省略
}

Instructor实体类 #

@Entity
@Table(name = "instructor")
public class Instructor {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private int id;

    @Column(name = "FNAME")
    private String fName;
    
    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "INSTRUCTOR_DETAILS_ID")
    private InstructorDetails instructorDetails;
    
    /*
     * 定义Instructor到Courses的一对多关系
     * 
     */
    @OneToMany(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH,
            CascadeType.DETACH })
    @JoinColumn(name = "instructor_id")
    private List<Courses> courses;

    public List<Courses> getCourses() {
        return courses;
    }

    public void setCourses(List<Courses> courses) {
        this.courses = courses;
    }
    // 其他代码省略
}

抛出异常的DAO方法 #

@Override
public List<Courses> FindCoursesByInstructorId(int id) {
    
    System.out.println("Searching for id :"+id);

    TypedQuery<Courses>query = this.entityManager.createQuery("SELECT c FROM Courses c WHERE c.instructor_id = :id " , Courses.class);
    
    query.setParameter("id", id);
    List<Courses> l = query.getResultList();
    
    return l;
}

异常信息 #

org.hibernate.query.QueryArgumentException: 参数[44]的类型[java.lang.Integer]与参数类型[com.example.Hibernate02_OneToMany.entity.Instructor (n/a)]不匹配
    at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:85) ~[hibernate-core-6.6.13.Final.jar:6.6.13.Final]
    at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:32) ~[hibernate-core-6.6.13.Final.jar:6.6.13.Final]
    at org.hibernate.query.internal.QueryParameterBindingImpl.validate(QueryParameterBindingImpl.java:366) ~[hibernate-core-6.6.13.Final.jar:6.6.13.Final]
    at org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:142) ~[hibernate-core-6.6.13.Final.jar:6.6.13.Final]
    at org.hibernate.query.spi.AbstractCommonQueryContract.setParameter(AbstractCommonQueryContract.java:835) ~[hibernate-core-6.6.13.Final.jar:6.6.13.Final]
    at org.hibernate.query.spi.AbstractSelectionQuery.setParameter(AbstractSelectionQuery.java:602) ~[hibernate-core-6.6.13.Final.jar:6.6.13.Final]
    at org.hibernate.query.sqm.internal.QuerySqmImpl.setParameter(QuerySqmImpl.java:1061) ~[hibernate-core-6.6.13.Final.jar:6.6.13.Final]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]

问题原因及解决方法 #

原因 #

Courses实体类中instructor_id字段的类型是Instructor实体对象，而JPQL查询中直接用它和Integer类型的讲师ID做比较，类型不匹配导致异常。

解决方法 #

方案1：修改JPQL，通过实体对象的ID属性查询

将JPQL语句改为通过instructor_id.id匹配讲师ID（instructor_id是Instructor对象，其id属性对应数据库中的讲师ID）：

TypedQuery<Courses> query = this.entityManager.createQuery(
    "SELECT c FROM Courses c WHERE c.instructor_id.id = :instructorId", 
    Courses.class
);
query.setParameter("instructorId", id);

方案2：先查询Instructor对象，再作为参数传入

先通过ID获取Instructor实体，再将该对象作为参数传入JPQL查询：

Instructor instructor = entityManager.find(Instructor.class, id);
TypedQuery<Courses> query = this.entityManager.createQuery(
    "SELECT c FROM Courses c WHERE c.instructor_id = :instructor", 
    Courses.class
);
query.setParameter("instructor", instructor);

额外建议：规范实体字段命名

为避免混淆，建议把Courses类中的instructor_id字段名改为instructor，符合Java驼峰命名规范，同时更清晰表示这是实体对象而非数据库字段：

// 修改字段名
private Instructor instructor;

// 对应的getter/setter
public Instructor getInstructor() {
    return instructor;
}

public void setInstructor(Instructor instructor) {
    this.instructor = instructor;
}

修改后JPQL语句对应调整为c.instructor.id或c.instructor，代码可读性更强。

内容的提问来源于stack exchange，提问作者D.Malim