一 概述
一个用户可以拥有多个订单,可以在用户pojo类中添加一个泛型为Orders的集合
方法:
1.映射文件中使用resultMap配置
2. 用户表中加List< Orders > orderList属性
二 案例代码
目标:查询指定用户的订单
1.写sql语句
SELECT u.id,u.username,o.id oid,o.number,o.createtime,o.note from user u LEFT JOIN orders o on u.id=o.user_id and u.id=1
2. pojo类(user类添加订单list)
public class User implements Serializable {
private Integer id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
private List<Orders> orders;
public List<Orders> getOrders() {
return orders;
}
public void setOrders(List<Orders> orders) {
this.orders = orders;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", sex=" + sex
+ ", birthday=" + birthday + ", address=" + address + "]";
}
}
3.映射文件配置sql语句
<resultMap type="User" id="UserOrderResultMap">
<id property="id" column="id"/>
<result property="username" column="username"/>
<!-- List<Orders>配置 -->
<collection property="orders" ofType="Orders">
<id property="id" column="oid"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
</collection>
</resultMap>
<!--查询用户id,用户名及其订单 -->
<select id="findUserWithOrders" parameterType="int" resultMap="UserOrderResultMap">
SELECT
u.id,
u.username,
o.id oid,
o.number,
o.createtime,
o.note
from user u
LEFT JOIN orders o
on u.id=o.user_id and u.id=#{id}
</select>
4.接口中定义对应方法
public interface UserDao {
// public List<User> findUserByQueryVo(QueryVo queryVo);
public List<User> findUserWithOrders(int id);
}
5.测试
package com.itheima.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.itheima.domain.OrderWithCutomer;
import com.itheima.domain.Orders;
import com.itheima.domain.QueryVo;
import com.itheima.domain.User;
import com.itheima.mapperTest.OrdersDao;
import com.itheima.mapperTest.UserDao;
public class UserOperateTest2 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setSqlSession() throws Exception{
//1.获得SqlSession对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
}
@Test
public void testFindOrderWithCustomer() throws Exception{
//1.获得SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//2.获得接口的代理对象 sqlSession.getMapper()
UserDao userDao = sqlSession.getMapper(UserDao.class);
//3.使用代理对象的方法操作
List<User> userWithOrders = userDao.findUserWithOrders(1);
for (User user : userWithOrders) {
System.out.println(user.getUsername());
List<Orders> orders = user.getOrders();
for (Orders orders2 : orders) {
System.out.println(orders2);
}
}
//4.释放资源
sqlSession.close();
}
}