CODEWITHSUNDEEP
springspring-data-jpa
marc_aragones
marc_aragones

Reputation: 4474

Find by many columns

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

Upvotes: 1

Views: 211

Answers (1)

Mykhailo Lytvyn
Mykhailo Lytvyn

Reputation: 191

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));
}

Upvotes: 1

Related Questions