MyBatis的注解实现复杂映射开发

​文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

实现复杂映射我们之前可以在映射文件中通过配置来实现，使用注解开发后，我们可以通过 @Results 注解，@Result 注解，@One 注解和 @Many 注解组合完成复杂关系的配置。文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

用户表和订单表的关系为，一个用户有多个订单，一共订单只属于一个用户文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

一对一查询需求：查询一个订单，与此同时查询该订单对应的用户文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

对应的 sql 语句文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

select * from orders;
select * from user where id=查询出订单的uid;

查询结果如下：文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

/**
 * 订单
 *
 * @name: Order
 * @author: terwer
 * @date: 2022-03-17 17:42
 **/
public class Order {
    private Integer id;
    private String orderTime;
    private Double total;

    // 代表当前订单属于哪一个用户
    private User user;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getOrderTime() {
        return orderTime;
    }

    public void setOrderTime(String orderTime) {
        this.orderTime = orderTime;
    }

    public Double getTotal() {
        return total;
    }

    public void setTotal(Double total) {
        this.total = total;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", orderTime='" + orderTime + '\'' +
                ", total=" + total +
                ", user=" + user +
                '}';
    }
}

/**
 * 用户
 *
 * @name: User
 * @author: terwer
 * @date: 2022-05-25 13:25
 **/
public class User {
    private Integer id;
    private String username;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                '}';
    }
}

/**
 * 订单映射
 *
 * @name: IUserMapper
 * @author: terwer
 * @date: 2022-03-17 17:54
 **/
public interface IOrderMapper {
    public List<Order> findOrderAndUser();
}

/**
 * 订单映射
 *
 * @name: IUserMapper
 * @author: terwer
 * @date: 2022-03-17 17:54
 **/
public interface IOrderMapper {
    /**
     * 查询订单同时查询订单所属用户
     *
     * @return
     */
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "orderTime", column = "ordertime"),
            @Result(property = "total", column = "total"),
            @Result(property = "user", column = "uid", javaType = User.class, one = @One(select = "com.terwergreen.mapper.IUserMapper.findUserById"))
    })
    @Select("select * from orders")
    public List<Order> findOrderAndUser();
}

/*
 * 用户映射
 *
 * @name: IUserMapper
 * @author: terwer
 * @date: 2022-05-25 13:27
 **/
public interface IUserMapper {
    /**
     * 根据ID查询用户
     * @param id
     * @return
     */
    @Select("select * from user where id=#{id}")
    User findUserById(Integer id);
}

/**
 * 订单测试
 *
 * @name: IOrderMapperTest
 * @author: terwer
 * @date: 2022-08-31 22:52
 **/
public class IOrderMapperTest {
    private IOrderMapper orderMapper;
    private SqlSession sqlSession;

    @Before
    public void before() throws Exception {
        System.out.println("before...");
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        sqlSession = sqlSessionFactory.openSession();
        // 这样也是可以的，这样的话后面就不用每次都设置了
        // sqlSession = sqlSessionFactory.openSession(true);
        orderMapper = sqlSession.getMapper(IOrderMapper.class);
    }

    @Test
    public void testFindOrder() throws Exception {
        List<Order> orderAndUser = orderMapper.findOrderAndUser();

        orderAndUser.forEach(order -> {
            System.out.println(order);
        });
    }
}

效果文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

Opening JDBC Connection
Created connection 2024453272.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@78aab498]
==>  Preparing: select * from orders
==> Parameters: 
<==    Columns: id, ordertime, total, uid
<==        Row: 1, 2019-12-12, 3000.0, 1
====>  Preparing: select * from user where id=?
====> Parameters: 1(Integer)
<====    Columns: id, username, password, birthday
<====        Row: 1, lucy, 123, 2019-12-12
<====      Total: 1
<==        Row: 2, 2019-12-12, 4000.0, 1
<==        Row: 3, 2019-12-12, 5000.0, 2
====>  Preparing: select * from user where id=?
====> Parameters: 2(Integer)
<====    Columns: id, username, password, birthday
<====        Row: 2, tom, 123, 2019-12-12
<====      Total: 1
<==      Total: 3
Order{id=1, orderTime='2019-12-12', total=3000.0, user=User{id=1, username='lucy'}}
Order{id=2, orderTime='2019-12-12', total=4000.0, user=User{id=1, username='lucy'}}
Order{id=3, orderTime='2019-12-12', total=5000.0, user=User{id=2, username='tom'}}

Process finished with exit code 0

image-20220901002006366文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户。文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

一对多查询需求：查询一个用户，与此同时查出该用户具有的订单。文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

对应查询语句：文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

对应的 sql 语句文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

select * from user;
select * from orders where uid=查询出用户的id;

查询结果如下：文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

/**
 * 订单
 *
 * @name: Order
 * @author: terwer
 * @date: 2022-03-17 17:42
 **/
