How can I use JPA to search for a substring in a field?

Question

My application uses JPA to access the backend database.

I have a Java class mapped to a table. The class has a row field (called status), which consists of a series of "0" and "1". I need to select multiple records based on the second character of the field. Here is what I can do without using JPA (I use MS SQLServer).

SELECT * FROM Machines where SUBSTRING(status, 2, 1) = '1' 

How can I do this using JPA?

Answer 1

JPA has a SUBSTRING function:

http://download.oracle.com/otn-pub/jcp/persistence-2.0-fr-eval-oth-JSpec/persistence-2_0-final-spec.pdf

4.6.17.2.1 "String Functions"

(...)

SUBSTRING (string_primary, simple_arithmetic_expression [, simple_arithmetic_expression])

(...)

The second and third arguments to the SUBSTRING function indicate the starting position and length of the returned substring. These arguments are integers. The third argument is optional. If not specified, a substring from the start position to the end of the line. the first position of the string is indicated by 1. The SUBSTRING function returns a string.

Answer 2

JPQL has a SUBSTRING(..) function: see here . This way it will be the same as in your own request.

When the JPA does not support any function that you need, you can make your own request and match the result with pojo.

Answer 3

JPQL has a subscript function that can be used to trim entities. Make sure you use the object names in your query, and also note that the substring function does not have a zero index .

 SELECT * FROM Machines m where SUBSTRING(m.status, 2, 1) = '1'