How to solve the hibernate N+1 problem?
In hibernate, one-to-one relationship causes the N+1 problem.
for example a husband has a wife, and a wife has a husband. it's a typical example. go on the following scenario. if we query the specified husband list. hibernate querys it in this hql: from Husband a [where ...] then the problem appears. one husband in the list don't know his wife 'cause there's a foreign key in wife referring to her husband but not in husband referring to his wife. so hibernate does another query for each. something like: from Wife b where b.husband=? so we can clearly see that N+1 problem occurs. It's really an annoyed problem. how to solve it? it's hard to say. but fortunately some one does a lot of practice. there're two solutions. first , change the table structure. add a foregin key in husband table referring to his wife table. it makes a many-to-one relationship between husband and wife, our hbm may look like: <many-to-one name="wife" column="WIFE_ID" not-null="false" cascade="all" class="mypackage.Wife" > </many-to-one> but if it's not doable, we'd better get the second one, this solution don't change the table structure, and hibernate supplys formula which helps to define the fetch mode. the code below presents it: <many-to-one name="wife" not-null="false" cascade="all" class="mypackage.Wife" > <formula>(select w.ID from WIFE w where w.HUSBAND_ID = ID)</formula> </many-to-one> Note that the second one can also cause N+1 problem if we want to show the information of the husband and his wife in the same page. the configuration above can only tell hibernate how to get the related wife's id, so if we code as the following: List <Husband> list = query.list(); list.get(0).getWife(); hibernate also does another query to the database. so I'm looking forward to your solutions and your share. |