案例:查询MySQL中user表和orders表所有用户信息及用户关联的订单信息,
用户信息和订单信息为一对多关系。
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 `orders` o ON u.id = o.user_id
1:在User类中加入 List < Order > orders属性:
public class User {
private List<Order> orders;
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
}
2:在UserMapper.xml添加sql:
<resultMap type="User" id="userOrderMap">
<id column="id" property="id" />
<result column="username" property="username" />
<result column="birthday" property="birthday" />
<result column="sex" property="sex" />
<result column="address" property="address" />
<collection property="orders" javaType="list" ofType="Order">
<id column="oid" property="id" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
<result column="note" property="note" />
</collection>
</resultMap>
<select id="queryAllUserAndOrder" resultMap="userOrderMap">
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 `orders` o ON u.id = o.user_id
</select>
3:编写UserMapper接口:
public List<User> queryAllUserAndOrder();
4:单元测试:
public class MyBatisTest {
SqlSessionFactory factory = null;
private UserMapper userMapper = null;
@Before
public void testInit() {
// 1. 创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 2. 加载SqlMapConfig.xml配置文件
InputStream in = MyBatisTest.class.getResourceAsStream("/SqlMapConfig.xml");
// 3. 创建SqlSessionFactory对象
factory = builder.build(in);
}
@Test
public void testqueryAllUserAndOrder(){
SqlSession session = factory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = mapper.queryAllUserAndOrder();
System.out.println(users);
}