Mybatis多表查询

练习中遇到的一些小问题，记录下来，理清一些逻辑。

需求：

一张餐桌表(可以看作用户表)，一个餐桌对应一张订单表，一个订单表包含多个订单项，查询所有餐桌的详细订单项。

数据库：

oSjd4s.png

代码：

实体类相关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
//餐桌
public class DinnerTable {
    private int id;
    private String tableName;
    private int tableStatus;
   private Date orderDate;
   private Orders orders;
}
//订单
public class Orders {
    private int oid;
    private int tid;
    private String orderDate;
    private double totalPrice;
    private  int orderStatus;
    private List<Orderdetail> orderdetails;
}
//订单项
public class Orderdetail {
    private int otid;
    private int oid;
    private int fid;
    private int foodCount;
    private Food food;
}

接口方法

1
2
3
4
5
6
7
8
9
10
11
public interface DinnerTableMapper {

     //查询所有桌子
    public List<DinnerTable> queryAllTables() ;
    //查询所有空闲状态的桌子
    public List<DinnerTable> queryTabLesByStatus(int i) ;
     //添加桌子
    public int updateTables(DinnerTable vo)  ;
    //修改桌子的状态
    public int updateTableStatus(DinnerTable vo, int i) ;
}

Mapper.xml(采用按结果查询)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
<mapper namespace="com.zwj.dao.DinnerTableMapper">
//查询dinnnertable先要查询订单表，查询订单表先要查询订单项表
//订单选项
    <resultMap id="OrderdetailMap" type="Orderdetail">
		<id column="id" property="otid"/>
		<result column="orderid" property="oid"/>
		<result column="food_id" property="fid"/>
		<result column="foodCount" property="foodCount"></result>
	</resultMap>
	<select id="getOrderdetail" resultMap="OrderdetailMap">
      	select * from Orderdetail where orderid=#{orderid}
    </select>
//订单与订单项 1对多 
//collection: property:属性名  JavaType是用来指定pojo中属性的类型
//ofType指定的是映射到list集合属性中pojo的类型 
//column是一对多的外键 , 写的是一的主键的列名
	<resultMap id="OrdersMap" type="Orders">
		<id column="id" property="oid"></id>
		<result column="table_id" property="tid"></result>
		<result column="orderDate" property="orderDate"></result>
		<result column="totalPrice" property="totalPrice"></result>
		<result column="orderStatus" property="orderStatus"></result>
		<collection property="orderdetails" javaType="ArrayList" ofType="orderdetail" column="id"
		            select="getOrderdetail"/>
	</resultMap>
	<select id="getOrders" resultMap="OrdersMap">
        select * from Orders where id=#{id}
	</select>
//association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名
	<resultMap id="DinnerTableOrders" type="DinnerTable">
		<id column="id" property="id"></id>
		<association property="orders" column="id" javaType="Orders" select="getOrders"></association>
	</resultMap>
	<select id="queryAllTables" resultMap="DinnerTableOrders">
     	select  * from DinnerTable
  	</select>

    <select id="queryTabLesByStatus" parameterType="int" resultType="DinnerTable">
         select  * from DinnerTable where tableStatus=#{tableStatus}
    </select>

    <insert id="updateTables" parameterType="Dinnertable">
         insert into DinnerTable(dtid, tableName, tableStatus,orderDate) values (#{dtid},#{tableName},#{tableStatus},#{orderDate})
    </insert>

    <update id="updateTableStatus" parameterType="Dinnertable" >
           update DinnerTable
        set tableStatus=#{tableStatus}
        where dtid=#{dtid}
    </update>
</mapper>

查询结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DinnerTable(id=1, tableName=1号桌, tableStatus=0, orderDate=Thu Jun 24 09:40:38 CST 2021, 
orders=Orders(oid=1, tid=1, orderDate=2021-06-19 00:00:00.0, totalPrice=80.0, orderStatus=0, 
orderdetails=
[Orderdetail(otid=1, oid=1, fid=1, foodCount=1, food=null), 
Orderdetail(otid=2, oid=1, fid=2, foodCount=1, food=null)]))

DinnerTable(id=2, tableName=2号桌, tableStatus=0, orderDate=Wed Jun 23 09:40:42 CST 2021, 
orders=Orders(oid=2, tid=2, orderDate=2021-06-25 00:00:00.0, totalPrice=144.0, orderStatus=1, 
orderdetails=
[Orderdetail(otid=3, oid=2, fid=3, foodCount=1, food=null),
 Orderdetail(otid=4, oid=2, fid=4, foodCount=1, food=null)]))

DinnerTable(id=3, tableName=3号桌, tableStatus=0, orderDate=Wed Jun 09 09:40:46 CST 2021, 
orders=Orders(oid=3, tid=3, orderDate=2021-06-17 00:00:00.0, totalPrice=160.0, orderStatus=0, 
orderdetails=
[Orderdetail(otid=5, oid=3, fid=1, foodCount=2, food=null),
 Orderdetail(otid=6, oid=3, fid=2, foodCount=2, food=null)]))