• 开源框架（四）：mybatis 两种开发方式 映射开发与注解开发

    一对一查询

    用户表和订单表的关系为: 一个用户有多个订单,一个订单只从属于一个用户.
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

    一对一查询的需求:查询一个订单,此同时查询出该订单所属的用户.

    对应的SQL语句:select * from t_orders o,t_user u where o.user_id=u.id

    查询结果如下:
    在这里插入图片描述

    创建User实体

    
public class User  {
  private int id;
  private  String username;

  private  String password;

  private String  bithday;

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    Order实体:

    public class Order {
  private  int id;
  private String ordertime;
  private  double total;
 /*
 代表当前订单从属哪个客户
 */
  private User user;

 
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    创建OrderMapper接口

    public interface OrderMapper {
    public List<Order> findAllOrder();


}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    XML 映射方式

    配置OrderMapper.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.th.mapper.OrderMapper">
  <resultMap id="orderMap" type="com.th.entity.Order">
    <result column="id" property="id"></result>
    <result column="order_time" property="ordertime"></result>
    <result column="total" property="total"></result>
    <association property="user" javaType="com.th.entity.User">
      <result column="user_id" property="id"></result>
      <result column="user_name" property="username"></result>
      <result column="pass_word" property="password"></result>
      <result column="bithday" property="bithday"></result>
    </association>

  </resultMap>
  <select id="findAllOrder" resultMap="orderMap">
    select * from t_orders o,t_user u where o.user_id =u.id
  </select>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    测试:

    
 @Test
 public void findAllOrder() throws IOException {
   InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
   SqlSessionFactory sqlSessionFactory= new SqlSessionFactoryBuilder().build(resourceAsStream);
   SqlSession sqlSession=sqlSessionFactory.openSession();
   OrderMapper orderMapper =sqlSession.getMapper(OrderMapper.class);
   List<Order> listOrder = orderMapper.findAllOrder();
   for (Order order : listOrder){
     System.out.println(order);
   }
 }


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    结果
    在这里插入图片描述

    注解开发

    使用注解配置OrderMapper

    public interface OrderByAnnotationMapper {
 /**
  * 一对一
  * @return
  */
 @Select("select * from t_orders")
 @Results({
   @Result(id = true,property = "id",column = "id"),
   @Result(property = "ordertime" ,column = "order_time"),
   @Result(property = "total",column = "total"),
   @Result(property = "user", column = "user_id",javaType = User.class,one = @One(select = "com.th.mapper.annotation.UserByAnnotationMapper.findUserById"))
 })
 public List<Order> findAllorderByAnnatotion();

}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    UserMapper

    public interface UserByAnnotationMapper {
 @Select("select * from t_user where id = #{id} ")
 @Results({
   @Result(id = true,property = "id" ,column = "id"),
   @Result(property = "username" , column = "user_name"),
   @Result(property = "password", column = "pass_word"),
   @Result(property = "bithday", column = "bithday")
 })
 public  User findUserById(int id);
}



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    测试:

    private UserByAnnotationMapper userByAnnotationMapper;
private RoleByAnnotationMapper roleByAnnotationMapper;
private  SqlSession sqlSession;
@Before
 public  void befor() throws IOException{
   InputStream resoureceAsStream = Resources.getResourceAsStream("mybatis-config-annatotion.xml");
   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resoureceAsStream);
   sqlSession = sqlSessionFactory.openSession();
   userByAnnotationMapper = sqlSession.getMapper(UserByAnnotationMapper.class);
   orderByAnnotationMapper = sqlSession.getMapper(OrderByAnnotationMapper.class);
   roleByAnnotationMapper = sqlSession.getMapper(RoleByAnnotationMapper.class);
 }


 @After
 public  void destory() throws  Exception{
  sqlSession.commit();
  sqlSession.close();;
 }
 @Test
 public  void findAllOrderByAnnotation(){

  List<Order> orderList = orderByAnnotationMapper.findAllorderByAnnatotion();
  for(Order order : orderList){
    System.out.println(order);
  }
 }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    结果:

    一对多查询

    一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单

    对应的SQL语句:select *,o.id oid from t_user u left JOIN t_orders o on u.id =o.user_id

    查询的结果如下:
    在这里插入图片描述

    User实体:

    public class User  {
  private int id;
  private  String username;

  private  String password;

  private String  bithday;
  
  /*
  代表当前用户具备哪些订单
  */
  private List<Order> orderList;

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    Order实体:

