Reputation: 196
I'm working with HSQLDB and Hibernate, and I want to perform search requests from my REST API.
For example, for a REST request like localhost:8080/search?token=a%20e
, my method should create the following query: FROM Course WHERE Course.description LIKE '%a%' OR Course.description LIKE '%e%'
and I get this exception:
javax.servlet.ServletException: java.lang.IllegalArgumentException: org.hibernate.QueryException: Unable to resolve path [Course.description], unexpected token [Course] [FROM model.Course WHERE Course.description LIKE '%a%' OR Course.description LIKE '%e%']
This is the code in SearchService
's method for searching Course
by description
or name
.
public List<Course> searchCourses(String token, MatchIn column) {
// Prepare and clean token, leaving only key words
String[] keyWords = token.split(" ");
// Build query and ask database to retrieve relevant courses
StringBuilder sb = new StringBuilder("FROM Course WHERE ");
String colName = "Course.";
if(column.equals(MatchIn.DESCRIPTION)) colName += "description";
else if(column.equals(MatchIn.NAME)) colName += "name";
sb.append(colName);
int i = 0;
sb.append(" LIKE \'");
sb.append("%");
sb.append(keyWords[i]);
sb.append("%\'");
if(keyWords.length != 1){
i++;
for (; i < keyWords.length; i++) {
sb.append(" OR " + colName +
" LIKE \'");
sb.append("%");
sb.append(keyWords[i]);
sb.append("%\'");
}
}
Query query = session.createQuery(sb.toString());
return query.list();
}
Note that in the exception I'm receiving, it says that my method is actually creating the following query: FROM *model.*Course WHERE Course.description LIKE '%a%' OR Course.description LIKE '%e%'
When I try SELECT * FROM Course WHERE c.description LIKE '%a%' OR c.DESCRIPTION LIKE '%e%';
in IDEA's SQL console, it runs successfully. (I'm not using SELECT *
in the query I'm creating because HQL doesn't use it)
I'm new to HQL and SQL, so I don't know where the problem is.
EDIT:
In Debugger mode I found the exact place where the exception is being called. There seems to be a problem with Hibernate:
I don't know what's causing this issue.
Upvotes: 0
Views: 244
Reputation: 1019
HQL works with binding parameters, so adding directly LIKE '%key%' won't work. Hibernate will convert the HQL to a SQL so to achieve that you may do this:
for (; i < keyWords.length; i++) {
sb.append(" OR " + colName + " LIKE " + "key" + String.valueOf(i));// notice that I'm not adding the '%%'
}
then you have to bind the parameters:
Query query = session.createQuery(sb.toString());
for (int j = 0; j < keyWords.length; j++) {
query.setParameter("key" + String.valueOf(j), "%" + keyWords[j] + "%")
}
As you can see it's a lot of code for a simple query.
So basically you have 2 options:
Create a native SQL. session.createSQLQuery(...)
Use Criteria.
String colName = "";
if(column.equals(MatchIn.DESCRIPTION)) {
colName = "description";
} else if(column.equals(MatchIn.NAME)) {
colName = "name";
}
Criteria criteria = session.createCriteria(Course.class)
for(String key : keyWords) {
criteria.add(Restrictions.or(Restrictions.like( colName, "%" + key + "%"));
}
return criteria.list();
TIPS:
DO NOT CONCAT YOUR PARAMS. USE query.setParameter(..)
Upvotes: 1