JPA CriteriaBuilder left outer join for table with no relations?

Question

Initially I had requirement to write code using JPA CriteraiBuilder for following SQL:

SELECT ve.col_1,
  (SELECT vm.col_4
  FROM table2 vm
  WHERE vm.col_2  = ve.col_2
  AND vm.col_3 = ve.col_3
  ) as col_a
FROM table1 ve;

But I learnt that, it is not possible to add subquery in select clause. So I changed my query to use left outer join like this.

SELECT ve.col_1,
  vm.col_4 as col_a
FROM table1 ve,
  table2 vm
WHERE 
vm.col_2 (+)    = ve.col_2 
AND vm.col_3 (+) = ve.col_3;

Now table1 and table2 do not have direct relations using foreign keys. Corresponding JPA entities look like:

Table1.java ->

@Column(name = "COL_1")
private String col_1;

@Column(name = "COL_2")
private String col_2;

@Column(name = "COL_3")
private String col_3;

@OneToOne(fetch = FetchType.LAZY)
@JoinColumns({
    @JoinColumn(name="COL_2"),
    @JoinColumn(name="COL_3")
})
private Table2 table2;


Table2.java ->

@Column(name = "COL_4")
private String col_4;

@Column(name = "COL_2")
private String col_2;

@Column(name = "COL_3")
private String col_3;

My code looks like:

final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

final CriteriaQuery<SearchTO> query = criteriaBuilder.createQuery(
        SearchTO.class);
Root<Table1> root = query.from(Table1.class);

final Join<Table1, Table2> joinTable2 = root.join(Table1_.table2,
        JoinType.LEFT);

Then I am trying to fetch value using:

joinTable2.get(Table2_.col_4)

Then now I am getting error as:

A Foreign key refering com.Table2 from com.Table1 has the wrong number of column

Table2 has around 6 columns with annotation @Id and I can not change change it to have only two columns with @Id annotation.

Please let me know:

If it is possible to write code using CriteriaBuilder for my approach 1 (subquery in select clause).
If thats not possible, how can I implement this left outer join as mentioned for approach 2. Please note that Table2 does not have any references of Table1.

Please note that I am using plain JPA APIs. DB is Oracle11g. JDK version is 1.7.

Any expert who can help here? – user613114 Aug 22 '16 at 08:12 — user613114, Aug 22 '16 at 08:12

score 1 · Answer 1 · answered Sep 27 '21 at 08:40

One solution is to create view and query against it using criteria builder. You can see my answer in Joining tables without relation using JPA criteria Only change you need to do is use left join in your view definition. Hope it solves your use case.

score 0 · Answer 2 · answered Nov 21 '16 at 18:38

For Approach 1: You can write subquery for criteria query

Subquery<Entity1> subquery = cq.subquery( Entity1.class );
    Root fromSubQuery = subquery.from( Entity1.class );
    subquery.select( cb.max( fromSubQuery.get( "startDate" ) ) );
    subquery.where( cb.equal( fromSubQuery.get( "xyzId" ), fromRootOfParentQuery.get( "xyzId" ) ) );

Use it as :

Root<Entity2> entity2 = cq.from( Entity2.class );
Predicate maxDatePredicate = cb.and( cb.equal( entyty2.get( "startDate" ), subquery ) );

For Approach 2: There is no other way than having relationship between two entities for left join. You can define private variable for relationship without getter & setter and use that variable for setting left join. Then add the predicate to criteriaBuilder

JPA CriteriaBuilder left outer join for table with no relations?

2 Answers2