How can I use JPA to search for a substring on 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 string field (called status) that consists of a series of "0"s and "1"s. I need to select a few records based on the field's second character. Here is what I can do without using JPA (I am using MS SQLServer).

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

How can I do it using JPA?

Answer 1

There is a SUBSTRING function in JPA:

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 of the SUBSTRING function denote the starting position and length of the substring to be returned. These arguments are integers. The third argument is optional. If it is not specified, the substring from the start position to the end of the string is returned. The first position of a string is denoted by 1. The SUBSTRING function returns a string.