一 订单商品数据模型-分析

分析思路

• user和orders：

  • user → orders:一个用户可以创建多个订单，一对多
  • orders → user:一个订单只能由一个用户创建，一对一

• orders和orderdetail：

  • orders → orderdetail:一个订单可以包括多个订单明细，因为一个订单可以购买多个商品，每个商品信息在orderdetail记录，一对多
  • orderdetail → orders:一个订单明细只能包括在一个订单中，一对一

• orderdetail和items：

  • orderdetail→ items:一个订单明细只对应一个商品信息，一对一
  • items → orderdetail:一个商品信息可以包括多个订单明细，一对多

二 一对一查询-使用resultType

• 需求：查询订单关联查询用户信息

OrdersBean.java

package entity;

import java.util.Date;

public class OrdersBean {
    private int id;
    private int user_id;
    private String number;
    private Date createtime;
    private String note;

    public int getId() {
        return id;
    }

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

    public int getUser_id() {
        return user_id;
    }

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

    public String getNumber() {
        return number;
    }

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

    public Date getCreatetime() {
        return createtime;
    }

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

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    @Override
    public String toString() {
        return "OrdersBean{" +
                "id=" + id +
                ", user_id=" + user_id +
                ", number='" + number + '\'' +
                ", createtime=" + createtime +
                ", note='" + note + '\'' +
                '}';
    }
}

OrdersCustom.java

package pojo;

import entity.OrdersBean;
//通过此类映射订单和用户查询的结果，让此类继承包括字段较多的pojo类
public class OrdersCustom extends OrdersBean {
    //添加用户属性
    private  String username;
    private String sex;
    private String address;

    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 String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return super.toString()+"OrdersCustom{" +
                "username='" + username + '\'' +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

OrdersMapperCustom.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="dao.OrdersMapperCustom">
    <select id="findOrdersUser" resultType="pojo.OrdersCustom">
        SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
    </select>
</mapper>

OrdersMapperCustom.java

package dao;

import java.util.List;

public interface OrdersMapperCustom {
    public List<OrdersMapperCustom> findOrdersUser() throws Exception;
}

OrdersMapperTest.java

import dao.OrdersMapperCustom;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class OrdersMapperTest {
    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void setUp() throws IOException {
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void findOrdersUser() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
         OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
         List<OrdersMapperCustom> ordersCustom = ordersMapperCustom.findOrdersUser();
        System.out.println(ordersCustom);
    }
}

执行结果

三 一对一查询-使用resultMap

OrdersBean.java
在OrdersBean中注入UserBean对象，并创建对应的get和set方法

图片.png

OrdersMapperCustom.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="dao.OrdersMapperCustom">
    <!--订单查询关联用户的resultMap-->
    <resultMap id="OrdersUserResultMap" type="entity.OrdersBean">
        <!--配置映射的订单信息-->
        <id column="id" property="id"></id>
        <result column="user_id" property="user_id"></result>
        <result column="number" property="number"></result>
        <result column="createtime" property="createtime"></result>
        <result column="note" property="note"></result>
        <!--配置映射的关联的用户信息-->
        <!--association:用于映射关联查询单个对象的信息
        property:要将关联查询的用户信息映射到Orders中哪个属性-->
        <association property="userBean" javaType="entity.UserBean">
            <!--id:关联查询用户的唯一标识
            column：指定唯一标识用户信息的列
            javaType：映射到user的那个属性-->
            <id column="user_id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="sex" property="sex"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>
    <!--查询订单关联查询用户信息，使用resultType-->
    <select id="findOrdersUser" resultType="pojo.OrdersCustom">
    SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
    <!--查询订单关联查询用户信息，使用resultMap-->
    <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
    SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
</mapper>

OrdersMapperCustom.java

    //查询订单关联查询用户使用resultMap
    public List<OrdersBean> findOrdersUserResultMap() throws Exception;

OrdersMapperTest.java

    @Test
    public void findOrdersUserResultMap() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        List<OrdersBean> ordersBeans = ordersMapperCustom.findOrdersUserResultMap();
        System.out.println(ordersBeans);
    }

测试结果