I am with problems to do a query with array column in PostgreSQL with Spring Data

Question

Entity

I have it entity with a property that is text[]:

    @Entity(name = "entityname")
@Table(name = "tablename")
@ToString
@TypeDefs({
  @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class),
  @TypeDef(name = "list-array", typeClass = ListArrayType.class)
})
public class EntityClass extends Audit
...

  @Type(type = "list-array")
  @Column(name = "participants", columnDefinition = "text[]")
  private List<String> participants;
  ....

Repository

I want to execute and findAll with a filter. This filter is Specification spec

data = entityRepository.findAll(filter, pageRequest);

The filter was implemented according to Specification:

public class Filter<T> implements Specification<T> {

  private final List<Condition> conditions = new ArrayList<>();

  public void addCondition(Condition condition) {
    this.conditions.add(condition);
  }

  private List<Predicate> buildPredicates(Root root, CriteriaQuery query, CriteriaBuilder cb) {
    List<Predicate> predicates = new ArrayList<>();
    for (Condition condition : conditions) {
      predicates.add(buildPredicate(condition, root, query, cb));
    }
    return predicates;
  }

  public Predicate buildPredicate(
      Condition condition, Root root, CriteriaQuery query, CriteriaBuilder cb) {
    if (!condition.getType().getValue().contains(condition.getComparison())) {
      throw new RuntimeException("UNSUPPORTED_COMPARISON_FOR_SPECIFIED_TYPE");
    }
    switch (condition.getComparison()) {
      case EQ:
        if (condition.getValue().getClass().isArray()) {
          final Predicate[] predicatesList =
              Arrays.stream((Object[]) condition.getValue())
                  .map(conditionValue -> cb.equal(root.get(condition.getField()), conditionValue))
                  .toArray(Predicate[]::new);
          return cb.or(predicatesList);
        }
        return cb.equal(root.get(condition.getField()), condition.getValue());
      case GT:
        if (condition.getType().equals(FilterFieldTypes.DATE)) {
          return cb.greaterThan(
              root.<Date>get(condition.getField()),
              new Date(condition.getValueAsNumber().longValue()));
        } else {
          return cb.gt(root.get(condition.getField()), condition.getValueAsNumber());
        }
      case GTE:
        if (condition.getType().equals(FilterFieldTypes.DATE)) {
          return cb.greaterThanOrEqualTo(
              root.<Date>get(condition.getField()),
              new Date(condition.getValueAsNumber().longValue()));
        } else {
          return cb.ge(root.get(condition.getField()), condition.getValueAsNumber());
        }
      case LT:
        if (condition.getType().equals(FilterFieldTypes.DATE)) {
          return cb.lessThan(
              root.<Date>get(condition.getField()),
              new Date(condition.getValueAsNumber().longValue()));
        } else {
          return cb.lt(root.get(condition.getField()), condition.getValueAsNumber());
        }
      case LTE:
        if (condition.getType().equals(FilterFieldTypes.DATE)) {
          return cb.lessThanOrEqualTo(
              root.<Date>get(condition.getField()),
              new Date(condition.getValueAsNumber().longValue()));
        } else {
          return cb.le(root.get(condition.getField()), condition.getValueAsNumber());
        }
      case FULL_TEXT:
        return cb.isTrue(cb.function("fts", Boolean.class, cb.literal(condition.getValue())));
      case JSONB_ARRAY_IN:
        return cb.isTrue(
            cb.function(
                "jsonb_extract_path_text", Boolean.class, cb.literal(condition.getValue())));
      case JSONB_PATH_EQ:
        return cb.isTrue(
            cb.function(
                "jsonb_array_contains",
                Boolean.class,
                cb.literal(condition.getField()),
                cb.literal(condition.getValue())));
      case IN:
        List<String> dataList = Arrays.asList(condition.getValue().toString());
        return cb.in(root.get(condition.getField())).value(dataList);

      default:
        throw new RuntimeException("UNSUPPORTED_COMPARISON");
    }
  }

  public int getConditionsCount() {
    return conditions.size();
  }

  @Override
  public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
    List<Predicate> predicates = buildPredicates(root, query, cb);
    return predicates.size() > 1 ? cb.and(predicates.toArray(Predicate[]::new)) : predicates.get(0);
  }
}

I am having problems with the implementation of my CASE IN:

      case IN:
        List<String> dataList = Arrays.asList(condition.getValue().toString());
        return cb.in(root.get(condition.getField())).value(dataList);

It produces the following error in it class:

public class ListArrayTypeDescriptor extends AbstractArrayTypeDescriptor<Object> {

...

    @Override
    public Object unwrap(Object value, Class type, WrapperOptions options) {
        if (value instanceof Object[]) {
            return value;
        } else if (value instanceof List) {
            return super.unwrap(((List) value).toArray(), type, options);
        } else {
            throw new UnsupportedOperationException("The provided " + value + " is not a Object[] or List!");
        }
    }
    
...

And the stack trace shows it message:

java.lang.UnsupportedOperationException: The provided d08d7785-1153-4bb3-b58c-6c79bf5a37f8 is not a Object[] or List!
    at com.vladmihalcea.hibernate.type.array.internal.ListArrayTypeDescriptor.unwrap(ListArrayTypeDescriptor.java:60)
    at

Do you have an idea about that is wrong in the implementation of my CASE IN?

Jose Amadeo Diaz Diaz · Accepted Answer · 2022-02-10 14:58:01Z

2

I was trying many options until that found it solution:

case ANY: List predicates = new ArrayList<>(); Expression delimiter = cb.literal(",");

    predicates.add(cb.like(
        cb.function(
            "array_to_string", String.class, root.get(condition.getField()), delimiter),
        "%" + condition.getValue().toString().toLowerCase() + "%"));
  return cb.and(predicates.toArray(new Predicate[0]));

I hope help if someone needs to implement "ANY" with criteriabuilder.

answered Feb 10, 2022 at 14:58

Jose Amadeo Diaz Diaz

5052 gold badges5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

I am with problems to do a query with array column in PostgreSQL with Spring Data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related