How to build a query with Panache which depends on optional fields?

Question

Let's say I have the following database entity:

Customer
- firstname
- lastname
- status (active | inactive)
- email

And the following (simplified) Java method:

getCustomers (String firstname, String lastname, String status, String email) {...}

If all strings are empty I want to retrieve all records from the database. If lastname and status have values I want to retrieve the relevant records. And so on. So when creating the query I need to check if the fields have values and then add these fields to the database query. How would I best implement this with Quarkus Panache?

Davide D'Alto · Accepted Answer · 2021-04-14T12:51:11.413

I don't think there is a specific method to do it but if you pass the parameters as Map<String, Object>:

public List<Customer> getCustomers(Map<String, Object> parameters)
    if ( parameters == null ) {
        return Customer.listAll();
    }

    Map<String, Object> nonNullParams = parameters.entrySet().stream()
        .filter( entry -> entry.getValue() != null )
        .collect( Collectors.toMap( Map.Entry::getKey, Map.Entry::getValue ) );

    if ( nonNullParams.isEmtpty() ) {
        return Customer.listAll();
    }

    String query = nonNullParams.entrySet().stream()
            .map( entry -> entry.getKey() + "=:" + entry.getKey() )
            .collect( Collectors.joining(" and ") );

    return Customer.find(query, nonNullParams);
}

or

public List<Customer> getCustomers (String firstname, String lastname, String status, String email) {
    Map<String, Object> parameters = new HashMap<>();
    addIfNotNull(parameters, "firstName", firstName );
    addIfNotNull(parameters, "lastName", lastName );
    addIfNotNull(parameters, "status", status );
    addIfNotNull(parameters, "email", email );

    if ( parameters.isEmtpty() ) {
        return Customer.listAll();
    }

    String query = parameters.entrySet().stream()
            .map( entry -> entry.getKey() + "=:" + entry.getKey() )
            .collect( Collectors.joining(" and ") );
    return Customer.find(query, parameters)
}

private static void addIfNotNull(Map<String, Object> map, String key, String value) {
    if (value != null) {
        map.put(key, value);
    }
}

Thanks a lot for your detailed answer. It works as expected. — StSch, Apr 14 '21 at 12:48
Maybe. It depends on what the rest of the application looks like. If you let the user define the key of the map, than you might have an issue. Otherwise, it's no different that writing a query and set a parameter. — Davide D'Alto, Oct 01 '21 at 16:40

score 4 · Answer 2 · answered Aug 05 '22 at 09:19

You can use filters for this, which is a Hibernate feature supported by Panache. It's not very well documented, so here is a working example.

Your database entity:

@Entity
@Table(name = "book")
@FilterDef(name = "Books.byISBN",
    defaultCondition = "isbn = :isbn",
    parameters = @ParamDef(isbn = "isbn", type = "string"))
@Filter(name = "Books.byISBN")
public class Book extends PanacheBaseEntity {}

Your web resource

@Path("book")
public class BookResource {
    @Inject
    BookRepository bookRepository;

    @GET
    @Produces(MediaType.TEXT_PLAIN)
    public String index(@QueryParam("isbn") Optional<String> isbnFilter) {
        final PanacheQuery<Book> bookQuery = bookRepository.findAll();

        isbnFilter.ifPresent(isbn -> bookQuery.filter("Book.byISBN", Parameters.with("isbn", isbn)));

        return bookQuery.list().stream()
                .map(Book::name)
                .collect(Collectors.joining(","));
    }
}

How to build a query with Panache which depends on optional fields?

2 Answers2

Linked