spring data jpa dynamic query which has IN clause

Question

I want to create dynamic query in spring data jpa. Doing many search I can implement it, but I came across a problem when I add IN operator in where clause. I need to check id IN (longlist) Here is my entity class

@Entity
@Table(name = "view_detail")
public class ViewDetailDom {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;

private String name;

@Column(name = "user_id")
private Long userId;

private String description;

Here is specification builder class and specification class

public class ViewDetailSpecificationsBuilder {

private final List<SearchCriteria> params;

public ViewDetailSpecificationsBuilder() {
    params = new ArrayList<SearchCriteria>();
}

public ViewDetailSpecificationsBuilder with(String key, Operation operation, Object value) {
    params.add(new SearchCriteria(key, operation, value));
    return this;
}

public Specification<ViewDetailDom> build() {
    if (params.size() == 0) {
        return null;
    }

    List<Specification<ViewDetailDom>> specs = new ArrayList<Specification<ViewDetailDom>>();
    for (SearchCriteria param : params) {
        specs.add(new ViewDetailSpecification(param));
    }

    Specification<ViewDetailDom> result = specs.get(0);
    for (int i = 1; i < specs.size(); i++) {
        result = Specifications.where(result).and(specs.get(i));
    }
    return result;
}

}



public class ViewDetailSpecification implements Specification<ViewDetailDom> {

private SearchCriteria criteria = new SearchCriteria();

public ViewDetailSpecification(SearchCriteria searchCriteria) {
    this.criteria.setKey(searchCriteria.getKey());
    this.criteria.setOperation(searchCriteria.getOperation());
    this.criteria.setValue(searchCriteria.getValue());
}

@Override
public Predicate toPredicate(Root<ViewDetailDom> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
    String value = criteria.getValue().toString().replaceAll(" ", "%");
    if (criteria.getOperation() != null && criteria.getOperation() != Operation.DEFAULT) {
        if (criteria.getOperation() == Operation.GREATHERTHANEQUALTO) {
            return builder.greaterThanOrEqualTo(root.<String>get(criteria.getKey()), value);
        } else if (criteria.getOperation() == Operation.LESSTHANEQUALTO) {
            return builder.lessThanOrEqualTo(root.<String>get(criteria.getKey()), value);
        } else if (criteria.getOperation() == Operation.EQUAL) {
            return builder.equal(root.<String>get(criteria.getKey()), value);
        } else if (criteria.getOperation() == Operation.IN) {
            Path<Long> view = root.<Long>get(criteria.getKey());
            return view.in(criteria.getValue());
        }
    } else {
        if (root.get(criteria.getKey()).getJavaType() == String.class) {
            return builder.like(builder.lower(root.<String>get(criteria.getKey())),
                    "%" + value.toLowerCase() + "%");
        } else {
            return builder.equal(root.get(criteria.getKey()), value);
        }
    }
    return null;
}

}

This method creates specification builder:

public ViewDetailSpecificationsBuilder createSearchSpecifications(ViewSearch view) {
    ViewDetailSpecificationsBuilder builder = new ViewDetailSpecificationsBuilder();
    if (StringUtils.isNotBlank(view.getName())) {
        builder.with("name", Operation.DEFAULT, view.getName());
    }
    if (StringUtils.isNotBlank(view.getDescription())) {
        builder.with("description", Operation.DEFAULT, view.getDescription());
    }

    return builder;
}

And finally I do this:

ViewDetailSpecificationsBuilder builder = createSearchSpecifications(view);
builder.with("userId", Operation.DEFAULT, userSessionHelper.getUserId());
builder.with("id", Operation.IN, viewids);
Specification<ViewDetailDom> spec = builder.build();
viewDetailDao.findAll(spec);

But I am getting following error:

"Unaware how to convert value [[5, 7, 8] : java.util.ArrayList] to requested type [java.lang.Long]; nested exception is java.lang.IllegalArgumentException: Unaware how to convert value [[5, 7, 8] : java.util.ArrayList] to requested type [java.lang.Long]"

Lusi · Accepted Answer · 2017-06-26 13:34:25Z

1

I have resolved this problem in this way:

ViewDetailSpecification class:

if (criteria.getOperation() == Operation.IN) {
            final List<Predicate> orPredicates = new ArrayList<Predicate>();
            List<Long> viewIds = (List<Long>) criteria.getValue();
            for (Long viewid : viewIds) {
                orPredicates.add(builder.or(builder.equal(root.<String>get(criteria.getKey()), viewid)));
            }
            return builder.or(orPredicates.toArray(new Predicate[orPredicates.size()]));
        }

answered Jun 26, 2017 at 13:34

Lusi

41111 silver badges29 bronze badges

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

Comments

VicML · Accepted Answer · 2020-04-20 23:17:09Z

1

In kotlin I have the same error, I change the ArrayList to Array, with this code:

fun values(): Array<String> {
    val elems = arrayListOf<String>()
    return elems.toTypedArray()
}

Try you convert ArrayList to array, for java see: make arrayList.toArray() return more specific types

answered Apr 20, 2020 at 23:17

VicML

111 bronze badge

Collectives™ on Stack Overflow

spring data jpa dynamic query which has IN clause

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related