HQL query throws QueryException with working SQL query

Question

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.

Add an alias for Course object and put for it the complete class path — Joe Taras
– Joe Taras, Commented May 2, 2018 at 14:58

Wilder Valera · Accepted Answer · 2018-05-02 13:46:49Z

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(..)

Collectives™ on Stack Overflow

HQL query throws QueryException with working SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related