0

In JPQL in a where condition I want to do:

book.id like lower(:search)

The problem: book.id is a Long but the :search parameter is a String. Will the above just work the way it is or do I need to explicitly convert book.id to String and if that's the case, how do I do it?

Roland
  • 7,525
  • 13
  • 61
  • 124

2 Answers2

1

I would convert :search parameter to Long and change the query condition to:

book.id = :search

then 

List<Books> books = em.createQuery("... WHERE book.id = :search")
                      .setParameter("search", Long.valueOf(strSearch))
                      .getResultList();

In general processing numbers is faster than strings.

wypieprz
  • 7,981
  • 4
  • 43
  • 46
  • 1
    That wouldn't work because in my case the `:search` parameter is not only used to compare to that `id` but also to the title(users can search for id or title). – Roland Nov 21 '14 at 08:36
  • Hmmm, such a case wasn't part of the initial question ;). If you need to compare `:search` with a title string then `LIKE` is the right choice. Anyway it would be helpful to update the question with the full query definition (even pseudocode) for better understanding the idea behind it. – wypieprz Nov 21 '14 at 21:38
1

You can use the CAST statement in JPQL like:

CAST(book.id AS string) LIKE lower(:search)

But with this solution you won't be able to use any index on the book.id column.

Julio Villane
  • 994
  • 16
  • 28