Example #1
0
  public static String DeleteDownload(String uuid, String callback)
      throws SQLException, JSONException {
    JSONObject jsonObj = new JSONObject();

    MySqlConn m_fpsql = getConn();

    Connection conn = m_fpsql.getConn();
    String strSql = "update adhoc_download set status='DEL',endtime=? where uuid=? ";
    PreparedStatement m_fps = conn.prepareStatement(strSql);
    try {
      int index = 1;
      m_fps.setTimestamp(index++, new java.sql.Timestamp(System.currentTimeMillis()));
      m_fps.setString(index++, uuid);

      m_fps.executeUpdate();
      String fullstrSql = m_fps.toString();
      jsonObj.put("code", "1");
      jsonObj.put("__debug", fullstrSql);
    } catch (Exception e) {
      jsonObj.put("code", "0");
      TableJoin.LOG.error("updatePercent" + m_fps.toString(), e);
      jsonObj.put("__debugerror", m_fps.toString());
    } finally {
      m_fps.close();
      m_fpsql.close();
    }
    if (callback != null && callback.length() > 0) {
      return callback + "(" + jsonObj.toString() + ")";
    } else {
      return jsonObj.toString();
    }
  }
Example #2
0
  public static String updatePercent(String uuid, String percent, String status)
      throws SQLException {
    MySqlConn m_fpsql = getConn();

    Connection conn = m_fpsql.getConn();
    String strSql =
        "update adhoc_joins set " + "percent=?,status=?,lastuptime=? where tableName=? ";
    System.out.println(strSql);
    PreparedStatement m_fps = conn.prepareStatement(strSql);
    try {
      int index = 1;
      m_fps.setString(index++, percent);
      m_fps.setString(index++, status);
      m_fps.setTimestamp(index++, new java.sql.Timestamp(System.currentTimeMillis()));
      m_fps.setString(index++, uuid);

      m_fps.executeUpdate();
      String fullstrSql = m_fps.toString();
      return fullstrSql;
    } catch (Exception e) {
      TableJoin.LOG.error("updatePercent" + m_fps.toString(), e);
    } finally {
      m_fps.close();
      m_fpsql.close();
    }
    return "";
  }
Example #3
0
  public static HashMap<String, String> getTableInfo(String uuid) throws SQLException {
    SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
    String day = fmt.format(new Date());
    Map stormconf = Utils.readStormConfig();
    String hdpConf = (String) stormconf.get("hadoop.conf.dir");
    String connstr = (String) stormconf.get("higo.download.offline.conn");
    String uname = (String) stormconf.get("higo.download.offline.username");
    String passwd = (String) stormconf.get("higo.download.offline.passwd");
    String tmppath =
        (String) stormconf.get("higo.download.offline.store")
            + "/"
            + day
            + "/tmp_"
            + java.util.UUID.randomUUID().toString();
    MySqlConn m_fpsql = new MySqlConn(connstr, uname, passwd);

    Connection conn = m_fpsql.getConn();
    Statement stmt = conn.createStatement();

    String sql =
        "select tableShowName,tableName,colsShowName,colsName,colsType,splitString,txtStorePath,indexStorePath,status,username,createtime,lastuptime,joins,percent from adhoc_joins where tableName='"
            + uuid.replaceAll("'", "")
            + "' limit 10";
    ResultSet res = stmt.executeQuery(sql);
    HashMap<String, String> rtn = new HashMap<String, String>();
    rtn.put("_exehql", sql);

    boolean issetup = false;
    while (res.next()) {
      rtn.put("tableShowName", res.getString("tableShowName"));
      rtn.put("txtStorePath", res.getString("txtStorePath"));
      rtn.put("indexStorePath", res.getString("indexStorePath"));
      rtn.put("splitString", res.getString("splitString"));
      rtn.put("colsName", res.getString("colsName"));
      rtn.put("status", res.getString("status"));
      rtn.put("splitString", res.getString("splitString"));
      issetup = true;
    }
    m_fpsql.close();

    if (!issetup) {
      return null;
    }
    return rtn;
  }
