public static void addJsonList(String json, String user) throws Exception { JSONObject jsonObj = new JSONObject(json); JSONArray list = jsonObj.getJSONObject("data").getJSONArray("users"); for (int i = 0; i < list.length(); i++) { JSONObject obj = list.getJSONObject(i); addjson(obj.toString(), user); } }
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(); }
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(); } }