首页 > > 程序设计 > Java技术 >

mybatis之旅第六篇-关联查询

2019-04-18 08:54:33来源：博客园 阅读 ()

新老客户大回馈,云服务器低至5折

一、引言

通过动态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();
}

结果：

 


原文链接:https://www.cnblogs.com/yuanqinnan/p/10719019.html
如有疑问请与原作者联系

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：tomcat端口修改以及jvm启动参数设置

下一篇：Java基础学习--数组

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签