JPA OneToOne generates additional queries

26 June 2015
By Gonçalo Marques
This article explains why do the Java Persistence API (JPA) often generates additional queries while dealing with OneToOne relationships

Sample case

It's true that JPA may generate additional queries while dealing with OneToOne relationships, and this is a subject where users usually ask for help in online forums and Q&A websites. This article will help you understand why this phenomenon occurs.

We will jump right through a practical example, since this is the best way to describe the scenario:

Suppose that we are representing a corporation with the typical Employees table. It happens that some of the employees may have a working Vehicle assigned to them. Since not all employees have an assigned vehicle, we conclude that the vehicle is in fact optional (this is an important detail).

A data sample of the just described scenario could look like the following:


EMPLOYEE:

EMPLOYEE_ID NAME
101 John
102 Mark
103 Paul


VEHICLE:

VEHICLE_ID EMPLOYEE_ID MODEL
1 101 Mitsubishi
2 103 Honda

And now the entities:

Employee

@Entity
@Table(name = "EMPLOYEE")
public class Employee {

  @Id
  @Column(name = "EMPLOYEE_ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @NotNull
  @Column(name = "NAME")
  private String name;

  @OneToOne(mappedBy = "employee")
  private Vehicle vehicle;

}



Vehicle

@Entity
@Table(name = "VEHICLE")
public class Vehicle {

  @Id
  @Column(name = "VEHICLE_ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @NotNull
  @Column(name = "MODEL")
  private String model;

  @NotNull
  @OneToOne
  @JoinColumn(name = "EMPLOYEE_ID")
  private Employee employee;

}

As you probably already know, when the JPA provider loads an entity, it will do one of the following for every LAZY loaded entity relationship:

  1. If the relationship is a collection (ex: OneToMany), it will create a proxy to the collection that will be initialized on the first access to any of the collection's properties;

  2. If the relationship is a single entity, it will create a proxy for the related entity if it knows that the relationship exists. On the other hand, it must assign a null reference to the related property if it knows that the relationship does not exist.

With the aforementioned rules in mind, and going back to our example, since the Vehicle is optional on the Employee entity and the foreign key belongs to the Vehicle table, there is no way for the JPA provider to know if an Employee that is being loaded from the database has a Vehicle without checking if there is actually a vehicle on the vehicles table that is related to the employee being loaded.

Since the JPA provider must check the vehicles table in order to verify if there is a vehicle for the employee being loaded, so it may assign a proxy for the relationship, or a null reference if there is no matching record, it will use this round-trip to the database to load the vehicle entity if there is actually one.

As we have just seen, this phenomenon only occurs if the relationship is optional. For mandatory OneToOne relationships [@OneToOne(mappedBy = "employee", optional = false)], the JPA provider will know that there is always a relationship so it will create a proxy and only load the related entity when it is actually accessed for the first time.

The same is true if we are actually loading a Vehicle. Since the vehicles table has a foreign key to the Employees table, the JPA provider will know if a vehicle being loaded has a corresponding employee, so it will create a proxy for the related employee without executing any additional query against the database.

Alternatives for the problem

The scenario we have just described in the previous section may quickly become problematic. Suppose that we want to generate a report containing all the corporation employees information, and we don't need the vehicles information in that same report. If we have thousands of employees, a single query for all the employees will trigger an additional query for the employee's vehicle for every single employee.

In order to work around the problem, one could actually define the Employee - Vehicle relationship as a OneToMany - ManyToOne relationship, and make sure that the collection contains only a single element. Additionally we expose helper methods that manipulate the single element collection under the covers, and hide direct access to the underlying collection:

Employee representing the single element Vehicle relationship as OneToMany

@Entity
@Table(name = "EMPLOYEE")
public class Employee {

  @Id
  @Column(name = "EMPLOYEE_ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @NotNull
  @Column(name = "NAME")
  private String name;

  @OneToMany(mappedBy = "employee")
  private List<Vehicle> vehicleList;

  public void setVehicle(Vehicle vehicle) {
    if (vehicleList == null) {
      vehicleList = new ArrayList<>();
    }
    vehicleList.clear();
    if (vehicle != null) {
      vehicleList.add(vehicle);
    }
  }

  public Vehicle getVehicle() {
    if (vehicleList != null && !vehicleList.isEmpty()) {
      return vehicleList.get(0);
    }
    return null;
  }

}



Vehicle with a ManyToOne relationship to the Employee entity

@Entity
@Table(name = "VEHICLE")
public class Vehicle {

  @Id
  @Column(name = "VEHICLE_ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @NotNull
  @Column(name = "MODEL")
  private String model;

  @NotNull
  @ManyToOne
  @JoinColumn(name = "EMPLOYEE_ID")
  private Employee employee;

}

What if someone manages to insert duplicated entries for the same Employee in the Vehicles table by using direct access to the database? Or if someone accesses the vehicleList in the Employee entity through reflection and inserts more than a single element into the collection?

One may create a UNIQUE index on the VEHICLE table, EMPLOYEE_ID column. With this physical database constraint we become protected against unexpected abnormalities that bypass our Employee entity definition. Alternatively one could also change the model and turn the VEHICLE table primary key equal to the corresponding EMPLOYEE_ID.

If creating an index is still not an option, one could create a custom annotation, let's call it SingleElement and annotate the relationship. Then we would use an entity listener to check for the collection size before persisting or updating the entity or after loading the entity:

Employee with attached listener

@Entity
@Table(name = "EMPLOYEE")
@EntityListeners(SingleElementListener.class)
public class Employee extends BaseEntity {

    @Id
    @Column(name = "EMPLOYEE_ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotNull
    @Column(name = "NAME")
    private String name;

    @OneToMany(mappedBy = "employee")
    @SingleElement
    private List<Vehicle> vehicleList;

    public void setVehicle(Vehicle vehicle) {
        if (vehicleList == null) {
            vehicleList = new ArrayList<>();
        }
        vehicleList.clear();
        vehicleList.add(vehicle);
    }

    public Vehicle getVehicle() {
        if (vehicleList != null && !vehicleList.isEmpty()) {
            return vehicleList.get(0);
        }
        return null;
    }

}

And the listener:

SingleElementListener

public class SingleElementListener {

  @PrePersist
  @PreUpdate
  @PostLoad
  public void checkSingleElement(BaseEntity entity) {
    // Access entity fields (collections) annotated with 
    // @SingleElement through reflection and check if 
    // any of the collections contains more than a single 
    // element.
    // If we find a collection containing more than a 
    // single element we throw an exception.
  }

}

Related Articles

Comments

About the author
Gonçalo Marques is a Software Engineer with several years of experience in software development and architecture definition. During this period his main focus was delivering software solutions in banking, telecommunications and governmental areas. He created the Bytes Lounge website with one ultimate goal: share his knowledge with the software development community. His main area of expertise is Java and open source.

GitHub profile: http://github.com/gonmarques

He is also the author of the WiFi File Browser Android application: