在MyBatis中處理多表關聯查詢有多種方法,以下是其中一種常用的方法:
<select id="selectUserById" resultType="User">
select * from user where id=#{id}
</select>
<select id="selectOrdersByUserId" resultType="Order">
select * from orders where user_id=#{userId}
</select>
<select id="selectUserWithOrders" resultMap="UserWithOrdersResultMap">
select * from user where id=#{id}
<collection property="orders" column="id" select="selectOrdersByUserId"/>
</select>
<resultMap id="UserWithOrdersResultMap" type="User">
<result property="id" column="id"/>
<result property="name" column="name"/>
<collection property="orders" ofType="Order">
<id property="orderId" column="order_id"/>
<result property="orderName" column="order_name"/>
</collection>
</resultMap>
在以上示例中,先定義了兩個基本的查詢語句selectUserById和selectOrdersByUserId,然后通過selectUserWithOrders來實現多表關聯查詢,同時使用resultMap來映射返回結果。
<select id="selectUserWithOrders" resultMap="UserWithOrdersResultMap">
select u.id as userId, u.name as userName, o.id as orderId, o.name as orderName
from user u
join orders o on u.id = o.user_id
where u.id=#{id}
</select>
<resultMap id="UserWithOrdersResultMap" type="User">
<result property="id" column="userId"/>
<result property="name" column="userName"/>
<collection property="orders" ofType="Order">
<id property="orderId" column="orderId"/>
<result property="orderName" column="orderName"/>
</collection>
</resultMap>
在以上示例中,使用JOIN關鍵字將user表和orders表關聯起來,通過resultMap來映射返回結果。
以上是兩種常用的處理多表關聯查詢的方法,開發者可以根據具體情況選擇合適的方式來實現多表關聯查詢。