mybatis笔记--多表查询

mybatis

多表查询一对多案例分析

最近在学习mybatis框架，有一个案例感觉挺好的，源码分享在GitHub了,会不定时更新。###

源码GitHub
https://github.com/Qingmengjuzi/mybatis-spring.git

案例：查询所有用户信息及用户关联的订单信息。用户信息和订但信息为一对多关系###

步骤：

• 1.SQL语句
• 2.修改pojo类
• 3.Mapper.xml
• 4.Mapper接口
• 5.测试
• 6.显示结果

---

1.SQL语句

   SELECT
   u.id,
   u.username,
   u.birthday,
   u.sex,
   u.address,
   o.id oid,
   o.number,
   o.createtime,
   o.note
   FROM
   `user` u
   LEFT JOIN `order` o ON u.id = o.user_id

2.修改pojo类

User.java

private List<Order> orders;

3.Mapper.xml

	<resultMap type="user" id="userOrderResultMap">
	<id property="id" column="id" />
	<result property="username" column="username" />
	<result property="birthday" column="birthday" />
	<result property="sex" column="sex" />
	<result property="address" column="address" />

	<!-- 配置一对多的关系 -->
	<collection property="orders" javaType="list" ofType="order">
		<!-- 配置主键，是关联Order的唯一标识 -->
		<id property="id" column="oid" />
		<result property="number" column="number" />
		<result property="createtime" column="createtime" />
		<result property="note" column="note" />
	</collection>
	</resultMap>

	<!-- 一对多关联，查询订单同时查询该用户下的订单 -->
	<select id="queryUserOrder" resultMap="userOrderResultMap">
	SELECT
	u.id,
	u.username,
	u.birthday,
	u.sex,
	u.address,
	o.id oid,
	o.number,
	o.createtime,
	o.note
	FROM
	`user` u
	LEFT JOIN `order` o ON u.id = o.user_id
	</select>

4.Mapper接口

   UserMapper.java

   List<user> queryUserOrder();

5.测试方法

   UserMapperTest.java

   @Test
   public void testQueryUserOrder() {
   // mybatis和spring整合，整合之后，交给spring管理
   SqlSession sqlSession = this.sqlSessionFactory.openSession();
   // 创建Mapper接口的动态代理对象，整合之后，交给spring管理
   UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

   // 使用userMapper执行根据条件查询用户
   List<User> list = userMapper.queryUserOrder();

   for (User u : list) {
   	System.out.println(u);
   }

   // mybatis和spring整合，整合之后，交给spring管理
   sqlSession.close();
   }

6.显示结果