Example #4
0
  public static HashMap<String, String> getUserTablesCount(String username, int type)
      throws SQLException {
    SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
    Map stormconf = Utils.readStormConfig();
    String connstr = (String) stormconf.get("higo.download.offline.conn");
    String uname = (String) stormconf.get("higo.download.offline.username");
    String passwd = (String) stormconf.get("higo.download.offline.passwd");
    MySqlConn m_fpsql = new MySqlConn(connstr, uname, passwd);

    Connection conn = m_fpsql.getConn();
    Statement stmt = conn.createStatement();

    String strsqlJoin =
        "select count(*) as cnt"
            + " from adhoc_joins where username='******'", "")
            + "' and status<>'DEL' ";

    StringBuffer bufferSql = new StringBuffer();
    if (type == 0) {
      String strsqlDownload =
          "select count(*) as cnt"
              + " from adhoc_download where username='******'", "")
              + "' and status<>'DEL' ";

      bufferSql.append("select sum(cnt) as cnt");
      bufferSql.append(" from (" + strsqlJoin + " union " + strsqlDownload + ") tmp  limit 10 ");
    }
    if (type == 1) // for join
    {
      bufferSql.append(strsqlJoin + " and status='INDEX' order by createtime desc limit 10 ");
    }

    String sql = bufferSql.toString();
    ResultSet res = stmt.executeQuery(sql);
    HashMap<String, String> rtn = new HashMap<String, String>();
    rtn.put("_exehql", sql);

    while (res.next()) {
      rtn.put("cnt", String.valueOf(res.getInt("cnt")));
    }
    m_fpsql.close();
    return rtn;
  }
Example #5
0
  public static String create(String userid, Map<String, String> val, String user)
      throws Exception {
    MySqlConn m_fpsql = getConn();
    val.put("opuser", String.valueOf(user));
    val.put("optime", getNowTime());
    JSONObject jsonObj = new JSONObject();

    StringBuffer sqlbuffer = new StringBuffer();
    StringBuffer sqlbuffer2 = new StringBuffer();

    String joinchar = "";
    String[] indexval = new String[val.size()];
    int index = 0;
    for (Entry<String, String> e : val.entrySet()) {
      sqlbuffer.append(joinchar).append(e.getKey());
      sqlbuffer2.append(joinchar).append("?");

      joinchar = ",";
      indexval[index] = e.getValue();
      index++;
    }

    Connection conn = m_fpsql.getConn();
    String strSql =
        "insert into users_json " + "(" + sqlbuffer + ")" + "values" + "(" + sqlbuffer2 + ")";
    PreparedStatement m_fps = conn.prepareStatement(strSql);
    try {
      for (int i = 0; i < indexval.length; i++) {
        m_fps.setString(i + 1, indexval[i]);
      }
      m_fps.executeUpdate();
      jsonObj.put("code", "1");

      jsonObj.put("____debug", m_fps.toString());
    } catch (Exception e) {
      jsonObj.put("____debug2", m_fps.toString());
      jsonObj.put("____debugerror", e.toString());
      jsonObj.put("code", "0");
    } finally {
      m_fps.close();
      m_fpsql.close();
    }
    return jsonObj.toString();
  }
Example #6
0
  public static String del(String userid, String user) throws Exception {
    JSONObject jsonObj = new JSONObject();
    jsonObj.put("code", "0");
    MySqlConn m_fpsql = getConn();

    Connection conn = m_fpsql.getConn();
    String strSql = "delete from users_json  where userid=? ";
    PreparedStatement m_fps = conn.prepareStatement(strSql);
    try {
      int index = 1;
      m_fps.setString(index++, userid);
      m_fps.executeUpdate();
      jsonObj.put("code", "1");
    } catch (Exception e) {
      jsonObj.put("message", e.toString());
      jsonObj.put("code", "0");
    } finally {
      m_fps.close();
      m_fpsql.close();
    }

    return jsonObj.toString();
  }
Example #7
0
  public static String updateKb(String uuid, long sz) throws SQLException {
    MySqlConn m_fpsql = getConn();

    Connection conn = m_fpsql.getConn();
    String strSql = "update adhoc_joins set " + "resultkb=? where tableName=? ";
    System.out.println(strSql);
    PreparedStatement m_fps = conn.prepareStatement(strSql);
    try {
      int index = 1;
      m_fps.setInt(index++, (int) sz / 1024);
      m_fps.setString(index++, uuid);

      m_fps.executeUpdate();
      String fullstrSql = m_fps.toString();
      return fullstrSql;
    } catch (Exception e) {
      TableJoin.LOG.error("updatePercent" + m_fps.toString(), e);
    } finally {
      m_fps.close();
      m_fpsql.close();
    }
    return "";
  }
