目录

1) 基本查询

1.1) 一对一查询

场景：订单所对应的用户

Model:

public class User {
  private int id;
  private String username;
  private String password;
}
public class Order {
  private int id;
  private Date ordertime;
  private User user; // 订单归属用户
}

Mapper 有两种写法：

<resultMap id="orderMap" type="cat.wars.mybatis_guide.model.Order">
    <result property="id" column="id"/>
    <result property="ordertime" column="ordertime"/>
    <association property="user" javaType="cat.wars.mybatis_guide.model.User">
        <result property="id" column="uid"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
    </association>
</resultMap>
<resultMap id="orderMap2" type="cat.wars.mybatis_guide.model.Order">
    <result property="user.id" column="uid"/>
    <result property="user.username" column="username"/>
    <result property="user.password" column="password"/>
</resultMap>
<select id="orderList">
    SELECT * FROM orders,user u WHERE o.uid = u.id
</select>

1.2) 一对多查询

场景：用户所对应的订单

Model:

public class Order {
  private int id;
  private Date ordertime;
  private User user; // 订单归属用户
}
public class User {
  private int id;
  private String username;
  private String password;
  private ArrayList<Order> orders; // 用户下所有订单
}

Mapper:

<resultMap id="userMap" type="cat.wars.mybatis_guide.model.User">
    <id property="id" column="uid"/>
    <result property="username" column="username"/>
    <result property="password" column="password"/>
    <collection property="orders" ofType="cat.wars.mybatis_guide.model.Order">
        <id property="id" column="id"/>
        <result property="orderTime" column="order_time"/>
    </collection>
</resultMap>
<select id="userList" resultMap="userMap">
        SELECT o.uid, username, password,
               o.id, order_time
        FROM user u
                 LEFT JOIN orders o ON u.id = o.uid
</select>

1.3) 多对多查询

场景：用户角色多对多，会有一张中见表存在

Model:

public class User {
  private int id;
  private String username;
  private String password;
  private ArrayList<Order> orders; // 用户下所有订单
  private ArrayList<Role> roles; // 此用户的所有角色
}
public class Role {
  private int id;
  private String roleName;
}

Mapper:

<resultMap id="userMap2" type="cat.wars.mybatis_guide.model.User">
        <id property="id" column="uid"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <collection property="orders" ofType="cat.wars.mybatis_guide.model.Order">
            <id property="id" column="id"/>
            <result property="orderTime" column="order_time"/>
        </collection>
        <collection property="roles" ofType="cat.wars.mybatis_guide.model.Role">
            <id property="id" column="rid"/>
            <result property="roleName" column="role_name"/>
        </collection>
</resultMap>
<select id="userList2" resultMap="userMap2">
        SELECT o.uid, username, password,
               o.id, order_time,
               rid, role_name
        FROM user u
                 LEFT JOIN orders o ON u.id = o.uid
                 LEFT JOIN user_role ur ON u.id = ur.uid
                 LEFT JOIN role r ON ur.rid = r.id
</select>

2) 注解操作

注解一定意义上可以代替我们的 mapper.xml 配置，嗯......还是比较习惯 XML 搭配 IDEA 的插件写 MyBatis

2.1) 增

@Insert

2.2) 删

@Delete

2.3) 改

@Update

2.4) 查

查询的常用注解大概是这 5 个：

  • @Select
  • @Result
  • @Results
  • @One
  • @Many

2.4.1) 一对一

场景：订单所对应的用户

UserMapper:

  @Select("SELECT * FROM user WHERE id = #{id}")
  User findById(int id);

OrderMapper:

@Select("SELECT * FROM orders o, user u WHERE o.uid = u.id")
@Results({
        @Result(property = "id", column = "id"),
        @Result(property = "orderTime", column = "order_time"),
        @Result(property = "user", column = "uid", javaType = User.class,
                one = @One(select = "cat.wars.mybatis_guide.mapper.UserMapper.findById")
        )
})
List<Order> orderList2();

2.4.2) 一对多

场景：用户名下的所有订单

OrderMapper:

@Select("SELECT id, order_time orderTime FROM orders WHERE uid = #{userId}")
List<Order> orderList3(int userId);

UserMapper:

@Select("SELECT * FROM user")
@Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(
                property = "orders", column = "id", javaType = List.class,
                many = @Many(select = "cat.wars.mybatis_guide.mapper.OrderMapper.orderList3")
        )
})
List<User> userList2();

2.4.3) 多对多

场景：用户的所有角色

RoleMapper:

@Select("SELECT id, role_name roleName FROM user_role ur, role r WHERE ur.rid = r.id AND ur.uid = #{userId}")
List<Role> findByUId(int userId);

UserMapper:

@Select("SELECT * FROM user")
@Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(
                property = "orders", column = "id", javaType = List.class,
                many = @Many(select = "cat.wars.mybatis_guide.mapper.OrderMapper.orderList3")
        ),
        @Result(
                property = "roles", column = "id", javaType = List.class,
                many = @Many(select = "cat.wars.mybatis_guide.mapper.RoleMapper.findByUId")
        )
})
List<User> userList2();