I have the following situation SQL that I need to implement into my criteria builder for dynamic query creation.
select t1.ticketnr
from tbl_ticket t1
left join tbl_tickets_updates t2 on t1.ticketnr = t2.ticketnr
where t1.description ilike '%EXAMPLE%' or t2.description ilike '%EXAMPLE%';
In my code I have the following:
tbl_ticket = Tickets.class PK = ticketnr
tbl_tickets_updates = TicketsUpdates.class and TicketsUpdatesPK.class PK = ticketnr, updatedby, timeofupdate
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tickets> query = builder.createQuery(Tickets.class);
EntityType<Tickets> type = em.getMetamodel().entity(Tickets.class);
Root<Tickets> root = query.from(Tickets.class);
List<Predicate> predicatesAnd = new ArrayList<Predicate>();
...
if (text.length() != 0) {
predicatesAnd.add(builder.or(
builder.like(
builder.lower(
root.get(
type.getDeclaredSingularAttribute("description", String.class))), "%" + text.toLowerCase() + "%"),
builder.like(
builder.lower(
root.get(
type.getDeclaredSingularAttribute("summary", String.class))), "%" + text.toLowerCase() + "%")));
Join<Tickets, TicketsUpdates> tupdates = root.join("ticketnr", JoinType.LEFT);
predicatesAnd.add(builder.like(builder.lower(tupdates.get("description").as(String.class)), "%" + text.toLowerCase() + "%"));
}
My code is failing at the Left Join with: java.lang.IllegalStateException: CAN_NOT_JOIN_TO_BASIC
I believe this is happening due to having a separate class for the composite PK, but so far I have not found a way to implement this properly.
Any ideas?