How to select just the foreign key value using Criteria Query?

Question

Suppose I have two entities as:

@Entity
public class A {

    @Id
    private int id;

    @ManyToOne
    private B b; 

    //more attributes
}

@Entity
public class B {

    @Id
    private int id;
}

So, the table for A is having a column as b_id as the foreign key.

Now, I want to select just the b_id based on some criteria on other fields. How can I do this using criteria query?

I tried doing following which throws IllegalArgumentException saying "Unable to locate Attribute with the given name [b_id] on this ManagedType [A]"

    CriteriaQuery<Integer> criteriaQuery = criteriaBuilder.createQuery(Integer.class);
    Root<A> root = criteriaQuery.from(A.class);
    Path<Integer> bId = root.get("b_id");
    //building the criteria
    criteriaQuery.select(bId);

score 6 · Answer 1 · answered Mar 06 '19 at 03:31

6

You can declare the foreign key in class A where "B_ID" is the name of the foreign key column in table A. And then you can root.get("bId") in your criteriabuilder example above. I have the same problem as you and this is working for me.

@Column(name="B_ID", insertable=false, updatable=false)
private int bId;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "B_ID")
private B b;

answered Mar 06 '19 at 03:31

David Windrem

61
1
1

@AnmolGupta could you please update the accepted answer. This solution prevents a table join, which should make the query way faster – Aron Hoogeveen Nov 12 '22 at 15:05

score 5 · Accepted Answer · answered Feb 17 '16 at 07:34

5

You need to join to B and then fetch the id:

Path<Integer> bId = root.join("b").get("id");

answered Feb 17 '16 at 07:34

wero

32,544
3
59
84

10

why is it necessary to join B when the info is present in A itself? – msfk Jun 02 '18 at 09:25
2

Well this is not selecting "just the fk" but it is doing a join... Agreed with @msfk why is the join needed? How should one just grab the fk field and use that in the criteria? – Pablo Matias Gomez Jun 24 '20 at 05:04
How to use in clause on column B without join . i.e just passing ids in "in" clause in criteria api – harsha kumar Reddy Oct 11 '21 at 15:57
1

A join of `B` is **not** necessary if you want to filter only by the foreign key. You can do `criteriaQuery.where(criteriaBuilder.equal(root.get("b").get("id"), )`. – T3rm1 Feb 24 '22 at 13:34
See the answer of David Windrem for a solution that does not use a table join – Aron Hoogeveen Nov 12 '22 at 15:06

score 2 · Answer 3 · answered Dec 07 '22 at 17:29

As correctly pointed out by @T3rm1 above:

A join of B is not necessary if you want to filter only by the foreign key. You can do criteriaQuery.where(criteriaBuilder.equal(root.get("b").get("id"), <idOfB>).

Basically, the root.get will get an expression to the mapped object in the model. You can therefore do a nested get on the foreign key. In summary:

root.get("b").get("id");

score 1 · Answer 4 · answered Nov 10 '21 at 07:56

You don't necessarily need to join if you're evaluating the FK primary key in the query, that is present in the A entity.

root.get("b")

This code will retrieve the FK by its primary key. For example you can do:

root.get("b").in(List.of(1,2,3,4));

And you have a valid predicate that is comparing the FK value with this list.

How to select just the foreign key value using Criteria Query?

4 Answers4

Linked