JPQL equivalent

Question

I have to write an JPQL query for the following Oracle-SQL:

SELECT * FROM Foo f, Bar b, Xyz x WHERE f.id = b.foo(+) and f.id = x.foo(+) and (lower(f.label) like lower('%filter%') or lower(f.code) like lower('%filter%') or lower(b.label) like lower('%filter%') or lower(x.label) like ('%filter%');

There are multiple Bar and Xyz tables, every Bar/Xyz pair is connected to a subclass of Foo:

@Entity
@Table(name = "FOO")
// JOINED doesn't work because of an EclipseLink error!
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "TP", discriminatorType = DiscriminatorType.STRING, length = 5)
public abstract class Foo extends AbstractEntity {

private static final long serialVersionUID = -9137682737124302402L;

@Column(name = "CODE", nullable = false, length = 40)
private String code;

@Column(name = "LABEL", nullable = false, length = 4000)
private String label;

    //...other properties

}

@Entity
@Table(name = "ABCD")
@DiscriminatorValue("ABCD")
public class Abcd extends Foo {

private static final long serialVersionUID = 250474360860393393L;

@OneToMany(mappedBy = "abcd")
@OrderColumn(name = "IDX")
private List<Bar> barList;

@OneToMany(mappedBy = "abcd")
@OrderColumn(name = "IDX")
private List<Xyz> xyzList;

protected Abcd() {
    this.barList = new ArrayList<Bar>();
    this.xyzList = new ArrayList<Xyz>();
}

    //...getter/setter
}

@Entity
@Table(name = "BAR")
public class Bar extends AbstractEntity {

private static final long serialVersionUID = 2628239098357340769L;

@ManyToOne(fetch = FetchType.EAGER, optional = false, cascade = CascadeType.ALL)
@JoinColumns({ @JoinColumn(name = "ABCD_ID", referencedColumnName = "ID", nullable = false) })
private Abcd abcd;

@Column(name = "LABEL", nullable = false, length = 4000)
private String label;

protected Bar() {
}

    // ...getter/setter, some other properties
}


@Entity
@Table(name = "XYZ")
public class Xyz extends AbstractEntity {

private static final long serialVersionUID = 2628239098357350769L;

@ManyToOne(fetch = FetchType.EAGER, optional = false, cascade = CascadeType.ALL)
@JoinColumns({ @JoinColumn(name = "ABCD_ID", referencedColumnName = "ID", nullable = false) })
private Abcd abcd;

@Column(name = "LABEL", nullable = false, length = 4000)
private String label;

protected Xyz() {
}

    // ...getter/setter, some other properties
}

@MappedSuperclass
public abstract class AbstractEntity {

private static final long serialVersionUID = -8574667350713432415L;

@EmbeddedId
private PersistId id;

public static boolean isId(PersistId id) {
    return (id != null && id.getId() > 0);
}

public boolean hasId() {
    return isId(getId());
}

public PersistId getId() {
    return this.id;
}

public void setId(PersistId id) {
    this.id = id;
}
}

PersistId encapsulates a column called ID, which is currently implemented as Long. As I said before, there are some other subclasses of Foo, which are connected to some other classes like Bar/Xyz.

How I can implement the SQL in Eclipselink (with the Criteria API)? I have tested some diffent things, but I don't get any results. I have also used subqueries to get the ABCD_ID which match with the filter, but then I have problems to match Foo and Abcd. I have two methods, which generates the query and the filter parameter.

private CriteriaQuery<Foo> getQuery() {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Foo> query = criteriaBuilder.createQuery(Foo.class);
Root<Foo> foo = query.from(Foo.class);
query.select(foo);
query.distinct(true);
return query;
}

private CriteriaQuery<Foo> addFilter(CriteriaQuery<Foo> query, String fullTextFilter) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
Set<Root<?>> roots = query.getRoots();
Iterator<Root<?>> iter = roots.iterator();
Root<Foo> foo = (Root<Foo>) iter.next();

List<Predicate> criteria = new ArrayList<Predicate>();

if (fullTextFilter != null) {
        ParameterExpression<String> p = criteriaBuilder.parameter(String.class, "filter");
    Path<String> label = foo.<String>get("label");
        Path<String> code = foo.<String>get("code");

        // TODO: add some code to handle Bar and Xyz labels
        criteria.add(criteriaBuilder.or(criteriaBuilder.like(criteriaBuilder.lower(label), criteriaBuilder.lower(p)),
                criteriaBuilder.like(criteriaBuilder.lower(code), criteriaBuilder.lower(p))));
    }
    if (criteria.size() == 1) {
        query.where(criteria.get(0));
    }
    if (criteria.size() > 1) {
        query.where(criteriaBuilder.and(criteria.toArray(new Predicate[0])));
    }

    return query;
}

Any ideas? I think, I haven't understood any essential things on the Criteria API, could you enlight me with some sourcecode?

Thanks Andre

Unrelated to the concrete problem: I am not an expert of single table inheritance, but I see your comment in the code and I can confirm that JOINED inheritance works well in Eclipselink. Perhaps it's worth asking another question on that issue... — perissf, Feb 12 '13 at 11:33
The comment is there because of [question 4489776](http://stackoverflow.com/questions/4489776/jpa-2-criteria-api-exception-converting-jpql-to-criteria-api-query-with-eclips). I had the same NPE with JOINED. — witchi, Feb 12 '13 at 12:31
The NPE in the referenced question is from 2 years ago, you migth try a newer version to see if its been fixed, or file a bug for it if not. Is there any reason why you cannot use the Abcd->Bar+Xyz relationships? This will cause a join from Foo to Abcd, but since you've mapped it so that only Abcd can be referenced by Bar and Xyz, it will not change the entity results. — Chris, Feb 12 '13 at 15:40

score 0 · Answer 1 · answered Feb 12 '13 at 15:46

If you can allow joining in the Abcd table, the query should be on the Abcd entity instead of Foo, since it has the relationships to Xyz and Bar. You would then add Joining or a fetch join to the query:

CriteriaQuery<Abcd > query = criteriaBuilder.createQuery(Abcd.class);
Root<Abcd> foo = query.from(Abcd.class);
foo.fetch("barList", JoinType.LEFT);
foo.fetch("xyzList", JoinType.LEFT);
query.select(foo);
query.distinct(true);

JPQL equivalent

1 Answers1