源码
domain
User.java
package cn.com.xuxiaowei.domain;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
@Data
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
private String username;
/**
* 一对一
*/
private UserProfile userProfile;
/**
* 一对多对象
*/
private List<Orders> ordersList;
/**
* 一对多 String List
*/
private List<String> roles;
}
Orders.java
package cn.com.xuxiaowei.domain;
import lombok.Data;
import java.io.Serializable;
@Data
public class Orders implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
private Long userId;
private String orderNumber;
}
UserProfile.java
package cn.com.xuxiaowei.domain;
import lombok.Data;
import java.io.Serializable;
@Data
public class UserProfile implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
private Long userId;
private String address;
}
Mapper
UserMapper.java
package cn.com.xuxiaowei.mapper;
import cn.com.xuxiaowei.domain.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@Mapper
public interface UserMapper {
/**
* 根据 ID 查询用户（包含一对一、一对多）
* @param id 用户ID
* @return 用户信息
*/
User selectUserById(@Param("id") Long id);
}
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="cn.com.xuxiaowei.mapper.UserMapper">
<resultMap id="UserResultMap" type="User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<!-- 一对一 -->
<association property="userProfile" javaType="UserProfile">
<id column="profile_id" property="id"/>
<result column="profile_user_id" property="userId"/>
<result column="address" property="address"/>
</association>
<!-- 一对多对象 -->
<collection property="ordersList" ofType="Orders">
<id column="order_id" property="id"/>
<result column="order_user_id" property="userId"/>
<result column="order_number" property="orderNumber"/>
</collection>
<!-- 一对多 String List -->
<collection property="roles" ofType="java.lang.String">
<result column="role_name"/>
</collection>
</resultMap>
<select id="selectUserById" resultMap="UserResultMap">
SELECT
u.id,
u.username,
p.id AS profile_id,
p.user_id AS profile_user_id,
p.address,
o.id AS order_id,
o.user_id AS order_user_id,
o.order_number,
r.role_name
FROM `user` u
LEFT JOIN `user_profile` p ON u.id = p.user_id
LEFT JOIN `orders` o ON u.id = o.user_id
LEFT JOIN `user_roles` r ON u.id = r.user_id
WHERE u.id = #{id}
</select>
</mapper>
SQL
schema.sql
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `user_profile` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint DEFAULT NULL,
`address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `orders` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint DEFAULT NULL,
`order_number` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `user_roles` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint DEFAULT NULL,
`role_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
data.sql
INSERT INTO `user` (`id`, `username`) VALUES (1, 'Alice');
INSERT INTO `user_profile` (`user_id`, `address`) VALUES (1, '123 Wonderland St');
INSERT INTO `orders` (`user_id`, `order_number`) VALUES (1, 'ORD-001');
INSERT INTO `orders` (`user_id`, `order_number`) VALUES (1, 'ORD-002');
INSERT INTO `user_roles` (`user_id`, `role_name`) VALUES (1, 'ADMIN');
INSERT INTO `user_roles` (`user_id`, `role_name`) VALUES (1, 'USER');
Tests
UserMapperTests.java
package cn.com.xuxiaowei.mapper;
import cn.com.xuxiaowei.domain.Orders;
import cn.com.xuxiaowei.domain.User;
import cn.com.xuxiaowei.domain.UserProfile;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import tools.jackson.databind.ObjectMapper;
import tools.jackson.databind.ObjectWriter;
import java.util.List;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
@Slf4j
@SpringBootTest
class UserMapperTests {
@Autowired
private UserMapper userMapper;
@Test
void selectUserById() {
Long id = 1L;
User user = userMapper.selectUserById(id);
assertNotNull(user);
assertEquals(id, user.getId());
assertEquals("Alice", user.getUsername());
UserProfile userProfile = user.getUserProfile();
assertNotNull(userProfile);
assertEquals("123 Wonderland St", userProfile.getAddress());
List<Orders> ordersList = user.getOrdersList();
assertNotNull(ordersList);
// Expecting 2 orders
// If Cartesian product is not handled, this might fail or have duplicates
assertEquals(2, ordersList.size());
List<String> roles = user.getRoles();
assertNotNull(roles);
// Expecting 2 roles
assertEquals(2, roles.size());
ObjectMapper objectMapper = new ObjectMapper();
ObjectWriter objectWriter = objectMapper.writerWithDefaultPrettyPrinter();
String string = objectWriter.writeValueAsString(user);
log.info("User: \n{}", string);
}
}