mybatis关联 多对多，一对多，多对一的编写

1. 准备

数据库 以及数据

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.23 : Database - test
*********************************************************************
*/
​
/*!40101 SET NAMES utf8 */;
​
/*!40101 SET SQL_MODE=''*/;
​
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
​
USE `test`;
​
/*Table structure for table `t_category` */
​
DROP TABLE IF EXISTS `t_category`;
​
CREATE TABLE `t_category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
​
/*Data for the table `t_category` */
​
insert  into `t_category`(`category_id`,`category_name`) values (1,'焦点'),(2,'国际'),(3,'社会'),(4,'房产'),(5,'财经'),(6,'娱乐');
​
/*Table structure for table `t_customer` */
​
DROP TABLE IF EXISTS `t_customer`;
​
CREATE TABLE `t_customer` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(50) NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
​
/*Data for the table `t_customer` */
​
insert  into `t_customer`(`customer_id`,`customer_name`) values (1,'zs'),(2,'ls'),(3,'ww'),(4,'xm');
​
/*Table structure for table `t_news` */
​
DROP TABLE IF EXISTS `t_news`;
​
CREATE TABLE `t_news` (
  `news_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  PRIMARY KEY (`news_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
​
/*Data for the table `t_news` */
​
insert  into `t_news`(`news_id`,`title`) values (1,'110'),(2,'111'),(3,'112'),(4,'113'),(5,'114'),(6,'115'),(7,'116'),(8,'117'),(9,'118');
​
/*Table structure for table `t_news_category` */
​
DROP TABLE IF EXISTS `t_news_category`;
​
CREATE TABLE `t_news_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `nid` (`nid`),
  KEY `cid` (`cid`),
  CONSTRAINT `t_news_category_ibfk_1` FOREIGN KEY (`nid`) REFERENCES `t_news` (`news_id`),
  CONSTRAINT `t_news_category_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `t_category` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;
​
/*Data for the table `t_news_category` */
​
insert  into `t_news_category`(`id`,`nid`,`cid`) values (1,1,1),(2,1,2),(3,2,1),(4,2,2),(5,3,1),(6,3,2),(7,3,3),(8,4,1),(9,4,2),(10,4,3),(11,4,4);
​
/*Table structure for table `t_order` */
​
DROP TABLE IF EXISTS `t_order`;
​
CREATE TABLE `t_order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(50) NOT NULL,
  `cid` int(11) NOT NULL,
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `order_no` (`order_no`),
  KEY `cid` (`cid`),
  CONSTRAINT `t_order_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `t_customer` (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
​
/*Data for the table `t_order` */
​
insert  into `t_order`(`order_id`,`order_no`,`cid`) values (1,'P01',1),(2,'P02',1),(3,'P03',1),(4,'P04',1),(5,'P05',1),(6,'P06',2),(7,'P07',2),(8,'P08',2),(9,'P09',3),(10,'P10',3);
​
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

实体创建 注意：所有主题 set get tostring 方法 全部采用了组件lombok生成

Order 订单表

package com.zking.mybatis01.model;
​
import lombok.Data;
​
@Data
​
public class Order {
    private Integer id;
    private String orderName;
    private Integer cid;
    private Customer customer;
}
​

Customer 用户表

package com.zking.mybatis01.model;
​
import lombok.Data;
​
import java.util.ArrayList;
import java.util.List;
@Data
public class Customer {
  private Integer customerid;
    private String customername;
  List<Order> order =new ArrayList<>();
​
​
}

​News 表

package com.zking.mybatis01.model;
​
import lombok.Data;
​
import java.util.ArrayList;
import java.util.List;
​
@Data
public class News {
    private Integer newsid;
    private String title;
    private List<Category> category=new ArrayList<>();
}

​

NewsCategory关联表与Category做关联

package com.zking.mybatis01.model;
​
import lombok.Data;
​
@Data
public class NewsCategory {
    private Integer id;
    private Integer nid;
    private Integer cid;
}

​

Category 表

package com.zking.mybatis01.model;
​
import lombok.Data;
​
@Data
public class Category {
    private Integer categoryid;
    private String categoryName;
​
}

​

1.一对多的实现

1.创建 mapper层

package com.zking.mybatis01.mapper;
​
import com.zking.mybatis01.model.Customer;
import com.zking.mybatis01.model.News;
import com.zking.mybatis01.model.Order;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
​
​
import java.util.List;
import java.util.Map;
@Repository
public interface IOrderMapper {
/**
*一对多测试方法
*/
    List<Customer> listCurrency(@Param("map") Map<Object,String> map);
​
​
​
}

​

2.创建对应的实现类 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.zking.mybatis01.mapper.IOrderMapper">
​
    <!-- 连表查询  一对多 处理方式  -->
    <resultMap id="currency" type="com.zking.mybatis01.model.Customer">
        <id column="customer_id" property="customerid" javaType="java.lang.Integer"/>
        <result column="customer_name" property="customername" javaType="java.lang.String"/>
         <!-- collection 表示我这个集合   第一个参数 集合名字 对应实体名
             第二个参数我这哥集合中放入的引用类型
            -->
        <collection property="order" ofType="com.zking.mybatis01.model.Order">
            <id column="order_id" property="id" javaType="java.lang.Integer"/>
            <result column="order_no" property="orderName" javaType="java.lang.String"/>
            <result column="cid" property="cid" javaType="java.lang.Integer"/>
        </collection>
    </resultMap>
​
    <select id="listCurrency" resultMap="currency">
        SELECT a.customer_id,a.customer_name,b.order_id,b.order_no,b.cid
        FROM `t_customer` a inner join t_order b on a.customer_id=b.cid
        <where>
            <if test="map != null and map.size != 0">
                 <!--使用 map 传递参数  指定了Map 名为 map-->
                and a.customer_name like concat(#{map.name},'%')
            </if>
        </where>
    </select>
​
​
​
</mapper>

3.创建service 实现 接口生成即可

package com.zking.mybatis01.service;
​
import com.zking.mybatis01.mapper.IOrderMapper;
import com.zking.mybatis01.model.Customer;
import com.zking.mybatis01.model.News;
import com.zking.mybatis01.model.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
​
import java.util.List;
import java.util.Map;
@Service
public class OrderService implements IOrderService {
  @Autowired
  private IOrderMapper order;
    @Override
    public List<Customer> listCurrency(Map<Object, String> map) {
        return order.listCurrency(map);
    }
​
}
​

4.测试接口

测试代码

package com.zking.mybatis01.service;
​
import com.zking.mybatis01.model.Customer;
import com.zking.mybatis01.model.News;
import com.zking.mybatis01.model.Order;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
​
import java.util.List;
​
import static org.junit.Assert.*;
//帮我们生成 spring 上下文对象
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath*:applicationContext*.xml"})
public class OrderServiceTest {
    @Autowired
    private IOrderService orders;
   @Test
    public void listCurrencyTest(){
       List<Customer> customers =  orders.listCurrency(null);
       customers.forEach(System.out::println);
   }
​
}
​
​

 

2.多对一的实现

多对一 可以理解成每个订单对应一个用户

1.在mapper中定义接口

   List<Order> listOrder ( Order or);

2.生成 xml文件

  <!-- 订单 多对一 处理 -->
    <resultMap id="order" type="com.zking.mybatis01.model.Order">
        <id column="order_id" property="id" javaType="java.lang.Integer"/>
        <result column="order_no" property="orderName" javaType="java.lang.String"/>
        <result column="cid" property="cid" javaType="java.lang.Integer"/>
        <!-- 设置引用类型（实体） association 后面的映射和集合不一样 集合是ofType 实体是javaType 和属性一样  -->
        <association property="customer" javaType="com.zking.mybatis01.model.Customer">
            <id column="customer_id" property="customerid" javaType="java.lang.Integer"/>
            <result column="customer_name" property="customername" javaType="java.lang.String"/>
        </association>
    </resultMap>
​
    <select id="listOrder" resultMap="order">
        SELECT a.customer_id,a.customer_name,b.order_id,b.order_no,b.cid
        FROM `t_customer` a inner join t_order b on a.customer_id=b.cid
        <where>
            <if test="orderName !=null and '' != orderName">
                and order_no like concat(orderName , '%')
            </if>
        </where>
    </select>

3.service 编写

List<Order> listOrder (Order or);

4.测试

 @Test
    public void listOrderTest(){
        List<Order> orders1 = orders.listOrder(null);
        orders1.forEach(System.out::println);
    }

3.多对多的实现

多对多 其实就是一个一对多 不过有个中间表连接

 /**
     * 多对多测试
     * @return
     */
   List<News>  listTnws();

mapper实现

 <!-- 多对多 测试 -->
    <resultMap id="news" type="com.zking.mybatis01.model.News">
        <id column="news_id" property="newsid" javaType="java.lang.Integer"/>
        <result column="title" property="title" javaType="java.lang.String"/>
        <collection property="category" ofType="com.zking.mybatis01.model.Category">
            <id column="category_id" property="categoryid" javaType="java.lang.Integer"/>
            <result column="category_name" property="categoryName" javaType="java.lang.String" />
        </collection>
    </resultMap>
    <select id="listTnws"  resultMap="news">
        SELECT t1.news_id, t1.title, t3.category_id, t3.category_name
        FROM t_news t1
                 left join t_news_category t2 on t1.news_id = t2.nid
                 Left join t_category t3 on t2.cid = t3.category_id
​
​
    </select>

service 实现

 @Override
  public List<News> listTnws() {
    return order.listTnws();
  }

测试

@Test
 public  void listTnwsTest(){
     List<News> news = orders.listTnws();
     news.forEach(System.out::println);
​
 }