CODEWITHSUNDEEP
javaspringpostgresqlhibernate-criteriacriteria-api
Семен Немытов
Семен Немытов

Reputation: 307

How to make join only on statments in .on() or convert join to equivalent satament?

I want to make a simple left join from the table on the same table.

public class User extends BaseEntity<User> {

  public static final String PARENT_FIELD = "parent";

  @OneToOne(fetch = LAZY)
  @JoinColumn(name = "parent_id")
  User parent;
}

I have the id column in BaseEntity

My criteria code is :

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> query = cb.createQuery(Long.class);
Root<User> root = query.from(User.class);
Join<User, User> userJoin = root.join(PARENT_FIELD, JoinType.LEFT);

....some other joins 

userJoin .on(
cb.equal(userJoin.getParent().get(PARENT_FIELD).get(ID_FIELD), root.get("id")),
cb.equal(userJoin.get(ID_FIELD), root.get("id"))
);

....groupBy

...Heaving

The problem is that I see this SQL statement in log :

select
user0_.id as col_0_0_
from
user user0_
left outer join
user user2_
on user0_.parent_id=user2_.id !!!! this genrated from join!!! its excess. I just need
and (                                                                         only from sql!     
user0_.parent_user_id=user0_.id  <- this need or stament
and user2_.id=user0_.id
)

The sql what i want

select * from user p
left outer join user p1 on p.id = p1.parent_id or p.id = p1.id

How can i make criteria cod without condition from join? OR!! join on id field, it cloud help too... I tryed join on id, but it throw error that i cand join on basic type. Another way is convert into select...wher or something equivalent satment...

Upvotes: 0

Views: 67

Answers (1)

JLazar0
JLazar0

Reputation: 1292

try this:

ENTITY

public class User extends BaseEntity<User> {

  public static final String PARENT_FIELD = "parent";
  public static final String CHILDRENS_FIELD = "childrens";

  @OneToOne(fetch = LAZY)
  @JoinColumn(name = "parent_id")
  User parent;

  //Define bidirectional relation
  @OneToMany(mappedBy = "parent", fetch = FetchType.LAZY, cascade = {})
  private List<User> childrens;
}

CRITERIA

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> query = cb.createQuery(Long.class);
Root<User> root = query.from(User.class);
Join<User, User> userJoin = root.join(CHILDRENS_FIELD, JoinType.LEFT);

....some other joins 

userJoin.on(
    cb.or(cb.equal(userJoin.get(ID_FIELD), root.get("id")))
);

....groupBy

...Heaving

When defining the bidirectional relationship, the Join created when initializing it is with the User children as you wish, and not with the parents.

the on method of Join, add the conditions so encapsulating it inside a cb.or the query should be generated as you wish.

Upvotes: 1

Related Questions