Hibernate produces extra sql

Question

I have two entities: Parent and Child. And there is @OneToOne relationship between them.

I have the following query: from Parent p left join fetch p.child.

If there is a row in Parent with non existing child key(-1 for example) hibernate issuing additional query for each case.

Hibernate: 
    select
        parent0_.PARENT_ID as PARENT1_2_0_,
        child1_.CHILD_ID as CHILD1_3_1_,
        parent0_.CHILD_ID as CHILD2_2_0_,
        child1_.NAME as NAME3_1_ 
    from
        PARENT parent0_ 
    left outer join
        CHILD child1_ 
            on parent0_.CHILD_ID=child1_.CHILD_ID
Hibernate: 
    select
        child0_.CHILD_ID as CHILD1_3_0_,
        child0_.NAME as NAME3_0_ 
    from
        CHILD child0_ 
    where
        child0_.CHILD_ID=?

Here's the code:

@Entity
@Table(name = "PARENT")
public class Parent {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "PARENT_ID", nullable = false)
    private int id;

    @OneToOne(optional = true)
    @JoinColumn(name = "CHILD_ID")
    @NotFound(action = NotFoundAction.IGNORE)
    private Child child;
}

@Entity
@Table(name="CHILD")
public class Child {


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "CHILD_ID", nullable = false)
    private int id;

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

Why does this happen? How to prevent this extra sql?

Here's the code for database generation:

create table PARENT(
PARENT_ID INTEGER GENERATED ALWAYS AS IDENTITY,
CHILD_ID  INTEGER, 
PRIMARY KEY (PARENT_ID)
);

create table CHILD(
CHILD_ID  INTEGER GENERATED ALWAYS AS IDENTITY,
NAME VARCHAR(100) NOT NULL,
PRIMARY KEY (CHILD_ID)
);

insert into PARENT(CHILD_ID) values(-1);

score 0 · Accepted Answer · answered Jun 27 '12 at 09:48

0

Design your database properly, and don't store non-existing child IDs in the PARENT.CHILD_ID column. Store NULL in this column if the parent doesn't have a child, and create a foreign key constraint for the column to make sure the column always holds NULL or a valid child ID.

This will allow you to remove the @NotFound annotation from your mapping, and will remove the additional query. Indeed, when Hibernate executes the first query, it gets -1 as the child ID. But since there is no way to know if -1 is a valid child ID or not, it must execute the additional query to decide if the child exists (and should thus be initialized in the parent), or doesn't (and should thus be set to null in the parent).

answered Jun 27 '12 at 09:48

JB Nizet

678,734
91
1,224
1,255

You are absolutely right about database design. But we have some legacy database and we have to deal with it somehow. Is there some workaround for this? To tell Hibernate don't try to check existence of the child if there is null in the result sql. – netslow Jun 27 '12 at 11:52
If there is null, it won't check. The problem is that there isn't null, there is -1. AFAIK, there's no workaround, other the sanitizing the data. – JB Nizet Jun 27 '12 at 11:54
It puzzles me that Hibernate isn't able to figure this out if you set not-found=ignore and eagerly fetch the association. If you eagerly fetch the association, say, with a join then Hibernate would in theory be able to figure out that the ID not corresponds to any row in the joined table...and it could thus just set it to null. – sbrattla Oct 26 '12 at 13:18
@sbrattla: it could, but I guess they didn't implement this optimization because NotFound is never used in a well-designed schema. – JB Nizet Oct 26 '12 at 15:40
That's right, but then again I'd say Hibernate should be able to accomodate different designs, and not the other way around. Anyway, this could become a long discussion...so I guess we just have to accept how it is and deal with it...by designing for Hibernate :-D – sbrattla Oct 28 '12 at 17:22

score 0 · Answer 2 · answered Jun 27 '12 at 11:55

You might possibly try a different fetchtype;

@OneToOne(optional = true, fetch = FetchType.EAGER)
private Child child;

But, like JB Nizet already explains, the underlying problem are the fictional identifiers stored in the the CHILD_ID field.

A different strategy would be to reverse the relationship, by either adding a mappedBy attribute in the @OneToOne annotation in the parent class, and adding a reference to the parent in the child class, or, only set the parent in the child class.

Hibernate produces extra sql

2 Answers2

Linked