Hibernate default joining for nullable many-to-one

Mike Q picture Mike Q · Oct 6, 2009 · Viewed 16.4k times · Source

I have a hibernate mapping like this in a ProductDfn class

@ManyToOne( fetch = FetchType.LAZY, optional = true )
@JoinColumn( name = "productTypeFk", nullable = true )
public ProductType getProductType()
{
    return productType;
}

Note that the relationship is defined as optional (and the column is nullable).

When doing HQL something like this

select p.name as col1, p.productType.name as col2 from ProductDfn p

An inner join is used to join ProductDfn to ProductType as hibernate generates the explicit SQL join from the implicit join in the select clause.

However when doing the above when productType is null (in the DB) no row is returned because of the inner join. For this relationship I would like to have hibernate default to doing an outer join (because the relationship is optional) so I would get a "null" back for col2 rather than no row at all.

Does anyone know if this is possible?

Thanks.

Answer

ChssPly76 picture ChssPly76 · Oct 6, 2009

An inner join is used because you've explicitly listed p.productType.name in your select clause. This wouldn't have happened were you just to select ProductDfn since your fetch is set to LAZY.

If you only need to retrieve those two properties you'll have to explicitly specify an outer join in your query:

select p.name as col1, ptype.name as col2
  from ProductDfn p
  left join fetch p.productType ptype