How to implement a search filter with Spring + Hibernate + MySQL?

Question

First of all, I'm new to Spring Framework and Hibernate. Please bear with me.

I'm trying to implement a search filter where the user can optionally specify first name, last name, date of birth, gender, country, language, etc. I've a table called Guest which stores these records. Some columns such as gender, country, and language are stored in separate tables with unique primary keys. Which goes to say, I'm storing foreign keys to these records in the Guest table.

All the fields for the search are optional. For every missing search field, the program has to assume "any".

With that in mind, I'm able to use LIKE and wildcards for string columns, such as first name and last name. For the foreign key columns I'm using the = operator. If a search field is missing, the field is replaced with null.

I'm not able to get anything in the output when I specify only the first name (or the last name for that matter).

Here's the code for the CrudRepository interface I've written.

public interface GuestRepository extends CrudRepository<Guest, Integer> {

    @Query("SELECT guest FROM Guest guest WHERE guest.propertyIdentifier = ?1 AND guest.firstName LIKE %?2% AND guest.lastName LIKE %?3% AND guest.titleIdentifier = ?4 AND guest.dateOfBirth = ?5 AND guest.genderIdentifier = ?6 AND guest.countryIdentifier = ?7 AND guest.languageIdentifier = ?8 AND guest.passport LIKE %?9%")
    Iterable<Guest> findGuests(Integer propertyIdentifier, String firstName, String lastName, Integer titleIdentifier, Date dateOfBirth, Integer genderIdentifier, Integer countryIdentifier, Integer languageIdentifier, String passport);
}

Here's the Guest entity.

@Entity
public class Guest {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer identifier;

    private Integer propertyIdentifier;

    private String firstName;

    private String lastName;

    private Integer titleIdentifier;

    @Temporal(TemporalType.DATE)
    private Date dateOfBirth;

    private Integer genderIdentifier;

    private Integer countryIdentifier;

    private Integer languageIdentifier;

    private String passport;

    private Integer status;

    public Integer getIdentifier() {
        return identifier;
    }

    public void setIdentifier(Integer identifier) {
        this.identifier = identifier;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public Date getDateOfBirth() {
        return dateOfBirth;
    }

    public void setDateOfBirth(Date dateOfBirth) {
        this.dateOfBirth = dateOfBirth;
    }

    public String getPassport() {
        return passport;
    }

    public void setPassport(String passport) {
        this.passport = passport;
    }

    public Integer getLanguageIdentifier() {
        return languageIdentifier;
    }

    public void setLanguageIdentifier(Integer languageIdentifier) {
        this.languageIdentifier = languageIdentifier;
    }

    public Integer getCountryIdentifier() {
        return countryIdentifier;
    }

    public void setCountryIdentifier(Integer countryIdentifier) {
        this.countryIdentifier = countryIdentifier;
    }

    public Integer getGenderIdentifier() {
        return genderIdentifier;
    }

    public void setGenderIdentifier(Integer genderIdentifier) {
        this.genderIdentifier = genderIdentifier;
    }

    public Integer getTitleIdentifier() {
        return titleIdentifier;
    }

    public void setTitleIdentifier(Integer titleIdentifier) {
        this.titleIdentifier = titleIdentifier;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public Integer getPropertyIdentifier() {
        return propertyIdentifier;
    }

    public void setPropertyIdentifier(Integer propertyIdentifier) {
        this.propertyIdentifier = propertyIdentifier;
    }
}

How can I implement a search filter which assumes "any" if the user does not specify a value for a search field? Is there any other alternatives I can use?

Answer 1

Consider using Criteria API. It would look something like this:

@Service
public class GuestService {

@PersistenceContext
private EntityManager em;

public List<Guest> findGuests(Guest searchedGuest) {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Guest> cq = cb.createQuery(Guest.class);
    Root<Guest> quest = cq.from(Guest.class);
    List<Predicate> predicates = new ArrayList<Predicate>();

    if (searchedGuest.getPropertyIdentifier != null) {
        predicates.add(cb.equal(quest.get("propertyIdentifier"), searchedGuest.getPropertyIdentifier));
    }
    if (searchedGuest.getFirstName != null) {
        predicates.add(cb.like(quest.get("firstName"), "%" + searchedGuest.getFirstName + "%"));
    }
    // other predicates

    cq.select(quest).where(predicates.toArray(new Predicate[] {}));
    List<Guest> guests = em.createQuery(cq).getResultList();
    return guests;
}

Where searchedGuest is your object with optionally filled fields.