1

For example:

This is the name property of an object I want to search for:

Lorem ipsum dolor sit amet, consectetur adipiscing elit

When I fill in "lo adip tetur" (lo (= Lorem), dipi (=adipiscing), tetur (=consectetur) ) I want to be able to find this object.

I tried to split my name property on space and pass it to the jpa method but I did not get any results.

List<Obj> findAllByNameIgnoreCaseContaining(String[] splittedName);

What would be the correct way to solve this problem? Thanks!

BrentL
  • 47
  • 1
  • 10
  • Seems like you are looking for this https://stackoverflow.com/questions/34837725/spring-data-repositories-find-where-field-in-list – Ajay Kumar Feb 04 '22 at 03:03
  • I'm afraid that the linked answer from @Ajay Kumar might fall short when it comes to text fragment search, since for example 'in' and 'like' cannot be combined – fladdimir Feb 04 '22 at 10:54
  • You're right fladdimir that indeed will be a problem for my use case. Thank you for the suggestion AjayKumar – BrentL Feb 04 '22 at 11:07

1 Answers1

1

A regex query will allow you to specify this type of complex text search criteria.
Regular expressions are supported by many databases, and can be supplied when using a native query.

An example for postgres could look like this:

@Query(nativeQuery = true, value =
  "SELECT * FROM my_entity
   WHERE text_column ~ :contentRegex")
List<MyEntity> findByContentRegex(@Param("contentRegex") String contentRegex);

To match the first two characters of the first three words, you could for example pass a regex like this one:

var result = repository.findByContentRegex("^Lo\S*\sip\S*\sdo.*");

(a string starting with Lo, followed by an arbitrary number of non-whitespace characters, a whitespace character, ip, an arbitrary number of non-whitespace characters, a whitespace character, do, and an arbitrary number of arbitrary characters)

Of course you can dynamically assemble the regex, e.g. by concatenating user-supplied search term fragments:

List<String> searchTerms = List.of("Lo", "ip", "do"); // e.g. from http request url params
String regex = "^"+String.join("\S*\s", searchTerms) + ".*";
var result = repository.findByContentRegex(regex);

See e.g. https://regex101.com/ for an interactive playground.

Note that complex expressions may cause the query to become expensive, so that one may consider more advanced approaches at some point, like e.g. full text search which can make use of special indexing. https://www.postgresql.org/docs/current/textsearch-intro.html
Also note that setting a query timeout is recommended for potentially hostile parameter sources.
Apart from that, there are also more specialized search servers e.g. like apache-solr.

fladdimir
  • 1,230
  • 1
  • 5
  • 12
  • 1
    Thank you for the suggestion! My regex looks something like this and it seems to be working: for (String term : searchTerms) { regex.append(String.format("(?=.*%s.*)", escapeSpecialRegexChars(term))); } – BrentL Feb 06 '22 at 00:17