private boolean isExsit(String activityId, String uniqueId) { boolean flag = false; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = DBUtil.getConnection(); String sql = "select id from youhui_cn_fanli.tyh_activity_join where activity_id =? and `activity_unique_id` = ?;"; ps = conn.prepareStatement(sql); ps.setString(1, activityId); ps.setString(2, uniqueId); rs = ps.executeQuery(); if (rs.next()) { flag = true; } } catch (SQLException e) { logger.error("error stack", e); } catch (Exception e) { logger.error("error stack", e); } finally { DBUtil.close(rs, ps, conn); } return flag; }
public long getLastJoinTime(String uid, String activityId) { long lastTime = 0; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = DBUtil.getConnection(); String sql = "select * from `ssyh_main`.`activity_join` where `uid` = ? and `activity_id`=? order by `create_time` desc;"; ps = conn.prepareStatement(sql); ps.setString(1, uid); ps.setString(2, activityId); rs = ps.executeQuery(); if (rs.next()) { lastTime = StringUtil.timestamp2long(rs.getTimestamp("create_time")); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, ps, conn); } return lastTime; }
public boolean insertShiftJF(String uid, int jf_num) { boolean flag = false; Connection conn = null; PreparedStatement ps = null; try { conn = DBUtil.getConnection(); String sql = "insert into `ssyh_main`.`trade_mx_" + uid.substring(0, 1) + "` (`trade_id`,`uid`,`jf_num`,`type`,`status`) values(?,?,?,?,?) "; ps = conn.prepareStatement(sql); ps.setString(1, StringUtil.generateTradeId()); ps.setString(2, uid); ps.setInt(3, jf_num); ps.setInt(4, Value.trade_mx_type_shift); ps.setInt(5, Value.trade_mx_status_success); ps.executeUpdate(); flag = true; } catch (SQLException e) { logger.error("error stack", e); } finally { DBUtil.close(ps, conn); } return flag; }
public int getJFAddMXNum(String uid) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; int count = 0; try { conn = DBUtil.getConnection(); String sql = "select count(*) from `ssyh_main`.`trade_mx_" + uid.substring(0, 1) + "` where `uid`=? and type in (1,2,3)"; ps = conn.prepareStatement(sql); ps.setString(1, uid); rs = ps.executeQuery(); if (rs.next()) { count = (rs.getInt(1)); } } catch (SQLException e) { logger.error("error stack", e); } finally { DBUtil.close(rs, ps, conn); } return count; }
/** * 新增或更新用户月度活跃时长统计,返回执行结果 * * @param bean 用户月度活跃时长统计Bean * @return 执行结果 */ public boolean addOrUpdateMAUStats(MAUStatsBean bean) { boolean result = false; Connection conn = null; PreparedStatement ps = null; String sql = "INSERT INTO `ssyh_main`.`mau_stats`" + "(`month_str`,`uid`,`stay_seconds`) VALUES(?,?,?) " + "ON DUPLICATE KEY UPDATE `stay_seconds`=`stay_seconds` + ?;"; try { conn = DBUtil.getConnection(); ps = conn.prepareStatement(sql); // insert参数 ps.setString(1, bean.getMonthStr()); ps.setString(2, bean.getUid()); ps.setInt(3, bean.getStaySeconds()); // update参数 ps.setInt(4, bean.getStaySeconds()); ps.executeUpdate(); result = true; } catch (SQLException e) { logger.error("addOrUpdateMAUStats exception:" + e.getMessage(), e); } finally { DBUtil.close(null, ps, conn); } return result; }
public int join( Activity activity, String uid, int runningDay, int status, String activityUniqueId) { int ret = 0; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = DBUtil.getConnection(); if (activity == null || activity.getId() == null || "".equals(activity.getId()) || uid == null || "".equals(uid)) { return ret; } if (isExsit(activity.getId(), activityUniqueId)) { ret = 2; return ret; } String sql = "insert into `ssyh_main`.`activity_join`(`id`,`uid`,`date`,`running_days`,`create_time`,`jf_num`,`activity_id`,`activity_name`,`status`,`activity_unique_id`) values(?,?,?,?,?,?,?,?,?,?)"; ps = conn.prepareStatement(sql); String today = ft.format(new Date()); int n = -1; int j = 0; String tradeId = null; while (n < 0 && j++ < 10) { tradeId = getTradeId(uid); ps.setString(1, tradeId); ps.setString(2, uid); ps.setString(3, today); ps.setInt(4, runningDay); ps.setTimestamp(5, StringUtil.long2timestamp(System.currentTimeMillis())); ps.setInt(6, activity.getJfNum()); ps.setString(7, activity.getId()); ps.setString(8, activity.getName()); ps.setInt(9, status); ps.setString(10, activityUniqueId); n = ps.executeUpdate(); if (n > 0) { ret = 1; } } } catch (SQLException e) { logger.error("error stack", e); } catch (Exception e) { logger.error("error stack", e); } finally { DBUtil.close(rs, ps, conn); } return ret; }
/** * 新增或更新用户月度活跃时长统计,返回执行结果 * * @param uidList 月度活跃uid分页清单 * @param millisList 对应用户在线时长list * @param monthStr 统计月度,yyyyMM * @return 执行结果 */ public boolean batchAddOrUpdateMAUStats( List<String> uidList, List<String> millisList, String monthStr) { if (uidList == null || millisList == null || uidList.isEmpty() || millisList.isEmpty()) { return false; } if (uidList.size() != millisList.size()) { return false; } int listSize = uidList.size(); boolean result = false; Connection conn = null; PreparedStatement ps = null; String sql = "INSERT INTO `ssyh_main`.`mau_stats`" + "(`month_str`,`uid`,`stay_seconds`,`stay_minutes`,`stay_hours`) VALUES(?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE `stay_seconds` = ?,`stay_minutes`=?,`stay_hours`=?;"; try { conn = DBUtil.getConnection(); // 关闭事务自动提交 conn.setAutoCommit(false); ps = conn.prepareStatement(sql); for (int i = 0; i < listSize; i++) { String uid = uidList.get(i); String millisStr = millisList.get(i); long stayMillis = NumberUtils.isNumber(millisStr) ? Long.parseLong(millisStr) : 0L; MAUStatsBean bean = new MAUStatsBean(monthStr, uid, stayMillis); // insert参数 ps.setString(1, bean.getMonthStr()); ps.setString(2, bean.getUid()); ps.setInt(3, bean.getStaySeconds()); ps.setInt(4, bean.getStayMinutes()); ps.setDouble(5, bean.getStayHours()); // update参数 ps.setInt(6, bean.getStaySeconds()); ps.setInt(7, bean.getStayMinutes()); ps.setDouble(8, bean.getStayHours()); ps.addBatch(); } ps.executeBatch(); // 关闭事务自动提交 conn.setAutoCommit(true); result = true; } catch (SQLException e) { result = false; logger.error("addOrUpdateMAUStats exception:" + e.getMessage(), e); } finally { DBUtil.close(null, ps, conn); } return result; }
/** * 删除指定月份之前的 所有月度活跃时长统计数据 * * @param yearMonthInt 指定月份,整形 * @return 删除结果 */ public boolean deleteBeforeMonth(int yearMonthInt) { boolean deleteResult = false; // 删除结果 Connection conn = null; PreparedStatement ps = null; String sql = "DELETE FROM `ssyh_main`.`mau_stats` WHERE `month_str` < ?;"; try { conn = DBUtil.getConnection(); ps = conn.prepareStatement(sql); ps.setInt(1, yearMonthInt); deleteResult = ps.executeUpdate() > 0 ? true : false; } catch (SQLException e) { logger.error("deleteBeforeMonth exception:" + e.getMessage(), e); } finally { DBUtil.close(ps, conn); } return deleteResult; }
/** * 新增或更新用户月度活跃时长统计,返回执行结果 * * @param bean 用户月度活跃时长统计Bean * @return 执行结果 */ public boolean batchAddOrUpdateMAUStats(List<MAUStatsBean> beans) { boolean result = false; Connection conn = null; PreparedStatement ps = null; String sql = "INSERT INTO `ssyh_main`.`mau_stats`" + "(`month_str`,`uid`,`stay_seconds`,`stay_minutes`,`stay_hours`) VALUES(?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE `stay_seconds` = ?,`stay_minutes`=?,`stay_hours`=?;"; try { conn = DBUtil.getConnection(); // 关闭事务自动提交 conn.setAutoCommit(false); ps = conn.prepareStatement(sql); for (MAUStatsBean bean : beans) { // insert参数 ps.setString(1, bean.getMonthStr()); ps.setString(2, bean.getUid()); ps.setInt(3, bean.getStaySeconds()); ps.setInt(4, bean.getStayMinutes()); ps.setDouble(5, bean.getStayHours()); // update参数 ps.setInt(6, bean.getStaySeconds()); ps.setInt(7, bean.getStayMinutes()); ps.setDouble(8, bean.getStayHours()); ps.addBatch(); } ps.executeBatch(); // 关闭事务自动提交 conn.setAutoCommit(true); result = true; } catch (SQLException e) { logger.error("addOrUpdateMAUStats exception:" + e.getMessage(), e); } finally { DBUtil.close(null, ps, conn); } return result; }
public List<TradeMx> getJFAddMX(String uid, int page, int pageSize) { List<TradeMx> tradeMxList = new ArrayList<TradeMx>(); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = DBUtil.getConnection(); String sql = "select * from `ssyh_main`.`trade_mx_" + uid.substring(0, 1) + "` where `uid`=? and type in (1,2,3) limit ?,?"; ps = conn.prepareStatement(sql); ps.setString(1, uid); ps.setInt(2, (pageSize * (page - 1))); ps.setInt(3, pageSize); rs = ps.executeQuery(); while (rs.next()) { TradeMx tradeMx = new TradeMx(); tradeMx.setDescription(rs.getString("description")); tradeMx.setId(rs.getInt("id")); tradeMx.setInsert_time(StringUtil.timestamp2datetime(rs.getTimestamp("insert_time"))); tradeMx.setJf_num(rs.getInt("jf_num")); tradeMx.setStatus(rs.getInt("status")); tradeMx.setTrade_id(rs.getString("trade_id")); tradeMx.setType(rs.getInt("type")); tradeMx.setUid(uid); tradeMx.setIcon( "http://gi2.md.alicdn.com/bao/uploaded/i2/TB1ypNbHXXXXXc1XpXXXXXXXXXX_!!0-item_pic.jpg_430x430q90.jpg"); tradeMxList.add(tradeMx); } } catch (SQLException e) { logger.error("error stack", e); } finally { DBUtil.close(rs, ps, conn); } return tradeMxList; }