Mybatis 一对多关联查询

1.配置文件

<resultMap type="user" id="user_order_map">

<id property="id" column="id" />

<result property="username" column="username"/>

<result property="sex" column="sex"/>

<result property="birthday" column="birthday"/>

<result property="address" column="address"/>

<collection property="orders" ofType="order">

    <id property="id" column="oid" />

    <id property="userId" column="id" />

<result property="number" column="number"/>

<result property="createtime" column="createtime"/>

<result property="note" column="note"/>

</collection>

</resultMap>

<select id="getOrderUserMap" resultMap="user_order_map">

select

u.`id`,

u.`username`,

u.`address`,

u.`sex`,

u.`birthday`,

o.`id` oid,

o.`number`,

o.`createtime`,

o.`note`

from `user` u

left join `order` o on o.user_id = u.id

</select>

 

2.接口名字3.

/**

*

* @return

*/

List<User> getOrderUserMap();

3.测试方法

@Test

public void getOrderUserMap() {

SqlSession session = SqlSessionFactoryUtils.geSqlSessionFactoryt().openSession();

UserMapper userMapper = session.getMapper(UserMapper.class);

List<User> list = userMapper.getOrderUserMap();

for (User user : list) {

System.out.println(user);

for (Order order : user.getOrders()) {

if (order.getId() != null) {

System.out.println("-----:"+order);

}

}

}

session.close();

}

 

4.输出结果

User [id=1, username=王五, sex=2, birthday=Wed Nov 21 00:24:17 CST 2018, address=null]

-----:Order [id=3, userId=1, number=1000010, createtime=Thu Feb 05 03:22:35 CST 2015, note=阿萨德后按多少送到后加速度, user=null]

-----:Order [id=4, userId=1, number=1000011, createtime=Wed Feb 04 03:22:41 CST 2015, note=昂首我昂首我昂首我阿善动奥德赛, user=null]

User [id=10, username=张飞3, sex=1, birthday=Thu Jul 10 13:00:00 CST 2014, address=北京市]

-----:Order [id=5, userId=10, number=1000012, createtime=Fri Feb 13 06:13:23 CST 2015, note=啊实打实大的还是哦, user=null]

User [id=16, username=张小明, sex=1, birthday=Wed Nov 21 00:24:17 CST 2018, address=河南郑州]

User [id=22, username=陈小明, sex=1, birthday=Wed Nov 21 00:24:17 CST 2018, address=河南郑州]

User [id=24, username=张三丰, sex=1, birthday=Wed Nov 21 00:24:17 CST 2018, address=河南郑州]

User [id=25, username=陈小明, sex=1, birthday=Wed Nov 21 00:24:17 CST 2018, address=河南郑州]

5.表结构