Search all columns for matching keyword with Spring JPA

Question

I'm building a rest service with Spring Boot. I've been using Spring Data JPA for the queries but I'm open to anything that will help solve my problem.

I want to find all entries in the database with a matching string, in any column.

Something along the lines of, FindAllBy(all 20 columns)(String parameter) or SELECT * FROM AllColumns() WHERE parameter = "Foo";

I have an entity with close to 20 attributes so I'd like to avoid writing all columns names in the search.

How could I do this?

spring data jps doesnt have any methods to do this. You have to write a query — pvpkiran, Nov 04 '19 at 10:43
@pvpkiran do queries have some way of searching all columns? — khansen, Nov 04 '19 at 10:48
Does this answer your question? [Spring Data - Multi-column searches](https://stackoverflow.com/questions/25872637/spring-data-multi-column-searches) — Robert Niestroj, Nov 04 '19 at 12:09

score 4 · Accepted Answer · answered Nov 04 '19 at 11:49

This URL (https://www.baeldung.com/rest-api-search-language-spring-data-specifications) will help you, I have copied some code from URL to explain you.

you need to define a criteria class

public class SearchCriteria {
    private String column;
    private String operation;
    private Object value;
}

Then you need to define your search specification

public class SearchSpecification implements Specification<MyEntity> {

    private SearchCriteria criteria;

    private SearchSpecification(SearchCriteria criteria){
        this.criteria =criteria;
    }

    @Override
    public Predicate toPredicate
      (Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) {


        if (criteria.getOperation().equalsIgnoreCase(":")) {
            if (root.get(criteria.getColumn()).getJavaType() == String.class) {
                return builder.like(
                  root.<String>get(criteria.getColumn()), "%" + criteria.getValue() + "%");
            } 
        }
        return null;
    }
}

Then finally you can modify your repository by extending JpaSpecificationExecutor

public interface MyRepository 
  extends JpaRepository<User, Long>, JpaSpecificationExecutor<MyEntity> {}

Now you can create a specification for each column for your case all twenty column

SearchSpecification spec1 = 
              new SearchSpecification(new SearchCriteria("column1", ":", "searchVal"));

SearchSpecification spec2 = 
              new SearchSpecification(new SearchCriteria("column2", ":", "searchVal"));

SearchSpecification spec3 = 
              new SearchSpecification(new SearchCriteria("column3", ":", "searchVal"));

Then you need to execute query like

List<MyEntity> results = 
      repository.findAll(Specification.where(spec1).or(spec2).or(spec3));

score 0 · Answer 2 · answered Nov 04 '19 at 11:07

0

Is the only way to search string in all column SELECT * FROM yourTable WHERE field1 LIKE '%value%' or field2 LIKE '%value%' ......;

answered Nov 04 '19 at 11:07

scridon darius

46
3

Search all columns for matching keyword with Spring JPA

2 Answers2

Linked