0

I have an entity with a List<> field in it. I'm trying to search entries in the database by a search string. Almost everything works fine, except the case when my entity's hardwareCharacteristicValueList field is null. When it happens the query doesn't return anything, even if I pass to specification a string with existing vendor, model, etc.

What am I doing wrong?

If I comment the line 

cb.like(cb.lower(root.join(Hardware_.hardwareCharacteristicValueList).get(HardwareCharacteristicValue_.value)), "%" + search.toLowerCase() + "%")

everything works just fine.

Entity:

import lombok.*;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Parameter;

import javax.persistence.*;
import java.io.Serializable;
import java.util.List;

@Entity
@Table(name = "hardware")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Hardware implements Serializable {

    @Id
    @GenericGenerator(name = "hardware_id_sequence", strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
            parameters = {@Parameter(name = "sequence_name", value = "SEQ_HARDWARE")})
    @GeneratedValue(generator = "hardware_id_sequence")
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "vendor")
    private String vendor;

    @Column(name = "model")
    private String model;

    @Column(name = "serial", nullable = false)
    private String serial;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "employee_id")
    private Employee employee;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "state_id")
    private HardwareState state;

    @Column(name = "is_visible",nullable = false)
    private Boolean isVisible;

    @Column(name = "comment")
    private String comment;

    @Column(name = "inspection_date")
    private Long inspectionDate;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "hardware_type_id",nullable = false)
    private HardwareType hardwareType;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "hardware")
    private List<HardwareCharacteristicValue> hardwareCharacteristicValueList;
}

Specification:

public static Specification<Hardware> searchSubstring(String search) {
        return (root, criteriaQuery, cb) -> cb.or(cb.like(cb.lower(root.get(Hardware_.model)), "%" + search.toLowerCase() + "%"),
                                                    cb.like(cb.lower(root.get(Hardware_.vendor)), "%" + search.toLowerCase() + "%"),
                                                    cb.like(cb.lower(root.get(Hardware_.serial)), "%" + search.toLowerCase() + "%"),
                                                    cb.like(cb.lower(root.get(Hardware_.hardwareType).get(HardwareType_.name)), "%" + search.toLowerCase() + "%"),
                                                    cb.like(cb.lower(root.get(Hardware_.employee).get(Employee_.lastName)), "%" + search.toLowerCase() + "%"),
                                                    cb.like(cb.lower(root.join(Hardware_.hardwareCharacteristicValueList).get(HardwareCharacteristicValue_.value)), "%" + search.toLowerCase() + "%"),
                                                    cb.like(cb.lower(root.get(Hardware_.state).get(HardwareState_.name)), "%" + search.toLowerCase() + "%"));
Nikolay
  • 1
  • 2
  • 1
    I think you have to specify JoinType. Can you try like this ? root.join(Hardware_.hardwareCharacteristicValueList, JoinType.LEFT).get(HardwareCharacteristicValue_.value) @nikolay – sonerokur Jul 26 '18 at 10:54

1 Answers1

0

Solution:

public static Specification<Hardware> searchSubstring(String search) {
    return (root, criteriaQuery, cb) -> cb.or(cb.like(cb.lower(root.get(Hardware_.model)), "%" + search.toLowerCase() + "%"),
                                                cb.like(cb.lower(root.get(Hardware_.vendor)), "%" + search.toLowerCase() + "%"),
                                                cb.like(cb.lower(root.get(Hardware_.serial)), "%" + search.toLowerCase() + "%"),
                                                cb.like(cb.lower(root.join(Hardware_.hardwareType, JoinType.LEFT).get(HardwareType_.name)), "%" + search.toLowerCase() + "%"),
                                                cb.like(cb.lower(root.join(Hardware_.employee, JoinType.LEFT).get(Employee_.lastName)), "%" + search.toLowerCase() + "%"),
                                                cb.like(cb.lower(root.join(Hardware_.hardwareCharacteristicValueList, JoinType.LEFT).get(HardwareCharacteristicValue_.value)), "%" + search.toLowerCase() + "%"),
                                                cb.like(cb.lower(root.join(Hardware_.state, JoinType.LEFT).get(HardwareState_.name)), "%" + search.toLowerCase() + "%"));
Nikolay
  • 1
  • 2