CCNAIVE

20240114

2024/1/14

# 1 Mybatis 一对多查询

# Xml

    <resultMap type="com.dataojo.domain.vo.EnterpriseActivityVo" id="enterpriseActivityVo">
        <result property="id" column="id"/>
        <result property="activityName" column="activity_name"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="numberPeople" column="number_people"/>
        <result property="activityImg" column="activity_img"/>
        <result property="activitySlogan" column="activity_slogan"/>
        <result property="activityRule" column="activity_rule"/>
        <result property="status" column="status"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="tenantCode" column="tenant_code"/>
        <collection property="bannerList" ofType="com.dataojo.domain.dto.EnterpriseActivityBannerDTO">
            <result column="banner_id" property="bannerId"/>
            <result column="banner_img" property="bannerImg"/>
        </collection>
        <collection property="giftsList" ofType="com.dataojo.domain.dto.EnterpriseGiftsDTO">
            <result column="gift_id" property="giftId"/>
            <result column="gift_url" property="giftUrl"/>
        </collection>
    </resultMap>

# dto

@Data
public class EnterpriseActivityBannerDTO implements Serializable {
	private static final long serialVersionUID = 1L;

	/**
	 * 活动banner id
	 */
	@TableId
	private Long bannerId;

	/**
	 * banner url
	 */
	private String bannerImg;


}

# Vo

@Data
public class EnterpriseActivityVo extends EnterpriseActivityEntity implements Serializable {

    @TableField(exist = false)
    List<EnterpriseActivityBannerDTO> bannerList;

    @TableField(exist = false)
    List<EnterpriseGiftsEntity> giftsList;
}

# Entity


/**
 * 企业活动表
 *
 * @author Lichengcan
 * @email ccnaive@qq.com
 * @date 2024-01-17 14:36:13
 */
@Data
@TableName("lb_enterprise_activity")
public class EnterpriseActivityEntity  implements Serializable {
	private static final long serialVersionUID = 1L;

	/**
	 * 企业活动id
	 */
	@TableId
	private Long id;
	/**
	 * 活动名称
	 */
	private String activityName;
	/**
	 * 活动开始时间
	 */
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
	private Date startTime;
	/**
	 * 活动结束时间
	 */
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
	private Date endTime;
	/**
	 * 参与人数
	 */
	private Integer numberPeople;
	/**
	 * 活动图片
	 */
	private String activityImg;
	/**
	 * 活动标语
	 */
	private String activitySlogan;
	/**
	 * 活动规则
	 */
	private String activityRule;
	/**
	 * 活动状态（0已上线 1已下线）
	 */
	private Integer status;
	/**
	 * 创建者
	 */
	private String createBy;
	/**
	 * 创建时间
	 */
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
	private Date createTime;
	/**
	 * 更新者
	 */
	private String updateBy;
	/**
	 * 更新时间
	 */
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
	private Date updateTime;
	/**
	 * 运营租户编码
	 */
	private Long tenantCode;

}

# Mapper

@Mapper
public interface EnterpriseActivityMapper extends BaseMapper<EnterpriseActivityEntity> {

    IPage<EnterpriseActivityVo> pageData(@Param("page") Page<EnterpriseActivityReq> page, @Param("req") EnterpriseActivityReq req);
}

# SQL

 <select id="pageData" resultMap="enterpriseActivityVo">
        SELECT
        ea.id,
        ea.activity_name,
        ea.start_time,
        ea.end_time,
        ea.number_people,
        ea.activity_img,
        ea.activity_slogan,
        ea.activity_rule,
        ea.`status`,
        ea.create_by,
        ea.create_time,
        ea.update_by,
        ea.update_time,
        ea.tenant_code,
        eab.banner_id,
        eab.banner_img,
        gifts.gift_id,
        gifts.gift_url
        FROM lb_enterprise_activity ea
        LEFT JOIN lb_enterprise_activity_banner eab ON ea.id = eab.activity_id
        LEFT JOIN lb_enterprise_gifts gifts ON ea.id = gifts.activity_id
        <where>
            <if test="req.activityName != null and req.activityName != ''">
                AND ea.activity_name LIKE CONCAT('%', #{req.activityName} , '%')
            </if>
            <if test="req.startTime != null and req.endTime !=null">
                AND ea.start_time <![CDATA[>= ]]> #{req.startTime}
                AND ea.end_time <![CDATA[<= ]]> #{req.endTime}
            </if>
            <if test="req.status != null">
                AND ea.status = #{req.status}
            </if>
            <if test="req.tenantCode != null">
                AND ea.tenant_code =#{req.tenantCode}
            </if>
        </where>
        ORDER BY ea.create_time DESC
    </select>

20240115