正文 

当前位置： 首页 > java > java框架

mybatis之旅第六篇-关联查询

茶底世界      2022-05-03     533

关键词：

一、引言

通过动态SQL我们可以进行复杂SQL的编写，但之前的例子都是单表查询，在实际开发中，当然不可能都是单表，很多时候我们需要进行关联多表查询（有些公司为了性能还是尽量的使用单表查询），表与表之间的关系分为一对一，一对多，多对多，我们讲讲这三种是如何编写的。

先进行表和实体的创建：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '王五', null, '2', null);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', null, null, null);

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '下单用户id',
  `number` varchar(32) NOT NULL COMMENT '订单号',
  `createtime` datetime NOT NULL COMMENT '创建订单时间',
  `note` varchar(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `FK_order_1` (`user_id`),
  CONSTRAINT `FK_order_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null);
INSERT INTO `order` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null);
INSERT INTO `order` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null);

User：

@Data
public class User implements Serializable {
    private Integer id;
    // 用户姓名
    private String username;
    // 性别
    private String sex;
    // 生日
    private Date birthday;
    // 地址
    private String address;
}

Order：

@Data
public class Order {

    // 订单id
    private int id;
    // 用户id
    private Integer userId;
    // 订单号
    private String number;
    // 订单创建时间
    private Date createtime;
    // 备注
    private String note;
}

二、一对一

订单表与用户表，一个订单是由一个客户创建的，当我们通过订单去反查用户时，就是一对一关系。

有两种方式可以实现

2.1、使用resultType

可以改造订单pojo类，此pojo类中包括了订单信息和用户信息，这样返回对象的时候，mybatis自动把用户信息也注入进来了，创建OrderUserVO：

@Data
public class OrderUserVO extends Order {
    /**
     * 客户名称
    */
    private String username;
    /**
     * 客户地址
    */
    private String address;  
}

xml文件：

 <select id="queryOrderUser" resultType="com.yuanqinnan.pojo.OrderUserVO">
    SELECTo.id,o.user_id,userId,o.number,o.createtime,o.note,u.username,u.address
    FROM
    `order` o
    LEFT JOIN `user` u ON o.user_id = u.id
  </select>

接口：

public interface OrderMapper {
    List<Order> queryOrderAll();
    List<Order> queryOrderAll2();
    List<OrderUserVO> queryOrderUser();
}

测试：

@Test
public void queryOrderUser(){
    SqlSession sqlSession = this.sqlSessionFactory.openSession();
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    List<OrderUserVO> list = orderMapper.queryOrderUser();
    for (OrderUserVO u : list) {
        System.out.println(u);
    }
    sqlSession.close();
}

结果：

定义专门的pojo类作为输出类型，其中定义了sql查询结果集所有的字段。此方法较为简单，企业中使用普遍。

2.2、使用resultMap

 

使用resultMap，定义专门的resultMap用于映射一对一查询结果，改造Order类，在Order类中加入User属性，user属性中用于存储关联查询的用户信息，因为订单关联查询用户是一对一关系，所以这里使用单个User对象存储关联查询的用户信息。

@Data
public class Order {

    // 订单id
    private int id;
    // 用户id
    private Integer userId;
    // 订单号
    private String number;
    // 订单创建时间
    private Date createtime;
    // 备注
    private String note;
    /**
     * 客户
    */
    private User user;
}

先定义resultMap：

<resultMap type="com.yuanqinnan.model.Order" id="orderUserResultMap">
    <id property="id" column="id"/>
    <result property="userId" column="user_id"/>
    <result property="number" column="number"/>
    <result property="createtime" column="createtime"/>
    <result property="note" column="note"/>
    <!-- association ：配置一对一属性 -->
    <!-- property:order里面的User属性名 -->
    <!-- javaType:属性类型 -->
    <association property="user" javaType="user">
        <!-- id:声明主键，表示user_id是关联查询对象的唯一标识-->
        <id property="id" column="user_id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
    </association>

</resultMap>

再添加查询

   <!-- 一对一关联，查询订单，订单内部包含用户属性 -->
 <select id="queryOrderUserResultMap" resultMap="orderUserResultMap">
    SELECT o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address
    FROM
    `order` o
    LEFT JOIN `user` u ON o.user_id = u.id
</select>

接口：

List<Order> queryOrderUserResultMap();

测试：

@Test
public void queryOrderUserResultMap(){
    SqlSession sqlSession = this.sqlSessionFactory.openSession();
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    List<Order> list = orderMapper.queryOrderUserResultMap();
    for (Order u : list) {
        System.out.println(u);
    }
    sqlSession.close();
}

结果：

三、一对多

一个客户会创建多个订单，当我们查询客户订单时，就会产生一对多的情况，我们以此为例。

首先，我们在User类中增加订单列表

@Data
public class User implements Serializable {

    //id
    private Integer id;
    //用户姓名
    private String username;
    //性别
    private String sex;
    //生日
    private Date birthday;
    //地址
    private String address;
    //订单列表
    private List<Order> orderList;
}

然后增加resultMap：

<resultMap type="com.yuanqinnan.model.Order" id="orderUserResultMap">
    <id property="id" column="id"/>
    <result property="userId" column="user_id"/>
    <result property="number" column="number"/>
    <result property="createtime" column="createtime"/>
    <result property="note" column="note"/>

    <!-- association ：配置一对一属性 -->
    <!-- property:order里面的User属性名 -->
    <!-- javaType:属性类型 -->
    <association property="user" javaType="user">
        <!-- id:声明主键，表示user_id是关联查询对象的唯一标识-->
        <id property="id" column="user_id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
    </association>
</resultMap>

增加查询：

   <!-- 一对一关联，查询订单，订单内部包含用户属性 -->
 <select id="queryOrderUserResultMap" resultMap="orderUserResultMap">
    SELECT o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address
    FROM
    `order` o
    LEFT JOIN `user` u ON o.user_id = u.id
</select>

测试：

@Test
public void queryUserOrder(){
    SqlSession sqlSession = this.sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> list = userMapper.queryUserOrder();
    for (User u : list) {
        System.out.println(u);
    }
    sqlSession.close();
}

结果：

四、多对多

多对多关系都需要中间表来维护，例如，一个订单包含多个产品，一种产品又可以出现在多个订单中，那这个时候我们就需要一个订单产品表作为中间表。这样我们需要新增两个表，产品表和订单产品表。

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT '产品名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf;

-- ----------------------------
-- Records of product
-- ---------------------------
INSERT INTO product VALUES('1','笔记本电脑');
INSERT INTO product VALUES('2','小米手机');

CREATE TABLE `order_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `oid` int(11) DEFAULT NULL COMMENT '订单id',
  `pid` int(11) DEFAULT NULL COMMENT '产品id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
-- ----------------------------
-- Records of order_product
-- ---------------------------
INSERT INTO order_product VALUES('1','3','1');
INSERT INTO order_product VALUES('2','3','2');
INSERT INTO order_product VALUES('3','4','1');

我们可能遇到的多对多查询是：查询这个订单的所有产品或者查询这个产品的所有订单

那这个时候我们的订单和产品表里都应该对应多个中间表，实体就应该这么设计：

order实体增加中间表：

@Data
public class Order {

    // 订单id
    private int id;
    // 用户id
    private Integer userId;
    // 订单号
    private String number;
    // 订单创建时间
    private Date createtime;
    // 备注
    private String note;
    //客户
    private User user;
    //中间表
    private List<OrderProduct> products;
}

OrderProduct：

@Data
public class OrderProduct {

    private Integer id;
    //订单主键
    private Integer oid;
    //产品主键
    private Integer pid;
    //订单
    private Order order;
    //产品
    private Product product;
}

product：

@Data
public class Product {
    //产品id
    private Integer id;
    //产品名称
    private String name;
    //中间表
    List<OrderProduct> orders;
}

resultmap:

<resultMap type="com.yuanqinnan.model.Order" id="orderBean">
    <id column="id" property="id"/>
    <result property="userId" column="user_id"/>
    <result property="number" column="number"/>
    <result property="createtime" column="createtime"/>
    <result property="note" column="note"/>

    <collection property="products" ofType="com.yuanqinnan.model.OrderProduct">
        <id column="oiid" property="id"/>
        <result column="oid" property="oid"/>
        <result column="pid" property="pid"/>
        <association property="product" javaType="com.yuanqinnan.model.Product">
            <id column="pid" property="id"/>
            <result column="pname" property="name"/>
        </association>
    </collection>
</resultMap>

新增查询订单的产品信息方法：

<select id="listOrder" resultMap="orderBean">
    SELECT
        o.*,
        o.id oid,
        p.id pid,
        oi.id oiid,
        p.NAME pname
    FROM
        `order` o
        LEFT JOIN order_product oi ON o.id = oi.oid
        LEFT JOIN product p ON p.id = oi.pid
</select>

测试：

@Test
public void listOrder(){
    SqlSession sqlSession = this.sqlSessionFactory.openSession();
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    List<Order> list = orderMapper.listOrder();
    for (Order u : list) {
        System.out.println(u);
    }
    sqlSession.close();
}

结果：

 

第六篇：springboot整合jpa

什么是Jpa？　　Jpa(JavaPersistenceAPI)是Sun官方提出的Java持久化规范。它为Java开发人员提供了一种对象/关联映射工具来管理Java应用中的关系数据。它的出现主要是为了简化现有的持久化开发工作和整合ORM技术，结束现在Hibernate，Top... 查看详情

第六篇：面向对象

第六篇：面向对象  PYTHON-面向对象类绑定方法PYTHON-面向对象继承派生PYTHON-面向对象-练习-王者荣耀对砍游戏  查看详情

第六篇vggnet——模型精讲

文章目录 查看详情

第六篇商城系统-实现规格参数和销售属性(代码片段)

规格参数1.基础页面  基础页面和属性组的页面非常类似，所以我们先创建了该页面，直接使用2.添加规格参数  规格数据需要绑定对应的属性组，所以我们在后台通过VO对象来接收信息在后端处理添加的逻辑就需... 查看详情

第六篇抽屉效果+uitabbarcontroller

依赖于第三方的框架RESideMenu1.AppDelegate.m中的实现-(BOOL)application:(UIApplication*)applicationdidFinishLaunchingWithOptions:(NSDictionary*)launchOptions{//Overridepointforcustomizationafterapplicationlaunch.//在 查看详情

打怪升级之小白的大数据之旅(六十五)＜hive旅程第六站:hive的查询＞(代码片段)

打怪升级之小白的大数据之旅(六十五)Hive旅程第六站:Hive的查询上次回顾经过前面的学习，我们已经可以初步使用Hive对数据的一些简单操作了，本章节是Hive的一个重点内容–查询查询HQL和我们前面学习的Mysql语法是一样... 查看详情

第六篇商城系统-实现规格参数和销售属性(代码片段)

规格参数1.基础页面  基础页面和属性组的页面非常类似，所以我们先创建了该页面，直接使用2.添加规格参数  规格数据需要绑定对应的属性组，所以我们在后台通过VO对象来接收信息在后端处理添加的逻辑就需... 查看详情

关于ajax第六篇

使用回调函数回调函数是一种以参数形式传递给另一个函数的函数。如果您的网站上存在多个AJAX任务，那么您应该为创建XMLHttpRequest对象编写一个标准的函数，并为每个AJAX任务调用该函数。该函数调用应该包含URL以及发生onreadys... 查看详情

第六篇avplayer地址视频播放控件

1.引用头文件#importAVFoundation2.自定义AVPlayer（播放的机器）3.自定义AVPlayerItem(胶片)>>视频的URL转成AVAsset4.AVPlayerLayer(白板)  查看详情

第六篇elasticsearchexpress删除索引数据

express框架删除elasticsearch索引数据1.在elasticsearch.js文件下添加functiondeleteDocument(id){returnelasticClient.delete({index:indexName,type:"foods",id:id});}exports.deleteDocument=deleteDocument;2.在路由删除数据代码块中添加el 查看详情

第六篇io流技术(代码片段)

packagecom.zzp.demo01;importjava.io.ByteArrayInputStream;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.io.InputStream;/****四 查看详情

android插件化开发——hook方式第六篇

http://blog.csdn.net/u013022222/article/details/51295208 查看详情

重构之路第六篇——处理概括关系

1PullupField(字段上移)2PullupMethod(函数上移)3PullupConstructorBody(构造函数本体上移)4PushDownMethod(函数下移)5PushDownField(字段下移)6ExtractSubclass(提炼子类)7ExtractSuperclass(提炼超类)8ExtractInterface(提炼接口)9CollapseHierarchy(折叠 查看详情

mybatis关联查询（嵌套查询）

Mybatis关联查询（嵌套查询）上一篇文章介绍了基于Mybatis对数据库的增、删、改、查。这一篇介绍下关联查询（joinquery）。三张表：userarticleblog表的存储sql文件：/*NavicatMySQLDataTransferSourceServer:localhostSourceServerVersion:50620SourceHost:loca... 查看详情

第六篇javascript面向对象

h1{background-color:palegreen}一、闭包闭包是指可以包含自由（未绑定到特定对象）变量的代码块。「闭包」，是指拥有多个变量和绑定了这些变量的环境的表达式（通常是一个函数），因而这些变量也是该表达式的一部分。闭包是个... 查看详情

第六篇xpath的用法

 使用pycharmdebug调试效率会比较慢，因为每次调试都需要向url发送请求，等返回信息,scrapy提供一种方便调试的功能，如下：>>>(third_project)[email protected]:pachong$scrapyshellhttp://blog.jobbole.com/112239/>>>title=response 查看详情

web前端第六篇javascript对象

在JavaScript中除了null和undefined以外其他的数据类型都被定义成了对象，也可以用创建对象的方法定义变量，String、Math、Array、Date、RegExp都是JavaScript中重要的内置对象，在JavaScript程序大多数功能都是基于对象实现的<scriptlanguage=... 查看详情

小刘同学的第六十六篇博文

　　夜深了，1:53了　　今天其实破了一个大戒　　距上一次写代码貌似就longlongago的事了　　我想我需要一场彻底的改变　　发自内心由内而外的改变　　睡了　　大家晚安 查看详情