[FIXED] Spring JPA find entity where property containing List

Issue

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!

Solution

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.

Answered By - fladdimir
Answer Checked By - Willingham (JavaFixing Volunteer)