This discussion is archived
9 Replies Latest reply: Jan 18, 2013 6:40 AM by 984733 RSS

JPA Criteria Query API  with Complex SQL

984733 Newbie
Currently Being Moderated
We're trying to implement JPA 2.0, and replacing a lot of convoluted SQL with the Criteria API in the process. I'm not a SQL guru by any means, and I'm new to JPA/Criteria, but I've read just about everything on it I could find via search engine.

Can the following SQL even be done using Criteria? It's making my head hurt, and I don't want to pursue this any further if it's not even feasible to do.
SELECT T2.TYPE, T2.SERIAL
    FROM TABLE1 T1, TABLE2 T2, TABLE3 T3
        LEFT OUTER JOIN TABLE4 T4 ON T4.EMPNO=T3.BOEMPNO
            WHERE T2.TYPE=T3.TYPE AND T2.SERIAL=T3.SERIAL AND T1.TYPE=T2.TYPE AND T1.SERIAL=T2.SERIAL
Edited by: 981730 on Jan 14, 2013 3:01 PM
  • 1. Re: JPA Criteria Query API  with Complex SQL
    cdelahun Pro
    Currently Being Moderated
    It is simple. JPA is based on entities, so generally queries would return entity objects but they can be used to return raw data as well if that is required. I'd recommend looking at the JPA 2.0 employee and criteria query examples found here:
    http://wiki.eclipse.org/EclipseLink/Examples/JPA

    I don't understand the purpose of the left outer join to T4 though since it is not used in the query.

    Best Regards,
    Chris
  • 2. Re: JPA Criteria Query API  with Complex SQL
    984733 Newbie
    Currently Being Moderated
    Thanks, Chris, for your help!

    However, I've already looked at the link you suggested. It shows how to get one entity type from a database. I've seen examples of getting two entites, but the second one is always accessed via a single column. All the examples I have seen in all my JPA reading have been very simplistic.

    In my SQL, I have four entities, an explicit join, using two columns, none of which are primary key fields. My problem is not the select to get the columns I want. It's figuring out how to code the from and where clauses on multiple fields and multiple tables.

    So, I respectfully disagree with your statement "it's simple", because it really doesn't seem to be (at least not to me). :-)

    Oh, BTW, the join to TABLE4 is just to make sure there is a row in that table, or the entry will be skipped. At least, that's how I understand it.

    Thanks again,
    Dave
  • 3. Re: JPA Criteria Query API  with Complex SQL
    984733 Newbie
    Currently Being Moderated
    So for instance, this is the first thing that is tripping me up. In the Apache OpenJPA User's Guide, it shows how to get a second table:
    Root<Customer> customer = qdef.from(Customer.class);
    Root<Order> order = customer.join(customer.get(Customer_.orders));
    However, in my case, the other tables are reached via two fields ('serial' and 'type'), and those two fields are not the primary keys of any of the tables except TABLE2. So I don't know how to do the equivalent of that 'customer.get()' on multiple fields.
  • 4. Re: JPA Criteria Query API  with Complex SQL
    cdelahun Pro
    Currently Being Moderated
    Each of your fields should be mapped to attributes in the entity which can be used the same way as fields within the table. So you are querying for the Entity2s that have Entity1s and Entity3s with matching serial and type fields. I don't know how these are mapped within Entity1+3, so I'm assuming they are basic mappings. If so something similar to:

    Using JPQL:
    em.createQuery("SELECT e2 FROM Entity2 e2, Entity1 e1, Entity3 e3 WHERE e2.serial = e1.serial AND e2.type= e1.type AND e2.serial = e3.serial AND e2.type= e3.type");

    Criteria API:
     CriteriaBuilder qb = em.getCriteriaBuilder();
            CriteriaQuery<Entity2>cq = qb.createQuery(Entity2.class);
            Root<Entity1> root1 = cq.from(Entity1.class);
            Root<Entity2> root2 = cq.from(Entity2.class);
            Root<Entity3> root3 = cq.from(Entity3.class);
    
            Expression expression1 = qb.and(qb.equal(root2.get("serial"), root1.get("serial")), qb.equal(root2.get("type"), root1.get("type")) );
            Expression expression3 = qb.and(qb.equal(root2.get("serial"), root3.get("serial")), qb.equal(root2.get("type"), root3.get("type")) );
    
            cq.where(qb.and(expression1, expression3));
            Query testQuery = em.createQuery(cq);
    If a join to T4 is neccessary, I am not sure why you are using the left outer join rather than an inner join which can be done the same way as shown above. Joins using an ON clause are supported in EclipseLink 2.5 in nightly builds as added through bug: https://bugs.eclipse.org/bugs/show_bug.cgi?id=367452 but is not apart of the JPA specification.

    If your entities have relationships to/from Entity2 using serial+type as foreign keys, you can use those relationships in your query instead.

    em.createQuery("SELECT e2 FROM Entity1 e1, Entity3 e3 join e3.entity2 as e2 WHERE e1.entity2 = e2");

    JPA/EclipseLink will perform the joins for you based on the entity2 mapping definitions. In criteria API:
      CriteriaQuery<Entity1>cq = qb.createQuery(Entity2.class);
            Root<Entity1> root1 = cq.from(Entity1.class);
            Root<Entity3> root3 = cq.from(Entity3.class);
            Join<Entity2>  join2 = root3.join("entity2");
    
            cq.select(join2);
            cq.where(qb.equal(root1.get("entity2"), join2));
            Query testQuery = em.createQuery(cq);
    Hope this helps
  • 5. Re: JPA Criteria Query API  with Complex SQL
    984733 Newbie
    Currently Being Moderated
    Thanks so much for your help! You shattered one of my major misconceptions by showing me an example of how this should be done properly. I was under the mistaken impression that you create one Root object, and then everything else has to come from that (since that was the only example I ever saw in my reading so far):
    Root<Customer> customer = qdef.from(Customer.class);
    Root<Order> order = customer.join(customer.get(Customer_.orders));
    Now I know you can create multiple Root objects from the CriteriaQuery object.

    As far as that outer join is concerned, this is old code, and I'm not a database expert, so I'm not exactly sure what it's supposed to accomplish, or why it's being done that way as opposed to using the inner join.
  • 6. Re: JPA Criteria Query API  with Complex SQL
    984733 Newbie
    Currently Being Moderated
    OOPS! Never mind the following! I found that it's an arg on the join() method call.

    ======================================

    I just discovered that the join does need to be an outer left one. The SQL in our code that was being generated could be modified dynamically later on, and T4.EMPNO could be used in a predicate in the WHERE clause.

    So how do I accomplish a LEFT OUTER JOIN with Criteria?

    Edited by: 981730 on Jan 15, 2013 5:54 PM

    Edited by: 981730 on Jan 15, 2013 6:04 PM
  • 7. Re: JPA Criteria Query API  with Complex SQL
    984733 Newbie
    Currently Being Moderated
    Sorry, one more question (I won't promise it's my last!).

    I saved the JOIN until last, and I'm looking at it now. I'm very confused by the syntax of your example, even of the JPQL. I'm having trouble relating it back to my scenario. I don't see Entity2 showing up as a table anywhere, and I'm confused as to what 'e3.entity2' is.

    Would it be too much for me to ask if you could give me the JPQL, and especially the Criteria code, for the following? I have the Criteria code working without the JOIN, so that's the part I'm still having trouble with. (Please note that there can be more SQL added later, so even though it seems like the JOIN is meaningless, it isn't necessarily so.)
    SELECT T2.TYPE, T2.SERIAL
        FROM TABLE1 T1, TABLE2 T2, TABLE3 T3
            LEFT OUTER JOIN TABLE4 T4 ON T4.EMPNO=T3.BOEMPNO
                WHERE T2.TYPE=T3.TYPE AND T2.SERIAL=T3.SERIAL AND T1.TYPE=T2.TYPE AND T1.SERIAL=T2.SERIAL
    Thanks in advance!
  • 8. Re: JPA Criteria Query API  with Complex SQL
    cdelahun Pro
    Currently Being Moderated
    The two examples I gave relied on assumptions on how you mapped the tables to entities. Both examples assumed Table1 mapped to a java class called Entity1, Table2 mapped to Entity2 etc.
    The first example is if there are no relation mappings and all, in which case you must join the entities manually in JPQL or criteria queries. It assumes T2.SERIAL maps to an attribute in Entity2 called serial etc. I'm not sure how you could do a left outer join using this approach.

    The second example assumed the object model might reflect relationships in the database, such that Entity1 might have a OneToOne mapping on an attribute "entity2" of type Entity2 using t1.TYPE and t1.SERIAL as foreign keys to T2's primary keys (specified through joinColumns). These relationships can be used in queries so that you do not need to redefine the joins each time as you would in the first example.

    I don't think I can give a better example unless you show how you mapped your java classes to the tables.

    As for the outer join - my understanding of it is that it allows the left side to exist with null on the right. Unless t4 is only allowed to have rows that reference rows in T3 (which means you can use an inner join and get the same results), then this query will return null values for each of those rows in the results table. If the query wanted to get all T2 values regardless of the existence of rows in T4, I think it should have been using:
    LEFT OUTER JOIN TABLE4 T4 ON T3.BOEMPNO=T4.EMPNO
    I could be wrong though. You can test by switching it to an inner join and seeing if you get different results, or if the code handles/expects null rows returned.
  • 9. Re: JPA Criteria Query API  with Complex SQL
    984733 Newbie
    Currently Being Moderated
    Thanks, it took me awhile for your examples to fully sink in, and with your help, I got the outer join working.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points