Spring Hibernate Query could not resolve property

Question

One staff reports to one reporting officer, and one reporting officer can have multiple staffs.

With that said, I have self-mapped them together in Staff.java

//reporting officer
@ManyToOne (cascade = {CascadeType.MERGE, CascadeType.DETACH, CascadeType.REFRESH})
@JoinColumn(name = "reporting_officer_id")
private Staff reportingOfficer;

And in my database

CREATE TABLE `tbl_staff` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `staff_no` INT(5) NOT NULL,
    `staff_name` VARCHAR(25) NOT NULL,
    `login_id` VARCHAR(16) NOT NULL,
    `date_of_birth` DATE NULL DEFAULT NULL,
    `joined_date` DATE NULL DEFAULT NULL,
    `department_id` INT NULL DEFAULT NULL,
    `designation` VARCHAR(255) NULL DEFAULT NULL,
    `reporting_officer_id` INT NULL DEFAULT NULL,
     `photograph` BLOB NULL DEFAULT NULL,
    FOREIGN KEY (`department_id`) REFERENCES tbl_department(`id`),
    PRIMARY KEY (`id`)
);

I'm trying to perform this query...

@Query("FROM Staff t WHERE " + "t.reporting_officer_id=:reportingOfficerId")
    public List<Staff> findStaffsWithSameReportingOfficer(@Param("reportingOfficerId") int reportingOfficerId);

And I'm getting this error...

Caused by: org.hibernate.QueryException: could not resolve property: reporting_officer_id of: com.staff.domain.Staff [FROM com.staff.domain.Staff t WHERE t.reporting_officer_id=1]
    at org.hibernate.QueryException.generateQueryException(QueryException.java:120)
    at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:217)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:545)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:654)
    ... 124 common frames omitted
Caused by: org.hibernate.QueryException: could not resolve property: reporting_officer_id of: com.staff.domain.Staff
    at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:62)
    at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:56)
    at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1859)
    at org.hibernate.hql.internal.ast.tree.FromElementType.getPropertyType(FromElementType.java:393)
    at org.hibernate.hql.internal.ast.tree.FromElement.getPropertyType(FromElement.java:512)
    at org.hibernate.hql.internal.ast.tree.DotNode.getDataType(DotNode.java:660)
    at org.hibernate.hql.internal.ast.tree.DotNode.prepareLhs(DotNode.java:264)
    at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:204)
    at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:109)
    at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:104)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:1033)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1286)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4699)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4169)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2134)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:813)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:607)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:311)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:259)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:261)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCom

Answer 1

@Query("SELECT t FROM Staff t WHERE t.reportingOfficer.id = :reportingOfficerId")
public List<Staff> findStaffsWithSameReportingOfficer(@Param("reportingOfficerId") Integer reportingOfficerId);