一、一对一查询
一个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<Order> 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<Order> 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<Order> orderList;
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> 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<User> 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<User> 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<Order> findAll();
//根据id查询订单信息(集合)
@Select("select *from orders where uid=#{uid}")
public List<Order> 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<User> allOrders = mapper.findAllOrders();
for (User user : allOrders) {
System.out.println(user.getUsername());
List<Order> 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<Role> roleList;
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> 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_role\sys_role中查询对应的角色
many = @Many(select = "mapper.RoleMapper.findById")
)
})
public List<User> findUserAndAllRole();
}
Role实体
:
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
private List<User> 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<Role> 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<User> 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:一对多结果集封装