Mybites ＜collection＞解决一对多关联查询

1、创建数据表

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50738
 Source Host           : 127.0.0.1:3306
 Source Schema         : crm

 Target Server Type    : MySQL
 Target Server Version : 50738
 File Encoding         : 65001

 Date: 19/04/2024 09:52:29
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for crm_goods
-- ----------------------------
DROP TABLE IF EXISTS `crm_goods`;
CREATE TABLE `crm_goods`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of crm_goods
-- ----------------------------
INSERT INTO `crm_goods` VALUES (1, '测试');
INSERT INTO `crm_goods` VALUES (2, '测试2');
INSERT INTO `crm_goods` VALUES (3, '测试3');
INSERT INTO `crm_goods` VALUES (4, '测试4');

-- ----------------------------
-- Table structure for crm_goods_img
-- ----------------------------
DROP TABLE IF EXISTS `crm_goods_img`;
CREATE TABLE `crm_goods_img`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NULL DEFAULT NULL,
  `imgpath` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `basepath` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of crm_goods_img
-- ----------------------------
INSERT INTO `crm_goods_img` VALUES (1, 1, '测试101', '测试102');
INSERT INTO `crm_goods_img` VALUES (2, 2, '测试2', '测试2');
INSERT INTO `crm_goods_img` VALUES (3, 1, '测试1011', '测试1021');
INSERT INTO `crm_goods_img` VALUES (4, 1, '测试1012', '测试1022');
INSERT INTO `crm_goods_img` VALUES (5, 3, '测试3', '测试3');

SET FOREIGN_KEY_CHECKS = 1;

2、创建实体对象

package com.crm.system.model;

import lombok.Data;

import java.util.List;

@Data
public class Goods {

    
    private Integer id;
    
    private String name;

    private List<GoodsImg> goodsImgList;


}

package com.crm.system.model;

import lombok.Data;

@Data
public class GoodsImg {

    private Integer id;
    
    private Integer fid;
    
    private String imgpath;
    
    private String basepath;

}

3、新增xml文件

第一种：（效率低，但是可以解决collection分页问题）

<?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.crm.system.mapper.WxQuestionMapper">
    

    <resultMap id="resultGoodsMap" type="com.crm.system.model.Goods">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <collection property="goodsImgList" ofType="com.crm.system.model.GoodsImg" select="selectGoodsImg" column="{fid = id}"/>
    </resultMap>

    <resultMap id="resultGoodsImgMap" type="com.crm.system.model.GoodsImg">
        <id column="i_id" property="id" jdbcType="INTEGER" />
        <result column="i_fid" property="fid" jdbcType="INTEGER" />
        <result column="i_imgpath" property="imgpath" jdbcType="LONGVARCHAR" />
        <result column="i_basepath" property="basepath" jdbcType="LONGVARCHAR" />
    </resultMap>
    <select id="selectGoods" resultMap="resultGoodsMap">
        SELECT g.id AS id, g.name AS name
        FROM crm_goods g
    </select>
    <select id="selectGoodsImg" resultMap="resultGoodsImgMap">
        SELECT i.id AS i_id, i.fid AS i_fid, i.imgpath AS i_imgpath, i.basepath AS i_basepath
        FROM crm_goods_img i
        WHERE i.fid = #{fid}
    </select>


</mapper>

第二种：（collection分页有问题，一般用于条件查询）

<?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.crm.system.mapper.WxQuestionMapper">
    

    <resultMap id="resultGoodsMap" type="com.crm.system.model.Goods">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <collection property="goodsImgList" ofType="com.crm.system.model.GoodsImg">
            <id column="i_id" property="id" jdbcType="INTEGER" />
            <result column="i_fid" property="fid" jdbcType="INTEGER" />
            <result column="i_imgpath" property="imgpath" jdbcType="LONGVARCHAR" />
            <result column="i_basepath" property="basepath" jdbcType="LONGVARCHAR" />
        </collection>
    </resultMap>
    
    <!--一般用于查询具体商品信息-->
    <select id="selectGoods" resultMap="resultGoodsMap">
        SELECT g.id AS id, g.name AS name, i.id AS i_id, i.fid AS i_fid, i.imgpath AS i_imgpath, i.basepath AS i_basepath
        FROM crm_goods g
        LEFT JOIN crm_goods_img i ON g.id = i.fid
        WHERE g.id = #{id}
    </select>


</mapper>

4、测试结果输出