签到系统设计

[TFdream]

业务场景

签到活动对于提高APP用户粘性是非常有帮助的。相信大家手机里面都是有京东、淘宝等等APP，大部分的APP都是有一个签到功能的，用户通过签到的形式，得到相对应的奖励，能够提高APP用户活跃度。

1.表结构设计

1.用户表

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `nickname` varchar(45) NOT NULL COMMENT '用户昵称',
  `name` varchar(45) NOT NULL DEFAULT '' COMMENT '用户真实姓名',
  `gender` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '性别, 0:男, 1:女, 2:未知',
  `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '密码',
  `version` int(10) unsigned NOT NULL DEFAULT 1 COMMENT '版本号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

2.用户签到表：

CREATE TABLE `user_sign_in` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
  `nickname` varchar(45) NOT NULL COMMENT '用户昵称',
  `last_sign_in_date` int(10) unsigned NOT NULL COMMENT '最近一次签到日期，格式: yyyyMMdd',
  `continuous_sign_in_days` int(10) unsigned NOT NULL COMMENT '连续签到天数',
  `version` int(10) unsigned NOT NULL DEFAULT 1 COMMENT '版本号',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户签到表';

3.用户签到log表：

CREATE TABLE `user_sign_in_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
  `sign_in_date` int(10) unsigned NOT NULL COMMENT '签到日期，格式: yyyyMMdd',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_id_sign_date` (`user_id`, `sign_in_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户签到log表';

核心逻辑

SignInService.java

package io.mindflow.architect.service;

import io.mindflow.architect.mapper.demo.UserMapper;
import io.mindflow.architect.model.demo.UserDO;
import io.mindflow.architect.model.demo.UserSignInDO;
import io.mindflow.architect.model.demo.UserSignInLogDO;
import io.mindflow.architect.util.DateUtils;
import io.mindflow.architect.util.Ints;
import io.mindflow.architect.web.vo.ApiResult;
import io.mindflow.architect.web.vo.UserSignInResultVO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.Date;

/**
 * @author Ricky Fung
 */
@Service
public class SignInService {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private UserMapper userMapper;

    @Resource(name = "signInTxService")
    private SignInTxService signInTxService;

    public ApiResult<UserSignInResultVO> doSignIn(Long userId, Date now) {
        UserDO userDO = userMapper.selectUserById(userId);
        if (userDO==null) {
            return ApiResult.buildFailureResult(1000, "用户不存在");
        }

        Integer signInDate = Integer.parseInt(DateUtils.format(now, DateUtils.DATE_COMPACT_FORMAT));
        try {
            UserSignInDO userSignInDO = signInTxService.queryUserSignInRecord(userId);
            Integer continuousSignInDays;
            if (userSignInDO==null) {
                //第一次签到
                UserSignInDO signInRecord = signInTxService.createUserSignInRecord(userDO, signInDate, now);
                UserSignInLogDO signInLogRecord = signInTxService.createUserSignInLogRecord(userId, signInDate, now);

                //保存
                signInTxService.saveSignIn(userId, signInRecord, signInLogRecord);
                continuousSignInDays = Ints.ONE;
                logger.info("用户签到服务-签到, userId:{}, signIdDate:{} 第一次签到成功", userId, signInDate);
            } else {
                if (userSignInDO.getLastSignInDate().intValue() == signInDate) {
                    return ApiResult.buildFailureResult(1001, "今日已签到");
                }

                boolean continuousSignIn = true;
                //计算时间差
                Date lastSignDate = DateUtils.parseDate(userSignInDO.getLastSignInDate().toString(), DateUtils.DATE_COMPACT_FORMAT);
                if (Math.abs(DateUtils.daysBetween(lastSignDate, now)) > Ints.ONE) {
                    continuousSignIn = false;
                }
                UserSignInDO signInRecord = signInTxService.createUserSignInRecordV1(userSignInDO, continuousSignIn, signInDate, now);
                UserSignInLogDO signInLogRecord = signInTxService.createUserSignInLogRecord(userId, signInDate, now);
                //保存
                signInTxService.saveSignIn(userId, signInRecord, signInLogRecord);
                continuousSignInDays = signInRecord.getContinuousSignInDays();
                logger.info("用户签到服务-签到, userId:{}, signIdDate:{} continuousSignIn:{}, continuousSignInDays:{} 签到成功",
                        userId, signInDate, continuousSignIn, continuousSignInDays);
            }
            UserSignInResultVO resultVO = new UserSignInResultVO();
            resultVO.setContinuousSignInDays(continuousSignInDays);
            return ApiResult.buildSuccessResult(resultVO);
        } catch (Exception e) {
            logger.error("用户签到服务-签到接口异常, userId:{}, signInDate:{}", userId, signInDate, e);
        }
        return ApiResult.buildSystemErrorResult();
    }
}

SignInTxService.java

package io.mindflow.architect.service;

