CODEWITHSUNDEEP
mysqlspringjpaspring-dataspring-data-jpa
balboa_21
balboa_21

Reputation: 384

In Spring Data how to read entity by foreign key value instead of join?

I am using Spring Data and I am in doubt why even after declaring foreign entities as Lazy loaded they are getting eagerly loaded for this method:

findByReportingManager_IdAndAndLevel(Long reporterManagerId, Integer level)

On logs, I can see the query as:

select userhierar0_.id as id1_28_,
       userhierar0_.LEVEL as LEVEL5_28_,     
       userhierar0_.REPORTING_MANAGER_ID as REPORTIN9_28_,   
       userhierar0_.USER_ID as USER_ID10_28_ 
from   USER_HIERARCHY userhierar0_ 
       left outer join 
       USER_V3 user1_ on userhierar0_.REPORTING_MANAGER_ID=user1_.id 
where user1_.id=? and userhierar0_.CUSTOMER_ID=? and userhierar0_.LEVEL=?

why extra join even if I am passing reporting manager id ?

UserHierarchy Class:

@Entity
@Table(name = "USER_HIERARCHY")
@JsonInclude(JsonInclude.Include.NON_EMPTY)
public class UserHierarchy {

  @Id
  @GeneratedValue
  private Long id;

  @ManyToOne(fetch = FetchType.LAZY) // LAZY LOADING
  @JoinColumn(name = "USER_ID",referencedColumnName = "ID")
  private User user;

  @ManyToOne(fetch = FetchType.LAZY) //LAZY LOADING
  @JoinColumn(name = "REPORTING_MANAGER_ID", referencedColumnName = "ID")
  private User reportingManager;

  @Column(name = "LEVEL")
  private Integer level;


  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public User getUser() {
    return user;
  }

  public void setUser(User user) {
    this.user = user;
  }

  public User getReportingManager() {
    return reportingManager;
  }

  public void setReportingManager(User reportingManager) {
    this.reportingManager = reportingManager;
  }

  public Integer getLevel() {
    return level;
  }

  public void setLevel(Integer level) {
    this.level = level;
  }

  @Override
  public String toString() {
    return ReflectionToStringBuilder.toStringExclude(this, "user", "reportingManager");
  }

User Entity class

@Entity
@Table(name = "USER")
public class User {

  @Id
  @GeneratedValue
  private Long id;

  @Column(name = "EMAIL")
  private String email;

  @Column(name = "CUSTOMER_ID")
  private Long customerId;

  @Column(name = "STATUS")
  private String status;

  // Getter and Setter

As per Spring's doc:

At query creation time you already make sure that the parsed property is a property of the managed domain class.

So does that mean in order to make User object in "managed state" it uses join or I am wrong in the implementation ?

Upvotes: 2

Views: 901

Answers (1)

Arnold Galovics
Arnold Galovics

Reputation: 3416

I stumbled across the same problem recently and it seems that there is no solution for this at the moment in Spring Data.

However I've created a ticket for it.

If you go with Criteria API or JPQL for this particular query, then it will work properly.

Upvotes: 1

Related Questions