Mybatis——多表查询注解开发实现

多表查询相关注解

实现复杂关系映射之前可以在映射文件中通过配置resultMap来实现，使用注解开发后，则可以使用@Results注解，@Result注解，@One注解，@Many注解组合完成复杂关系的配置

数据表与实体类

这里有涉及到java.util.Date类型与数据库中bigint类型的转换器，具体可以参考Mybatis——typeHandlers标签自定义类型处理器
User类和User表

public class User {
    private int id;
    private String name;
    private String password;
    private Date birthday;
    //......注意配置get和set和ToString函数  ![请添加图片描述](https://img-blog.csdnimg.cn/aa6efe13a97a4b0da103c3555a1aa6d9.png)

}

Order类和Orders表

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    private User user;
    //......注意配置get和set和ToString函数  
}

Role类和Role表

public class Role {
    private int id;
    private String roleName;
    //......注意配置get和set和ToString函数    
}

user_role表

一对一 多表查询

需求：完成对每个order订单的查询以及通过订单内的uid属性查询对应的user用户，这里有两种方法实现
第二种实现方法中使用了@One标签，@One标签代替了配置中的assocation标签，用来指定子查询返回单一对象，内部的select语句需要在user中配置相应的select语句并导入

OrderMapper

@Mapper
@Repository
public interface OrderMapper {
//实现方法一:
    @Select("select * from orders o,user u where o.uid=u.id")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "ordertime",property = "ordertime"),
            @Result(column = "total",property = "total"),
            @Result(column = "uid",property = "user.id"),
            @Result(column = "name",property = "user.name"),
            @Result(column = "password",property = "user.password")
    })
    public List<Order> findAll();

//实现方法二:
    @Select("select * from orders")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "ordertime",property = "ordertime"),
            @Result(column = "total",property = "total"),
            @Result(
                    property = "user",//要封装的属性名称
                    column = "uid",//根据哪个字段去查询user表的数据
                    javaType = User.class,
                    //select属性，代表查询哪个接口的方法获得数据
                    one = @One(select = "com.example.springbootmybatis03.mapper.UserMapper.findById")
            )
    })
    public List<Order> findAll2();
}

UserMapper

@Mapper
@Repository
public interface UserMapper {
    @Select("select * from user where id=#{id}")
    public User findById(int id);
}

测试

@SpringBootTest
class SpringBootMybatis03ApplicationTests {
    @Autowired
    private OrderMapper orderMapper;

    @Test
    public void findAllOrderAnno(){
        List<Order> orderList = orderMapper.findAll();
        for(Order order:orderList){
            System.out.println(order);
        }
    }

    @Test
    public void findAllOrderAnno2(){
        List<Order> orderList = orderMapper.findAll2();
        for(Order order:orderList){
            System.out.println(order);
        }
    }
}

一对多 多表查询

需求：完成每个用户及其所有订单的查询，在User类中创建Order类型的List存储映射用户的所有订单信息，联结user表和orders表进行查询
@Many标签代替了配置中的collection标签，用来指定子查询返回对象集合，内部的select语句需要在order中配置相应的select语句并导入

UserMapper

@Mapper
@Repository
public interface UserMapper {
    @Select("select * from user")
    @Results({
            @Result(id = true,column = "id",property = "id"),//这个标识主键
            @Result(column = "name",property = "name"),
            //@Result(column = "password",property = "password"),
            @Result(
                    property = "orderList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select="com.example.springbootmybatis03.mapper.OrderMapper.findByUid")
            )
    })
    public List<User> findAllUserAndOrder();
}

OrderMapper

@Mapper
@Repository
public interface OrderMapper {
    @Select("select * from orders where uid=#{uid}")
    public List<Order> findByUid(int uid);
}

测试

@SpringBootTest
class SpringBootMybatis03ApplicationTests {
    @Autowired
    private OrderMapper userMapper;

    @Test
    public void findAllUserAndOrderAnno(){
        List<User> userList = userMapper.findAllUserAndOrder();
        for(User user:userList){
            System.out.println(user);
        }
    }
}

多对多 多表查询

需求：一个user对应一个或多个role，而一个role也可以有多个user，查询需要用到三个表，与User类对应的user表，与Role类对应的role表，以及利用userId与roleId存储user和role对应关系的中间表user_role

OrderMapper

@Mapper
@Repository
public interface UserMapper {

    @Select("select * from user")
    @Results({
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "password",property = "password"),
            @Result(
                    property = "roleList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "com.example.springbootmybatis03.mapper.RoleMapper.findByUid")
            )
    })
    public List<User> findAllUserAndRole();
}

RoleMapper

@Mapper
@Repository
public interface RoleMapper {
    @Select("select * from user_role ur,role r where ur.roleId=r.id AND ur.userId=#{uid}")
    public List<Role> findByUid(int uid);
}

测试

@SpringBootTest
class SpringBootMybatis03ApplicationTests {
    @Autowired
    private OrderMapper userMapper;

    @Test
    public void findAllUserAndRoleAnno(){
        List<User> userList = userMapper.findAllUserAndRole();
        for(User user:userList){
            System.out.println(user);
        }
    }