Hibernate Native Query Error: Column not found

Question

I am using Java8 with Spring, Hibernate, JPA and MySQL.

I have the following tables:

 +----------+   +-----------------+    +-----------+
 |  PERSON  |   |  RATING_PERSON  |    |  RATING   |
 +----------+   +-----------------+    +-----------+
 |    ID    |   |     PER_ID      |    |    ID     |
 |          |   |     RAT_ID      |    |           |
 +----------+   +-----------------+    +-----------+

Then execute the following code:

    @Override
    public List<Rating> findByRatedBy(Long personId) {
        StringBuilder sb = new StringBuilder();     
        sb.append(" SELECT * FROM ebdb.rating as r ");
        sb.append(" WHERE r.ID = (SELECT rp.RAT_ID ");
        sb.append(" from ebdb.rating_person as rp where rp.PER_ID = :perId) ");
//      sb.append(" SELECT r.* FROM ebdb.rating as r ");
//      sb.append(" INNER JOIN ebdb.rating_person as rp ON r.ID = rp.RAT_ID ");
//      sb.append(" WHERE rp.PER_ID = :perId ");
        Query q = entityManager.createNativeQuery(sb.toString(), Rating.class);
        q.setParameter("perId", personId);
        List<Rating> ratings = (List<Rating>) q.getResultList();
        return ratings;
    }

But get the following error:

WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-7) SQL Error: 0, SQLState: S0022
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-7) Column 'PER_ID' not found.

When I run the exact same SQL statement in MySQLWorkbench, it executes with no problems.

 SELECT * FROM ebdb.rating as r  WHERE r.ID = (SELECT rp.RAT_ID  from ebdb.rating_person as rp where rp.PER_ID = 385)

Question

Can anyone please advise how I can get the Hibernate Native Query to execute this SQL?

Thanks

UPDATE

I tried removing the Rating.class, i.e. changing:

Query q = entityManager.createNativeQuery(sb.toString(), Rating.class);

to:

Query q = entityManager.createNativeQuery(sb.toString());

This partially solves the problem.

I do get a result set:

List<Rating> ratings = (List<Rating>) q.getResultList();

The value of ratings:

[[Ljava.lang.Object;@5b5c9618]

However, when I try use the ratings:

for (Rating rating : ratings) {
   ...
}

I get the following error:

[Ljava.lang.Object; cannot be cast to com.jobs.spring.domain.Rating

More Info:

Rating.java

@Entity
@Table(name="rating")
@XmlRootElement(name="rating")
public class Rating extends AbstractDomain<Long> {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "STARS", nullable = false)
    private Long rating;    

    @Size(min=0, max=500)
    @Column(name = "REVIEW", nullable = false)
    private String review;

    @Column(name = "REVIEW_DATE", nullable = false)
    private Long reviewDate;

    @ManyToOne(fetch=FetchType.EAGER)
    @JoinTable
    (
        name="rating_job",
        joinColumns={ @JoinColumn(name="RAT_ID", referencedColumnName="ID") },
        inverseJoinColumns={ @JoinColumn(name="JOB_ID", referencedColumnName="ID") }
    )
    private Job job;

    @ManyToOne(cascade = CascadeType.ALL, fetch=FetchType.EAGER)
    @JoinTable
    (
        name="rating_person",
        joinColumns={ @JoinColumn(name="RAT_ID", referencedColumnName="ID") },
        inverseJoinColumns={ @JoinColumn(name="PER_ID", referencedColumnName="ID") }
    )
    private Person person;

    @Column(name = "ANONYMOUS", nullable = false)
    private Integer anonymous;

    @XmlElement
    public Integer getAnonymous() {
        return anonymous;
    }

    public void setAnonymous(Integer anonymous) {
        this.anonymous = anonymous;
    }

    @XmlElement
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @XmlElement
    public Long getRating() {
        return rating;
    }

    public void setRating(Long rating) {
        this.rating = rating;
    }

    @XmlElement
    public String getReview() {
        return review;
    }

    public void setReview(String review) {
        this.review = review;
    }

    @XmlElement
    public Long getReviewDate() {
        return reviewDate;
    }

    public void setReviewDate(Long reviewDate) {
        this.reviewDate = reviewDate;
    }

    @XmlElement
    public Job getJob() {
        return job;
    }

    public void setJob(Job job) {
        this.job = job;
    }

    @XmlElement
    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }
}

check this out https://stackoverflow.com/questions/2110809/use-of-entitymanager-createnativequeryquery-foo-class — Nidhi257, Jul 12 '17 at 07:26
Thank you, removing the `Rating.class` from the `createNativeQuery` solved my problem. — Richard, Jul 12 '17 at 07:32
However, now when I try use the object, I get `[Ljava.lang.Object; cannot be cast to com.jobs.spring.domain.Rating`. — Richard, Jul 12 '17 at 07:35
@Darshit Chokshi, thanks for the reply. I have added it in the `More info` section above. — Richard, Jul 12 '17 at 07:54
I guess if remove aliases rp and r and add Query q = entityManager.createNativeQuery(sb.toString(), Rating.class); this will work — Nidhi257, Jul 12 '17 at 08:16
@Nidhi257, thanks for the feedback. I actually did try removing the alias's, but still got the same error. — Richard, Jul 12 '17 at 08:18

score 1 · Answer 1 · answered Jul 12 '17 at 08:09

I am not sure why the native query doesn't work, but I solved this issue by using a hibernate query:

@Override
public List<Rating> findByRatedBy(Person person) {
    final EntityManagerFactory entityManagerFactory = entityManager.getEntityManagerFactory();
    final CriteriaBuilder criteriaBuilder = entityManagerFactory.getCriteriaBuilder();
    CriteriaQuery<Rating> criteria = criteriaBuilder.createQuery(Rating.class);
    Root<Rating> root = criteria.from(Rating.class);
    ParameterExpression<Job> paramJob = criteriaBuilder.parameter(Job.class);
    ParameterExpression<Person> paramPerson = criteriaBuilder.parameter(Person.class);

    criteria.select(root).where(
        criteriaBuilder.equal(root.get("person"), paramPerson)
    );

    List<Order> orderList = new <Order>ArrayList();
    orderList.add(criteriaBuilder.desc(root.get("reviewDate")));
    criteria.orderBy(orderList);
    TypedQuery<Rating> queryRating = entityManager.createQuery(criteria);
    queryRating.setParameter(paramPerson, person);
    List<Rating> results = null;

    results = queryRating.getResultList();

    return results;
}

Hibernate Native Query Error: Column not found

1 Answers1