Search code examples
jpamicronautmicronaut-data

How to find all employees that contain all character sequenences in their name?

I'm trying to write a Micronaut Data JPA finder that finds all employees that match to a list of strings.

Given the following employees (first name, last name)

  • John Doe
  • Jane Doe
  • Peter Pan
  • Silvio Wangler
  • Franka Potente
  • Frank Potter

Lets pretend a user queries the system with a query like Silvio then the result should be

  • Employee: Silvio Wangler

If the query is Frank the result should be

  • Employee: Franka Potente
  • Employee: Frank Potter

And if the query is frank pot the result should as well (case insensitive like)

  • Employee: Franka Potente
  • Employee: Frank Potter

I managed to write a finder for a single string as listed below

Page<Employee> findAllByLastNameIlikeOrFirstNameIlike(
      String lastName, String firstName, Pageable pageable);

For a query like frank pot I would like to tokenize/split the string to a list ["frank", "pot"] and was wondering if I could implement something like

Page<Employee> findAllByLastNameIlikeOrFirstNameIlike(
      List<String> lastName, List<String> firstName, Pageable pageable);

or even better use the Criteria API of JPA. How would you implementent such a search finder? Can you point me the direction?

Solution

  • First thing i always think of, is how to do it in plain SQL. Found that one MySQL Like multiple values

    So i suggest the best way is using the criteria API and build your own dynamic query. Have a look at the first example https://micronaut-projects.github.io/micronaut-data/latest/guide/#repositories which gives you everything you need from micronaut.

    i use the ciriteria api also if i have some kind of dynamic querys like 'give me everything from start to end but if start is not specified just give me everything before end ... '