1

I want to perform a query like this:

SELECT * FROM table WHERE
col1 = 'value1' OR
col2 = 'value2' OR
col3 = 'value3' OR
col4 = 'value4';

Using Spring's JpaRepository I should use something like this:

List<MyEntity> findByCol1OrCol2OrCol3OrCol4(
  String col1, String col2, String col3, String col4
);

Now imagine I don't want to check 4 columns but 10 or 20, the method name would be really long!

I have seen in this answer that I could use Specification to search the same text in many columns, but I want different text values for every one.

Is there any way to shorten the find method and add the columns (and the corresponding values) dynamically?

Thanks

Community
  • 1
  • 1
marc_aragones
  • 4,344
  • 4
  • 26
  • 38
  • 1
    Short answer is no (for `or` joins). You can use QueryDSL support to build the query dynamically. – manish Dec 14 '16 at 13:14
  • You can use criteria and query for that. Repositories are not that good for the complex queries as specified here http://stackoverflow.com/questions/17008947/whats-the-difference-between-spring-datas-mongotemplate-and-mongorepository – KayV Dec 14 '16 at 13:16
  • It seems that support for `OR` matching may be coming soon through the `Example`s API. You may want to track [this Spring Data JIRA](https://jira.spring.io/browse/DATAJPA-1025) for release information. – manish Dec 27 '16 at 08:01

1 Answers1

1

This can be achieved via Specifications and Map(attribute name, value). Code sample (works for any data):

Map<String, Object> whereClause = new HashMap<>();
whereClause.put("lastname", "Super-Lastname");
whereClause.put("firstname", "Firstńame");

userRepository.findOne(Specifications.where(
    CommonSpecifications.attributesEquals(whereClause))
)

public static <T> Specification<T> attributesEquals(Map<String, Object> whereClause) {
    return (root, query, builder) -> builder.or(root.getModel().getDeclaredSingularAttributes().stream()
            .filter(a -> whereClause.keySet().contains(a.getName()))
            .map(a -> builder.equal(root.get(a.getName()), whereClause.get(a.getName())))
            .toArray(Predicate[]::new));
}
Mykhailo Lytvyn
  • 191
  • 1
  • 5