0

I am stuck in a problem where I am not able to find out how to generate a left outer join on two tables using criteriaQuery. I have two table A_1 and A_2

@Entity()
@Table(name = "A_1")
public class A1 {
    private long id_1;
    private long name;
    private long city;
}

@Entity()
@Table(name = "A_2")
public class A2 {
    private long id_2;
    private long a_1_id_1;
    private long name;
}

I have to find out all those entry which are present in A_1 table but not in A_2 table. id_1 in A_1 table is same as a_1_id_1.

For this I am trying query like:

select * from A_1 LEFT OUTER JOIN A_2 ON A_1.id_1 = A_2.a_1_id_1 where A_2.a_1_id_1 is NULL

I am getting desired results from my query. But I am not able to find out how Can I change it to code, basically this left outer join part. I have tried lots of options from net but unable to find out the solution.

My code is something like this:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<A_1> root1 = query.from_1(A_1.class);
Root<A_2> root2 = query.from(A_2.class);
List<Predicate> predicates = getPredicates(cb, root1, root1, request);

I am not able to decide how can I use this to get my desired results. Thanks in advance.

Geek_To_Learn
  • 1,816
  • 5
  • 28
  • 48
  • you mean, like using `join` method? rather than inventing a new root object –  Feb 14 '19 at 13:01
  • @BillyFrost : couldn't understand your point. I want to use normal left outer join – Geek_To_Learn Feb 15 '19 at 05:58
  • You use "normal left outer join" by using the `join` method in JPA Criteria! And you havent, you simply did `from` twice, and each one creates a root for the query (aka cross join). There are ample docs on the internet for this ... http://www.datanucleus.org:15080/products/accessplatform_5_2/jpa/query.html#_criteria_api_from_clause_joins You join using relations, and you don't seem to have grasped that object-oriented point in your model –  Feb 15 '19 at 07:07
  • Thanks @BillyFrost : went through document and it was helpful. Needs a little more help, is it possible to add null value in a predicate. select o.o_id from orders_table as o left outer join pttt_table as p on o.o_id = p.o_id where p.o_id is null; this is my query I want to do same as this, but I am not able to understand how can I specify this null condition – Geek_To_Learn Feb 15 '19 at 11:45

1 Answers1

0

You can try something like this -

final Root<A_1> a1 = criteriaQuery.from(A_1.class);
Join<A_1, A_2> join1 = a1.join("joinColumnName", JoinType.LEFT);

Predicate predicate = criteriaBuilder.equal(A_2.<String> get("YOUR_COULMN"), value;
criteria.add(predicate);
criteriaQuery.where(predicate);
criteriaQuery.distinct(true);
Sai prateek
  • 11,842
  • 9
  • 51
  • 66