本帖转自：http://www.cnblogs.com/snidget/archive/2013/01/22/2871590.html
1，最近网站要上一个签到的功能，一个多游戏的平台，每种游戏的官网都有签到功能，设计打算把数据放到平台。
2，首先要设计签到表，这里直接给出过了一遍dba，需求人员，设计人员脑子的结果：
4，功能抽取
非常明显，只有三个主要的功能，第一个签到之前的登录；第二个，签到；第三个，领取礼包；
| 功能 | 功能概述 | 功能的具体逻辑 | 接口方法设计 |
| 登录之前的查询 | 通过查询，以前签过到的，显示签到历史；没有签过到的，神马也不显示； | 查询dt_sign表，通过gid和uid查询，如果查询到，返回签到信息，如果没有查到，返回提示信息 | SignMsg loginQuery(int gid,int uid) |
| 签到 | 点击签到，如果当天已经签过到了，提示已经签过到了；如果从来没有签过到，插入数据，把积分设置为1，连续签到次数设置为1，最后修改时间设置为当天，历史为1；如果今天没有签过到，首先计算出有多少天没签到了，如果是昨天签了的，连续签到次数加1，历史左移一位，积分按照积分规则加上；如果超过两天没有签到，连续签到次数设置为1，历史左移天数位，积分加上签到单次的积分，时间为当前的修改时间； | 首先查询dt_sign，按照gid和uid，如果没查到，插入记录；查到了，判断最后修改时间，做相应的处理； | SignMsg todaySign(int gid,int uid) |
| 领取礼包 | 点击领取礼包，如果分数够，减去积分，允许该用户领取礼包；如果分数不过，提示积分不够； | 点击领取礼包，如果分数够，减去积分，允许该用户领取礼包；如果分数不过，提示积分不够； | SignMsg getGiftPack(int gid,int uid,int score) |
5，具体实现
jdbc实现：
package com.sz7road.userplatform.dao.jdbc;
import com.google.common.base.Strings;
import com.sz7road.userplatform.dao.SignDao;
import com.sz7road.userplatform.ws.sign.ScoreRuleAndMoveByte;
import com.sz7road.userplatform.ws.sign.Sign;
import com.sz7road.userplatform.ws.sign.SignObject;
import com.sz7road.utils.CommonDateUtils;
import org.apache.commons.dbutils.DbUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
/**
* Created with IntelliJ IDEA.
* User: cutter.li
* Date: 13-1-18
* Time: 上午11:01
*/
public class SignDaoJdbcImp extends JdbcDaoSupport<SignObject> implements SignDao {
private final static Logger log = LoggerFactory.getLogger(SignDaoJdbcImp.class);
private Connection conn = null;
@Override
public Sign querySign(int uid, int gid) {
Sign sign = new Sign();
ResultSet rs = null;
PreparedStatement preparedStatement = null;
try {
conn = getQueryRunner().getDataSource().getConnection();
preparedStatement = conn.prepareStatement(" select * from db_userplatform.dt_sign where userid=? and gameid=? ;");
preparedStatement.setInt(1, uid);
preparedStatement.setInt(2, gid);
rs = preparedStatement.executeQuery();
if (rs.next()) {
sign.setCode(200);
sign.setMsg("成功的查询到签到信息!");
sign.setContinueSignCount(rs.getInt("signCount"));
sign.setTotalScore(rs.getInt("integration"));
sign.setLastModifyDate(new Date(rs.getDate("lastModifyTime").getTime()));
sign.setSignHistory(rs.getLong("signHistory"));
} else {
sign.setCode(300);
sign.setMsg("该用户从来没有签过到！");
}
} catch (SQLException e) {
sign.setCode(404);
sign.setMsg("平台或者db异常！");
e.printStackTrace();
} finally {
DbUtils.closeQuietly(rs);
try {
DbUtils.close(preparedStatement);
} catch (SQLException e) {
e.printStackTrace();
}
DbUtils.closeQuietly(conn);
}
return sign;
}
@Override
public Sign signThenReturn(int uid, int gid) {
Sign sign = new Sign();
ResultSet rs = null;
PreparedStatement preparedStatement = null, executePreparedStatement = null;
try {
conn = getQueryRunner().getDataSource().getConnection();
preparedStatement = conn.prepareStatement(" select * from db_userplatform.dt_sign where userid=? and gameid=? ;");
preparedStatement.setInt(1, uid);
preparedStatement.setInt(2, gid);
rs = preparedStatement.executeQuery();
if (rs.next()) {//查到了更新
SignObject signObject = new SignObject();
signObject.setId(rs.getInt("id"));
signObject.setUid(rs.getInt("userid"));
signObject.setGid(rs.getInt("gameid"));
signObject.setSignCount(rs.getInt("signCount"));
signObject.setIntegration(rs.getInt("integration"));
signObject.setLastModifyTime(new Date(rs.getDate("lastModifyTime").getTime()));
signObject.setSignHistory(rs.getLong("signHistory"));
signObject.setExt(rs.getString("ext"));
Timestamp lastModifyTimeStamp = new Timestamp(signObject.getLastModifyTime().getTime());
Timestamp todayStartTimeStamp = CommonDateUtils.getTodayStartTimeStamp();
if (todayStartTimeStamp.after(lastModifyTimeStamp)) {//今天没有签过到
final long missDays= (System.currentTimeMillis()-signObject.getLastModifyTime().getTime())/(24*60*60*1000);
int newSignCount=signObject.getSignCount();
String newExt="签到";
if(missDays==1)
{ //连续签到，加分，连续签到次数增加1 ，签到历史移动一位
newSignCount+=1;
}else
{//不连续签到，加分，连续签到次数为1，签到历史移动missDays位
newSignCount=1;
}
if(newSignCount>=91)
{ //签到超过90天，连续签到次数重置为1
newSignCount=1;
newExt="连续签到天数重置为1，时间："+CommonDateUtils.getDate(System.currentTimeMillis());
}
final long newSignHistory= ScoreRuleAndMoveByte.moveByte(signObject.getSignHistory(),missDays);
final int newIntegration=signObject.getIntegration()+ScoreRuleAndMoveByte.getScoreByRule(newSignCount);
executePreparedStatement = conn.prepareStatement(" update db_userplatform.dt_sign set signCount=? , integration=? , signHistory=? , lastModifyTime=? , ext=? where id=?; ");
executePreparedStatement.setInt(1, newSignCount);
executePreparedStatement.setInt(2, newIntegration);
executePreparedStatement.setLong(3, newSignHistory);
java.sql.Date signDate= new java.sql.Date(System.currentTimeMillis());
executePreparedStatement.setDate(4,signDate);
executePreparedStatement.setString(5,newExt);
executePreparedStatement.setInt(6,signObject.getId());
int effectRows = executePreparedStatement.executeUpdate();
if (effectRows >= 1) {
sign.setCode(206);
sign.setMsg("签到成功！成功更新数据!");
sign.setContinueSignCount(newSignCount);
sign.setLastModifyDate(signDate);
sign.setTotalScore(newIntegration);
sign.setSignHistory(newSignHistory);
} else {
sign.setCode(208);
sign.setMsg("签到失败，更新数据失败!");
}
}
else
{//今天已经签过到了
sign.setCode(300);
sign.setMsg("该用户今天已经签过到了!");
sign.setLastModifyDate(signObject.getLastModifyTime());
sign.setContinueSignCount(signObject.getSignCount());
sign.setSignHistory(signObject.getSignHistory());
sign.setTotalScore(signObject.getIntegration());
}
} else {//没查到，插入
executePreparedStatement = conn.prepareStatement(" insert into db_userplatform.dt_sign(userid,gameid,signCount,integration,lastModifyTime,signHistory,ext) values(?,?,1,1,?,1,?); ");
executePreparedStatement.setInt(1, uid);
executePreparedStatement.setInt(2, gid);
final java.sql.Date insertDate= new java.sql.Date(System.currentTimeMillis());
executePreparedStatement.setDate(3, insertDate);
executePreparedStatement.setString(4,"首次签到,时间:"+insertDate);
int effectRows = executePreparedStatement.executeUpdate();
if (effectRows >= 1) {
sign.setCode(200);
sign.setMsg("该用户第一次签到！成功插入数据!");
sign.setContinueSignCount(1);
sign.setLastModifyDate(insertDate);
sign.setTotalScore(1);
sign.setSignHistory(1);
} else {
sign.setCode(204);
sign.setMsg("该用户第一次签到，插入数据失败!");
}
}
} catch (SQLException e) {
sign.setCode(404);
sign.setMsg("平台或者db异常！");
e.printStackTrace();
} finally {
DbUtils.closeQuietly(rs);
try {
DbUtils.close(preparedStatement);
} catch (SQLException e) {
e.printStackTrace();
}
DbUtils.closeQuietly(conn);
}
return sign;
}
@Override
public Sign getGiftPackThenReturn(int uid, int gid, int giftPackScore) {
Sign sign = new Sign();
ResultSet rs = null;
PreparedStatement preparedStatement = null, executePreparedStatement = null;
try {
conn = getQueryRunner().getDataSource().getConnection();
preparedStatement = conn.prepareStatement(" select * from db_userplatform.dt_sign where userid=? and gameid=? and integration >=? ;");
preparedStatement.setInt(1, uid);
preparedStatement.setInt(2, gid);
preparedStatement.setInt(3, giftPackScore);
rs = preparedStatement.executeQuery();
if (rs.next()) { //如果查到了减去积分
SignObject signObject = new SignObject();
signObject.setId(rs.getInt("id"));
signObject.setUid(rs.getInt("userid"));
signObject.setGid(rs.getInt("gameid"));
signObject.setSignCount(rs.getInt("signCount"));
signObject.setIntegration(rs.getInt("integration"));
signObject.setLastModifyTime(new Date(rs.getDate("lastModifyTime").getTime()));
signObject.setSignHistory(rs.getLong("signHistory"));
signObject.setExt(rs.getString("ext"));
executePreparedStatement = conn.prepareStatement(" update db_userplatform.dt_sign set integration=? where id=? ;");
executePreparedStatement.setInt(1, signObject.getIntegration() - giftPackScore);
executePreparedStatement.setInt(2, signObject.getId());
int effectRows = executePreparedStatement.executeUpdate();
if (effectRows >= 1) {
sign.setCode(200);
sign.setMsg("成功领取礼包，积分消耗" + giftPackScore);
sign.setLastModifyDate(signObject.getLastModifyTime());
sign.setContinueSignCount(signObject.getSignCount());
sign.setSignHistory(signObject.getSignHistory());
sign.setTotalScore(signObject.getIntegration() - giftPackScore);
} else { //减去积分失败
sign.setCode(400);
sign.setMsg("领取礼包失败,积分没有减去！");
}
} else { //没查到，说明积分不够 返回300
sign.setCode(300);
sign.setMsg("积分不够领取礼包！");
}
} catch (Exception e) {//发生异常则是404
sign.setCode(404);
sign.setMsg("平台或db异常");
e.printStackTrace();
} finally {
DbUtils.closeQuietly(rs);
try {
DbUtils.close(preparedStatement);
} catch (SQLException e) {
e.printStackTrace();
}
DbUtils.closeQuietly(conn);
}
return sign;
}
}
移位和规则类：
package com.sz7road.userplatform.ws.sign;
import java.math.BigInteger;
/**
* Created with IntelliJ IDEA.
* User: cutter.li
* Date: 13-1-18
* Time: 下午5:24
* 移位和积分规则类
*/
public class ScoreRuleAndMoveByte {
public static Long moveByte(long oldHistory,long moveAmonut)
{
long moveResult= oldHistory<<moveAmonut;
long result= Long.parseLong(toFullBinaryString(moveResult),2)+1;
return result;
}
/**
* 读取
* @param num
* @return
*/
private static String toFullBinaryString(long num) {
final int size=42;
char[] chs = new char[size];
for(int i = 0; i < size; i++) {
chs[size - 1 - i] = (char)(((num >> i) & 1) + '0');
}
return new String(chs);
}
/**
* 按照积分规则，得到积分 ,
* 积分规则如下：
签到功能说明
1.每天只能签到一次（按服务器系统时间为准）
2.连续签到 额外奖励积分，同种礼包只能使用一次
3.连续签到10天，一次性奖励2积分
4.连续签到30天，一次性奖励10积分
5.连续签到60天，一次性奖励30积分
6.连续签到90天，一次性奖励100积分
* @param signCount 连续签到次数
* @return 增加的积分
*/
public static int getScoreByRule(int signCount)
{
int addScore=1;
if(signCount==10)
{
addScore+=2;
}
else if(signCount==30)
{
addScore+=10;
}
else if(signCount==60)
{
addScore+=30;
}
else if(signCount==90)
{
addScore+=100;
}
return addScore;
}
public static void main(String[] args)
{
long result= moveByte(1,3);
System.out.println("移位结果："+result);
System.out.println("连续签到次数9：所增加的积分："+getScoreByRule(9));
System.out.println("连续签到次数10：所增加的积分："+getScoreByRule(10));
System.out.println("连续签到次数29：所增加的积分："+getScoreByRule(29));
System.out.println("连续签到次数30：所增加的积分："+getScoreByRule(30));
System.out.println("连续签到次数59：所增加的积分："+getScoreByRule(59));
System.out.println("连续签到次数60：所增加的积分："+getScoreByRule(60));
System.out.println("连续签到次数89：所增加的积分："+getScoreByRule(89));
System.out.println("连续签到次数90：所增加的积分："+getScoreByRule(90));
System.out.println("连续签到次数91：所增加的积分："+getScoreByRule(91));
}
}
