Specifications and (null) Many-To-One Relationship

Question

I have an MVC Controller that return a List of Contacts as JSON. On frontend side i use jquery datatables plugin. There is a searchfield on the frontend to filter the entity list.

my entities:

@Entity
public class Contact implements Serializable {

    protected final static Logger   LOGGER              = LoggerFactory.getLogger(Contact.class);

    private static final long       serialVersionUID    = -3691953100225344828L;

    @Id
    @GeneratedValue(generator = "hibernate-uuid")
    @Column(length = 36, unique = true)
    private String                  id;

    @Version
    @JsonIgnore
    private int                     version;

    private String                  firstname;
    private String                  lastname;

    @ManyToOne
    private Company                 company;

    ... GETTER/SETTER ...
}

and

@Entity
public class Company implements Serializable {

    protected final static Logger   LOGGER              = LoggerFactory.getLogger(Company.class);

    private static final long       serialVersionUID    = -7863930456400256944L;

    @Id
    @GeneratedValue(generator = "hibernate-uuid")
    @Column(length = 36, unique = true)
    private String                  id;

    private String                  companyName;
    private String                  companyName1;
    private String                  companyName2;

    ... GETTER/SETTER ...
}

I use server side processing for the search field and on server side i use specifications.

public class ContactSpecifications {

    public static Specification<Contact> contactFirstnameLike(final String needle) {
        return new Specification<Contact>() {

            @Override
            public Predicate toPredicate(Root<Contact> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                return cb.like(cb.lower(root.<String> get(Contact_.firstname)), needle != null ? needle.toLowerCase() : null);
            }
        };
    }

    public static Specification<Contact> contactLastnameLike(final String needle) {
        return new Specification<Contact>() {

            @Override
            public Predicate toPredicate(Root<Contact> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                return cb.like(cb.lower(root.<String> get(Contact_.lastname)), needle != null ? needle.toLowerCase() : null);
            }
        };
    }

    public static Specification<Contact> contactFullnameLike(final String needle) {
        return new Specification<Contact>() {

            @Override
            public Predicate toPredicate(Root<Contact> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                return cb.or(cb.like(cb.lower(root.<String> get(Contact_.lastname)), needle != null ? needle.toLowerCase() : null), cb.like(cb.lower(root.<String> get(Contact_.firstname)), needle != null ? needle.toLowerCase() : null));
            }
        };
    }

    public static Specification<Contact> contactCompanyCompanyNameLike(final String needle) {
        return new Specification<Contact>() {

            @Override
            public Predicate toPredicate(Root<Contact> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                final Path<Company> company = root.<Company> get(Contact_.company);
                return cb.like(cb.lower(company.<String> get(Company_.companyName)), needle != null ? needle.toLowerCase() : null);
            }
        };
    }
}

My db query

 contactRepository.findAll(specifications, new PageRequest(0,100));

and specifications are

 specifications = Specifications.where(ContactSpecifications.contactFullnameLike(needle)).or(ContactSpecifications.contactCompanyCompanyNameLike(needle));

needle is the search key from the frontend and mask with surrounding % (for example "%asdf%")

My problem is, if the contact has no company the specifications not working as expected.

For example i have 3 Contacts:

1. Lastname: Schmitz, Firstname: Max, Company: (null)
2. Lastname: Schmitz, Firstname: Moritz, Company: XY

3. Lastname: Muster, Firstname: Max, Company: XY

   • If i now enter Schmitz as search key, only contact 2 returned, contact 1 not.
   • If i enter max as search key, only contact 3 returned, contact 1 not
   • Only if the search key is null/empty, all contacts returned

I miss something?

kind regards Rizzi

Answer 1

answer myself ;)

After research the sql queries i found the solution. I have to rewrite my specifications. On related entities i have to add an left join path to prevent criteria builder automatically use cross/inner joins.

Inner join only returned entities, that has all fields set. if some entity relation is null, this entity is drop from result list. Normal inner join behaviour.

so...

Correct specification must be like this.

public static Specification<Contact> contactCompanyCompanyNameLike(final String needle) {
    return new Specification<Contact>() {

        @Override
        public Predicate toPredicate(Root<Contact> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            final Join<Contact,Company> company = root.join(Contact_.company, JoinType.LEFT);
            return cb.like(cb.lower(company.<String> get(Company_.companyName)), needle != null ? needle.toLowerCase() : null);
        }
    };
}

With this small modifications it's starts working correctly now.

kind regards Rizzi