mybatis多表查询怎么查询代码示例
时间: 2024-05-11 13:12:14 浏览: 9
在MyBatis中进行多表查询可以通过以下几种方式实现:
1. 使用嵌套查询(Nested Queries):在Mapper XML文件中使用嵌套查询语句,通过关联条件将多个表连接起来,然后获取所需的数据。
示例代码如下:
```xml
<!-- Mapper XML -->
<select id="getOrderDetails" resultMap="orderDetailResultMap">
SELECT od.order_id, od.product_id, p.product_name, od.quantity
FROM order_details od
INNER JOIN products p ON od.product_id = p.product_id
WHERE od.order_id = #{orderId}
</select>
<!-- ResultMap -->
<resultMap id="orderDetailResultMap" type="OrderDetail">
<id property="orderId" column="order_id"/>
<result property="productId" column="product_id"/>
<result property="productName" column="product_name"/>
<result property="quantity" column="quantity"/>
</resultMap>
```
2. 使用关联映射(Association Mapping):在实体类中定义关联关系,并在Mapper XML文件中使用关联映射来获取多个表的数据。
示例代码如下:
```java
// 实体类
public class Order {
private int orderId;
private List<OrderDetail> orderDetails;
// getter和setter方法省略
}
public class OrderDetail {
private int orderDetailId;
private int orderId;
private int productId;
private Product product;
// getter和setter方法省略
}
public class Product {
private int productId;
private String productName;
// getter和setter方法省略
}
<!-- Mapper XML -->
<select id="getOrderWithDetails" resultMap="orderResultMap">
SELECT o.order_id, od.order_detail_id, od.product_id, p.product_name
FROM orders o
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
WHERE o.order_id = #{orderId}
</select>
<resultMap id="orderResultMap" type="Order">
<id property="orderId" column="order_id"/>
<collection property="orderDetails" ofType="OrderDetail" resultMap="orderDetailResultMap"/>
</resultMap>
<resultMap id="orderDetailResultMap" type="OrderDetail">
<id property="orderDetailId" column="order_detail_id"/>
<association property="product" javaType="Product" resultMap="productResultMap"/>
</resultMap>
<resultMap id="productResultMap" type="Product">
<id property="productId" column="product_id"/>
<result property="productName" column="product_name"/>
</resultMap>
```
3. 使用嵌套结果(Nested Results):在Mapper XML文件中使用嵌套结果来获取多个表的数据。
示例代码如下:
```xml
<!-- Mapper XML -->
<select id="getOrderWithDetails" resultMap="orderResultMap">
SELECT o.order_id, od.order_detail_id, od.product_id, p.product_name
FROM orders o
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
WHERE o.order_id = #{orderId}
</select>
<resultMap id="orderResultMap" type="Order">
<id property="orderId" column="order_id"/>
<collection property="orderDetails" ofType="OrderDetail">
<id property="orderDetailId" column="order_detail_id"/>
<result property="productId" column="product_id"/>
<association property="product" javaType="Product">
<id property="productId" column="product_id"/>
<result property="productName" column="product_name"/>
</association>
</collection>
</resultMap>
```
以上是三种常见的MyBatis多表查询的方式,你可以根据具体的需求选择适合的方式来实现。如果还有其他问题,请继续提问。