Bidirectional @OneToOne causing N+1 when performing query

Question

When I add an @OneToOne mapping on the non-owning side (User), I am getting extra selects loading Staff when performing a query. Once I remove the @OneToOne in the user class, N+1 problem is resolved.

User.staff is set to lazy, so it should not be trying to load that.

Anything I'm missing, or is this always the case with bidirectional associations?

/* named HQL query Staff.findByName */ 
/* load com.xxx.Staff */
/* load com.xxx.Staff */
/* load com.xxx.Staff */

PS. A user is not necessarily a staff member, but staff has to be a user.

Staff class

@Entity
@Table(name = "staff")
@NamedQueries({
@NamedQuery(name = Staff.QUERY_BY_NAME, query = "from Staff s left join fetch s.user u left join fetch u.staff where (lower(s.user.displayName) like :key) or (lower(s.user.lastName) like :key)")})
public class Staff extends AbstractDomainObject<Long> {
    public static final String QUERY_BY_NAME = "Staff.findByName";

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id")
    private User user;

User Class

@Entity
@Table(name = "env_user")
@SecondaryTable(name = "app_user")
public class User extends AbstractUserDetailsDomainObject<Long> {
    public static final String QUERY_BY_USERNAME = "User.findByUsername";

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;


    @OneToOne(optional = true, fetch = FetchType.LAZY, mappedBy = "user")
    private Staff staff;
}

I'd like to keep the reference on the user side, so that I can navigate to staff from the security principal. — miklesw, Dec 04 '13 at 12:10
See here: http://stackoverflow.com/questions/20074881/additional-queries-in-jpa/20075095#20075095 — Alan Hay, Dec 04 '13 at 12:33
On the user side it's optional, but I tried adding optional=false just the same with no success. I also tried LazyToOne with both proxy and no_proxy. @OneToOne(fetch = FetchType.LAZY, mappedBy = "user", optional = false) @LazyToOne(LazyToOneOption.PROXY) — miklesw, Dec 04 '13 at 13:22
Would switching to table-per-class inheritance resolve the eager fetching problem? (e.g. staff is-a user) — miklesw, Dec 04 '13 at 13:54

score 1 · Answer 1 · answered Dec 04 '13 at 15:02

Credit goes to Alan Hay for providing link with solution. Cleanest solution in my option is to use FieldHandled. @OneToMany compromises logical structure and the instrument stuff is too easy to miss and forget by future developers.

public class User extends AbstractUserDetailsDomainObject<Long> implements FieldHandled {
    public static final String QUERY_BY_USERNAME = "User.findByUsername";
    /**
     * <p>
     * Required to allow lazy loading of Staff @OneToOne association
     * </p>
     */
    private FieldHandler fieldHandler;

    @OneToOne(mappedBy = "user", fetch = FetchType.LAZY, optional = true)
    @LazyToOne(LazyToOneOption.NO_PROXY)
    private Staff staff;

   /**
     * <p>
     * Staff Getter
     * </p>
     * Uses fieldhandler to force lazy loading
     * 
     * @return the staff
     */
    public Staff getStaff() {
        Staff result = this.staff;
        if (this.fieldHandler != null) {
            result = (Staff) this.fieldHandler.readObject(this, "staff", this.staff);
        }
        return result;
    }

    /**
     * <p>
     * Staff Setter
     * </p>
     * Uses fieldhandler to force lazy loading
     * 
     * @param staff
     *            the staff to set
     */
    public void setStaff(final Staff staff) {
        if (this.fieldHandler != null) {
            this.staff = (Staff) this.fieldHandler.writeObject(this, "staff", this.staff, staff);
        } else {
            this.staff = staff;
        }
    }

    /**
     * <p>
     * FieldHandled interface method.
     * </p>
     * Required to allow lazy loading of Staff @OneToOne association
     */
    public FieldHandler getFieldHandler() {
        return this.fieldHandler;
    }

    /**
     * <p>
     * FieldHandled interface method.
     * </p>
     * Required to allow lazy loading of Staff @OneToOne association
     */
    public void setFieldHandler(final FieldHandler fieldHandler) {
        this.fieldHandler = fieldHandler;

    }

}

Bidirectional @OneToOne causing N+1 when performing query

1 Answers1