Escape character in LIKE through Criteria api

I'm trying to allow users from a web front end to search for some attributes in a database. The attribute names may include the special SQL characters % and _ used in like. Is there a way to escape the special characters?

I'm using Criteria API with Glassfish 5.1, Apache Derby, JPA 2.1 and EclipseLink 2.7.4.

Other databases use \ as a default escape character but Derby doesn't. From reading, it seems the strict standard calls for no implicit escape characters.

I'll illustrate with this data:

ID           SETNAME             DESCRIPTION                                         
----------------------------------------------
1            Set_1               The very first set
2            Set%2               The second set

and SQL direct to Derby through JDBC. The tables are generated from the below entities.

SELECT * FROM ATTRIBUTESETMETA WHERE SETNAME LIKE 'Set%2'

returns both rows, as you'd expect (% = wildcard).

I'm looking to replicate the following in Criteria API

SELECT * FROM ATTRIBUTESETMETA WHERE SETNAME LIKE 'Set\%2' ESCAPE '\'

which just returns the first row because \ is explicitly set as the escape character.

If I use

SELECT * FROM ATTRIBUTEMETA WHERE SETNAME LIKE 'Set\%2'

without the escape clause, I get nothing (unlike other DBs)

Here are the entity class and a wrapper to execute the criteria api query.

/* Imports ... */
@Entity
public class AttributeSetMeta implements Serializable {
    private static final long serialVersionUID = 3L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String setName;
    private String description;

// Getters and setters ...

// Imports
@Dependent
public class AttributeSetMetaFacade {

    @PersistenceContext(unitName = "oms")
    private EntityManager em;

    public AttributeSetMeatFacade() {
    }

    public List<AttributeSetMeta> getSetByName(String name) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<AttributeSetMeta> cq = 
                cb.createQuery(AttributeSetMeta.class);
        Root<AttributeSetMeta> set = cq.from(AttributeSetMeta.class);
        cq.select(set)
                .where(cb.like(
                    set.get(AttributeSetMeta_.setName), name)
                );
        return em.createQuery(cq).getResultList();
    }

// Other code ...

The code is equivalent to my first SQL query and returns the same results.

Is there any way to change the code to make it behave like my second query? (pun intended)

Solution

In JPA, CriteriaBuilder's like method has several overloads which have a third parameter, that is the escape character. It can be either of types char or Expression<Character>.

So, for your case, easiest way is to go like this:

cb.like(set.get(AttributeSetMeta_.setName), name, '\\')

Relevant API references: