[SOLVED] How to select using CONTAINS instead of LIKE with JPA

How to select using CONTAINS instead of LIKE with JPA

I'm developing small java applications to school projects with PostgreSQL.

Using the JDBC driver I could select a field using the ~ operator to act as CONTAINING instead of concatenating the filter using LIKE.

Let me give you an example:

SELECT * FROM USERS WHERE NAME ~ ? ORDER BY NAME

Since we started using the JPA framework to ease the development, I noticed that the same sample using JPA throws an error when I use the ~ operator...

Error thrown:

An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [select u from Users u where u.name ~ ?1 order by u.name]. 
[30, 41] The expression is not a valid conditional expression.

All code samples I could find on google are made using LIKE instead of CONTAINING.

My question is, does JPA support the containing operator when working with PostgreSQL?

Some info:

JDK 15
Netbeans 12.3
JPA 2.1
PostgreSQL 13.2

Solution

Your intuition is correct because jpa does not support the ~ operator, for further informations you can check the persistence-2_1 oracle link containing the jpa pdf specification with the list of all available jpa operators not including the ~ operator. The comparison operators available are just the following: =, >, >=, <, <=, <> (not equal), [NOT]BETWEEN, [NOT]LIKE,[NOT]IN, IS[NOT]NULL, IS[NOT]EMPTY, [NOT]MEMBER[OF], [NOT]EXISTS