"org.hibernate.QueryException: Unable to resolve path..." error with parametrized queries on JPA

Question

I'm having a poblem on the first argument of the following method with the parametrized SQL query:

lista = miDao.find("SELECT c FROM Idioma WHERE c.palabra =:param", o1 , entityManager);

where:

String o1= "playa";
List<Object> lista;

The table "Idioma" has 3 columns "palabra", "idioma" and "wordId". The method is expected to look for the word "playa" within the column "palabra" and calls to the following Dao method:

@SuppressWarnings("unchecked")
public <T> List<T> find(String queryString, Object param, EntityManager em) {
    Query query = em.createQuery(queryString);
    query.setParameter( "param" , param);
    return query.getResultList();
}

When I run the program I'm "rollbacked" with:

Unable to resolve path [c.palabra], unexpected token [c] [SELECT c FROM com.aprendeidiomas.entity.Idioma WHERE c.palabra =:param]

I'm sure I have a problem with my parametrized sql query. Even though I have red a lot of documents, I cannot solve my error. Could you pleas tell me what is wrong with my parametrized query?

Thank you very much in advance.

Answer 1

First, what you are using is not SQL but JPQL (or HQL as an extension).

Next, the error message already tells you what is wrong: c is unknown, hence c.palabra cannot be resolved. I assume you mean to select all idioms which match the word in param and your Idioma entity looks like this:

//Annotations and methods omitted for simplicity
class Idioma {
   String palabra; 
}

Thus your query should state that c is the alias for Idioma, i.e.
SELECT c FROM Idioma c WHERE c.palabra =:param.

A short break down:

• SELECT c - select whatever is meant by c
• FROM Idioma c - select Idioma entities and give them the alias c so that the query will know to return the entities that match
• WHERE c.palabra = :param - this condition means that all entities having the value passed as param (which is the value of o1 in your example) match their palabra property.

As a clarification: after query.setParameter( "param" , param); the query internally might look like this:

SELECT c FROM Idioma c WHERE c.palabra = "playa"

Side note: If you want to select the words instead, use SELECT c.palabra ....

Answer 2

I guess chould be an instace of Idioma ? So you have to add the alias to your statemen:

lista = miDao.find("SELECT c FROM Idioma c WHERE c.palabra =:param", o1 , entityManager);