CODEWITHSUNDEEP
javaoracle-databasehibernate
eatSleepCode
eatSleepCode

Reputation: 4637

One to one relationship hibernate results in many queries

I have following class in one to one relationship

@Entity
@Table(name = "PERSON")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "PERSON_ID")
    private int personId;
    @Column(name = "PERSON_NAME", nullable = false, length = 30)
    private String personName;
    @OneToOne(mappedBy = "person", cascade = CascadeType.ALL)
    private DrivingLicense drivingLicense;
}

@Entity
@Table(name = "DRIVING_LICENSE")
public class DrivingLicense {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "LICENSE_NUMBER")
    private int licenseNumber;
    @Column(name = "DATE_OF_ISSUE")
    private Date dateOfIssue;
    @OneToOne
    @JoinColumn(name = "PERSON_ID", unique = true)
    private Person person;
}

currently there are 3 rows in each table

but when I do a query on person like below

Query query = entityManager.createQuery("from Person p");

after getting resultlist its resulting in too many queries like below;

Hibernate: select person0_.PERSON_ID as PERSON_ID1_1_, person0_.PERSON_NAME as PERSON_NAME2_1_ from PERSON person0_
Hibernate: select drivinglic0_.LICENSE_NUMBER as LICENSE_NUMBER1_0_1_, drivinglic0_.DATE_OF_ISSUE as DATE_OF_ISSUE2_0_1_, drivinglic0_.PERSON_ID as PERSON_ID3_0_1_, person1_.PERSON_ID as PERSON_ID1_1_0_, person1_.PERSON_NAME as PERSON_NAME2_1_0_ from DRIVING_LICENSE drivinglic0_ left outer join PERSON person1_ on drivinglic0_.PERSON_ID=person1_.PERSON_ID where drivinglic0_.PERSON_ID=?
Hibernate: select drivinglic0_.LICENSE_NUMBER as LICENSE_NUMBER1_0_1_, drivinglic0_.DATE_OF_ISSUE as DATE_OF_ISSUE2_0_1_, drivinglic0_.PERSON_ID as PERSON_ID3_0_1_, person1_.PERSON_ID as PERSON_ID1_1_0_, person1_.PERSON_NAME as PERSON_NAME2_1_0_ from DRIVING_LICENSE drivinglic0_ left outer join PERSON person1_ on drivinglic0_.PERSON_ID=person1_.PERSON_ID where drivinglic0_.PERSON_ID=?
Hibernate: select drivinglic0_.LICENSE_NUMBER as LICENSE_NUMBER1_0_1_, drivinglic0_.DATE_OF_ISSUE as DATE_OF_ISSUE2_0_1_, drivinglic0_.PERSON_ID as PERSON_ID3_0_1_, person1_.PERSON_ID as PERSON_ID1_1_0_, person1_.PERSON_NAME as PERSON_NAME2_1_0_ from DRIVING_LICENSE drivinglic0_ left outer join PERSON person1_ on drivinglic0_.PERSON_ID=person1_.PERSON_ID where drivinglic0_.PERSON_ID=?

clearly for fetching 3 rows hibernate fired 4 queries, how to solve this problem? am I doing something wrong with relationships?

update

now if I fetch with Driving license like

Query query = entityManager.createQuery("from DrivingLicense dl");

its even more worse, 7 queries are fired.

Upvotes: 4

Views: 1595

Answers (2)

SubOptimal
SubOptimal

Reputation: 22973

To have a JPA vendor independent solution you could use following JPQL queries.

get all Person with a DrivingLicense

Query query = entityManager.createQuery("from Person p join fetch p.drivingLicense");

get all Person idependent if they have a DrivingLicense or not

Query query = entityManager.createQuery("from Person p left join fetch p.drivingLicense");

or using the Criteria API

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Person> criteriaQuery = criteriaBuilder.createQuery(Person.class);
Root<Person> root = criteriaQuery.from(Person.class);
root.fetch("drivingLicense", JoinType.INNER);
criteriaQuery.select(root);
List<Person> resultList = em.createQuery(criteriaQuery).getResultList();

resp.

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Person> criteriaQuery = criteriaBuilder.createQuery(Person.class);
Root<Person> root = criteriaQuery.from(Person.class);
root.fetch("drivingLicense", JoinType.LEFT);
criteriaQuery.select(root);
List<Person> resultList = em.createQuery(criteriaQuery).getResultList();

Upvotes: 1

udit khare
udit khare

Reputation: 354

This seems due to your query, i.e

Query query = entityManager.createQuery("from Person p");

Instead use: 

session.createCriteria();

This will fire only 1 query to fetch Person:

Hibernate: select this_.id as id1_1_1_, this_.name as name2_1_1_, drivinglic2_.id as id1_0_0_, drivinglic2_.DL_no as DL_no2_0_0_, drivinglic2_.PERSON_ID as PERSON_I3_0_0_ from PERSON this_ left outer join DRIVING_LICENSE drivinglic2_ on this_.id=drivinglic2_.PERSON_ID order by this_.id asc

Upvotes: 0

Related Questions