mybatis映射查询一对多，多对一

最新推荐文章于 2024-06-23 17:07:18 发布

ekkcole

最新推荐文章于 2024-06-23 17:07:18 发布

阅读量613

点赞数

分类专栏： java 文章标签： java postgresql mybatis 一对多多对一

本文链接：https://blog.csdn.net/weixin_45559862/article/details/122448003

版权

java 专栏收录该内容

106 篇文章 4 订阅

订阅专栏

sql

CREATE TABLE "public"."yj_advice" (
  "uuid" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "title" varchar(255) COLLATE "pg_catalog"."default",
  "type" varchar(10) COLLATE "pg_catalog"."default",
  "content" text COLLATE "pg_catalog"."default",
  "applicant" varchar(255) COLLATE "pg_catalog"."default",
  "status" varchar(20) COLLATE "pg_catalog"."default",
  "create_time" varchar(50) COLLATE "pg_catalog"."default",
  "update_time" varchar(50) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."yj_advice"."title" IS '标题';
COMMENT ON COLUMN "public"."yj_advice"."type" IS '类型';
COMMENT ON COLUMN "public"."yj_advice"."content" IS '意见内容';
COMMENT ON COLUMN "public"."yj_advice"."applicant" IS '申请人';
COMMENT ON COLUMN "public"."yj_advice"."status" IS '状态';
COMMENT ON COLUMN "public"."yj_advice"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."yj_advice"."update_time" IS '更新时间';

-- ----------------------------
-- Records of yj_advice
-- ----------------------------
INSERT INTO "public"."yj_advice" VALUES ('fa91ae681b8a4153a3ca7cee2da6a073', 'test', '系统bug', '111', '超级管理员', '未解决', '2022-01-07 14:51:51.0', '2022-01-07 14:51:51.0');
INSERT INTO "public"."yj_advice" VALUES ('d5806338df89491995c046bf33aa82c7', '231', '数据问题', '23', '超级管理员', '未解决', '2022-01-07 14:52:04.0', '2022-01-07 14:52:04.0');
INSERT INTO "public"."yj_advice" VALUES ('64eedecea44449bf84db00b344e97c33', '111', '系统bug', '23', '超级管理员', '未解决', '2022-01-07 14:53:25.0', '2022-01-07 14:53:25.0');
INSERT INTO "public"."yj_advice" VALUES ('64312d170032496992f1ffba406c2296', '1222', '数据问题', '1', '超级管理员', '未解决', '2022-01-07 15:16:19.0', '2022-01-07 15:16:19.0');
INSERT INTO "public"."yj_advice" VALUES ('4cd5ab5b97b24c4083233df5c600fd73', '曹猛测试', '数据问题', '222', '超级管理员', '未解决', '2022-01-07 15:18:12.0', '2022-01-07 15:18:12.0');
INSERT INTO "public"."yj_advice" VALUES ('150dd7a0487d453ca0435bafe4426c12', '测试改22223', '系统bug改', '测试修改', '超级管理员', '未解决', '2021-12-31 09:56:07.0', '2022-01-07 15:21:01.0');

CREATE TABLE "public"."yj_advice_attach" (
  "uuid" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "ad_uuid" varchar(255) COLLATE "pg_catalog"."default",
  "path" text COLLATE "pg_catalog"."default",
  "create_time" varchar(50) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."yj_advice_attach"."ad_uuid" IS '反馈意见id';
COMMENT ON COLUMN "public"."yj_advice_attach"."path" IS '路径';
COMMENT ON COLUMN "public"."yj_advice_attach"."create_time" IS '创建时间';

-- ----------------------------
-- Records of yj_advice_attach
-- ----------------------------
INSERT INTO "public"."yj_advice_attach" VALUES ('07c3fa6df5784e32a7fcdccd42fc38a7', '150dd7a0487d453ca0435bafe4426c12', 'adviceFile/150dd7a0487d453ca0435bafe4426c12/07c3fa6df5784e32a7fcdccd42fc38a7/震惊.jpg', '2021-12-31 10:08:05.0');
INSERT INTO "public"."yj_advice_attach" VALUES ('fe23ee821a3a411aa6653505caeb23c1', '150dd7a0487d453ca0435bafe4426c12', 'adviceFile/150dd7a0487d453ca0435bafe4426c12/fe23ee821a3a411aa6653505caeb23c1/震惊①.jpg', '2021-12-31 10:08:05.0');
INSERT INTO "public"."yj_advice_attach" VALUES ('171c37181da84333bed8367d9387c0f9', '64312d170032496992f1ffba406c2296', 'adviceFile/64312d170032496992f1ffba406c2296/171c37181da84333bed8367d9387c0f9/污水 (1).png', '2022-01-07 15:16:20.0');
INSERT INTO "public"."yj_advice_attach" VALUES ('4855adff9bec4104b243535c87732368', '4cd5ab5b97b24c4083233df5c600fd73', 'adviceFile/4cd5ab5b97b24c4083233df5c600fd73/4855adff9bec4104b243535c87732368/3.jpg', '2022-01-07 15:18:12.0');

-- ----------------------------
-- Primary Key structure for table yj_advice
-- ----------------------------
ALTER TABLE "public"."yj_advice" ADD CONSTRAINT "feedback_pkey" PRIMARY KEY ("uuid");

-- ----------------------------
-- Primary Key structure for table yj_advice_attach
-- ----------------------------
ALTER TABLE "public"."yj_advice_attach" ADD CONSTRAINT "feedback_attach_pkey" PRIMARY KEY ("uuid");

一对多

实体类

/**
@Author ekkcole
@Date 2022/1/12 9:28
@Detail 
*/
@Data
public class YjAdvice {
    private String uuid;
    private String title;
    private String type;
    private String content;
    private String applicant;
    private String status;
    private String createTime;
    private String updateTime;
    // 意见附件集合
    @TableField(select = false)
    private List<YjAdviceAttach> attachList;
}

mapper

/**
 * @Author ekkcole
 * @Date 2022/1/12 9:28
 * @Detail
 */
@Mapper
public interface YjAdviceMapper {
    List<YjAdvice> getYjByUUID(@Param("uuid") String uuid);
}

mapper.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">
<mapper namespace="com.ekkcole.testjianzhu.mapper.YjAdviceMapper">
  <!--id是自己起的一个别名，type为要映射的实体类-->
  <resultMap id="yjAttach" type="com.ekkcole.testjianzhu.entity.YjAdvice">
    <!--<id></id>标签是主键的映射，<result></result>是非主键的字段映射-->
    <id column="uuid" jdbcType="VARCHAR" property="uuid" />
    <result column="title" jdbcType="VARCHAR" property="title" />
    <result column="type" jdbcType="VARCHAR" property="type" />
    <result column="content" jdbcType="LONGVARCHAR" property="content" />
    <result column="applicant" jdbcType="VARCHAR" property="applicant" />
    <result column="status" jdbcType="VARCHAR" property="status" />
    <result column="create_time" jdbcType="VARCHAR" property="createTime" />
    <result column="update_time" jdbcType="VARCHAR" property="updateTime" />
    <!-- collection 是用于建立一对多中集合属性的对应关系    ofType 用于指定集合元素的数据类型   column 用于查询的关联字段-->
    <!--这里有个重点，column只能有一个id,不然查询数据时会出现只查询到一条数据的情况-->
    <collection property="attachList" ofType="com.ekkcole.testjianzhu.entity.YjAdviceAttach" column="ad_uuid">
       <!--记住一点，<collection>标签里的column并不是与数据库表字段对应，而且自己起的一个别名，
           将数据库查询到的数据映射或者说是赋值给这个别名，再油这个别名映射到property对应的实体类属性上-->
      <id column="attuuid" jdbcType="VARCHAR" property="uuid" /><!--column = "attuuid"是自己起的一个别名，防止有多个id-->
      <result column="ad_uuid" jdbcType="VARCHAR" property="adUuid" />
      <result column="path" jdbcType="LONGVARCHAR" property="path" />
    </collection>
  </resultMap>

  <select id="getYjByUUID" resultMap="yjAttach">
    select t1.*,t2.uuid attuuid,t2.path,t2.ad_uuid from yj_advice t1 left join yj_advice_attach t2 on t1.uuid=t2.ad_uuid
    <where>
      <if test="uuid != null and uuid != ''">
        and t2.ad_uuid=#{uuid}
      </if>
    </where>
  </select>

</mapper>

测试结果

/**
 * @Author ekkcole
 * @Date 2022/1/12 9:39
 * @Detail
 */
@RestController
@RequestMapping("onetomany")
public class OneToManyController {
 	@Autowired
    private YjAdviceMapper yjAdviceMapper;
    /**
     * 一对多
     * @param uuid 主表uuid
     * @return
     */
    @GetMapping
    public List<YjAdvice> getYj(String uuid){
        List<YjAdvice> yjByUUID = yjAdviceMapper.getYjByUUID(uuid);
        return yjByUUID;
    }
}

在这里插入图片描述

多对一

实体类

/**
@Author ekkcole
@Date 2022/1/12 9:28
@Detail 
*/
@Data
public class YjAdviceAttach {
    private String uuid;
    private String adUuid;
    private String path;
    /**
     * 意见表
     */
    @TableField(select = false)
    private YjAdvice yjAdvice;
}

mapper

/**
@Author ekkcole
@Date 2022/1/12 9:28
@Detail 
*/
@Mapper
public interface YjAdviceAttachMapper {

    List<YjAdviceAttach> listYjAttach(String uuid);
}

mapper.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">
<mapper namespace="com.ekkcole.testjianzhu.mapper.YjAdviceAttachMapper">

 <!--id是自己起的一个别名，type为要映射的实体类-->
  <resultMap id="yjAdviceByAttach" type="com.ekkcole.testjianzhu.entity.YjAdviceAttach">
    <id column="uuid" jdbcType="VARCHAR" property="uuid" />
    <result column="ad_uuid" jdbcType="VARCHAR" property="adUuid" />
    <result column="path" jdbcType="LONGVARCHAR" property="path" />
    <!-- association 是用于建立多对一中集合属性的对应关系    ofType 用于指定集合元素的数据类型-->
    <!--这里有个重点，column只能有一个id,不然查询数据时会出现只查询到一条数据的情况-->
    <association property="yjAdvice" javaType="com.ekkcole.testjianzhu.entity.YjAdvice">
      <!--记住一点，<collection>标签里的column并不是与数据库表字段对应，而且自己起的一个别名，
           将数据库查询到的数据映射或者说是赋值给这个别名，再油这个别名映射到property对应的实体类属性上-->
        <id column="yjuuid" jdbcType="VARCHAR" property="uuid" /><!--column = "yjuuid"是自己起的一个别名，防止有多个id-->
        <result column="title" jdbcType="VARCHAR" property="title" />
        <result column="type" jdbcType="VARCHAR" property="type" />
        <result column="content" jdbcType="LONGVARCHAR" property="content" />
        <result column="applicant" jdbcType="VARCHAR" property="applicant" />
        <result column="status" jdbcType="VARCHAR" property="status" />
    </association>
  </resultMap>
  <select id="listYjAttach" resultMap="yjAdviceByAttach">
    SELECT t1.*,t2.uuid yjuuid,t2.title,t2."type",t2.status,t2."content",t2.applicant FROM "yj_advice_attach" t1,yj_advice t2
    <where>
      and t1.ad_uuid=t2.uuid
      <if test="_parameter != null and _parameter != ''">
        and t1.uuid=#{uuid}
      </if>
    </where>
  </select>

</mapper>

测试结果

/**
 * @Author ekkcole
 * @Date 2022/1/12 9:39
 * @Detail
 */
@RestController
@RequestMapping("onetomany")
public class OneToManyController {

    @Autowired
    private YjAdviceMapper yjAdviceMapper;
    @Autowired
    private YjAdviceAttachMapper yjAdviceAttachMapper;

    /**
     * 一对多
     * @param uuid 主表uuid
     * @return
     */
    @GetMapping
    public List<YjAdvice> getYj(String uuid){
        List<YjAdvice> yjByUUID = yjAdviceMapper.getYjByUUID(uuid);
        return yjByUUID;
    }

    /**
     * 多对一
     * @param uuid 主表uuid
     * @return
     */
    @GetMapping("getYjAttach")
    public List<YjAdviceAttach> getYjAttach(String uuid){
        List<YjAdviceAttach> yjAdviceAttaches = yjAdviceAttachMapper.listYjAttach(uuid);
        return yjAdviceAttaches;
    }
}

在这里插入图片描述