示例#1
0
 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;
 }
示例#2
0
 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;
 }
示例#3
0
 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;
 }
示例#4
0
  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;
  }
示例#5
0
  /**
   * 新增或更新用户月度活跃时长统计,返回执行结果
   *
   * @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;
  }
示例#6
0
  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;
  }
示例#7
0
  /**
   * 新增或更新用户月度活跃时长统计,返回执行结果
   *
   * @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;
  }
示例#8
0
 /**
  * 删除指定月份之前的 所有月度活跃时长统计数据
  *
  * @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;
 }
示例#9
0
  /**
   * 新增或更新用户月度活跃时长统计,返回执行结果
   *
   * @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;
  }
示例#10
0
 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;
 }