第 5 章 广告投放系统的开发

5-1 广告投放系统数据表设计

譬如案例：

用户账户表

推广计划表

推广单元表

创意表

建表语句

-- ad 数据库
drop DATABASE ad;
CREATE DATABASE ad character set utf8;

use ad;

-- 用户表
CREATE TABLE `ad_user`
(
    `id`          bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `username`    varchar(128) NOT NULL DEFAULT '' COMMENT '用户名',
    `token`       varchar(256) NOT NULL DEFAULT '' COMMENT '给用户生成的 token',
    `user_status` tinyint(4)   NOT NULL DEFAULT '0' COMMENT '用户状态',
    `create_time` datetime     NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '创建时间',
    `update_time` datetime     NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = utf8 COMMENT ='用户信息表';


-- 推广计划表
CREATE TABLE `ad_plan`
(
    `id`          bigint(20)  NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `user_id`     bigint(20)  NOT NULL DEFAULT '0' COMMENT '标记当前记录所属用户',
    `plan_name`   varchar(48) NOT NULL COMMENT '推广计划名称',
    `plan_status` tinyint(4)  NOT NULL DEFAULT '0' COMMENT '推广计划状态',
    `start_date`  datetime    NOT NULL COMMENT '推广计划开始时间；',
    `end_date`    datetime    NOT NULL COMMENT '推广计划结束时间；',
    `create_time` datetime    NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '创建时间',
    `update_time` datetime    NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = utf8 COMMENT ='推广计划表';


-- 推广单元表
CREATE TABLE `ad_unit`
(
    `id`            bigint(20)  NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `plan_id`       bigint(20)  NOT NULL DEFAULT '0' COMMENT '关联推广计划 id',
    `unit_name`     varchar(48) NOT NULL COMMENT '推广单元名称',
    `unit_status`   tinyint(4)  NOT NULL DEFAULT '0' COMMENT '推广单元状态',
    `position_type` tinyint(4)  NOT NULL DEFAULT '0' COMMENT '广告位类型(开屏, 贴片, 中贴, 暂停帖, 后贴)',
    `budget`        bigint(20)  NOT NULL COMMENT '预算',
    `create_time`   datetime    NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '创建时间',
    `update_time`   datetime    NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = utf8 COMMENT ='推广单元表';


-- 创意表
CREATE TABLE `ad_creative`
(
    `id`            bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `name`          varchar(48)  NOT NULL COMMENT '创意名称',
    `type`          tinyint(4)   NOT NULL DEFAULT '0' COMMENT '物料类型(图片, 视频)',
    `material_type` tinyint(4)   NOT NULL DEFAULT '0' COMMENT '物料子类型(图片: bmp, jpg 等等)',
    `height`        int(10)      NOT NULL DEFAULT '0' COMMENT '高度',
    `width`         int(10)      NOT NULL DEFAULT '0' COMMENT '宽度',
    `size`          bigint(20)   NOT NULL DEFAULT '0' COMMENT '物料大小, 单位是 KB',
    `duration`      int(10)      NOT NULL DEFAULT '0' COMMENT '持续时长, 只有视频才不为 0',
    `audit_status`  tinyint(4)   NOT NULL DEFAULT '0' COMMENT '审核状态',
    `user_id`       bigint(20)   NOT NULL DEFAULT '0' COMMENT '标记当前记录所属用户',
    `url`           varchar(256) NOT NULL COMMENT '物料地址',
    `create_time`   datetime     NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '创建时间',
    `update_time`   datetime     NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = utf8 COMMENT ='创意表';


-- 创意与推广单元的关联表
CREATE TABLE `creative_unit`
(
    `id`          bigint(20) NOT NULL AUTO_INCREMENT,
    `creative_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '创意 id',
    `unit_id`     bigint(20) NOT NULL DEFAULT '0' COMMENT '推广单元 id',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = utf8 COMMENT ='创意和推广单元关联表';


-- 推广单元关键词 Feature
CREATE TABLE `ad_unit_keyword`
(
    `id`      int(11)     NOT NULL AUTO_INCREMENT,
    `unit_id` int(11)     NOT NULL COMMENT '推广单元 id',
    `keyword` varchar(30) NOT NULL COMMENT '关键词',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = utf8 COMMENT ='推广单元关键词 Feature';


-- 推广单元兴趣 Feature
CREATE TABLE `ad_unit_it`
(
    `id`      int(11)     NOT NULL AUTO_INCREMENT,
    `unit_id` int(11)     NOT NULL COMMENT '推广单元 id',
    `it_tag`  varchar(30) NOT NULL COMMENT '兴趣标签',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = utf8 COMMENT ='推广单元兴趣 Feature';


-- 推广单元地域 Feature
CREATE TABLE `ad_unit_district`
(
    `id`       int(11)     NOT NULL AUTO_INCREMENT,
    `unit_id`  int(11)     NOT NULL COMMENT '推广单元 id',
    `province` varchar(30) NOT NULL COMMENT '省',
    `city`     varchar(30) NOT NULL COMMENT '市',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = utf8 COMMENT ='推广单元地域 Feature';

5-2 Model 层设计

AdUser

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
@Table(name = "ad_user")
public class AdUser {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "username", nullable = false)
    private String username;

    @Column(name = "token", nullable = false)
    private String token;

    @Column(name = "user_status", nullable = false)
    private Integer userStatus;

    @Column(name = "create_time", nullable = false)
    private Date createTime;

    @Column(name = "update_time", nullable = false)
    private Date updateTime;

    public AdUser(String username, String token) {
        this.username = username;
        this.token = token;
        this.userStatus = CommonStatus.VALID.getStatus();
        this.createTime = new Date();
        this.updateTime = createTime;
    }
}

AdPlan

/**
 * 推广计划
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
@Table(name = "ad_plan")
public class AdPlan {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "user_id", nullable = false)
    private Long userId;

    @Column(name = "plan_name", nullable = false)
    private String planName;

    @Column(name = "plan_status", nullable = false)
    private Integer planStatus;

    @Column(name = "start_date", nullable = false)
    private Date startDate;

    @Column(name = "end_date", nullable = false)
    private Date endDate;

    @Column(name = "create_time", nullable = false)
    private Date createTime;

    @Column(name = "update_time", nullable = false)
    private Date updateTime;

    public AdPlan(Long userId, String planName, Date startDate, Date endDate) {
        this.userId = userId;
        this.planName = planName;
        this.planStatus = CommonStatus.VALID.getStatus();
        this.startDate = startDate;
        this.endDate = endDate;
        this.createTime = new Date();
        this.updateTime = createTime;
    }
}

AdUnit

/**
 * 推广单元
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
@Entity
@Table(name = "ad_unit")
public class AdUnit {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "plan_id", nullable = false)
    private Long planId;

    @Column(name = "unit_name", nullable = false)
    private String unitName;

    @Column(name = "unit_status", nullable = false)
    private Integer unitStatus;

    /**
     * 广告位类型：开屏广告，贴片广告，中贴广告...
     */
    @Column(name = "position_type", nullable = false)
    private Integer positionType;

    @Column(name = "budget", nullable = false)
    private Long budget;

    @Column(name = "create_time", nullable = false)
    private Date createTime;

    @Column(name = "update_time", nullable = false)
    private Date updateTime;

    public AdUnit(Long planId, String unitName, Integer positionType, Long budget) {
        this.planId = planId;
        this.unitName = unitName;
        this.unitStatus = CommonStatus.VALID.getStatus();
        this.positionType = positionType;
        this.budget = budget;
        this.createTime = new Date();
        this.updateTime = createTime;
    }
}

Creative

/**
 * 创意，对应到创意表
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
@Table(name = "ad_creative")
public class Creative {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    /**
     * 创意类型:
     * 图片，视频...
     */
    @Column(name = "type", nullable = false)
    private Integer type;

    /**
     * 创意的物料类型，属于创意类型的子类型:
     * 图片：
     * - jpg
     * - png
     * 视频：
     * - .avi
     * - .mp4
     */
    @Column(name = "material_type", nullable = false)
    private Integer materialType;

    /**
     * 物料的高度
     */
    @Column(name = "height", nullable = false)
    private Integer height;

    /**
     * 物料的宽度
     */
    @Column(name = "width", nullable = false)
    private Integer width;

    /**
     * 物料的大小
     */
    @Column(name = "size", nullable = false)
    private Long size;

    /**
     * 物料持续的时长，只有当物料为视频时，才不会为 0
     */
    @Column(name = "duration", nullable = false)
    private Integer duration;

    /**
     * 审核状态
     */
    @Column(name = "audit_status", nullable = false)
    private Integer auditStatus;

    /**
     * 物料所属用户
     */
    @Column(name = "user_id", nullable = false)
    private Long userId;

    /**
     * 物料所在的地址
     */
    @Column(name = "url", nullable = false)
    private String url;

    @Column(name = "create_time", nullable = false)
    private Date createTime;

    @Column(name = "update_time", nullable = false)
    private Date updateTime;

}

AdUnitDistrict

/**
 * 推广单元：
 * <p>
 * 地域限制
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
@Table(name = "ad_unit_district")
public class AdUnitDistrict {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "unit_id", nullable = false)
    private Long unitId;

    @Column(name = "province", nullable = false)
    private String province;

    @Column(name = "city", nullable = false)
    private String city;

    public AdUnitDistrict(Long unitId, String province, String city) {
        this.unitId = unitId;
        this.province = province;
        this.city = city;
    }
}

AdUnitIt

/**
 * 推广单元：
 * <p>
 * 兴趣限制
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
@Table(name = "ad_unit_it")
public class AdUnitIt {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "unit_id", nullable = false)
    private Long unitId;

    /**
     * 兴趣标签
     */
    @Column(name = "it_tag", nullable = false)
    private String itTag;

    public AdUnitIt(Long unitId, String itTag) {
        this.unitId = unitId;
        this.itTag = itTag;
    }
}

AdUnitKeyword

/**
 * 推广单元：
 * <p>
 * 关键词限制
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
@Table(name = "ad_unit_keyword")
public class AdUnitKeyword {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "unit_id", nullable = false)
    private Long unitId;

    @Column(name = "keyword", nullable = false)
    private String keyword;

    public AdUnitKeyword(Long unitId, String keyword) {
        this.unitId = unitId;
        this.keyword = keyword;
    }
}

CreativeUnit

/**
 * 创意与推广单元关联表
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
@Table(name = "creative_unit")
public class CreativeUnit {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "creative_id", nullable = false)
    private Long creativeId;

    @Column(name = "unit_id", nullable = false)
    private Long unitId;

    public CreativeUnit(Long creativeId, Long unitId) {
        this.creativeId = creativeId;
        this.unitId = unitId;
    }
}

5-3 Dao 接口的定义

AdUserRepository

/**
 * 完成对 AdUser 的增删改查
 */
public interface AdUserRepository extends JpaRepository<AdUser, Long> {
    AdUser findByUsername(String username);
}

AdPlanRepository

/**
 * 完成对 AdPlan 的增删改查
 */
public interface AdPlanRepository extends JpaRepository<AdPlan, Long> {

    AdPlan findByIdAndUserId(Long id, Long userId);

    List<AdPlan> findAllByIdInAndUserId(List<Long> ids, Long userId);

    AdPlan findByUserIdAndPlanName(Long userId, String planName);

    List<AdPlan> findAllByPlanStatus(Integer status);
}

AdUnitRepository

/**
 * 完成对 AdUnit 的增删改查
 */
public interface AdUnitRepository extends JpaRepository<AdUnit, Long> {
    AdUnit findByPlanIdAndUnitName(Long planId, String unitName);

    List<AdUnit> findAllByUnitStatus(Integer unitStatus);
}

CreativeRepository

/**
 * 完成对 Creative 的增删改查
 */
public interface CreativeRepository extends JpaRepository<Creative, Long> {
}

后面的接口省略… …

5-4 广告投放系统在网关中的配置

# 广告投放系统在网关中的配置
zuul:
  routes:
    sponsor:
      path: /ad-sponsor/**
      serviceId: eureka-client-ad-sponsor
      strip-prefix: false