Example #8
0
  public String update(String userid, Map<String, String> val, String user) throws SQLException {
    MySqlConn m_fpsql = getConn();
    val.put("opuser", String.valueOf(user));
    val.put("optime", getNowTime());
    StringBuffer sqlbuffer = new StringBuffer();
    String joinchar = "";
    String[] indexval = new String[val.size()];
    int index = 0;
    for (Entry<String, String> e : val.entrySet()) {
      sqlbuffer.append(joinchar).append(e.getKey() + "=?");
      joinchar = ",";
      indexval[index] = e.getValue();
      index++;
    }

    Connection conn = m_fpsql.getConn();
    String strSql = "update users_json set " + " " + sqlbuffer + " where userid=? ";
    System.out.println(strSql);
    PreparedStatement m_fps = conn.prepareStatement(strSql);
    try {
      for (int i = 0; i < indexval.length; i++) {
        m_fps.setString(i + 1, indexval[i]);
      }
      m_fps.setString(indexval.length + 1, userid);

      m_fps.executeUpdate();
      String fullstrSql = m_fps.toString();
      return fullstrSql;
    } catch (Exception e) {
      LOG.error("updatePercent" + m_fps.toString(), e);
    } finally {
      m_fps.close();
      m_fpsql.close();
    }
    return "";
  }
Example #9
0
  public static String getJson(String daystart, boolean extamsg)
      throws JSONException, SQLException {
    JSONObject jsonObj = new JSONObject();
    jsonObj.put("code", "1");
    MySqlConn m_fpsql = getConn();

    String strSql =
        "select userid,email,cname,role,permission,'-' as queryday,opuser,optime from users_json order by role desc,userid";
    if (daystart != null && !daystart.isEmpty()) {
      strSql =
          " select userid,email,cname,role,permission,opuser,optime from users_json where (cname not in (select nick from query_analyser.day_user_pv  where queryday>'"
              + daystart
              + "')) order by role desc,userid";
    }

    if (extamsg) {
      strSql =
          " select a.userid as userid,a.email as email ,a.cname as cname,a.role as role,a.permission as permission,a.opuser as opuser,a.optime as optime, b.queryday as queryday "
              + "from ( "
              + ""
              + strSql
              + ") "
              + "  a "
              + " left join (select nick,max(queryday) as queryday from query_analyser.day_user_pv group by nick ) b"
              + " on a.cname=b.nick order by role desc,queryday desc";
    }

    jsonObj.put("_exehql", strSql);

    Connection conn = m_fpsql.getConn();
    Statement stmt = conn.createStatement();
    try {
      ResultSet res = stmt.executeQuery(strSql);
      JSONArray userlist = new JSONArray();
      while (res.next()) {
        JSONObject item = new JSONObject();
        item.put("userid", String.valueOf(res.getString("userid")));
        item.put("email", String.valueOf(res.getString("email")));
        item.put("queryday", String.valueOf(res.getString("queryday")));

        item.put("cname", String.valueOf(res.getString("cname")));

        item.put("role", Integer.parseInt(res.getString("role")));
        item.put("permission", new JSONArray(res.getString("permission")));
        item.put("opuser", String.valueOf(res.getString("opuser")));
        item.put("optime", String.valueOf(res.getString("optime")));

        userlist.put(item);
      }
      m_fpsql.close();
      jsonObj.put("code", "1");
      jsonObj.put("message", "success");
      JSONObject dddd = new JSONObject();
      dddd.put("users", userlist);
      jsonObj.put("data", dddd);
    } catch (Exception e) {
      jsonObj.put("message", e.toString());
      jsonObj.put("code", "0");
    } finally {
      m_fpsql.close();
    }

    return jsonObj.toString();
  }
