关于网站签到功能的设计
1，最近网站要上一个签到的功能，一个多游戏的平台，每种游戏的官网都有签到功能，设计打算把数据放到平台。
2，首先要设计签到表，这里直接给出过了一遍dba，需求人员，设计人员脑子的结果：
最精彩的地方是signHistory的设计，香港虚拟主机，直接存成bigint,通过转换成二进制来记录签到的历史；
3，预览图
4，功能抽取
非常明显，只有三个主要的功能，美国空间，第一个签到之前的登录；第二个，签到；第三个，领取礼包；
5，具体实现
jdbc实现：
1 package com.sz7road.userplatform.dao.jdbc; com.google.common.base.Strings; 4 import com.sz7road.userplatform.dao.SignDao; 5 import com.sz7road.userplatform.ws.sign.ScoreRuleAndMoveByte; 6 import com.sz7road.userplatform.ws.sign.Sign; 7 import com.sz7road.userplatform.ws.sign.SignObject; 8 import com.sz7road.utils.CommonDateUtils; 9 import org.apache.commons.dbutils.DbUtils; 10 import org.slf4j.Logger; 11 import org.slf4j.LoggerFactory;java.sql.*; 14 import java.text.ParseException; 15 import java.text.SimpleDateFormat; 16 import java.util.*; 17 import java.util.Date; * Created with IntelliJ IDEA. 21 * User: cutter.li 22 * Date: 13-1-18 23 * Time: 上午11:01SignDaoJdbcImp extends JdbcDaoSupport<SignObject> implements SignDao {Logger log = LoggerFactory.getLogger(SignDaoJdbcImp.class);Connection conn = null; 30 31 @Override 32public Sign querySign(int uid, int gid) { 33Sign sign = new Sign(); 34ResultSet rs = null; 35PreparedStatement preparedStatement = null; 36try { 37conn = getQueryRunner().getDataSource().getConnection(); 38preparedStatement = conn.prepareStatement(” select * from db_userplatform.dt_sign where userid=? and gameid=? ;”); 39 40preparedStatement.setInt(1, uid); 41preparedStatement.setInt(2, gid); 42 43rs = preparedStatement.executeQuery(); 44if (rs.next()) { 45sign.setCode(200); 46sign.setMsg(“成功的查询到签到信息!”); 47sign.setContinueSignCount(rs.getInt(“signCount”)); 48sign.setTotalScore(rs.getInt(“integration”)); 49sign.setLastModifyDate(new Date(rs.getDate(“lastModifyTime”).getTime())); 50sign.setSignHistory(rs.getLong(“signHistory”)); 51} else { 52sign.setCode(300); 53sign.setMsg(“该用户从来没有签过到！”); 54 } 55 56} catch (SQLException e) { 57sign.setCode(404); 58sign.setMsg(“平台或者db异常！”); 59 e.printStackTrace(); 60} finally { 61 DbUtils.closeQuietly(rs); 62try { 63 DbUtils.close(preparedStatement); 64} catch (SQLException e) { 65 e.printStackTrace(); 66 } 67 DbUtils.closeQuietly(conn); 68 } 69return sign; 70 } @Override 74public Sign signThenReturn(int uid, int gid) { 75Sign sign = new Sign(); 76ResultSet rs = null; 77PreparedStatement preparedStatement = null, executePreparedStatement = null; 78try { 79conn = getQueryRunner().getDataSource().getConnection(); 80preparedStatement = conn.prepareStatement(” select * from db_userplatform.dt_sign where userid=? and gameid=? ;”); 81preparedStatement.setInt(1, uid); 82preparedStatement.setInt(2, gid); 83 84rs = preparedStatement.executeQuery();SignObject signObject = new SignObject(); 87signObject.setId(rs.getInt(“id”)); 88signObject.setUid(rs.getInt(“userid”)); 89signObject.setGid(rs.getInt(“gameid”)); 90signObject.setSignCount(rs.getInt(“signCount”)); 91signObject.setIntegration(rs.getInt(“integration”)); 92signObject.setLastModifyTime(new Date(rs.getDate(“lastModifyTime”).getTime())); 93signObject.setSignHistory(rs.getLong(“signHistory”)); 94signObject.setExt(rs.getString(“ext”));Timestamp lastModifyTimeStamp = new Timestamp(signObject.getLastModifyTime().getTime()); 98Timestamp todayStartTimeStamp = CommonDateUtils.getTodayStartTimeStamp();missDays= (System.currentTimeMillis()-signObject.getLastModifyTime().getTime())/(24*60*60*1000);101int newSignCount=signObject.getSignCount();102String newExt=”签到”;103if(missDays==1)newSignCount+=1;newSignCount=1;109 }110if(newSignCount>=91)newSignCount=1;113newExt=”连续签到天数重置为1，时间：”+CommonDateUtils.getDate(System.currentTimeMillis());114 }newSignHistory= ScoreRuleAndMoveByte.moveByte(signObject.getSignHistory(),missDays);newIntegration=signObject.getIntegration()+ScoreRuleAndMoveByte.getScoreByRule(newSignCount);117executePreparedStatement = conn.prepareStatement(” update db_userplatform.dt_sign set signCount=? , integration=? , signHistory=? , lastModifyTime=? , ext=? where id=?; “);118executePreparedStatement.setInt(1, newSignCount);119executePreparedStatement.setInt(2, newIntegration);120executePreparedStatement.setLong(3, newSignHistory);121java.sql.Date signDate= new java.sql.Date(System.currentTimeMillis());122executePreparedStatement.setDate(4,signDate);123executePreparedStatement.setString(5,newExt);124executePreparedStatement.setInt(6,signObject.getId());effectRows = executePreparedStatement.executeUpdate();(effectRows >= 1) {129sign.setCode(206);130sign.setMsg(“签到成功！成功更新数据!”);131 sign.setContinueSignCount(newSignCount);132 sign.setLastModifyDate(signDate);133 sign.setTotalScore(newIntegration);134 sign.setSignHistory(newSignHistory);135} else {136sign.setCode(208);137sign.setMsg(“签到失败，更新数据失败!”);138 }139 }{sign.setCode(300);143sign.setMsg(“该用户今天已经签过到了!”);144 sign.setLastModifyDate(signObject.getLastModifyTime());145 sign.setContinueSignCount(signObject.getSignCount());146 sign.setSignHistory(signObject.getSignHistory());147 sign.setTotalScore(signObject.getIntegration());148 }executePreparedStatement = conn.prepareStatement(” insert into db_userplatform.dt_sign(userid,gameid,signCount,integration,lastModifyTime,signHistory,ext) values(?,?,1,1,?,1,?); “);152executePreparedStatement.setInt(1, uid);153executePreparedStatement.setInt(2, gid);154final java.sql.Date insertDate= new java.sql.Date(System.currentTimeMillis());155executePreparedStatement.setDate(3, insertDate);156executePreparedStatement.setString(4,”首次签到,时间:”+insertDate);157int effectRows = executePreparedStatement.executeUpdate();(effectRows >= 1) {160sign.setCode(200);161sign.setMsg(“该用户第一次签到！成功插入数据!”);162sign.setContinueSignCount(1);163 sign.setLastModifyDate(insertDate);164sign.setTotalScore(1);165sign.setSignHistory(1);166} else {167sign.setCode(204);168sign.setMsg(“该用户第一次签到，插入数据失败!”);169 }170 171 }172} catch (SQLException e) {173sign.setCode(404);174sign.setMsg(“平台或者db异常！”);175 e.printStackTrace();176} finally {177 DbUtils.closeQuietly(rs);178try {179 DbUtils.close(preparedStatement);180} catch (SQLException e) {181 e.printStackTrace();182 }183 DbUtils.closeQuietly(conn);184 }185return sign;186 }187 188 @Override189public Sign getGiftPackThenReturn(int uid, int gid, int giftPackScore) {190Sign sign = new Sign();191ResultSet rs = null;192PreparedStatement preparedStatement = null, executePreparedStatement = null;193try {194conn = getQueryRunner().getDataSource().getConnection();195preparedStatement = conn.prepareStatement(” select * from db_userplatform.dt_sign where userid=? and gameid=? and integration >=? ;”);196 197preparedStatement.setInt(1, uid);198preparedStatement.setInt(2, gid);199preparedStatement.setInt(3, giftPackScore);200 201rs = preparedStatement.executeQuery();SignObject signObject = new SignObject();204 205signObject.setId(rs.getInt(“id”));206signObject.setUid(rs.getInt(“userid”));207signObject.setGid(rs.getInt(“gameid”));208signObject.setSignCount(rs.getInt(“signCount”));209signObject.setIntegration(rs.getInt(“integration”));210signObject.setLastModifyTime(new Date(rs.getDate(“lastModifyTime”).getTime()));211signObject.setSignHistory(rs.getLong(“signHistory”));212signObject.setExt(rs.getString(“ext”));executePreparedStatement = conn.prepareStatement(” update db_userplatform.dt_sign set integration=? where id=? ;”);216executePreparedStatement.setInt(1, signObject.getIntegration() – giftPackScore);217executePreparedStatement.setInt(2, signObject.getId());effectRows = executePreparedStatement.executeUpdate();(effectRows >= 1) {222sign.setCode(200);223sign.setMsg(“成功领取礼包，积分消耗” + giftPackScore);224 sign.setLastModifyDate(signObject.getLastModifyTime());225 sign.setContinueSignCount(signObject.getSignCount());226 sign.setSignHistory(signObject.getSignHistory());227sign.setTotalScore(signObject.getIntegration() – giftPackScore);sign.setCode(400);230sign.setMsg(“领取礼包失败,积分没有减去！”);231 }sign.setCode(300);234sign.setMsg(“积分不够领取礼包！”);235 }sign.setCode(404);238sign.setMsg(“平台或db异常”);239 e.printStackTrace();240} finally {241 DbUtils.closeQuietly(rs);242try {243 DbUtils.close(preparedStatement);244} catch (SQLException e) {245 e.printStackTrace();246 }247 DbUtils.closeQuietly(conn);248 }249return sign;250 }251 }
移位和规则类：
幸福就是重复。每天跟自己喜欢的人一起，通电话，
