例如有两张表,分别是客户表和订单表,一个客户有多个订单,一个订单属于一个客户。
两个实体类Customer Order 如下:
package com.itlike.domain;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.ArrayList;
import java.util.List;
@Setter@Getter@ToString
public class Customer {
private Integer cust_id;
private String cust_name;
private String cust_profession;
private String cust_phone;
private String email;
private List<Order> orders = new ArrayList<>();
}
package com.itlike.domain;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
@Setter@Getter@ToString
public class Order {
private Integer order_id;
private String order_name;
private String order_num;
private Customer customer;
}
数据库中的表如下:
1.一对多的左连接查询
在CustomerMapper接口中定义方法
//查询所有的客户
public List<Customer> getAllCustomers();
在CustomerMapper.xml中注入SQL
collection中的 javatype 表示 orders是一个list集合 oftype表示list集合中装的都是Order类型
<select id="getAllCustomers" resultMap="custMap">
SELECT * from `customer` as c LEFT JOIN `order` as o ON c.cust_id=o.cust_id;
</select>
<resultMap id="custMap" type="Customer">
<id column="cust_id" property="cust_id"/>
<result column="cust_name" property="cust_name"/>
<result column="cust_profession" property="cust_profession"/>
<result column="cust_phone" property="cust_phone"/>
<result column="email" property="email"/>
<collection property="orders" javaType="list" ofType="Order">
<id column="order_id" property="order_id"/>
<result column="order_num" property="order_num"/>
<result column="order_name" property="order_name"/>
</collection>
</resultMap>
2.一对多的分步查询
CustmoerMapper.xml中的SQL注入
<!--分步查询-->
<select id="getAllCustomers" resultMap="custMap">
select * from `customer`;
</select>
<resultMap id="custMap" type="Customer">
<id column="cust_id" property="cust_id"/>
<result column="cust_name" property="cust_name"/>
<result column="cust_profession" property="cust_profession"/>
<result column="cust_phone" property="cust_phone"/>
<result column="email" property="email"/>
<collection property="orders" javaType="list" ofType="Order"
select="com.itlike.mapper.OrderMapper.getOrderWithCustId" column="cust_id">
</collection>
</resultMap>
OrderMapper.xml中的SQL注入
<select id="getOrderWithCustId" resultType="com.itlike.domain.Order">
select * from `order` where cust_id=#{id};
</select>
3.一对多的插入操作
在CustomerMapper接口中定义方法
//添加客户
public void insertCustomer(Customer customer);
在CustomerMapper.xml中注入插入客户信息的SQL 先插入客户信息
<!--插入客户,并获得生成的主键-->
<insert id="insertCustomer" parameterType="Customer"
useGeneratedKeys="true"
keyColumn="cust_id"
keyProperty="cust_id"
>
insert into `customer`(cust_name,cust_profession,cust_phone,email)
values (#{cust_name},#{cust_profession},#{cust_phone},#{email})
</insert>
在OrderMapper.xml中注入插入订单信息的SQL 再插入订单信息
<insert id="insertOrder" parameterType="Order"
useGeneratedKeys="true"
keyColumn="order_id"
keyProperty="order_id"
>
insert into `order` (order_name,order_num,cust_id )
values (#{order_name},#{order_num},#{customer.cust_id})
</insert>
最后还要在order表中写入外键 即对应的客户编号 更新客户和订单的关系
在OrderMapper.xml中注入插入更新关系的SQL
<update id="updateCustId">
update `order` set cust_id = ${custId} where order_id = ${orderId}
</update>
测试类代码:
public void test6(){
SqlSession sqlSession = MyBatisUtils.openSession();
CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
Customer customer = new Customer();
customer.setCust_name("新客户");
Order order1 = new Order();
order1.setOrder_name("订单1");
Order order2 = new Order();
order2.setOrder_name("订单2");
customer.getOrders().add(order1);
customer.getOrders().add(order2);
//保存数据
customerMapper.insertCustomer(customer);
orderMapper.insertOrder(order1);
orderMapper.insertOrder(order2);
//更新关系
for (Order order : customer.getOrders()) {
orderMapper.updateCustId(order.getOrder_id(),customer.getCust_id());
}
sqlSession.commit();
sqlSession.close();
}
4.一对多的删除操作
必须先得打破外键的关系 然后再分别删除客户表和订单表中的信息
(1)在订单表Order中打破外键
OrderMapper接口中定义方法
//打破外键关系
public void updateRelationCustomer(Integer custId);
OrderMapper.xml中的SQL
<update id="updateRelationCustomer">
update `order` set cust_id =null where cust_id=#{custId}
</update>
测试类代码:
public void test7(){
SqlSession sqlSession = MyBatisUtils.openSession();
CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
//一对多删除之前 要先打破外键关系
orderMapper.updateRelationCustomer(18);
customerMapper.deleteCustomer(18);
sqlSession.commit();
sqlSession.close();
}
在多对多的关系中,处理方法和一对多相似
查询时可以左连接查询 也可以分步查询
(重点是查询时SQL的写法 左连接查询时 表一左连接中间表左连接表二 )
<select id="getAllTeacher" resultMap="teacherMap">
SELECT * from `teacher` as t LEFT JOIN `stu_teacher_rel` as str ON t.teacher_id= str.teacher_id
LEFT JOIN student as s ON str.stu_id = s.stu_id;
</select>
插入时也分别插入两个表的信息 然后建立中间表的对应关系
删除时也应该先打破中间表的关系 然后再分别删除