Example #10
0
  public static String create(
      String tableShowName, String colsShowName, String splitString, String username, String joins)
      throws Exception {
    JSONObject jsonObj = new JSONObject();

    SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
    String day = fmt.format(new Date());
    Map stormconf = Utils.readStormConfig();
    String hdpConf = (String) stormconf.get("hadoop.conf.dir");
    String connstr = (String) stormconf.get("higo.download.offline.conn");
    String uname = (String) stormconf.get("higo.download.offline.username");
    String passwd = (String) stormconf.get("higo.download.offline.passwd");
    String store =
        (String) stormconf.get("higo.download.offline.store")
            + "/"
            + day
            + "/"
            + java.util.UUID.randomUUID().toString();
    MySqlConn m_fpsql = new MySqlConn(connstr, uname, passwd);

    String tableName = java.util.UUID.randomUUID().toString();
    Connection conn = m_fpsql.getConn();
    String strSql =
        "insert into adhoc_joins "
            + "(tableShowName,tableName,colsShowName,colsName,colsType,splitString,txtStorePath,indexStorePath,status,username,createtime,lastuptime,joins,percent)"
            + "values"
            + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    PreparedStatement m_fps = conn.prepareStatement(strSql);
    try {
      int index = 1;
      m_fps.setString(index++, tableShowName);
      m_fps.setString(index++, tableName);
      m_fps.setString(index++, colsShowName);
      StringBuffer colsName = new StringBuffer();
      StringBuffer colsType = new StringBuffer();
      String[] cols = colsShowName.split(",");
      String join = "";
      HashMap<String, String> colsNames = new HashMap<String, String>();
      for (int i = 0; i < cols.length; i++) {
        String colname = "cols_" + i + "_s";
        colsName.append(join);
        colsName.append(colname);
        colsType.append(join);
        colsType.append("string");
        colsNames.put(cols[i], colname);
        join = ",";
      }
      m_fps.setString(index++, colsName.toString());
      m_fps.setString(index++, colsType.toString());
      m_fps.setString(index++, MakeIndex.parseSplit(splitString));
      m_fps.setString(index++, store + "/txt");
      m_fps.setString(index++, store + "/index");
      m_fps.setString(index++, "init");
      m_fps.setString(index++, username);

      long nowtims = System.currentTimeMillis();
      m_fps.setTimestamp(index++, new java.sql.Timestamp(nowtims));
      m_fps.setTimestamp(index++, new java.sql.Timestamp(nowtims));

      StringBuffer joinbuff = new StringBuffer();
      String joinchar = "";
      for (String joindesc : joins.split(",")) {
        String[] arr = joindesc.split(":");
        joinbuff.append(joinchar);
        joinbuff.append(arr[0]);
        joinbuff.append(":");
        joinbuff.append(arr[1]);
        joinbuff.append(":");
        joinbuff.append(colsNames.get(arr[2]));
        joinchar = ",";
      }
      m_fps.setString(index++, joinbuff.toString());
      m_fps.setString(index++, "");

      m_fps.executeUpdate();
      jsonObj.put("code", "1");
      jsonObj.put("tableid", tableName);
      jsonObj.put("____debug", m_fps.toString());
    } catch (Exception e) {
      jsonObj.put("____debug2", m_fps.toString());
      jsonObj.put("____debugerror", e.toString());
      jsonObj.put("code", "0");
    } finally {
      m_fps.close();
      m_fpsql.close();
    }

    return jsonObj.toString();
  }
