How can I use JPA to search for a substring on a f

问题:

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?

回答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.

回答2:

JPQL has the SUBSTRING(..) function as well: see here. So it will be the same as in the native query.

When JPA doesn't support some function that you need, you can make a native query and map the result to a pojo.

回答3:

JPQL has a substring function which can be used to trim entities. Make sure you use entity names in your query and also note that the substring function is not zero indexed.

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