create table users( id int primary key auto_increment, username varchar(255) not null, email varchar(255) unique not null, registered_at date not null ); create table orders( id int primary key auto_increment, user_id int, order_date date not null, amount decimal(10,2) not null, foreign key (user_id) references users(id) )
测试数据
-- 插入用户数据 insert into users(username, email, registered_at) values('user1', '<EMAIL>', '2023-04-01'); insert into users(username, email, registered_at) values('user2', '<EMAIL>', '2023-04-02'); -- 插入订单数据 insert into orders(user_id, order_date, amount) values(1, '2023-04-03', 100.00); insert into orders(user_id, order_date, amount) values(1, '2023-04-04', 50.00); insert into orders(user_id, order_date, amount) values(2, '2023-04-05', 75.00);
定义实体类
public class User { private int id; private String username; private String email; private Date registeredDate; // getter & setter}
public class Order { private int id; private int userId; private Date orderDate; private BigDecimal amount; // getter & setter}
完善User类
public class User { private int id; private String username; private String email; private Date registeredDate; private List<Order> orders; // getter & setter}
在接口中实现
@Mapper@Repositorypublic interface UserRepository { @Select("SELECT * FROM users WHERE id = #{id}") public User getUserByUserId(int id); // 查询用户及其所有订单 @Select("select * from users where id = #{userId}") @Results({ @Result(property = "id", column = "id"), @Result(property = "username", column = "username"), @Result(property = "email", column = "email"), @Result(property = "registeredDate", column = "registered_date"), @Result(property = "orders", javaType = Select.List.class, column = "id", many = @Many(select = "getOrderByUserId")) }) User getUserWithOrders(int userId); // 根据用户id获取所有订单 @Select("select * from orders where user_id = #{userId}") List<Order> getOrderByUserId(int userId); }
一对一
在一对一关系中，可以使用@One注解来处理
完善Order类
public class Order { private int id; private int userId; private Date orderDate; private BigDecimal amount; private User user; // getter & setter}
创建OrderRepository接口
@Mapper@Repositorypublic interface OrderRepository { @Select("SELECT * FROM orders WHERE id = #{orderId}") @Results({ @Result(property = "id", column = "id"), @Result(property = "orderDate",column = "order_date"), @Result(property = "amount",column = "amount"), @Result(property = "user",column = "user_id", one = @One (select = "com.example.filmreview_java.repository.UserRepository.getUserByUserId")) }) Order getOrderById(int orderId); }
<?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.filmreview_java.repository.UserRepository"> <select id="getUserByUserId" resultType="com.example.filmreview_java.entity.User"> SELECT * FROM users WHERE id = #{id} </select> <resultMap id="UserWithOrdersMap" type="com.example.filmreview_java.entity.User"> <id property="id" column="id"/> <result property="username" column="username"/> <result property="email" column="email"/> <result property="registeredDate" column="registered_date"/> <collection property="orders" ofType="com.example.filmreview_java.entity.Order" select="getOrderByUserId" column="id"/> </resultMap> <select id="getUserWithOrders" resultMap="UserWithOrdersMap"> SELECT * FROM users WHERE id = #{userId} </select> <select id="getOrderByUserId" resultType="com.example.filmreview_java.entity.Order"> SELECT * FROM orders WHERE user_id = #{userId} </select> </mapper>
特别注意！！
简单映射用 select，复杂映射使用 resultMap
在编写 resultType 字段时，必须使用完整的类路径，否则程序会出错！
启动类
使用@MapperScan注解来启动MyBatis Mapper接口的自动扫描
@SpringBootApplication@MapperScan("com.example.filmreview_java.repository") public class FilmReviewJavaApplication { public static void main(String[] args) { SpringApplication.run(FilmReviewJavaApplication.class, args); }}