标题: MyBatis多表查询 [打印本页] 作者: Keai720 时间: 2019-6-5 19:54 标题: MyBatis多表查询 一对一查询:
对应的sql语句:select * from orders o,user u where o.uid=u.id;
创建Order和User实体:
public class Order {
private int id;
private Date ordertime;
private double total;
//代表当前订单从属于哪一个客户
private User user;
}
public class User {
private int id;
private String username;
private String password;
private Date birthday;
}
创建OrderMapper接口
public interface OrderMapper {
List<Order> findAll();
}
一对多查询:
对应的sql语句:select *,o.id oid from user u,orders o WHERE u.id = o.uid
修改User实体
public class Order {
private int id;
private Date ordertime;
private double total;
//代表当前订单从属于哪一个客户
private User user;
}
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
}
创建UserMapper接口:
public interface UserMapper {
List<User> findAll();
}
<select id="findAll" resultMap="userMap">
select *,o.id oid from user u,orders o WHERE u.id = o.uid
</select>
测试结果
多对多查询
对应的sql语句:select * from user u,sys_user_role ur,sys_role r where u.id=ur.userId and ur.roleId=r.id
创建Role实体,修改User实体:
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//描述的是当前用户具备哪些角色
private List<Role> roleList;
}
public class Role {
private int id;
private String roleName;
private String roleDesc;
}
<select id="findUserAndRoleAll" resultMap="userRoleMap">
select * from user u,sys_user_role ur,sys_role r where u.id=ur.userId and ur.roleId=r.id
</select>
public interface RoleMapper {
@Select("select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=#{uid}")
List<Role> findByUid(int uid);
}
//测试结果
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> all = mapper.findAllUserAndRole();
for(User user : all){
System.out.println(user.getUsername());
List<Role> roleList = user.getRoleList();
for(Role role : roleList){
System.out.println(role);
}
System.out.println("----------------------------------");
}