public class Order {
    private Integer id;
    private String orderTime;
    private Double total;

    // 代表当前订单属于哪一个用户
    private User user;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getOrderTime() {
        return orderTime;
    }

    public void setOrderTime(String orderTime) {
        this.orderTime = orderTime;
    }

    public Double getTotal() {
        return total;
    }

    public void setTotal(Double total) {
        this.total = total;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", orderTime='" + orderTime + '\'' +
                ", total=" + total +
                ", user=" + user +
                '}';
    }
}

/**
 * 用户
 *
 * @name: User
 * @author: terwer
 * @date: 2022-05-25 13:25
 **/
public class User {
    private Integer id;
    private String username;

    // 代表当前用户具备那些订单
    private List<Order> orderList;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", orderList=" + orderList +
                '}';
    }
}

/**
 * 用户映射
 *
 * @name: IUserMapper
 * @author: terwer
 * @date: 2022-05-25 13:27
 **/
public interface IUserMapper {
    /**
     * 查询用户和订单
     *
     * @return
     */
    List<User> findUserAndOrder();
}

/**
 * 用户映射
 *
 * @name: IUserMapper
 * @author: terwer
 * @date: 2022-05-25 13:27
 **/
public interface IUserMapper {
    /**
     * 查询用户和订单
     *
     * @return
     */
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "orderList", column = "id", many = @Many(select = "com.terwergreen.mapper.IOrderMapper.findOrderByUid"), javaType = List.class)
    })
    @Select("select * from user")
    List<User> findUserAndOrder();
}

/**
 * 订单映射
 *
 * @name: IUserMapper
 * @author: terwer
 * @date: 2022-03-17 17:54
 **/
public interface IOrderMapper {
    @Select("select * from orders where uid=#{uid}")
    public List<Order> findOrderByUid(Integer uid);
}

public class IUserMapperTest {

    private IUserMapper userMapper;
    private SqlSession sqlSession;

    @Before
    public void before() throws Exception {
        System.out.println("before...");
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        sqlSession = sqlSessionFactory.openSession();
        // 这样也是可以的，这样的话后面就不用每次都设置了
        // sqlSession = sqlSessionFactory.openSession(true);
        userMapper = sqlSession.getMapper(IUserMapper.class);
    }

    @Test
    public void testGetUserOrders() {
        List<User> userAndOrder = userMapper.findUserAndOrder();
        userAndOrder.forEach(user -> {
            System.out.println(user);
        });
    }
}

结果如下：文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

Opening JDBC Connection
Created connection 98394724.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5dd6264]
==>  Preparing: select * from user
==> Parameters: 
<==    Columns: id, username, password, birthday
<==        Row: 1, lucy, 123, 2019-12-12
====>  Preparing: select * from orders where uid=?
====> Parameters: 1(Integer)
<====    Columns: id, ordertime, total, uid
<====        Row: 1, 2019-12-12, 3000.0, 1
<====        Row: 2, 2019-12-12, 4000.0, 1
<====      Total: 2
<==        Row: 2, tom, 123, 2019-12-12
====>  Preparing: select * from orders where uid=?
====> Parameters: 2(Integer)
<====    Columns: id, ordertime, total, uid
<====        Row: 3, 2019-12-12, 5000.0, 2
<====      Total: 1
<==        Row: 8, 测试2, null, null
====>  Preparing: select * from orders where uid=?
====> Parameters: 8(Integer)
<====      Total: 0
<==        Row: 9, 测试3, null, null
====>  Preparing: select * from orders where uid=?
====> Parameters: 9(Integer)
<====      Total: 0
<==      Total: 4
User{id=1, username='lucy', orderList=[Order{id=1, orderTime='2019-12-12', total=3000.0, user=null}, Order{id=2, orderTime='2019-12-12', total=4000.0, user=null}]}
User{id=2, username='tom', orderList=[Order{id=3, orderTime='2019-12-12', total=5000.0, user=null}]}
User{id=8, username='测试2', orderList=[]}
User{id=9, username='测试3', orderList=[]}

Process finished with exit code 0

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用。文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

查询用户的同时查询该用户对应的角色。文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

select * from user;
select * from sys_role r,sys_user_role ur where r.id=ur.roleid and ur.userid=用户的id

查询结果如下：文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

• 创建 User 实体和 Role 实体文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

  /**
   * 用户
   *
   * @name: User
   * @author: terwer
   * @date: 2022-05-25 13:25
   **/
  public class User {
      private Integer id;
      private String username;
  
      // 代表当前用户具备那些订单
      private List<Order> orderList;
  
  
      // 代表当前用户具备的那些角色
      private List<Role> roleList;
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public String getUsername() {
          return username;
      }
  
      public void setUsername(String username) {
          this.username = username;
      }
  
      public List<Order> getOrderList() {
          return orderList;
      }
  
      public void setOrderList(List<Order> orderList) {
          this.orderList = orderList;
      }
  
      public List<Role> getRoleList() {
          return roleList;
      }
  
      public void setRoleList(List<Role> roleList) {
          this.roleList = roleList;
      }
  
      @Override
      public String toString() {
          return "User{" +
                  "id=" + id +
                  ", username='" + username + '\'' +
                  ", orderList=" + orderList +
                  ", roleList=" + roleList +
                  '}';
      }
  }
  

