mybatis注解实现：一对一、一对多与多对多查询

文章目录

一、一对一查询

1.orders表和user表2.实体&接口3.查询结果二、一对多查询

1.建表2.实体&接口3.查询结果三、多对多查询

1.建表2.实体&接口3.查询结果总结

一、一对一查询

一个orders对应一个user

1.orders表和user表

与xml文件配置所用的相同

2.实体&接口

Orders实体：

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    //表示当前订单属于哪一个用户
    private User user;

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

    public int getId() {
        return id;
    }

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

    public Date getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(Date ordertime) {
        this.ordertime = ordertime;
    }

    public double getTotal() {
        return total;
    }

    public User getUser() {
        return user;
    }

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

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

OrdersMapper接口：

public interface OrderMapper {
    @Select("SELECT *FROM orders")
    @Results({
            @Result(property = "id", column = "oid"),
            @Result(property = "ordertime", column = "ordertime"),
            @Result(property = "total",column = "total"),
            @Result(
                    property = "user", //当前类order所封装的属性名user
                    column = "uid", //在数据表中根据字段名来查询user表数据
                    javaType = User.class,//要封装的实体类型
                    one = @One(select = "mapper.UserMapper.findUserById") //查询UserMapper接口中的方法来获取数据
            )
    })
    public List findAll();

UserMapper接口：

public interface UserMapper {
//根据主键id查询用户信息
    @Select("select * from user where id=#{id}")
    public User findUserById(Integer id);
}

3.查询结果

public class MyBatisTest2 {
    @Test
    public void findAll() {
        InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        SqlSession sqlSession = build.openSession(true);
        OrdersMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List orders = mapper.findAll();
        for (Order order : orders) {
            System.out.println(order);
        }
    }

}

二、一对多查询

一个user对应多个orders

1.建表

同上

2.实体&接口

User实体：

public class User {

    private int id;
    private String username;
    private String password;
//    当前用户有哪些订单
    private List orderList;

    public List getOrderList() {
        return orderList;
    }

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

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

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

UserMapper接口：

public interface UserMapper {
    //根据id查询用户
    @Select("select * from user where id=#{id}")
    public User findUserById(Integer id);
	//查询所有用户
    @Select("select *from user")
    public List findAll();
	
	//查询每个用户的订单集合
    @Select("select *from user")
    @Results({
            @Result(id=true ,property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
            @Result(
                    property = "orderList",
                    column = "id", //根据user表中的id来查询 此用户所拥有的订单信息
                    javaType = List.class, //返回此用户所拥有订单的List集合类型
                    many = @Many(select = "mapper.OrderMapper.findById") //根据user数据表中id 在orders表中来查询此订单
            )
    })
    public List findAllOrders();
}

OrdersMapper接口：

public interface OrderMapper {
	//查询所有订单信息
    @Select("SELECT *FROM orders")
    @Results({
            @Result(property = "id", column = "oid"),
            @Result(property = "ordertime", column = "ordertime"),
            @Result(property = "total",column = "total"),
            @Result(
                    property = "user", //当前类order所封装的属性名user
                    column = "uid", //在数据表中根据字段名来查询user表数据
                    javaType = User.class,//要封装的实体类型
                    one = @One(select = "mapper.UserMapper.findUserById") //查询UserMapper接口中的方法来获取数据
            )
    })
    public List findAll();
	//根据id查询订单信息（集合）
    @Select("select *from orders where uid=#{uid}")
    public List findOrderById();

3.查询结果

public class MyBatisTest3 {
   @Test
    public void test1(){
        InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        SqlSession sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
        List allOrders = mapper.findAllOrders();
        for (User user : allOrders) {
            System.out.println(user.getUsername());
            List orderList = user.getOrderList();
            //此用户所拥有的订单
            for (Order order : orderList) {
                System.out.println(order);
            }
        }
   }
}

三、多对多查询

一个User可以有多个Role，且一个Role也可以被多个User充当；

1.建表

user表：

sys_role表：

sys_user_role表（中间表）：

2.实体&接口

user实体：

public class User {

    private int id;
    private String username;
    private String password;
    //当前用户有哪些角色
    private List roleList;
    public List getRoleList() {
        return roleList;
    }
    public void setRoleList(List roleList) {
        this.roleList = roleList;
    }

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +

                ", roleList=" + roleList +
                '}';
    }
}

UserMapper接口：

public interface UserMapper {
    //查询所有用户以及所对应的角色
    @Select("select *from user")
    @Results({
    //封装User实体内部属性
            @Result(id=true ,property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
   //封装User实体内的Role实体对象
            @Result(
                    property = "roleList",
                    column = "id", //根据user表中的id来查询 此用户所拥有的订单信息
                    javaType = List.class, //返回List集合类型
   				//根据user表中的id->在sys_user_rolesys_role中查询对应的角色
                    many = @Many(select = "mapper.RoleMapper.findById")
            )
    })
    public List findUserAndAllRole();

}

Role实体：

public class Role {
    private Integer id;
    private String roleName;
    private String roleDesc;
    private List userList;
    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + ''' +
                ", roleDesc='" + roleDesc + ''' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }
}

RoleMapper接口：

public interface RoleMapper {
//此处接受的参数id即UserMapper接口column的id属性值
    @Select("SELECT *FROM sys_user_role ur,sys_role r WHERe ur.roleId=r.id AND ur.userId=#{id}")
    public List findById(int id);
}

3.查询结果

public class MyBatisTest3 {
   @Test
    public void test1(){
       InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
       SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
       SqlSession sqlSession = build.openSession(true);
       mapper = sqlSession.getMapper(UserMapper.class);
       List userAndAllRole = mapper.findUserAndAllRole();
       for (User user : userAndAllRole) {
           System.out.println(user);
       }
   	}
   }

总结

添加到相应的接口方法上

@Insert("sql语句")：插入
@Update("sql语句")：更新
@Delete("sql语句")：删除
@Select("sql语句")：查询
@Result：结果集封装
@Results：可以与@Result 一起使用，封装多个结果集
	如：	@Results({
			@Resullt(),
			//select(此处是调用了所封装实体-->实体所对应接口Mapper的方法)
			@Resullt(property,column,one=@One(select="...")/many=@Many=(select="..."))
			...
		})
@One：一对一结果集封装
@Many：一对多结果集封装

mybatis注解实现：一对一、一对多与多对多查询

Java相关栏目本月热门文章