JPA Like Operator with Integer

Question

Can someone tell me, why this is not working:

criteria.add(cb.like((myentity.<Integer>get("integerid")).as(String.class), "2%"))

I get the following error:

The object [2%], of class [class java.lang.String], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[integerid-->MYENTITY.INTEGERID]] with descriptor [RelationalDescriptor(org.example.model.MyEntity --> [DatabaseTable(MYENTITY)])], could not be converted to [class java.lang.Integer]

Is the only solution to change the myinteger property to a string in the model, in order that I can use the like operator?

BR

Answer 1

Here are example of how it work with JPA, so u can use LIKE with integer inside your JPQL.

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

Note: i tested it and worked fine with me, i am using JPA 2.1 with Hibernate provider.

Answer 2

JPA does not support like() with Integers, only Strings. Some databases do support like with integer and others do not.

EclipseLink should allow usage of like() with Integer (provided your database supports it). What version are you using? May need to use >= 2.1. If it fails on the latest version, then please log a bug.

You can also convert the integer to a string using a "CHAR, "TO_CHAR" or "CONVERT" function depending on your database. The criteria API supports a function() API to call a native function.

Note that the as() API is not intended for converting from Integer to String, it is for casting to a subclass entity.

Answer 3

My guess : you are trying to put a String in integerId which is a Integer.

Moreover, a like on an integer value is not possible.

Change your String.class to Integer.class. and "2%" by an integer value

Answer 4

I have faced the same issue. I use JPA2.0 and EclipseLink 2.3 as implementation.

The column codeClient is actually an Integer in my DataBase.

The annotation @Convert simply convert my Integer column to a String column.

In that way i can use a like predicate over numbers.

@Entity
@Table(name="COLIS_SO_VM")
public class Colis implements Serializable {

    ...

    @Convert
    private String codeClient;

    ...
}

I know this is an old post but if it can help someone else !