• 创建 IRoleMapper文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

  /**
   * 角色映射
   *
   * @name: IRoleMapper
   * @author: terwer
   * @date: 2022-09-06 00:04
   **/
  public interface  IRoleMapper {
      @Select("select * from sys_role r,sys_user_role ur where r.id=ur.roleid and ur.userid=#{userId}")
      List<Role> findRolesByUserId(Integer userId);
  }
  

  /**
   * 角色
   *
   * @name: Role
   * @author: terwer
   * @date: 2022-05-12 14:14
   **/
  public class Role {
      private Integer id;
      private String rolename;
  
      @Override
      public String toString() {
          return "Role{" +
                  "id=" + id +
                  ", rolename='" + rolename + '\'' +
                  '}';
      }
  }
  

• 修改 IUserMapper文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

  /**
   * 用户映射
   *
   * @name: IUserMapper
   * @author: terwer
   * @date: 2022-05-25 13:27
   **/
  public interface IUserMapper {
      @Results({
              @Result(property = "id", column = "id"),
              @Result(property = "username", column = "username"),
              @Result(property = "roleList", column = "id", javaType = List.class,
                      many = @Many(select = "com.terwergreen.mapper.IRoleMapper.findRolesByUserId"))
      })
      @Select("select * from user")
      List<User> findUserAndRole();
  }
  

• 添加测试方法文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

  public class IUserMapperTest {
  
      private IUserMapper userMapper;
      private SqlSession sqlSession;
  
      @Before
      public void before() throws Exception {
          System.out.println("before...");
          InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
          sqlSession = sqlSessionFactory.openSession();
          // 这样也是可以的，这样的话后面就不用每次都设置了
          // sqlSession = sqlSessionFactory.openSession(true);
          userMapper = sqlSession.getMapper(IUserMapper.class);
      }
  
      @Test
      public void testFindUserAndRole() {
          userMapper.findUserAndRole().forEach(user -> {
              System.out.println(user);
          });
      }
  }
  

• 效果如下文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

  Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@51e5fc98]
  ==>  Preparing: select * from user
  ==> Parameters: 
  <==    Columns: id, username, password, birthday
  <==        Row: 1, lucy, 123, 2019-12-12
  ====>  Preparing: select * from sys_role r,sys_user_role ur where r.id=ur.roleid and ur.userid=?
  ====> Parameters: 1(Integer)
  <====    Columns: id, rolename, roleDesc, userid, roleid
  <====        Row: 1, CTO, CTO, 1, 1
  <====        Row: 2, CEO, CEO, 1, 2
  <====      Total: 2
  <==        Row: 2, tom, 123, 2019-12-12
  ====>  Preparing: select * from sys_role r,sys_user_role ur where r.id=ur.roleid and ur.userid=?
  ====> Parameters: 2(Integer)
  <====    Columns: id, rolename, roleDesc, userid, roleid
  <====        Row: 1, CTO, CTO, 2, 1
  <====        Row: 2, CEO, CEO, 2, 2
  <====      Total: 2
  <==        Row: 8, 测试2, null, null
  ====>  Preparing: select * from sys_role r,sys_user_role ur where r.id=ur.roleid and ur.userid=?
  ====> Parameters: 8(Integer)
  <====      Total: 0
  <==        Row: 9, 测试3, null, null
  ====>  Preparing: select * from sys_role r,sys_user_role ur where r.id=ur.roleid and ur.userid=?
  ====> Parameters: 9(Integer)
  <====      Total: 0
  <==      Total: 4
  User{id=1, username='lucy', orderList=null, roleList=[Role{id=1, rolename='CTO'}, Role{id=2, rolename='CEO'}]}
  User{id=2, username='tom', orderList=null, roleList=[Role{id=1, rolename='CTO'}, Role{id=2, rolename='CEO'}]}
  User{id=8, username='测试2', orderList=null, roleList=[]}
  User{id=9, username='测试3', orderList=null, roleList=[]}
  
  Process finished with exit code 0
  

文章更新历史文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

2024/05/13 同步文章到其他平台文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

2022-08-30 feat:初稿 文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html

本文介绍了使用 XML 配置方式实现复杂映射的方法，以及在注解开发中通过 @Results、@Result、@One 和 @Many 注解来完成复杂关系的配置，包括一对一查询的模型和语句以及创建 User 和 Order 实体。文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-s-annotation-achieves-complex-mapping-development-z1l39xe.html