Turn: ibatis's n + 1 problem solution

N + 1 selection problem definition:
The n + 1 selects problem is caused by trying to load child records that are related to a list of parent records.

There are three solutions in ibatis

1. Lazy Loading
2. Avoid n + 1 select
3. Use two select statements to retrieve data from two tables and then assemble

1 lazy loading:

First set lazyloadingenabled = "true"
Note that

<Resultmap id = "flightandpricedo"
 Class = "com. Taobao. Et. biz. Dal. Core. dataobject. flightinfodo">
 <Result property = "ID" column = "ID" jdbctype = "Number"/>
 ........
 <Result property = "priceinfos" column = "ID"
  Select = "flightinfodo. getpriceinfosbyflightid"/>
 </Resultmap>

The SELECT statement is the same for the same table.
<Select id = "getflightinfowithprices" resultmap = "flightandpricedo">
<! [CDATA [
Select
ID,
Standard_price
From flight_result
Where dep_airport_code = # depairport. airportcode #
AndArr_airport_code = # arrairport. airportcode #
]>
</SELECT>
<Select id = "getpriceinfosbyflightid" parameterclass = "Java. Lang. Long"
Resultmap = "cabinpricedo">
<! [CDATA [
Select
ID,
Flightid,
From cabin_price
Where flightid = # value #
And cabin_number! = '0'

]>
</SELECT>

2. Avoid n + 1 select

Modify the Mapping File
And select statements

<Resultmap id = "flightandpricedoavoidn1"
 Class = "com. Taobao. Et. biz. Dal. Core. dataobject. flightinfodo" groupby = "ID">
 <Result property = "ID" column = "ID" jdbctype = "Number"/>
 <Result property = "depairport. airportcode"
  Column = "dep_airport_code" jdbctype = "varchar2"/>
 <Result property = "priceinfos"Resultmap = "flightinfodo. cabinpricedo"/>
</Resultmap>

<Resultmap id = "cabinpricedo"
Class = "com. Taobao. Et. biz. Dal. Core. dataobject. cabinpricedo">
<Result property = "ID" column = "ID" jdbctype = "Number"/>
<Result property = "flightid" column = "flightid" jdbctype = "Number"/>
</Resultmap>

<Select id = "getflightinfowithpricesaVoidn1 "resultmap =" flightandpricedoavoidn1 ">
<! [CDATA [
Select *
From flight_result a join cabin_price B on A. ID = B. flightid
Where a. dep_airport_code = # depairport. airportcode #
AndA. arr_airport_code = # arrairport. airportcode #
Order by A. ID, B. flightid
]>
</SELECT>

3. Use two select statements to retrieve data from two tables and then assemble
A sub-table must provide a select * From zibiao in () method.

Comparison of the three solutions has their own Applicability:

The first scheme is mainly used for data that does not need to be retrieved from the master table and sub-table at a time. Instead, the data in the sub-table is retrieved again and again through more detailed queries.
The second solution is suitable for Master/Slave tables with a small amount of data. A single statement is used to retrieve all data.
The third solution is to query the assembly results at the program end through two select statements.