java签到 表设计_关于网站签到功能的设计

1 packagecom.sz7road.userplatform.dao.jdbc;2

3 importcom.google.common.base.Strings;4 importcom.sz7road.userplatform.dao.SignDao;5 importcom.sz7road.userplatform.ws.sign.ScoreRuleAndMoveByte;6 importcom.sz7road.userplatform.ws.sign.Sign;7 importcom.sz7road.userplatform.ws.sign.SignObject;8 importcom.sz7road.utils.CommonDateUtils;9 importorg.apache.commons.dbutils.DbUtils;10 importorg.slf4j.Logger;11 importorg.slf4j.LoggerFactory;12

13 import java.sql.*;14 importjava.text.ParseException;15 importjava.text.SimpleDateFormat;16 import java.util.*;17 importjava.util.Date;18

19 /**

20 * Created with IntelliJ IDEA.21 * User: cutter.li22 * Date: 13-1-1823 * Time: 上午11:0124 */

25 public class SignDaoJdbcImp extends JdbcDaoSupport implementsSignDao {26

27 private final static Logger log = LoggerFactory.getLogger(SignDaoJdbcImp.class);28

29 private Connection conn = null;30

31 @Override32 public Sign querySign(int uid, intgid) {33 Sign sign = newSign();34 ResultSet rs = null;35 PreparedStatement preparedStatement = null;36 try{37 conn =getQueryRunner().getDataSource().getConnection();38 preparedStatement = conn.prepareStatement(" select * from db_userplatform.dt_sign where userid=? and gameid=? ;");39

40 preparedStatement.setInt(1, uid);41 preparedStatement.setInt(2, gid);42

43 rs =preparedStatement.executeQuery();44 if(rs.next()) {45 sign.setCode(200);46 sign.setMsg("成功的查询到签到信息!");47 sign.setContinueSignCount(rs.getInt("signCount"));48 sign.setTotalScore(rs.getInt("integration"));49 sign.setLastModifyDate(new Date(rs.getDate("lastModifyTime").getTime()));50 sign.setSignHistory(rs.getLong("signHistory"));51 } else{52 sign.setCode(300);53 sign.setMsg("该用户从来没有签过到！");54 }55

56 } catch(SQLException e) {57 sign.setCode(404);58 sign.setMsg("平台或者db异常！");59 e.printStackTrace();60 } finally{61 DbUtils.closeQuietly(rs);62 try{63 DbUtils.close(preparedStatement);64 } catch(SQLException e) {65 e.printStackTrace();66 }67 DbUtils.closeQuietly(conn);68 }69 returnsign;70 }71

72

73 @Override74 public Sign signThenReturn(int uid, intgid) {75 Sign sign = newSign();76 ResultSet rs = null;77 PreparedStatement preparedStatement = null, executePreparedStatement = null;78 try{79 conn =getQueryRunner().getDataSource().getConnection();80 preparedStatement = conn.prepareStatement(" select * from db_userplatform.dt_sign where userid=? and gameid=? ;");81 preparedStatement.setInt(1, uid);82 preparedStatement.setInt(2, gid);83

84 rs =preparedStatement.executeQuery();85 if (rs.next()) {//查到了更新

86 SignObject signObject = newSignObject();87 signObject.setId(rs.getInt("id"));88 signObject.setUid(rs.getInt("userid"));89 signObject.setGid(rs.getInt("gameid"));90 signObject.setSignCount(rs.getInt("signCount"));91 signObject.setIntegration(rs.getInt("integration"));92 signObject.setLastModifyTime(new Date(rs.getDate("lastModifyTime").getTime()));93 signObject.setSignHistory(rs.getLong("signHistory"));94 signObject.setExt(rs.getString("ext"));95

96

97 Timestamp lastModifyTimeStamp = newTimestamp(signObject.getLastModifyTime().getTime());98 Timestamp todayStartTimeStamp =CommonDateUtils.getTodayStartTimeStamp();99 if (todayStartTimeStamp.after(lastModifyTimeStamp)) {//今天没有签过到

100 final long missDays= (System.currentTimeMillis()-signObject.getLastModifyTime().getTime())/(24*60*60*1000);101 int newSignCount=signObject.getSignCount();102 String newExt="签到";103 if(missDays==1)104 { //连续签到，加分，连续签到次数增加1 ，签到历史移动一位

105 newSignCount+=1;106 }else

107 {//不连续签到，加分，连续签到次数为1，签到历史移动missDays位

108 newSignCount=1;109 }110 if(newSignCount>=91)111 { //签到超过90天，连续签到次数重置为1

112 newSignCount=1;113 newExt="连续签到天数重置为1，时间："+CommonDateUtils.getDate(System.currentTimeMillis());114 }115 final long newSignHistory=ScoreRuleAndMoveByte.moveByte(signObject.getSignHistory(),missDays);116 final int newIntegration=signObject.getIntegration()+ScoreRuleAndMoveByte.getScoreByRule(newSignCount);117 executePreparedStatement = conn.prepareStatement(" update db_userplatform.dt_sign set signCount=? , integration=? , signHistory=? , lastModifyTime=? , ext=? where id=?; ");118 executePreparedStatement.setInt(1, newSignCount);119 executePreparedStatement.setInt(2, newIntegration);120 executePreparedStatement.setLong(3, newSignHistory);121 java.sql.Date signDate= newjava.sql.Date(System.currentTimeMillis());122 executePreparedStatement.setDate(4,signDate);123 executePreparedStatement.setString(5,newExt);124 executePreparedStatement.setInt(6,signObject.getId());125

126 int effectRows =executePreparedStatement.executeUpdate();127

128 if (effectRows >= 1) {129 sign.setCode(206);130 sign.setMsg("签到成功！成功更新数据!");131 sign.setContinueSignCount(newSignCount);132 sign.setLastModifyDate(signDate);133 sign.setTotalScore(newIntegration);134 sign.setSignHistory(newSignHistory);135 } else{136 sign.setCode(208);137 sign.setMsg("签到失败，更新数据失败!");138 }139 }140 else

141 {//今天已经签过到了

142 sign.setCode(300);143 sign.setMsg("该用户今天已经签过到了!");144 sign.setLastModifyDate(signObject.getLastModifyTime());145 sign.setContinueSignCount(signObject.getSignCount());146 sign.setSignHistory(signObject.getSignHistory());147 sign.setTotalScore(signObject.getIntegration());148 }149

150 } else {//没查到，插入

151 executePreparedStatement = conn.prepareStatement(" insert into db_userplatform.dt_sign(userid,gameid,signCount,integration,lastModifyTime,signHistory,ext) values(?,?,1,1,?,1,?); ");152 executePreparedStatement.setInt(1, uid);153 executePreparedStatement.setInt(2, gid);154 final java.sql.Date insertDate= newjava.sql.Date(System.currentTimeMillis());155 executePreparedStatement.setDate(3, insertDate);156 executePreparedStatement.setString(4,"首次签到,时间:"+insertDate);157 int effectRows =executePreparedStatement.executeUpdate();158

159 if (effectRows >= 1) {160 sign.setCode(200);161 sign.setMsg("该用户第一次签到！成功插入数据!");162 sign.setContinueSignCount(1);163 sign.setLastModifyDate(insertDate);164 sign.setTotalScore(1);165 sign.setSignHistory(1);166 } else{167 sign.setCode(204);168 sign.setMsg("该用户第一次签到，插入数据失败!");169 }170

171 }172 } catch(SQLException e) {173 sign.setCode(404);174 sign.setMsg("平台或者db异常！");175 e.printStackTrace();176 } finally{177 DbUtils.closeQuietly(rs);178 try{179 DbUtils.close(preparedStatement);180 } catch(SQLException e) {181 e.printStackTrace();182 }183 DbUtils.closeQuietly(conn);184 }185 returnsign;186 }187

188 @Override189 public Sign getGiftPackThenReturn(int uid, int gid, intgiftPackScore) {190 Sign sign = newSign();191 ResultSet rs = null;192 PreparedStatement preparedStatement = null, executePreparedStatement = null;193 try{194 conn =getQueryRunner().getDataSource().getConnection();195 preparedStatement = conn.prepareStatement(" select * from db_userplatform.dt_sign where userid=? and gameid=? and integration >=? ;");196

197 preparedStatement.setInt(1, uid);198 preparedStatement.setInt(2, gid);199 preparedStatement.setInt(3, giftPackScore);200

201 rs =preparedStatement.executeQuery();202 if (rs.next()) { //如果查到了减去积分

203 SignObject signObject = newSignObject();204

205 signObject.setId(rs.getInt("id"));206 signObject.setUid(rs.getInt("userid"));207 signObject.setGid(rs.getInt("gameid"));208 signObject.setSignCount(rs.getInt("signCount"));209 signObject.setIntegration(rs.getInt("integration"));210 signObject.setLastModifyTime(new Date(rs.getDate("lastModifyTime").getTime()));211 signObject.setSignHistory(rs.getLong("signHistory"));212 signObject.setExt(rs.getString("ext"));213

214

215 executePreparedStatement = conn.prepareStatement(" update db_userplatform.dt_sign set integration=? where id=? ;");216 executePreparedStatement.setInt(1, signObject.getIntegration() -giftPackScore);217 executePreparedStatement.setInt(2, signObject.getId());218

219 int effectRows =executePreparedStatement.executeUpdate();220

221 if (effectRows >= 1) {222 sign.setCode(200);223 sign.setMsg("成功领取礼包，积分消耗" +giftPackScore);224 sign.setLastModifyDate(signObject.getLastModifyTime());225 sign.setContinueSignCount(signObject.getSignCount());226 sign.setSignHistory(signObject.getSignHistory());227 sign.setTotalScore(signObject.getIntegration() -giftPackScore);228 } else { //减去积分失败

229 sign.setCode(400);230 sign.setMsg("领取礼包失败,积分没有减去！");231 }232 } else { //没查到，说明积分不够 返回300

233 sign.setCode(300);234 sign.setMsg("积分不够领取礼包！");235 }236 } catch (Exception e) {//发生异常则是404

237 sign.setCode(404);238 sign.setMsg("平台或db异常");239 e.printStackTrace();240 } finally{241 DbUtils.closeQuietly(rs);242 try{243 DbUtils.close(preparedStatement);244 } catch(SQLException e) {245 e.printStackTrace();246 }247 DbUtils.closeQuietly(conn);248 }249 returnsign;250 }251 }