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?

Answer 1

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);
    }
}