Mybatis 关联查询:(一对多)
需求:查询所有用户信息及用户关联的订单信息。
用户信息和订单信息为一对多关系。
示例:
一、改造pojo类
在user中加入order属性,order属性中用于存储关联查询的订单信息,用户与订单为一对多的关系。
user类:
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer id;
private String username;
private String password;
private String email;
private String gender;
private Integer flag; // 0 没激活 1 激活 2 无效
private Integer role; // 角色 0 管理员 1 普通用户
private String code;
private List<Order> orders;
}
订单类:
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.math.BigDecimal;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order {
private String id;
private Integer uid;
private BigDecimal money;
private String status;
private Date time;
private Integer aid;
}
编写Dao接口:
import com.vince.pojo.User;
public interface UserDao {
// 根据用户查询订单信息
List<User> userQueryOrder();
}
配置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">
<!-- namespace:命名空间,用于隔离sql -->
<!-- 还有一个很重要的作用,使用动态代理开发DAO,1. namespace必须和Mapper接口类路径一致 -->
<mapper namespace="com.vince.dao.UserDao">
<!--根据用户查询订单信息-->
<resultMap id="userOrderMap" type="User">
<!--主键id-->
<id property="id" column="id"></id>
<!--其他列-->
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<result property="email" column="email"></result>
<result property="gender" column="gender"></result>
<result property="flag" column="flag"></result>
<result property="role" column="role"></result>
<result property="code" column="code"></result>
<!--外键对象-->
<collection property="orders" javaType="list" ofType="Order" >
<!--外键id-->
<id property="id" column="oid"></id>
<!--其他列-->
<result property="money" column="money"></result>
<result property="status" column="status"></result>
<result property="time" column="time"></result>
<result property="aid" column="aid"></result>
</collection>
</resultMap>
<select id="userQueryOrder" resultMap="userOrderMap">
SELECT u.*,o.id as oid,o.`money`,o.`status`,o.`time`,o.`aid` FROM tb_user u LEFT JOIN tb_order o ON u.`id`=o.`uid`;
</select>
</mapper>
测试类:
@Test
public void testUserOrder(){
ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) context.getBean("userDao");
List<User> users = userDao.userQueryOrder();
for (User user : users) {
System.out.println(user);
}
}
运行结果:
User(id=208, username=李四, password=202cb962ac59075b964b07152d234b70, email=1234567@qq.com, gender=女, flag=1, role=1, code=2019091116482433531b, orders=[Order(id=20190913113110015, uid=null, money=24.00, status=1, time=Fri Sep 13 11:31:10 CST 2019, aid=11), Order(id=20190914111654268, uid=null, money=1.00, status=1, time=Sat Sep 14 11:16:54 CST 2019, aid=8)])
User(id=209, username=张三, password=96e79218965eb72c92a549dd5a330112, email=xiaofei_0280@163.com, gender=男, flag=1, role=1, code=20190917190600697317, orders=[Order(id=20190917190936525, uid=null, money=2533.00, status=1, time=Tue Sep 17 19:09:37 CST 2019, aid=13)])
以用户表为主表订单表为从表可以查询用户对应订单信息。