如何用mybatis-plus去写跨表(表关联)查询
时间: 2023-08-13 09:32:48 浏览: 50
使用 MyBatis-Plus 进行跨表查询主要有两种方式:
1. 使用注解 `@TableName` 和 `@TableId` 映射实体类,然后在 XML 文件中使用 `join` 关键字进行关联查询。
举一个例子,假设我们有两张表 `user` 和 `order`,并且它们之间是一对多的关系。我们可以定义两个实体类 `User` 和 `Order`,并使用注解 `@TableName` 和 `@TableId` 指定表名和主键字段。然后在 `OrderMapper.xml` 文件中使用 `join` 关键字进行关联查询:
```xml
<!-- UserMapper.xml -->
<select id="getUserWithOrders" resultMap="userWithOrdersResultMap">
SELECT u.*, o.*
FROM user u
LEFT JOIN order o ON u.id = o.user_id
WHERE u.id = #{id}
</select>
```
```java
// User.java
@Data
@TableName("user")
public class User {
@TableId("id")
private Long id;
private String name;
// ...
}
// Order.java
@Data
@TableName("order")
public class Order {
@TableId("id")
private Long id;
private Long userId;
private String orderNo;
// ...
}
// OrderMapper.java
public interface OrderMapper extends BaseMapper<Order> {
@Select("SELECT u.*, o.* FROM user u LEFT JOIN order o ON u.id = o.user_id WHERE u.id = #{id}")
@Results(id = "userWithOrdersResultMap", value = {
@Result(property = "id", column = "id"),
@Result(property = "name", column = "name"),
@Result(property = "orders", column = "id", many = @Many(select = "com.example.mapper.OrderMapper.getOrdersByUserId"))
})
User getUserWithOrders(@Param("id") Long id);
@Select("SELECT * FROM order WHERE user_id = #{userId}")
List<Order> getOrdersByUserId(@Param("userId") Long userId);
}
```
2. 使用 `@TableField` 注解在实体类中定义关联字段,然后在 XML 文件中使用 `LEFT JOIN` 进行关联查询。
举一个例子,假设我们有两张表 `user` 和 `order`,并且它们之间是一对多的关系。我们可以在 `Order` 实体类中使用 `@TableField` 注解定义关联字段 `user`,并在 `OrderMapper.xml` 文件中使用 `LEFT JOIN` 进行关联查询:
```xml
<!-- OrderMapper.xml -->
<select id="getOrdersWithUser" resultMap="ordersWithUserResultMap">
SELECT o.*, u.*
FROM order o
LEFT JOIN user u ON o.user_id = u.id
WHERE o.id = #{id}
</select>
```
```java
// User.java
@Data
@TableName("user")
public class User {
@TableId("id")
private Long id;
private String name;
// ...
}
// Order.java
@Data
@TableName("order")
public class Order {
@TableId("id")
private Long id;
private Long userId;
private String orderNo;
@TableField(exist = false)
private User user;
// ...
}
// OrderMapper.java
public interface OrderMapper extends BaseMapper<Order> {
@Select("SELECT o.*, u.* FROM order o LEFT JOIN user u ON o.user_id = u.id WHERE o.id = #{id}")
@Results(id = "ordersWithUserResultMap", value = {
@Result(property = "id", column = "id"),
@Result(property = "userId", column = "user_id"),
@Result(property = "orderNo", column = "order_no"),
@Result(property = "user", column = "id", one = @One(select = "com.example.mapper.UserMapper.selectById"))
})
Order getOrdersWithUser(@Param("id") Long id);
}
```
以上两种方式均可实现跨表查询。具体选择哪一种方式,根据实际业务需求和个人喜好来决定。