| Using Left Join without metamodel classes [message #1042451] |
Tue, 16 April 2013 13:05  |
 Alexandre Alves
Messages: 3
Registered: April 2013 |
Junior Member |
|
|
I have the following situation SQL that I need to implement into my criteria builder for dynamic query creation.
select distinct 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>();
...
// Bunch of conditions
...
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 with my current implementation.
Also, in another forum, it was mentioned that I should use the Metamodel classes.
Do I really need to implement metamodel classes to obtain the desired output?
|
|
|
|
| Re: Using Left Join without metamodel classes [message #1042478 is a reply to message #1042472] |
Tue, 16 April 2013 13:35  |
Alexandre Alves
Messages: 3
Registered: April 2013 |
Junior Member |
|
|
I do have the relationship:
From TicketsUpdates:
Quote:
@JoinColumn(name = "ticketnr", referencedColumnName = "ticketnr", insertable = false, updatable = false)
@ManyToOne(optional = false)
private Tickets tickets;
And from
Tickets:
Quote:
@OneToMany(cascade = CascadeType.ALL, mappedBy = "tickets")
private Collection<TicketsUpdates> ticketsUpdatesCollection;
[Updated on: Tue, 16 April 2013 14:13] Report message to a moderator |
|
|
| Re: Using Left Join without metamodel classes [message #1043128 is a reply to message #1042478] |
Wed, 17 April 2013 10:12 |
Alexandre Alves
Messages: 3
Registered: April 2013 |
Junior Member |
|
|
Just as a matter of making it solved.
I had actually two errors it seems:
1. I was giving column name to the join instead of the Relationship name :/
2. I was missing the from for the second table.
So in the end I got:
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tickets> query = builder.createQuery(Tickets.class);
EntityType<Tickets> type = em.getMetamodel().entity(Tickets.class);
EntityType<TicketsUpdates> typeTU = em.getMetamodel().entity(TicketsUpdates.class);
Root<Tickets> root = query.from(Tickets.class);
Root<TicketsUpdates> rootTicketsUpdates = query.from(TicketsUpdates.class);
Join<Tickets,TicketsUpdates> tupdates = rootTicketsUpdates.join("tickets");
//
..... a lot of other conditions ....
//
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() + "%"),
builder.like(
builder.lower(
tupdates.get(
typeTU.getDeclaredSingularAttribute("description", String.class))), "%"+text.toLowerCase()+"%")
));
}
query.orderBy(builder.desc(root.get("startdate")));
query.distinct(true); |
|
|
Powered by
FUDForum. Page generated in 0.05436 seconds
] 
Search
Help
Register
Login
Home
