Skip to content

签到系统设计 #32

Open
Open
签到系统设计#32

Description

@TFdream
TFdream
opened on Nov 1, 2018

业务场景

签到活动对于提高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>

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions