JPQL LIKE not working

Question

I have a JPQL query like this:

@NamedQuery(
        name = "UserAccount.searchEmail",
        query = "SELECT u FROM UserAccount u WHERE u.email LIKE :searchText")

I then set the searchText parameter with this code:

    public List<UserAccount> search(String namedQuery, String searchText) {
    Query query = getEntityManager().createNamedQuery(namedQuery);
    query.setParameter("searchText", "%" + searchText + "%");
    try
    {
        return query.getResultList();
    }
    catch(Exception e)
    {
        logger.log(Level.SEVERE, "Error when searching UserAccount",e);
    }
    return null;
}

As noted in my JPQL NamedQuery, UserAccount has a field called email. When I run this code with the serachText as "whatever", it doesn't return anything. However, when I run the corresponding MySql SQL statement of:

select * from user_account where email like '%whatever%';

It works as expected. Any ideas what may be wrong with either my JPQL statement of my Java code?

and the SQL invoked by the JPA implementation is ? in its log. — Neil Stockton, Aug 20 '14 at 15:56
@NeilStockton Good idea. How can I find the SQL invoked by JPA? WHere would that be logged at? — user489041, Aug 20 '14 at 15:56
@NeilStockton Nevermind, I found the answer here: http://stackoverflow.com/questions/4362876/how-to-view-the-sql-queries-issued-by-jpa . I will run the code and post here in a sec. — user489041, Aug 20 '14 at 16:01
Here's what I would do. 1. remove the catch block and let the exception bubble as it should, to make sure I don't miss it. 2. use the debugger and check that the searchText is what you think it is. 3. test with a blank string and check that it retrieves all the rows in the table. If all those tests fail, it's probably that your table is indeed empty, either because you clear it when starting the app, or because you're not using the database you think you're using, or because the data you see when executing the SQL hasn't been committed yet. — JB Nizet, Aug 20 '14 at 16:15

score 0 · Answer 1 · answered Aug 20 '14 at 16:08

I found my answer. It was a logical error on my part of me calling the wrong NamedQuery. I wanted to answer this on my own because it was noted in the comments that I should look at the REAL SQL being executed. This is what allowed me to see what is going on.

If you are unsure how to do that... As I was, I posted a link to the answer in the comments.

JPQL LIKE not working

1 Answers1