Java Persistence Query Language

How to use LIKE expression in Java Persistence Query Language

Java Persistence Query Language (JPQL) is used to define queries over entities and their persistent state. JPQL enables the application developer to specify the semantics of queries in a portable way, independent of the particular database in use in an enterprise environment.

Here comparison operator LIKE expression is further discussed because it is a bit trick in Java environment.

The syntax for the use of the comparison operator LIKE in a conditional expression is as follows:

string_expression LIKE pattern_value [ESCAPE escape_character]

The string_expression must have a string value. The pattern_value is a string literal or a string-valued input parameter in which an underscore (_) stands for any single character, a percent (%) character stands for any sequence of characters. The optional escape_character is a single-character string literal or a character-valued input parameter and is used to escape the special meaning of the underscore and percent characters in pattern_value.

For example,

aword.underscored LIKE '\_%' ESCAPE '\' is true for '_foo" and false for 'bar'

In Java environment, since '\' is also an escape character, to make the above query working, the code should be like:

// Create EMF for a persistence unit called j2seEnvironment.
EntityManagerFactory emf =
Persistence.createEntityManagerFactory("j2seEnvironment");

// create EM
EntityManager em = emf.createEntityManager();

// get Transaction
EntityTransaction tx = em.getTransaction();

// create Item and insert into database
Item item = new Item();
item.setItemId(new Integer(1));
item.setName("Jie Leng");
item.setStatus(Item.EmployeeStatus.FULL_TIME);
tx.begin();
em.persist(item);
tx.commit();

// run an EJBQL query using input parameters
String ejbql = "SELECT i from Item i WHERE i.name LIKE :pattern ESCAPE
:esc";
Query query = em.createQuery(ejbql);
query.setParameter("pattern", "\\_%");
query.setParameter("esc", '\\');
List result = query.getResultList();

As you can see, two backslashes are used in setParameter(). The first backslash is processed by Java compiler and the second backslash is processed by JPQL compiler. And the result is to match string having '_'.

Here is another piece of code working without using input parameters:

String ejbql = "SELECT i FROM Item i WHERE i.name LIKE '\\_%' ESCAPE '\\'";
Query query = em.createQuery(ejbql);
List result = query.getResultList();

If your code is like:

String ejbql = "SELECT i FROM Item i WHERE i.name LIKE '\_%' ESCAPE '\'";

Or like:

// run an EJBQL query using input parameters
String ejbql = "SELECT i from Item i WHERE i.name LIKE :pattern ESCAPE
:esc";
Query query = em.createQuery(ejbql);
query.setParameter("pattern", "\_%");
query.setParameter("esc", '\');

An illegal escape character error is thrown.

Posted at 05:00AM Apr 07, 2006 by jielin in Sun  |  Comments[3]