Mybatis实现数据库多对多查询，用户订单商品查询

//model层 
package com.model;

import java.io.Serializable;
import java.util.Date;
import java.util.List;


public class User implements Serializable {
   private int id;
   private String username;// 用户姓名
   private String sex;// 性别
   private Date birthday;// 生日
   private String address;// 地址
   private List<Orders> ordersList;

   public List<Orders> getOrdersList() {
      return ordersList;
   }

   public void setOrdersList(List<Orders> ordersList) {
      this.ordersList = ordersList;
   }

   public User() {
   }

   public User(String username, String sex, Date birthday, String address) {
      this.username = username;
      this.sex = sex;
      this.birthday = birthday;
      this.address = address;
   }
   public User(String username, String sex,  String address) {
      this.username = username;
      this.sex = sex;
      this.address = address;
   }
   public int getId() {
      return id;
   }
   public void setId(int 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 + "]";
   }
}

//

package com.model;

import java.util.List;

public class Orders {
    private String number;
    private Integer id;
    private Integer user_id;
    private String createtime;


    private List<OrderDetail> orderDetail;//订单详情
    private User user;//用户

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public List<OrderDetail> getOrderDetail() {
        return orderDetail;
    }

    public void setOrderDetail(List<OrderDetail> orderDetail) {
        this.orderDetail = orderDetail;
    }

    @Override
    public String toString() {
        return "Order{" +
                "number='" + number + '\'' +
                ", id=" + id +
                ", user_id=" + user_id +
                ", createtime='" + createtime + '\'' +
                '}'+this.getOrderDetail().toString()+this.getUser().toString();
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getCreatetime() {
        return createtime;
    }

    public void setCreatetime(String createtime) {
        this.createtime = createtime;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }
}

//

package com.model;

public class OrderDetail {

    private Integer id;
    private Integer orders_id;
    private Integer items_id;
    private Integer items_num;
    private Items items;

    public Items getItems() {
        return items;
    }

    public void setItems(Items items) {
        this.items = items;
    }

    @Override
    public String toString() {
        return "OrderDetail{" +
                "id=" + id +
                ", orders_id=" + orders_id +
                ", items_id=" + items_id +
                ", items_num=" + items_num +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getOrders_id() {
        return orders_id;
    }

    public void setOrders_id(Integer orders_id) {
        this.orders_id = orders_id;
    }

    public Integer getItems_id() {
        return items_id;
    }

    public void setItems_id(Integer items_id) {
        this.items_id = items_id;
    }

    public Integer getItems_num() {
        return items_num;
    }

    public void setItems_num(Integer items_num) {
        this.items_num = items_num;
    }

}

//

package com.model;

public class Items {
    private Integer id;
    private String name;
    private float price;

    @Override
    public String toString() {
        return "Iteams{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price=" + price +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getPrice() {
        return price;
    }

    public void setPrice(float price) {
        this.price = price;
    }
}

//UserMapper

package com.mapper;
import com.model.User;
import java.util.List;

public interface UserMapper {
    public List<User> findUserAndItems();
}

映射思路

1. 将用户信息映射到user中。
2. 在user类中添加订单列表属性List<Orders> orderslist，将用户创建的订单映射到orderslist
3. 在Orders中添加订单明细列表属性List<Orderdetail> detailList，将订单的明细映射到detailList
4. 在Orderdetail中添加Items属性，将订单明细所对应的商品映射到Items

//UserMapper.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">
<mapper namespace="com.mapper.UserMapper">

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

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

        <collection property="ordersList"  ofType="com.gyf.model.Orders">
            <id property="id" column="orders_id" />

        <collection property="orderDetail" ofType="com.gyf.model.OrderDetail">
            <id property="id" column="orderdetail_id" />
            <result property="items_num" column="items_num"/>

            <association property="items" javaType="items">
                <id property="id" column="items_id"/>
                <result property="name" column="name"/>
                <result property="price" column="price"/>
            </association>
        </collection>
        </collection>
    </resultMap>

    <select id="findUserAndItems"  resultMap="userAndItemsResMap">
        select
            u.id,
            u.username,
            u.address,
            o.id orders_id,
            od.id orderdetail_id,
            i.id items_id,
            i.name,
            od.items_num,
            i.price
        from
            user u,
            orders o,
            orderdetail od,
            items i
        where
            u.id = o.user_id
        and
            o.id = od.orders_id
        and
            i.id = od.items_id

    </select>

</mapper>

//test

public class Demo001 {
    SqlSession session;
    @Before
    public void before() throws IOException {
        System.out.println("before.....获取session");
//        a)读取配置文件；
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");

        //b)通过SqlSessionFactoryBuilder创建SqlSessionFactory会话工厂。
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);

        session = sessionFactory.openSession();
    }

    @After
    public void after(){
        session.close();
    }

  @Test
    public void test001() throws IOException {
        UserMapper userMapper = session.getMapper(UserMapper.class);
       List<User> list = userMapper.findUserAndItems();
        for (User user : list){
            System.out.println("用户名：" + user.getUsername() +
                    "用户ID："+user.getId() + "地址：" + user.getAddress());
            for(Orders order : user.getOrdersList()){
                System.out.println("订单号为：" + order.getId());
                for (OrderDetail orderDetail : order.getOrderDetail()){
                    System.out.println("订单详情ID："+ orderDetail.getId() +
                            "数量："+ orderDetail.getItems_num() +
                            "商品名称：" + orderDetail.getItems().getName()
                                + "价格" + orderDetail.getItems().getPrice());
                }
                System.out.println("============================");
            }
        }
    }
}

//配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <!--数据源-->
    <properties resource="db.properties"/>


    <!--配置别名-->
    <typeAliases>
        <!--<typeAlias type="com.model.User" alias="user"></typeAlias>-->

        <!--指定包名，别名就是类名，第一个小写 User 别名就是user-->
        <package name="com.model"></package>
    </typeAliases>

    <!-- 配置mybatis的环境信息 -->
    <environments default="development">
        <environment id="development">
            <!-- 配置JDBC事务控制，由mybatis进行管理 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置数据源，采用dbcp连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="${driverClass}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--告诉mybatis加载映射文件-->
    <mappers>
        <!--<mapper resource="com/sqlmap/User.xml"></mapper>-->

        <!--第一种:写映射文件的名字-->
        <!--<mapper resource="com/mapper/UserMapper.xml"></mapper>-->

        <!--第二种:写类名,一定要有个映射文件与之对应
        如果没有，那么在UserMapper要声明注解-->
        <!--<mapper class="com.mapper.UserMapper"></mapper>-->

        <!--第三种：可以写包名-->
        <package name="com.mapper"></package>

    </mappers>
</configuration>

控制台打印结果：
用户名：王五用户ID：1地址：江西宜春
订单号为：3
订单详情ID：1数量：1商品名称：台式机价格3000.0
订单详情ID：2数量：3商品名称：笔记本价格6000.0
============================
订单号为：4
订单详情ID：3数量：4商品名称：macbook笔记本价格22200.0
订单详情ID：4数量：3商品名称：笔记本价格6000.0
============================

 

总结

resultType：将查询结果按照sql列名pojo属性名一致性映射到pojo中。

resultMap：使用association和collection完成一对一和一对多高级映射（对结果有特殊的映射要求）。

association：将关联查询信息映射到一个pojo对象中。

collection：将关联查询信息映射到一个list集合中。