數據庫設計

建表 SQL

CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL
);CREATE TABLE `order` (id INT PRIMARY KEY AUTO_INCREMENT,user_id INT NOT NULL,order_no VARCHAR(50) NOT NULL,FOREIGN KEY (user_id) REFERENCES user(id)
);CREATE TABLE role (id INT PRIMARY KEY AUTO_INCREMENT,role_name VARCHAR(50) NOT NULL
);CREATE TABLE user_role (user_id INT NOT NULL,role_id INT NOT NULL,PRIMARY KEY (user_id, role_id),FOREIGN KEY (user_id) REFERENCES user(id),FOREIGN KEY (role_id) REFERENCES role(id)
);

插入以下數據：

INSERT INTO user (name) VALUES ('Alice'), ('Bob');
INSERT INTO `order` (user_id, order_no) VALUES (1, 'ORD001'), (1, 'ORD002'), (2, 'ORD003');
INSERT INTO role (role_name) VALUES ('Admin'), ('User');
INSERT INTO user_role (user_id, role_id) VALUES (1, 1), (1, 2), (2, 2);

MyBatis 配置

我們使用 MySQL 數據庫，MyBatis 的核心配置文件如下：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="com.mysql.cj.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/test_db?useSSL=false"/><property name="username" value="root"/><property name="password" value="123456"/></dataSource></environment></environments><mappers><mapper resource="mapper/UserMapper.xml"/></mappers>
</configuration>

定義 Java 實體類，映射數據庫表：

@Data
public class User {private Integer id;private String name;private Order order; // 一對一：用戶關聯一個訂單private List<Order> orders; // 一對多：用戶關聯多個訂單private List<Role> roles; // 多對多：用戶關聯多個角色
}@Data
public class Order {private Integer id;private Integer userId;private String orderNo;
}@Data
public class Role {private Integer id;private String roleName;
}

MyBatis 映射文件

在 UserMapper.xml 中定義關聯查詢，展示一對一、一對多和多對多的實現。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UserMapper"><!-- 一對一 --><resultMap id="UserWithOrderMap" type="com.example.entity.User"><id property="id" column="user_id"/><result property="name" column="user_name"/><association property="order" javaType="com.example.entity.Order"><id property="id" column="order_id"/><result property="userId" column="user_id"/><result property="orderNo" column="order_no"/></association></resultMap><select id="selectUserWithOrder" resultMap="UserWithOrderMap">SELECT *FROM user uLEFT JOIN `order` o ON u.id = o.user_idWHERE u.id = #{id}LIMIT 1</select><!-- 一對多 --><resultMap id="UserWithOrdersMap" type="com.example.entity.User"><id property="id" column="user_id"/><result property="name" column="user_name"/><collection property="orders" ofType="com.example.entity.Order"><id property="id" column="order_id"/><result property="userId" column="user_id"/><result property="orderNo" column="order_no"/></collection></resultMap><select id="selectUserWithOrders" resultMap="UserWithOrdersMap">SELECT *FROM user uLEFT JOIN `order` o ON u.id = o.user_idWHERE u.id = #{id}</select><!-- 多對多 --><resultMap id="UserWithRolesMap" type="com.example.entity.User"><id property="id" column="user_id"/><result property="name" column="user_name"/><collection property="roles" ofType="com.example.entity.Role"><id property="id" column="role_id"/><result property="roleName" column="role_name"/></collection></resultMap><select id="selectUserWithRoles" resultMap="UserWithRolesMap">SELECT *FROM user uLEFT JOIN user_role ur ON u.id = ur.user_idLEFT JOIN role r ON ur.role_id = r.idWHERE u.id = #{id}</select>
</mapper>

Mapper 接口

定義對應的 Mapper 接口：

public interface UserMapper {User selectUserWithOrder(Integer id); // 一對一User selectUserWithOrders(Integer id); // 一對多User selectUserWithRoles(Integer id); // 多對多
}

以下是測試代碼，驗證查詢結果：

public class MyBatisTest {public static void main(String[] args) throws Exception {SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(new FileInputStream("mybatis-config.xml"));try (SqlSession session = factory.openSession()) {UserMapper mapper = session.getMapper(UserMapper.class);// 測試一對一User userWithOrder = mapper.selectUserWithOrder(1);System.out.println(userWithOrder.getName());// 測試一對多User userWithOrders = mapper.selectUserWithOrders(1);System.out.println(userWithOrders.getName());userWithOrders.getOrders().forEach(order -> System.out.println("  - " + order.getOrderNo()));// 測試多對多User userWithRoles = mapper.selectUserWithRoles(1);System.out.println(userWithRoles.getName());userWithRoles.getRoles().forEach(role -> System.out.println("  - " + role.getRoleName()));}}
}

總結

一對一：通過 association 標簽，查詢用戶及其關聯的一個訂單。
一對多：通過 collection 標簽，查詢用戶及其所有訂單，MyBatis 自動將多行訂單數據映射到 List。
多對多：通過 collection 標簽，查詢用戶及其所有角色，借助中間表 user_role 實現關聯。