Search code examples
mysqljpacriteria

JPA NamedNativeQuery is causing a SQL Exception error code 1064

Could someone explain why I am getting the following error code in a NamedNativeQuery. The syntax to me looks absolutely correct. I am using MySQL. I have tried providing the position as ?1 and same thing.

@NamedNativeQuery(name = "Category.findRandomByLimit", 
        query = "SELECT * FROM Category ORDER BY RAND() LIMIT ?")

Should I look into the Criteria API for the order by? I would think that a native query would work instead. I also tried removing the LIMIT and the parameter and using setMaxResults but I still get the error code.

public List<Category> findRandom(int limit) {
    return getEntityManager()
            .createNativeQuery("Category.findRandomByLimit")
            .setParameter(1, limit)
            .getResultList();
}
Solution

  • You're calling createNativeQuery when you should be using createNamedQuery.