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

user_1477769 0
Viewed 3855

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?

quarkus-panache
2 Answers

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

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(","));
    }
}
user_29296930
Related