I have 2 tables in database "Courses" and "Instructor" with "Instructor" table having one to many relation with "Courses" table and "Courses" table having many to one relation with same.
Means one instructor can have multiple courses where relationship is maintained using "instructor_id" column.
Now I have created entity classes for both of them with mappings accordingly as shown below.
Further I have implemented a DAO class where I'am using JPQL to access "Courses" table.
My requirement is to get all the "Courses" objects for a particular "instructor_id" using JPQL but Iam getting "QueryArgumentException" for same.
I'm guessing I have missed something any help would be appreciated.
Please find the method where JPQL is implemented listed below.
Table relationship shown below.
Entity 01
@Entity
@Table(name = "Courses")
public class Courses {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private int id;
@Column(name = "COURSENAME")
private String courseName;
/*
* Define many to one relation ship from courses to instructor table
* PLEASE NOTE: we need to disable delete operation in case any course
* is deleted we don't need to delete the 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;
}
and so on
...
Entity 02
@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;
/*
* Defining one to many relation ship for one instructor to many 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;
}
Method giving exception
@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;
}
Exception generated
org.hibernate.query.QueryArgumentException: Argument [44] of type [java.lang.Integer] did not match parameter type [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]
Did you try passing an Instructor
entity instead of just an id?
@Override
public List<Courses> FindCoursesByInstructorId(int id) {
Instructor instructor = entityManager.find(Instructor.class, id); // Load the entity by ID
TypedQuery<Courses> query = entityManager.createQuery(
"SELECT c FROM Courses c WHERE c.instructor_id = :instructor", Courses.class
);
query.setParameter("instructor", instructor);
return query.getResultList();
}