import io.mindflow.architect.mapper.demo.UserSignInLogMapper;
import io.mindflow.architect.mapper.demo.UserSignInMapper;
import io.mindflow.architect.model.demo.UserDO;
import io.mindflow.architect.model.demo.UserSignInDO;
import io.mindflow.architect.model.demo.UserSignInLogDO;
import io.mindflow.architect.util.Ints;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;

/**
 * @author Ricky Fung
 */
@Component
public class SignInTxService {
    @Autowired
    private UserSignInMapper signInMapper;

    @Autowired
    private UserSignInLogMapper signInLogMapper;

    @Transactional
    public int saveSignIn(Long userId, UserSignInDO signInRecord, UserSignInLogDO signInLogRecord) {
        if (signInRecord.getId()==null) {
            signInMapper.insert(signInRecord);
        } else {
            signInMapper.updateByPrimaryKeySelective(signInRecord);
        }

        return signInLogMapper.insert(signInLogRecord);
    }

    //-------
    public UserSignInDO createUserSignInRecordV1(UserSignInDO userSignInDO, boolean continuousSignIn, Integer signInDate, Date now) {
        UserSignInDO signInDO = new UserSignInDO();
        //主键id
        signInDO.setId(userSignInDO.getId());

        signInDO.setLastSignInDate(signInDate);
        if (continuousSignIn) {
            //连续签到
            signInDO.setContinuousSignInDays(userSignInDO.getContinuousSignInDays() + Ints.ONE);
        } else {
            signInDO.setContinuousSignInDays(Ints.ONE);
        }

        signInDO.setVersion(userSignInDO.getVersion());
        signInDO.setUpdateTime(now);
        return signInDO;
    }

    public UserSignInDO createUserSignInRecord(UserDO userDO, Integer signIdDate, Date now) {
        UserSignInDO signInDO = new UserSignInDO();
        signInDO.setUserId(userDO.getId());
        signInDO.setNickname(userDO.getNickname());
        signInDO.setLastSignInDate(signIdDate);
        signInDO.setContinuousSignInDays(Ints.ONE);
        signInDO.setVersion(Ints.ONE);
        signInDO.setCreateTime(now);
        signInDO.setUpdateTime(now);
        return signInDO;
    }

    public UserSignInLogDO createUserSignInLogRecord(Long userId, Integer signIdDate, Date now) {
        UserSignInLogDO signInLogDO = new UserSignInLogDO();
        signInLogDO.setUserId(userId);
        signInLogDO.setSignInDate(signIdDate);
        signInLogDO.setCreateTime(now);
        signInLogDO.setUpdateTime(now);
        return signInLogDO;
    }

    //-------
    public UserSignInDO queryUserSignInRecord(Long userId) {
        return signInMapper.selectByUserId(userId);
    }

    public UserSignInLogDO queryUserSignInRecord(Long userId, Integer signIdDate) {
        return signInLogMapper.selectByUserIdAndDate(userId, signIdDate);
    }

}

UserSignInMapper.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="io.mindflow.architect.mapper.demo.UserSignInMapper">
    <resultMap id="BaseResultMap" type="io.mindflow.architect.model.demo.UserSignInDO">
        <id column="id" jdbcType="BIGINT" property="id" />
        <id column="user_id" jdbcType="BIGINT" property="userId" />
        <result column="nickname" jdbcType="VARCHAR" property="nickname" />
        <result column="last_sign_in_date" jdbcType="INTEGER" property="lastSignInDate" />
        <result column="continuous_sign_in_days" jdbcType="INTEGER" property="continuousSignInDays" />
        <result column="version" jdbcType="INTEGER" property="version" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    </resultMap>

    <sql id="Base_Column_List">
        id, user_id, nickname, last_sign_in_date, continuous_sign_in_days, version, create_time, update_time
    </sql>

    <select id="selectByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM `user_sign_in`
        WHERE  user_id = #{userId}
    </select>

    <insert id="insert" parameterType="io.mindflow.architect.model.demo.UserSignInDO" useGeneratedKeys="true" keyProperty="id">
        insert into `user_sign_in` (
        user_id,
        nickname,
        last_sign_in_date,
        continuous_sign_in_days,
        version,
        create_time,
        update_time)
        values (
        #{userId},
        #{nickname},
        #{lastSignInDate},
        #{continuousSignInDays},
        #{version},
        #{createTime},
        #{updateTime})
    </insert>

    <update id="updateByPrimaryKeySelective" parameterType="io.mindflow.architect.model.demo.UserSignInDO">
        update `user_sign_in`
        <set>
            <if test="userId != null">
                user_id = #{userId},
            </if>
            <if test="nickname != null">
                nickname = #{nickname},
            </if>
            <if test="lastSignInDate != null">
                last_sign_in_date = #{lastSignInDate},
            </if>
            <if test="continuousSignInDays != null">
                continuous_sign_in_days = #{continuousSignInDays},
            </if>
            version = version + 1,
            update_time = now()
        </set>
        where id = #{id}
        AND version = #{version}
    </update>

</mapper>