Example #11
0
  public static String getUserTables(
      String username, int start, int rows, int type, String callback)
      throws SQLException, JSONException {
    SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
    Map stormconf = Utils.readStormConfig();
    String connstr = (String) stormconf.get("higo.download.offline.conn");
    String uname = (String) stormconf.get("higo.download.offline.username");
    String passwd = (String) stormconf.get("higo.download.offline.passwd");
    MySqlConn m_fpsql = new MySqlConn(connstr, uname, passwd);

    Connection conn = m_fpsql.getConn();
    Statement stmt = conn.createStatement();

    String strsqlJoin =
        "select '1' as source "
            + ",tableShowName as tableShowName"
            + ",tableName as tableName"
            + ",colsShowName as colsShowName"
            + ",colsName as colsName"
            + ",colsType as colsType"
            + ",splitString as splitString"
            + ",txtStorePath as txtStorePath"
            + ",indexStorePath as indexStorePath"
            + ",'0' as extval"
            + ",'0' as isfinish"
            + ",status as status"
            + ",username as username"
            + ",createtime as createtime"
            + ",lastuptime as lastuptime"
            + ",joins as joins"
            + ",'2' as stage"
            + ",percent as percent"
            + ",resultkb as resultkb"
            + " from adhoc_joins where username='******'", "")
            + "' and status<>'DEL'  ";

    StringBuffer bufferSql = new StringBuffer();
    if (type == 0) // 个人中心列表
    {
      String strsqlDownload =
          "select '2' as source "
              + ",jobname as tableShowName"
              + ",uuid as tableName"
              + ",'empty' as colsShowName"
              + ",'empty' as colsName"
              + ",'empty' as colsType"
              + ",'empty' as splitString"
              + ",storedir as txtStorePath"
              + ",'empty' as indexStorePath"
              + ",extval as extval"
              + ",isfinish as isfinish"
              + ",'INDEX' as status"
              + ",username as username"
              + ",starttime as createtime"
              + ",endtime as lastuptime"
              + ",'' as joins"
              + ",stage as stage"
              + ",percent as percent"
              + ",resultkb as resultkb"
              + " from adhoc_download where username='******'", "")
              + "' and status<>'DEL'  ";
      bufferSql.append(
          "select source,tableShowName,tableName,colsShowName"
              + ",colsName,colsType,splitString,txtStorePath,indexStorePath,extval,status,username,"
              + "createtime,lastuptime,joins,stage,percent,resultkb");
      bufferSql.append(
          " from ("
              + strsqlJoin
              + " union "
              + strsqlDownload
              + ") tmp order by tmp.createtime desc limit "
              + start
              + ","
              + rows
              + " ");
    }
    if (type == 1) // for join
    {
      bufferSql.append(
          strsqlJoin
              + " and status='INDEX' order by createtime desc limit "
              + start
              + ","
              + rows
              + " ");
    }

    String sql = bufferSql.toString();
    TableJoin.LOG.info("getUserTables:" + sql);
    ResultSet res = stmt.executeQuery(sql);
    JSONObject jsonObj = new JSONObject();
    jsonObj.put("code", "1");
    jsonObj.put("_exehql", sql);
    JSONArray jsonArray = new JSONArray();

    while (res.next()) {
      JSONObject item = new JSONObject();

      item.put("source", res.getString("source"));
      item.put("tableShowName", res.getString("tableShowName")); // 展示名称
      item.put("tableName", res.getString("tableName")); // uuid
      item.put("colsShowName", res.getString("colsShowName"));
      item.put("colsName", res.getString("colsName"));
      item.put("colsType", res.getString("colsType"));
      item.put("splitString", res.getString("splitString"));
      item.put("txtStorePath", res.getString("txtStorePath"));
      item.put("indexStorePath", res.getString("indexStorePath"));
      item.put("extval", res.getString("extval"));
      item.put("status", res.getString("status"));
      item.put("username", res.getString("username"));
      item.put("createtime", res.getString("createtime"));
      item.put("lastuptime", res.getString("lastuptime"));
      item.put("joins", res.getString("joins"));
      item.put("stage", res.getString("stage"));
      item.put("percent", res.getString("percent"));
      item.put("resultkb", res.getString("resultkb"));
      boolean issuccess =
          res.getString("status").equals("INDEX") && res.getString("extval").equals("0");
      item.put(
          "proccess", parsePercent(res.getString("stage"), res.getString("percent"), issuccess));
      item.put("isActive", String.valueOf(issuccess));
      jsonArray.put(item);
    }
    HashMap<String, String> cnt = getUserTablesCount(username, type);

    JSONObject data = new JSONObject();
    data.put("list", jsonArray);
    data.put("total", cnt.get("cnt"));
    jsonObj.put("data", data);
    jsonObj.put("total_debug", new JSONObject(cnt));
    m_fpsql.close();

    if (callback != null && callback.length() > 0) {
      return callback + "(" + jsonObj.toString() + ")";
    } else {
      return jsonObj.toString();
    }
  }