Query handling for Null reference in Hibernate

Question

I Am using hibernate and i have a Entity like

@Entity
@Table(name = "MyEntity")
Class MyEntity {
        @Id
    @GeneratedValue
    private long id;

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

    //some more attributes here 

        @OneToOne
    @JoinColumn(name = "PARENT_ID")
        MyEntity parent;

}

I have one record in database

id   |  name | parent_id 

125 |   n1  |   null

and when i am trying to get this record with hibernate Query

Select e.id,e.name,e.parent.name from MyEntity e where e.id =125

this query is returning me zero records.because the parent is null here, so is there any way to handle this kind of situation. thanks in advc.

it doesn't make sense. why do you think it's because the parent is null? — Elias Dorneles, Dec 13 '11 at 12:20
but i want that record .it should return e.parent.name null if parent is null. — Sagar Varpe, Dec 13 '11 at 12:33
the `e.parent.name` leads to an inner join which fails because of null. Check this out: [http://stackoverflow.com/questions/601615/how-to-simulate-nvl-in-hql](http://stackoverflow.com/questions/601615/how-to-simulate-nvl-in-hql) — Thomas Rawyler, Dec 13 '11 at 12:39

score 5 · Accepted Answer · answered Dec 13 '11 at 12:38

In your case Hibernate implicitly uses inner join that doesn't return anything when one of the sides is null.

You can instruct Hibernate to use left outer join instead as follows:

select e.id, e.name, p.name from MyEntity e left join e.parent p where e.id = 125

score -1 · Answer 2 · edited May 23 '17 at 12:27

Like others said, this leads to an innerjoin...

You may add the following properties to your hibernate:

hibernate.show_sql=true
hibernate.format_sql=true

This way you would be able to know what hibernate is trying to do and notice by yourself such problems...

If the SQL generated doesn't give you the expected result, then you have a problem in your hibernate request. In this case it's not hard to understand that you must tell hibernate to do a left outer join instead of an inner join.

If one day you need method parameters, look at this: Hibernate show real SQL

You could also have used the Criteria api which is pretty simple et powerful to use and maintain when there are no joins in every directions.

Query handling for Null reference in Hibernate

2 Answers2