signed

QiShunwang

“诚信为本、客户至上”

mybatis多表关联查询+ResultMap结果映射

2021/5/14 23:03:24   来源:

一:实体类

Goods类:

package com.imooc.mybatis.entity;

/**
 * @author lihaisong
 * @version 1.0
 * @date 2021/5/14 17:44
 */
public class Goods {
    //商品编号
    private Integer goodsId;
    //标题
    private String title;
    //子标题
    private String subTitle;
    //原始价格
    private Float originalCost;
    //当前价格
    private Float currentPrice;
    //折扣率
    private Float  discount;
    //是否包邮
    private Float isFreeDelivery;
    //分类编号
    private Integer categoryId;


    public Integer getGoodsId() {
        return goodsId;
    }

    public void setGoodsId(Integer goodsId) {
        this.goodsId = goodsId;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getSubTitle() {
        return subTitle;
    }

    public void setSubTitle(String subTitle) {
        this.subTitle = subTitle;
    }

    public Float getOriginalCost() {
        return originalCost;
    }

    public void setOriginalCost(Float originalCost) {
        this.originalCost = originalCost;
    }

    public Float getCurrentPrice() {
        return currentPrice;
    }

    public void setCurrentPrice(Float currentPrice) {
        this.currentPrice = currentPrice;
    }

    public Float getDiscount() {
        return discount;
    }

    public void setDiscount(Float discount) {
        this.discount = discount;
    }

    public Float getIsFreeDelivery() {
        return isFreeDelivery;
    }

    public void setIsFreeDelivery(Float isFreeDelivery) {
        this.isFreeDelivery = isFreeDelivery;
    }

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }
}

Category类:

package com.imooc.mybatis.entity;

/**
 * 分类
 * @author lihaisong
 * @version 1.0
 * @date 2021/5/14 22:34
 */
public class Category {
    private Integer categoryId;
    private String categoryName;
    private Integer parentId;
    private Integer categoryLevel;
    private Integer categoryOrder;

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }

    public Integer getParentId() {
        return parentId;
    }

    public void setParentId(Integer parentId) {
        this.parentId = parentId;
    }

    public Integer getCategoryLevel() {
        return categoryLevel;
    }

    public void setCategoryLevel(Integer categoryLevel) {
        this.categoryLevel = categoryLevel;
    }

    public Integer getCategoryOrder() {
        return categoryOrder;
    }

    public void setCategoryOrder(Integer categoryOrder) {
        this.categoryOrder = categoryOrder;
    }
}

二:GoodsDTO:数据传输对象,多表关联查询返回结果的DTO对象(goods+category)

package com.imooc.mybatis.dto;

import com.imooc.mybatis.entity.Category;
import com.imooc.mybatis.entity.Goods;

/**
 * 数据传输对象:goods+category
 * 多表关联查询返回结果的DTO对象
 * @author lihaisong
 * @version 1.0
 * @date 2021/5/14 21:41
 */
public class GoodsDTO {
    private Goods goods = new Goods();
    private Category category = new Category();
    private String test;

    public Goods getGoods() {
        return goods;
    }

    public void setGoods(Goods goods) {
        this.goods = goods;
    }

    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }

    public String getTest() {
        return test;
    }

    public void setTest(String test) {
        this.test = test;
    }
}

三:goods.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">
<!-- namespace命名空间下 的id是唯一的 -->
<mapper namespace="goods">
   
    <!--结果映射: 使用DTO对象接收 多表关联查询返回的结果集-->
    <resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
        <!--goods表:设置主键-->
        <id property="goods.goodsId" column="goods_id"/>
        <!--goods表:除了id以外的字段-->
        <result property="goods.title" column="title"/>
        <result property="goods.subTitle" column="sub_title"/>
        <result property="goods.originalCost" column="original_cost"/>
        <result property="goods.currentPrice" column="current_price"/>
        <result property="goods.discount" column="discount"/>
        <result property="goods.isFreeDelivery" column="is_free_delivery"/>
        <result property="goods.categoryId" column="category_id"/>
        <!--category表:DTO的属性-->
        <result property="category.categoryId" column="category_id"/>
        <result property="category.categoryName" column="category_name"/>
        <result property="category.parentId" column="parent_id"/>
        <result property="category.categoryLevel" column="category_level"/>
        <result property="category.categoryOrder" column="category_order"/>
        <result property="test" column="test"/>
    </resultMap>
    <select id="selectGoodsDTO" resultMap="rmGoods">
        select g.*, c.*,'1' as test
        from  t_goods g, t_category c
        where g.category_id  = c.category_id
    </select>
