CODEWITHSUNDEEP
javajpa
reen
reen

Reputation: 2502

JPA Criteria API select distinct on String Lob with Constructor Expression

I have a static method that creates a criteria query:

public static CriteriaQuery<ReportInfo> reportInfoQuery(EntityManager em){
    List<Predicate> criteria = new ArrayList<Predicate>();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<ReportInfo> c = cb.createQuery(ReportInfo.class);
    Root<Flaw> flaw = c.from(Flaw.class);
    c.distinct(true);

    c.multiselect(flaw.get("hostinfo").get("name"), flaw.get("severity"),
            flaw.get("plugin").get("pluginid"),
            flaw.get("port"), flaw.get("pluginName"),
            flaw.get("report").get("scan").get("scanDate"),
            flaw.get("text"));

    criteria.add(cb.equal(flaw.get("plugin").get("pluginid"), 0);
    criteria.add(cb.equal(flaw.get("hostinfo").get("name"), "ahostname");

    if (reports.size() > 1 && !reports.contains(null)) {
        List<Predicate> orCriteria = new ArrayList<Predicate>();
        for (String report : reports) {
            orCriteria.add(cb.equal(flaw.get("report").get("name"), report));
        }
        criteria.add(cb.or(orCriteria.toArray(new Predicate[0])));
    } else if (reports.size() == 1 && !reports.contains(null)) {
        criteria.add(cb.equal(flaw.get("report").get("name"), reports.get(0)));
    }

    if (criteria.size() == 1) {
        c.where(criteria.get(0));
    } else if (criteria.size() > 1) {
        c.where(cb.and(criteria.toArray(new Predicate[0])));
    }

    return c;
}

ReportInfo is a constructer expression class, which holds values selected in multiselect.

This all works fine with c.distinct(false), if I use c.distinct(true) I get an execption, because the value flaw.text is a String annotated with @Lob (large texts)

Has anyone an Idea, how I can solve that problem? We use Eclipse Link 2.

Upvotes: 0

Views: 4614

Answers (1)

James
James

Reputation: 18379

Your database does not support the distinct operation on a LOB. You need to either remove the distinct, or remove the Lob from the select. Not sure why you require a distinct, you are not doing any joins, so should not get any duplicate rows.

If you are getting duplicates you could try to filter them in a sub-select, or define an equals method in your ReportInfo class and add your results to a list to remove the duplicates.

Upvotes: 1

Related Questions