Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Using Left Join without metamodel classes
Using Left Join without metamodel classes [message #1042451] Tue, 16 April 2013 13:05 Go to next message
Alexandre Alves is currently offline Alexandre AlvesFriend
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 #1042472 is a reply to message #1042451] Tue, 16 April 2013 13:26 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

What is the relationship from Tickets to TicketsUpdates? You need to have an @OneToMany, @ManyToOne or @OneToOne relationship to use join.

See,
http://en.wikibooks.org/wiki/Java_Persistence/Criteria#Join


James : Wiki : Book : Blog : Twitter
Re: Using Left Join without metamodel classes [message #1042478 is a reply to message #1042472] Tue, 16 April 2013 13:35 Go to previous messageGo to next message
Alexandre Alves is currently offline Alexandre AlvesFriend
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 Go to previous message
Alexandre Alves is currently offline Alexandre AlvesFriend
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);
Previous Topic:eclipselink.id-validation=NONE not working
Next Topic:Static Metamodel gencode error ... for wildcard Collection field
Goto Forum:
  


Current Time: Wed Nov 26 16:55:33 GMT 2014

Powered by FUDForum. Page generated in 0.01652 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software