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 }