    public class Order {
  private  int id;
  private String ordertime;
  private  double total;
 /*
 代表当前订单从属哪个客户
 */
  private User user;

 
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    创建UserMapper接口:

    public interface UserMapper {
    public List<User> findAllOrderByUser();
}

    • 1
    • 2
    • 3

    XML 映射方式

    配置UserMapper.xml

    <mapper namespace="com.lagou.mapper.UserMapper">
  <resultMap id="userMap" type="com.lagou.domain.User">
    <result column="id" property="id"></result>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <result column="birthday" property="birthday"></result>
    <collection property="orderList" ofType="com.lagou.domain.Order">
      <result column="oid" property="id"></result>
      <result column="ordertime" property="ordertime"></result>
      <result column="total" property="total"></result>
    </collection>
  </resultMap>
  <select id="findAll" resultMap="userMap">
    select *,o.id oid from user u left join orders o on u.id=o.uid
  </select>
</mapper>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    测试:

    @Test
  public  void findAllOrderByUser() throws IOException {
    InputStream resourceAsStream= Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory= new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    com.th.mapper.UserMapper userMapper=sqlSession.getMapper(com.th.mapper.UserMapper.class);
    List<com.th.entity.User> userList =userMapper.findAllOrderByUser();
    for (com.th.entity.User user:userList){
      System.out.println(user.getUsername());
      List<Order> orderList = user.getOrderList();
      for(Order order: orderList){
        System.out.println(order);
      }
      System.out.println("--------------------------------------");
    }
  }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    在这里插入图片描述

    注解方式

    使用注解配置UserMapper:

    public interface UserByAnnotationMapper {

  /**
   * 一对多
   * @return
   */
  @Select("select * from t_user")
  @Results({
    @Result(id = true ,property = "id" ,column = "id"),
    @Result(property = "username", column = "user_name"),
    @Result(property = "password", column = "pass_word"),
    @Result(property = "bithday", column = "bithday"),
    @Result(property = "orderList", column = "id", javaType = List.class,many = @Many(select ="com.th.mapper.annotation.OrderByAnnotationMapper.findOrderByUserId"))
  })
  public List<User> findAllUserAndOrderByAnnotation();

 
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    使用注解配置OrderMapper.xml

    public interface OrderByAnnotationMapper {
 

  @Select("select * from t_orders where id = #{id}")
  @Results({
    @Result(id = true,property = "id",column = "id"),
    @Result(property = "ordertime" ,column = "order_time"),
    @Result(property = "total",column = "total")
  })
  public  Order findOrderByUserId(int id);
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    测试:

