CODEWITHSUNDEEP
javajpajpql
Nelly Junior
Nelly Junior

Reputation: 566

convert BigDecimal to String in order to filter some BigDecimal ids using LIKE in JPQL query

I want to filter BigDecimal ids using LIKE keyword in JPQL. I use EclipseLink.

Let say we have ids: 197718, 182123, 182912, 123456 and I want to extract all rows from the table where the entity.id like %18%. If the operation would be successful, the result would be: 182123, 182912.

My query would be. 

public List<MyEntity> getFilteredMyEntity(String idStr) {
   return entityManager.createQuery("select m from MyEntity m where m.id like :idStr")
                       .query.setParameter("idStr", "%" + idStr+ "%")
                       .getResultList();
}

Let say I call this method this way in order to get all entities that contains "18" in the id: 

List<MyEntity> entities = getFilteredMyEntity("18");

But I get an exception because I can't compare a BigDecimal with a String. So, I have to convert this BigDecimal to String

Is there a way in jpql (not native query) to convert BigDecimal to String? Something like to_char().

Upvotes: 1

Views: 1993

Answers (1)

mibrahim.iti
mibrahim.iti

Reputation: 2060

You can do it in JPQL like next (i tested it my side and it worked with me)

Note: i am using JPA 2.1 with Hibernate provider

public List<MyEntity> getFilteredMyEntity(String idStr) {
   return entityManager.createQuery("SELECT m FROM MyEntity m WHERE CAST( m.id AS string ) LIKE :idStr")
         .setParameter("idStr", "%" + idStr+ "%").getResultList();
}

And here are another way using native query

public List<MyEntity> getFilteredMyEntity(String idStr) {
   return entityManager.createNativeQuery("SELECT m FROM MyEntity m WHERE CAST(m.id AS varchar(10)) LIKE :idStr")
         .setParameter("idStr", "%" + idStr+ "%").getResultList();
}

Upvotes: 2

Related Questions