0

I have String "AlphaBetaZeta GammaTheta" (table field from MS SQL database), and i need search with spring-data with many like and with random order and random count of key-words

I'm trying search with:

findByLabelContaining(Collection<String> labels)
findByLabelLike(Collection<String> labels)

but these do not work.

Enable full-text search in the database or use elastic I can not, only Spring-Data with native interface method or with custom jpql-query.

SELECT id, label
FROM TABLE10
WHERE label like '%gam%' AND
      label like '%alpha%' AND
      label LIKE '%theta%'

SELECT id, label
FROM TABLE10
WHERE label like '%alpha%' AND
      label like '%theta%'

and more..

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
JavaCapibara
  • 5
  • 2
  • I don't really understand what exactly you mean by do not work, but I assume that you mean that query doesn't find anything. My guess would be that it's because you query is case sensitive at the moment. Have a look at this question, I think it's similar to what you re looking for: https://stackoverflow.com/questions/22573428/case-insensitive-query-with-spring-crudrepository – Igor Nikolaev Feb 02 '19 at 12:37
  • I'll try to explain: I have a string (one field of the entity in the database), I need to search for this entity by several keywords that can be included in the string, and the number of keywords is different and the order can be different, as in the above example, ie, in fact it all comes down to writing SQL query [search with many LIKE with random order of word] by methods Spring – JavaCapibara Feb 02 '19 at 12:41

1 Answers1

0

Use a custom native query:

@Query(value = "SELECT id, label
FROM TABLE10
WHERE label like %?1%", native = true)
  List<User> findByLabel(String a);

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#_using_advanced_like_expressions

If you need a variable number of arguments, you'll have to write your own query:

@PersistenceContext
EntityManager entityManager;

@Override
public List<YourClass> findByLabelLike(List<String> labels) {
    String q = "SELECT id, label FROM TABLE10";

    for (String l : labels) {
       // Add where and ORs
    }

    Query query = entityManager.createNativeQuery(q, YourClass.class);
    int i = 0;
    for (String label : labels) {
          query.setParameter(++i, "%"+label+"%");
    }

    return query.getResultList();
}
Alexey Soshin
  • 16,718
  • 2
  • 31
  • 40