CODEWITHSUNDEEP
javaspringjpaspring-data-jpaspring-data
J. Abel
J. Abel

Reputation: 946

Trim() Function with Spring JPA like Query Methods

I am making a rest service that internally is using JPA to find data with a prefix (for instance: Information prefix).

My query method with Spring data looks like this:

List<Entity> findAllByFieldStartsWithIgnoreCase(String field);

Everything is working fine, however, there are data in the database that have the Information prefix, but also have a white space, like in the followings example:

Field:

Information 1
    Information 2
 Information 3
Information 4

Like you can see in the above examples, Information 1 does not have some white space, but Information 2 and 3 have one, or more, white spaces.

I am looking the way to ignore these spaces, I know that sql has the trim function to make it, but in JPA repository with Spring, with query methods, does not exist:

I would like to find a function to type the query methods like this:

List<Entity> findAllByField  Trim   StartsWithIgnoreCase(String field);

In order to execute trim function before the StartWith.

I would apprecite your help, regards.

Upvotes: 2

Views: 5705

Answers (1)

susheem_k
susheem_k

Reputation: 156

You can use a JPQL query here using the @Query annotation. For example, the following snippet should give you a prefix search on a trimmed column

@Query("select e from Entity e where trim(e.field) like concat(:field,'%')")
List<Entity> findAllByFieldStartsWithIgnoreCase(@Param("field") String field);

Here's a resource on how to use native/JPQL queries with Hibernate

Upvotes: 4

Related Questions