一对一查询

用户表和订单表的关系为: 一个用户有多个订单,一个订单只从属于一个用户.

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

对应的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;

}

Order实体:

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

 
}

创建OrderMapper接口

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


}

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>

测试:

 @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);
   }
 }

结果

注解开发

使用注解配置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();

}

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);
}

测试:

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);
  }
 }

结果:

一对多查询

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

对应的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;

}

Order实体:

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

 
}

创建UserMapper接口:

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

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>

测试:

@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("--------------------------------------");
    }
  }

注解方式

使用注解配置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();

 
}

使用注解配置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);
}

测试:

 @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("========================================================");
   }
  }

结果

多对对查询

用户表和角色表的关系为: 一个用户有多个角色,一个角色被多个用户使用.

对应的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;
}

User实体:

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

  private  String password;

  private String  bithday;

  private List<Role> roleList;

}

添加UserMapper接口:

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

}

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>

测试:

@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("===========================================================");
     }
  }

结果

注解方式

使用注解配置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();
}

使用注解配置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);
}

测试:

 @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("========================================================");

   }
}

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