     @Before
  public  void befor() throws IOException{
    InputStream resoureceAsStream = Resources.getResourceAsStream("mybatis-config-annatotion.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resoureceAsStream);
    sqlSession = sqlSessionFactory.openSession();
    userByAnnotationMapper = sqlSession.getMapper(UserByAnnotationMapper.class);
    orderByAnnotationMapper = sqlSession.getMapper(OrderByAnnotationMapper.class);
    roleByAnnotationMapper = sqlSession.getMapper(RoleByAnnotationMapper.class);
  }


  @After
  public  void destory() throws  Exception{
   sqlSession.commit();
   sqlSession.close();;
  }
@Test
  public  void findAllUserAndOrderByAnnotation(){
   List<User> userList =userByAnnotationMapper.findAllUserAndOrderByAnnotation();
   for(User user : userList){
     System.out.println(user.getUsername());
     List<Order> orderList = user.getOrderList();
     for (Order order : orderList){
       System.out.println(order);
     }
     System.out.println("========================================================");
   }
  }


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    结果
    在这里插入图片描述

    多对对查询

    用户表和角色表的关系为: 一个用户有多个角色,一个角色被多个用户使用.
    在这里插入图片描述

    对应的sql 语句:select u., r. ,r.id rid from t_user u left JOIN t_user_role ur on u.id=ur.user_id INNER JOIN t_role r on ur.role_id= r.id

    查询结果:
    在这里插入图片描述

    Role实体:

    public class Role {
  private int id;
  private String rolename;
}

    • 1
    • 2
    • 3
    • 4

    User实体:

    public class User implements Serializable {
  private int id;
  private  String username;

  private  String password;

  private String  bithday;

  private List<Role> roleList;

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    添加UserMapper接口:

    public interface UserMapper {
  public List<User>  findAllUserAndRole();

}

    • 1
    • 2
    • 3
    • 4

    XML映射方式

    <resultMap id="userRoleMap" type="com.lagou.domain.User">
  <result column="id" property="id"></result>
  <result column="username" property="username"></result>
  <result column="password" property="password"></result>
  <result column="birthday" property="birthday"></result>
  <collection property="roleList" ofType="com.lagou.domain.Role">
    <result column="rid" property="id"></result>
    <result column="rolename" property="rolename"></result>
  </collection>
</resultMap>
<select id="findAllUserAndRole" resultMap="userRoleMap">
select u.*,r.*,r.id rid from user u left join user_role ur on
u.id=ur.user_id
inner join role r on ur.role_id=r.id
</select>


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    测试:

    @Test
  public  void findAllUserAndRole() throws IOException {
     InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
     SqlSessionFactory sqlSessionFactory =new SqlSessionFactoryBuilder().build(resourceAsStream);
     SqlSession sqlSession=sqlSessionFactory.openSession();

     com.th.mapper.UserMapper userMapper = sqlSession.getMapper(com.th.mapper.UserMapper.class);
     List<com.th.entity.User> userList = userMapper.findAllUserAndRole();
     for (com.th.entity.User user: userList){
       System.out.println(user.getUsername());
       List<Role> roleList = user.getRoleList();
       for (Role role : roleList){
         System.out.println(role);
       }
       System.out.println("===========================================================");
     }
  }


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    结果
    在这里插入图片描述

    注解方式

    使用注解配置UserMapper

    public interface UserByAnnotationMapper {

  @Select("select * from t_user ")
  @Results({
    @Result(id = true,property = "id", column = "id"),
    @Result(property = "username", column = "user_name"),
    @Result(property = "password", column = "pass_word"),
    @Result(property = "bithday", column = "bithday"),
    @Result(property = "roleList" ,column = "id",javaType = List.class,many=@Many(select="com.th.mapper.annotation.RoleByAnnotationMapper.findRoleByUserId"))
  })
  public  List<User> findAllUserAndRoleByAnnotation();
}



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    使用注解配置RoleMapper

    public interface RoleByAnnotationMapper {

  @Select("select * from t_role r ,t_user_role ur where r.id = ur.role_id and ur.user_id= #{userId}")
  @Results({
    @Result(id = true ,property = "id" , column = "id"),
    @Result(property = "rolename", column = "role_name")
  })
  List<Role> findRoleByUserId(int userId);
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    测试:

     @Before
  public  void befor() throws IOException{
    InputStream resoureceAsStream = Resources.getResourceAsStream("mybatis-config-annatotion.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resoureceAsStream);
    sqlSession = sqlSessionFactory.openSession();
    userByAnnotationMapper = sqlSession.getMapper(UserByAnnotationMapper.class);
    orderByAnnotationMapper = sqlSession.getMapper(OrderByAnnotationMapper.class);
    roleByAnnotationMapper = sqlSession.getMapper(RoleByAnnotationMapper.class);
  }


  @After
  public  void destory() throws  Exception{
   sqlSession.commit();
   sqlSession.close();;
  }
@Test
public  void findAllUserAndRoleByAnnotation(){
   List<User> userList = userByAnnotationMapper.findAllUserAndRoleByAnnotation();
   for (User user: userList){
     System.out.println(user.getUsername());
     List<Role> roleList= user.getRoleList();
     for (Role role : roleList){
       System.out.println(role);
     }
     System.out.println("========================================================");

   }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

    在这里插入图片描述

    总结:
    在实际开发中, XML 映射和注解方式很多时候是配合使用以此提高效率.根据实际情况,对于比较复杂的SQL 查询,建议使用XML 映射方式,相对简单的操作,可以使用注解方式.

  • 相关阅读:
    社区系统项目复盘-2
    UE4 C++ 笔记（二）：基础知识
    基于WEB的考研论坛网站的设计与实现
    [微信小程序源码精品]基于Python实现的大学校园短视频社交软件系统-微信小程序[包运行成功]
    网络编程（一）
    IO系列第三篇——NIO（Selector）
    计算机毕业设计（附源码）python用户体验的线上租房系统
    电脑病毒感染C卷(Java&&Python&&C++&&Node.js&&C语言)
    【软件安装】docker 安装 elasticsearch 和 kibana
    Json“牵手”唯品会商品详情数据方法，唯品会商品详情API接口，唯品会API申请指南
  • 原文地址：https://blog.csdn.net/tang_huan_11/article/details/125428172