</mapper>

四:测试用例

@Test
public void testSelectGoodsDTO() throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtils.openSession();

            List<GoodsDTO> list = sqlSession.selectList("goods.selectGoodsDTO");
            for (GoodsDTO g : list){
                System.out.println(g.getGoods().getTitle()+"---"+g.getCategory().getCategoryName());
            }
        }catch (Exception e){
            throw e;
        }finally {
            MybatisUtils.closeSession(sqlSession);
        }
}

五:相关的sql

/*
Navicat MySQL Data Transfer

Source Server         : xampp
Source Server Version : 100140
Source Host           : localhost:3306
Source Database       : babytun

Target Server Type    : MYSQL
Target Server Version : 100140
File Encoding         : 65001

Date: 2021-05-14 22:57:53
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_category
-- ----------------------------
DROP TABLE IF EXISTS `t_category`;
CREATE TABLE `t_category` (
  `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '分类id',
  `category_name` varchar(64) NOT NULL COMMENT '分类名称',
  `parent_id` int(10) DEFAULT NULL COMMENT '上级分类',
  `category_level` int(2) DEFAULT NULL COMMENT '分类的级别',
  `category_order` int(6) DEFAULT '0' COMMENT '前端顺序  数字大的优先显示',
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COMMENT='分类表';

-- ----------------------------
-- Records of t_category
-- ----------------------------
INSERT INTO `t_category` VALUES ('1', '母婴专区', null, '1', '100');
INSERT INTO `t_category` VALUES ('2', '美妆护肤', null, '1', '200');
INSERT INTO `t_category` VALUES ('3', '家装生活', null, '1', '300');
INSERT INTO `t_category` VALUES ('4', '食品营养', null, '1', '400');
INSERT INTO `t_category` VALUES ('5', '面膜', '2', '2', '0');
INSERT INTO `t_category` VALUES ('6', '面部护理', '2', '2', '0');
INSERT INTO `t_category` VALUES ('7', '孕产护肤', '2', '2', '0');
INSERT INTO `t_category` VALUES ('8', '纸尿裤', '1', '2', '0');
INSERT INTO `t_category` VALUES ('9', '彩妆', '2', '2', '0');
INSERT INTO `t_category` VALUES ('10', '身材护理', '2', '2', '0');

-- ----------------------------
-- Table structure for t_goods
-- ----------------------------
DROP TABLE IF EXISTS `t_goods`;
CREATE TABLE `t_goods` (
  `goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `title` varchar(128) NOT NULL COMMENT '标题',
  `sub_title` varchar(128) NOT NULL COMMENT '子标题',
  `original_cost` double(9,2) NOT NULL COMMENT '原始价格',
  `current_price` double(9,2) NOT NULL COMMENT '当前价格',
  `discount` double(5,2) DEFAULT NULL COMMENT '折扣率',
  `is_free_delivery` int(1) DEFAULT '0' COMMENT '是否包邮 1包邮 0不包邮',
  `category_id` int(10) NOT NULL COMMENT '分类编号',
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COMMENT='商品表';

-- ----------------------------
-- Records of t_goods
-- ----------------------------
INSERT INTO `t_goods` VALUES ('16', '数学15', '这个是⾯试专题概要', '9900.00', '500.00', '0.44', '0', '1');
INSERT INTO `t_goods` VALUES ('17', '数学16', '这个是⾯试专题概要', '9900.00', '632.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('18', '数学17', '这个是⾯试专题概要', '635.00', '600.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('20', '测试--xml-插入数据2', '这个是⾯试专题概要', '635.00', '555.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('21', '测试--xml-插入数据3', '这个是⾯试专题概要', '635.00', '588.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('22', '测试--xml-插入数据4', '这个是⾯试专题概要', '635.00', '566.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('23', '测试--xml-插入数据5', '这个是⾯试专题概要', '635.00', '577.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('24', '小滴课堂1', '不偶像', '400.00', '356.00', '0.44', '0', '1');
INSERT INTO `t_goods` VALUES ('25', '小滴课堂23', '不偶像2', '700.00', '666.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('26', '小滴课堂155', '不偶像', '400.00', '388.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('27', '小滴课堂26', '不偶像2', '700.00', '635.00', '0.44', '0', '4');