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、测试结果输出
![](https://i-blog.csdnimg.cn/blog_migrate/b5ee82fed2f10c9ad6e1c4c11f294425.png)