Spring data query not working properly, because the field is having Or in the name. Field name is - approvedOrRejectedBy

Question

Spring data query not working properly, because the field is having Or in it.

The field is String approvedOrRejectedBy. So while writing the query it is confusing with the default Or.

The query I wrote is:

List<Object> findAllByTimeStampBetweenAndCameraSlugInAndApprovedOrRejectedBy(long startTime, long endTime, List<String> cameraSlugs, String id);

Because of Or present in the field name, it's throwing an error. Any suggestions or workaround on how to solve this issue, other than updating the filed name and DB mapping.

Answer 1

The query by method-name will interpret certain keywords from the name:

• Subject Keywords like here findAllBy
• Predicate Keywords like here And, Or, Between together with the property names

Since your field/property seems named as approvedOrRejectedBy you could work around by naming the object property differently and annotate it with the mapped DB-column name as given, e.g. @Column(name "ApprovedOrRejectedBy").

@Column(name "approvedOrRejectedBy")
String reviewedBy;

Then your query-method can be rewritten as

List<Object> findAllByTimeStampBetweenAndCameraSlugInAndReviewedBy(long startTime, long endTime, List<String> cameraSlugs, String id);

Alternatively, since the method name becomes hard to read, you could shorten the name and specify the SELECT on @Query annotation like this:

@Query("SELECT * FROM entityOrTable x WHERE x.timeStamp BETWEEN ?1 AND ?2 AND x.cameraSlug IN ?3 AND x.approvedOrRejectedBy = ?4")
List<Object> findByIntervalCameraSlugInAndReviewedBy(long startTime, long endTime, List<String> cameraSlugs, String id);

See also:

• Spring-Data-Jpa Repository - Underscore on Entity Column Name
• Spring Data JPA repository methods don't recognize property names with underscores

Answer 2

Try writing custom query using JPQL, like @Query annotation. Please see

https://www.baeldung.com/spring-data-jpa-query