CODEWITHSUNDEEP
javahibernatehibernate-mapping
partyboy
partyboy

Reputation: 165

ManyToOne Outer Join in a single query (1 to 0 relationship)

I'm struggling with a mapping problem in hibernate.

Got This mapping between 2 tables

Table 1

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name="CNT_ID",referencedColumnName = "CNT_ID",insertable = false, updatable = false)
    @NotFound(action=NotFoundAction.IGNORE)
    private Table2 table2;

Table 2

@OneToMany(mappedBy="table2")
private List<Table1> table1List;

I'm making a query like this : 

select table1 from Table1 table1 left outer join table1.table2

The result is ok : got all data in table1 and some table2 (some table1 don't have link to table2) so the outer join is ok.

The problem is that hibernate is making one query to catch all table1 and after 1 query for each line result for table1. So If I have 100lines in table 1, hibernate is making 101 queries ...

Do I miss something ? I'm working with oracle database, also try the (+) operator (outer operator in oracle) but hibernate didn't want it.

Thanks for your help !

Upvotes: 2

Views: 3107

Answers (2)

v.ladynev
v.ladynev

Reputation: 19956

Firstly, you need to change fetch of table2 to FetchType.LAZY. Always do so, if you don't have a good reasons to eagerly fetching a @ManyToOne part of the association.

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="CNT_ID",referencedColumnName = "CNT_ID",insertable = false, updatable = false)
@NotFound(action=NotFoundAction.IGNORE)
private Table2 table2;

If you need to get Table1 that have some properties in table2 just use a join

from Table1 table1 left join table1.table2 table2 where table2.someProperty = :somePropertyValue

If you need to load table2 use a join fetch

from Table1 table1 left join fetch table1.table2 table2 where table2.someProperty = :somePropertyValue

if you need only part of table2 use projections

select table1.property1, table1.property2, table2.someProperty from Table1 table1 left join table1.table2 table2 where table2.someProperty = :somePropertyValue

If you will use a projections you can use a new syntax to populate DTO objects

select new com.pack.TableDto(table1.property1, table1.property2, table2.someProperty) from Table1 table1 left join table1.table2 table2 where table2.someProperty = :somePropertyValue

or you can use a transformer.

Upvotes: 2

isah
isah

Reputation: 5341

Because Table2(parent) is EAGER and you're selecting from Table1, for every row in Table1 Hibernate will issue a query to populate Table2 reference. One solution is to join fetch table2 reference on the first query. This is a known behavior of Hibernate when using HQL(with Criteria API I don't think it applies). I have blogged on detail about it here

Upvotes: 0

Related Questions