COALESCE not work with empty list in Spring JPA Query

Question

I am writing a RESTful API including three entities.

Category:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor

@Entity
@Table(name = "Category")
public class Category {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int categoryId;

    @Column(length = 50, unique = true, nullable = false)
    private String categoryName;

    private Timestamp createdAt;

    private Timestamp updatedAt;

    private boolean isDisabled;

    @OneToMany(mappedBy = "category")
    private List<Document> documents = new ArrayList<>();

    @PrePersist
    protected void onCreate() {
        createdAt = new Timestamp(System.currentTimeMillis());
        updatedAt = new Timestamp(System.currentTimeMillis());
    }

    @PreUpdate
    protected void onUpdate() {
        updatedAt = new Timestamp(System.currentTimeMillis());
    }
}

Field:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor

@Entity
@Table(name = "Field")
public class Field {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int fieldId;

    @Column(unique = true, length = 100, nullable = false)
    private String fieldName;

    private Timestamp createdAt;

    private Timestamp updatedAt;

    private boolean isDisabled;

    @OneToMany(mappedBy = "field")
    private List<Document> documents = new ArrayList<>();

    @PrePersist
    protected void onCreate() {
        createdAt = new Timestamp(System.currentTimeMillis());
        updatedAt = new Timestamp(System.currentTimeMillis());
    }

    @PreUpdate
    protected void onUpdate() {
        updatedAt = new Timestamp(System.currentTimeMillis());
    }
}

Document:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor

@Entity
public class Document {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int docId;

    @Column(nullable = false)
    private String docName;

    @Column(length = 65535)
    private String docIntroduction;

    @Column(nullable = false)
    private String viewUrl;

    @Column(nullable = false)
    private String downloadUrl;

    private Timestamp uploadedAt;

    private Timestamp updatedAt;

    private int totalView;

    private String thumbnail;

    @ManyToOne
    @JoinColumn(name = "uploadedBy")
    private User userUploaded;

    @ManyToOne
    @JoinColumn(name = "categoryId")
    private Category category;

    @ManyToOne
    @JoinColumn(name = "fieldId")
    private Field field;

    @OneToMany(mappedBy = "document", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<DocumentLike> documentLikes = new ArrayList<>();


    @PrePersist
    protected void onCreate() {
        uploadedAt = new Timestamp(System.currentTimeMillis());
        updatedAt = new Timestamp(System.currentTimeMillis());
    }

}

I want to find Document by Category and Field.

    @Query("SELECT d FROM Document d " +
            "WHERE (LOWER(d.docName) LIKE LOWER(CONCAT('%', :q, '%')) OR LOWER(d.docIntroduction) LIKE LOWER(CONCAT('%', :q, '%'))) " +
            "AND (COALESCE(:categories, NULL) IS NULL OR d.category IN :categories) " +
            "AND (COALESCE(:fields, NULL) IS NULL OR d.field IN :fields) " +
            "ORDER BY SIZE(d.documentLikes) DESC")
    Page<Document> findAllOrderByLikes(String q, List<Category> categories, List<Field> fields, Pageable pageable);

But when I use COALESCE to pass category and field list when they are empty, I encountered this problem:

Error accessing field [private int com.advanced_mobile_programing.docs_sharing.entity.Category.categoryId] by reflection for persistent property [com.advanced_mobile_programing.docs_sharing.entity.Category#categoryId] : [com.advanced_mobile_programing.docs_sharing.entity.Category@1a4917d1]

I has been stuck in for hours and I cannot find out the issue. If I check it with is NULL, it return nothing if the list is empty. If I use EMPTY, it throws error too. Please help me.

Answer 1

I think you should validate these list-type values before execute query.

It can make your code clearly.

In addition, should not do so much thing in query. Logic should be handled by code, don't let database do.

Database should only use for query.

In your case:

• Put left LOWER function to logic code
• AND (COALESCE(:categories, NULL) IS NULL: it's just check whether categories is null, so it not be there in query.
• AND (COALESCE(:fields, NULL) IS NULL is the same.

Because SQL is not case-sensitive So use lower case for column name (snake_case) (some case can cause error when using camelCase)

You can also use `@Column(name = "this_is_column_name") for mapping column.

Hope this can help you!