Unexpected Query Getting Fired With Hibernate And JPA

Question

I have written code for getting data from DB but it's also firing an unexpected query:

@SuppressWarnings("unchecked")
    @Transactional
    public List<Job> getAppliedPositionsById(Long userId) {
//      String currentDate = SQLDateFormator.getCurrentDateInSqlFormat();
        String strQuery = "from Job x left join x.applications a where a.applicant.id = :userId";
        Query query = entityManager.createQuery(strQuery);
        query.setParameter("userId", userId);
        return query.getResultList();
    }

And on return query.getResultList(); it's firing two queries. And because of the second query I am getting exception.

Two queries

Hibernate:

    select
        job0_.id as id1_5_0_,
        applicatio1_.id as id1_1_1_,
        job0_.close_date as close_da2_5_0_,
        job0_.committee_chair_id as committe6_5_0_,
        job0_.description as descript3_5_0_,
        job0_.publish_date as publish_4_5_0_,
        job0_.title as title5_5_0_,
        applicatio1_.applicant_id as applican6_1_1_,
        applicatio1_.current_job_institution as current_2_1_1_,
        applicatio1_.current_job_title as current_3_1_1_,
        applicatio1_.current_job_year as current_4_1_1_,
        applicatio1_.cv_id as cv_id7_1_1_,
        applicatio1_.job_id as job_id8_1_1_,
        applicatio1_.research_statement_id as research9_1_1_,
        applicatio1_.submit_date as submit_d5_1_1_,
        applicatio1_.teaching_statement_id as teachin10_1_1_ 
    from
        jobs job0_ 
    left outer join
        applications applicatio1_ 
            on job0_.id=applicatio1_.job_id 
    where
        applicatio1_.applicant_id=?

Hibernate:

    select
        user0_.id as id1_8_0_,
        user0_.address as address2_8_0_,
        user0_.email as email3_8_0_,
        user0_.first_name as first_na4_8_0_,
        user0_.last_name as last_nam5_8_0_,
        user0_.password as password6_8_0_,
        user0_.phone as phone7_8_0_ 
    from
        users user0_ 
    where
        user0_.id=?

That second query on Users table is totally unnecessary.

Job entity

   @Id
    @GeneratedValue
    private Long id;

    private String title;

    private String description;

    @Column(name = "publish_date")
    private Date publishDate;

    @Column(name = "close_date")
    private Date closeDate;

    @ManyToOne
    @JoinColumn(name = "committee_chair_id")
    private User committeeChair;

    @ManyToMany
    @JoinTable(name = "job_committee_members",
        joinColumns = @JoinColumn(name = "job_id") ,
        inverseJoinColumns = @JoinColumn(name = "user_id") )
    @OrderBy("lastName asc")
    private List<User> committeeMembers;

    @OneToMany(mappedBy = "job")
    @OrderBy("date asc")
    private List<Application> applications;
}

Application entity:

 @Id
    @GeneratedValue
    private Long id;

    @ManyToOne
    private Job job;

    @ManyToOne
    private User applicant;

    @Column(name = "submit_date")
    private Date submitDate;

    @Column(name = "current_job_title")
    private String currentJobTitle;

    @Column(name = "current_job_institution")
    private String currentJobInstitution;

    @Column(name = "current_job_year")
    private Integer currentJobYear;

    @ElementCollection
    @CollectionTable(name = "application_degrees",
        joinColumns = @JoinColumn(name = "application_id") )
    @OrderBy("year desc")
    private List<Degree> degrees;

    @OneToOne
    private File cv;

    @OneToOne
    @JoinColumn(name = "research_statement_id")
    private File researchStatement;

    @OneToOne
    @JoinColumn(name = "teaching_statement_id")
    private File teachingStatement;

    @OneToMany(mappedBy = "application",
        cascade = { CascadeType.MERGE, CascadeType.PERSIST })
    @OrderColumn(name = "round_index")
    private List<Round> rounds;
}

User entity:

@Id
    @GeneratedValue
    private Long id;

    @Column(unique = true, nullable = false)
    private String email;

    @Column(nullable = false)
    private String password;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    private String address;

    private String phone;

    @OneToMany(mappedBy = "applicant")
    @OrderBy("id desc")
    private List<Application> applications;
}

Answer 1

as luksch pointed out, your model defines a @ManyToOne relationship to User that is by default Eagerly fetched everytime you load a Job instance(or Application instance in your model). However, switching it FetchType.LAZY might now produce the expected result. With oneToOne or manyToOne hibernate will have to make the extra query even if it is LAZY. Only if you specify optional=false attribute of the relationship along with FetchType.LAZY will it automatically set a Proxy object as the value of user attribute. This is caused by the fact, that Hibernate has no way of knowing, whether the user attribute exists or is null until it checks the DB. According to your model a more appropriate solution is to update your query to fetch the User object in one query like so:

String strQuery = "from Job x left join fetch x.committeeChair u left join x.applications a where a.applicant.id = :userId"

The important part is left join fetch x.committeeChair u which tells the hibernate to add an extra join and fetch the related object.

This fixes the behaviour when using JPQL to fetch Job instances. If you try to load a single Job instance by its id via EntityManager.find method. it will still produce an extra query for User committeeChair. You loading strategy can be further optimized by using Hibernate specific(not JPA standard yet) fetch modes Please note, that fetch mode may disable Lazy loading what might not be desired. I would recommend to first decide what data needs to be loaded, what data is always present and based on that optimize your queries. Often an extra query is better than loading a whole entity graph in one query. Good luck

Answer 2

From the JPA 2.0 spec, the defaults are like so:

OneToMany: LAZY

ManyToOne: EAGER

ManyToMany: LAZY

OneToOne: EAGER

You have in the Application class

@ManyToOne
private User applicant;

If you switch that to LAZY

@ManyToOne(fetch = FetchType.LAZY)

it should work the way you want.