protected void processRequest(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      Connection con =
          DriverManager.getConnection(Utility.connection, Utility.username, Utility.password);

      String email = request.getParameter("email_id");

      String number = "";
      boolean exists = false;
      String user_name = "";
      int user_id = -1;
      String str1 = "SELECT USER_ID,NAME,PHONE_NUMBER FROM USERS WHERE EMAIL_ID=?";
      PreparedStatement prep1 = con.prepareStatement(str1);
      prep1.setString(1, email);
      ResultSet rs1 = prep1.executeQuery();
      if (rs1.next()) {
        exists = true;
        user_id = rs1.getInt("USER_ID");
        user_name = rs1.getString("NAME");
        number = rs1.getString("PHONE_NUMBER");
      }
      int verification = 0;
      JSONObject data = new JSONObject();
      if (exists) {
        verification = (int) (Math.random() * 9535641 % 999999);
        System.out.println("Number " + number + "\nVerification: " + verification);
        SMSProvider.sendSMS(
            number, "Your One Time Verification Code for PeopleConnect Is " + verification);
      }

      data.put("user_name", user_name);
      data.put("user_id", user_id);
      data.put("verification_code", "" + verification);
      data.put("phone_number", number);

      String toSend = data.toJSONString();
      out.print(toSend);
      System.out.println(toSend);

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      out.close();
    }
  }
  /** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
  @SuppressWarnings("unchecked")
  protected void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {

    response.setContentType("application/json");
    response.setHeader("Cache-Control", "nocache");
    response.setCharacterEncoding("utf-8");
    PrintWriter out = response.getWriter();
    StringWriter result = new StringWriter();

    // get received JSON data from request
    BufferedReader br = new BufferedReader(new InputStreamReader(request.getInputStream()));
    String postData = "";
    if (br != null) {
      postData = br.readLine();
    }

    try {
      JSONObject json = (JSONObject) new JSONParser().parse(postData);
      JSONObject resultObj = new JSONObject();
      JSONArray list = new JSONArray();
      List<Tracking> trackingList = new ArrayList<Tracking>();

      // get the website list
      if (json.get("type").equals("websiteslist")) {
        trackingList = trackingDao.websiteList(pool);
        for (Tracking item : trackingList) {
          list.add(item.getWebsite());
        }
      }
      // render report
      else if (json.get("type").equals("submit")) {
        if (json.get("criteria").equals("date")) {
          // render repoty by date
          trackingList = trackingDao.getListByDate(pool, json.get("date").toString());
        } else if (json.get("criteria").equals("daterange")) {
          // render repoty by date range
          trackingList =
              trackingDao.getListByDateRange(
                  pool, json.get("fromdate").toString(), json.get("todate").toString());
        } else if (json.get("criteria").equals("website")) {
          // render repoty by website
          String website = (json.get("website") == null ? "" : json.get("website").toString());
          trackingList = trackingDao.getListByWebsite(pool, website);
        }

        for (Tracking item : trackingList) {
          JSONObject trackingObj = new JSONObject();
          trackingObj.put("date", item.getDate());
          trackingObj.put("website", item.getWebsite());
          trackingObj.put("visit", item.getVisit());
          list.add(trackingObj);
        }
      }
      resultObj.put("result", list);
      resultObj.writeJSONString(result);
      // finally output the json string
      out.print(result.toString());
    } catch (ParseException | SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
示例#3
0
  private String ManageSql(String[] data) {
    String result = new String();
    String initData = data[0] + DELIMITER;

    // 회원가입
    if (data[0].equals("join")) {
      String email = data[1];
      String pw = data[2];
      String lastName = data[3];
      String firstName = data[4];
      String gender = data[5];
      String locale = data[6];

      Connection connection = null;
      Statement iStmt = null;
      Statement sStmt = null;

      try {
        connection = DBManager.getConnection();
        iStmt = connection.createStatement();
        sStmt = connection.createStatement();

        String idSQL = "select * from bs_user where email = '" + email + "'";
        String insertSQL =
            "insert into bs_user values('"
                + email
                + "', '"
                + pw
                + "', '"
                + lastName
                + "', '"
                + firstName
                + "', '"
                + gender
                + "', '"
                + locale
                + "', 'normal')";

        ResultSet rs = sStmt.executeQuery(idSQL);

        while (true) {
          if (rs.next()) {
            result = initData + "no" + DELIMITER + "id_exist";
            break;
          } else {
            int count = iStmt.executeUpdate(insertSQL);
            if (count == 1) {
              System.out.println("Success");
              result = initData + "yes";
            } else {
              System.out.println("Fail");
            }
            break;
          }
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    // 로그인
    else if (data[0].equals("login")) {
      // 페이스북
      if (data[1].equals("facebook")) {
        Object o = JSONValue.parse(data[2]);
        JSONObject json = (JSONObject) o;

        String email = (String) json.get("email");
        String lastName = (String) json.get("last_name");
        String firstName = (String) json.get("first_name");
        String gender = (String) json.get("gender");
        String locale = (String) json.get("locale");

        Connection connection = null;
        Statement iStmt = null;
        Statement sStmt = null;

        try {
          connection = DBManager.getConnection();
          iStmt = connection.createStatement();
          sStmt = connection.createStatement();

          String idSQL = "select * from bs_user where email = '" + email + "'";
          String insertSQL =
              "insert into bs_user values('"
                  + email
                  + "', '', '"
                  + lastName
                  + "', '"
                  + firstName
                  + "', '"
                  + gender
                  + "', '"
                  + locale
                  + "', '"
                  + data[1]
                  + "')";

          ResultSet rs = sStmt.executeQuery(idSQL);

          while (true) {
            if (rs.next()) {
              result = initData + "no" + DELIMITER + "id_exist";
              break;
            } else {
              int count = iStmt.executeUpdate(insertSQL);
              if (count == 1) {
                System.out.println("Success");
                result = initData + "yes";
              } else {
                System.out.println("Fail");
              }
              break;
            }
          }
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
      // 일반
      else if (data[1].equals("normal")) {
        String email = data[2];
        String pw = data[3];

        Connection connection = null;
        Statement Stmt = null;

        try {
          connection = DBManager.getConnection();
          Stmt = connection.createStatement();

          String SQL = "select * from bs_user where email = '" + email + "' and pw ='" + pw + "'";

          ResultSet rs = Stmt.executeQuery(SQL);

          while (true) {
            if (rs.next()) {
              result = initData + "yes";
              break;
            } else {
              result = initData + "no";
              break;
            }
          }
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    }
    // 히스토리
    else if (data[0].equals("history")) {
      String email = data[1];
      String first_keyword = data[2];

      Connection connection = null;
      Statement stmt = null;
      Statement stmt2 = null;

      try {
        connection = DBManager.getConnection();
        stmt = connection.createStatement();
        stmt2 = connection.createStatement();

        String kSQL = "insert into bs_keyword values('" + email + "', '" + first_keyword + "')";

        int count = stmt.executeUpdate(kSQL);

        // kSQL 성공
        if (count == 1) {
          // depth/p_node_id/#node_id#keyword/^
          String line = data[3];

          // ss는 두줄이 올 경우가 있어, '^'으로 줄바꿈처리를 해준 문자열
          String ss = "";
          for (int i = 0; i < line.length(); i++) {
            char c = line.charAt(i);
            if (c == '^') {
              c = '\n';
            }
            ss += c;
          }

          String[] splitString = ss.split("/");
          String depth = splitString[0];
          String p_node_id = splitString[1];

          for (int i = 2; i < splitString.length; i++) {
            String[] nodeData = splitString[i].split("#");
            for (int j = 1; j < nodeData.length - 1; j++) {
              String node_id = nodeData[j];
              String keyword = nodeData[j + 1];

              String hSQL =
                  "insert into bs_history values('"
                      + email
                      + "', '"
                      + depth
                      + "', '"
                      + p_node_id
                      + "', '"
                      + node_id
                      + "', '"
                      + keyword
                      + "','')";

              int cnt = stmt2.executeUpdate(hSQL);

              if (cnt == 1) {
                System.out.println("hSQL 성공");
              } else {
                System.out.println("hSQL 실패");
              }
            }
          }
        }
        // kSQL 실패
        else {
          System.out.println("kSQL 실패");
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    } else {
      System.out.println("처리할 수 없는 작업입니다.");
    }
    return result;
  }
  /** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    // TODO Auto-generated method stub
    PrintWriter out = response.getWriter();
    String id = request.getParameter("hashID");
    JSONObject result = new JSONObject();

    if (id != null && id.trim().isEmpty()) {
      response.setContentType("text/plain");
      response.setStatus(400);
      out.println("Empty hash ID");
      return;
    }

    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    String password;
    try {
      // Read the SQL password from a file
      BufferedReader reader = null;
      try {
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("SQLpw.txt");
        reader = new BufferedReader(new InputStreamReader(inputStream));
        password = reader.readLine();
      } catch (NullPointerException e) {
        e.getStackTrace();
        password = "";
      }

      // create a mysql database connection
      String myDriver = "com.mysql.jdbc.Driver";
      String myUrl = "jdbc:mysql://localhost/lodstories";
      Class.forName(myDriver);
      conn = DriverManager.getConnection(myUrl, "root", password);
      st = conn.createStatement();
      rs = st.executeQuery("SELECT hash,title,author FROM hash_objects where id='" + id + "'");

      if (!rs.next()) {
        response.setContentType("text/plain");
        response.setStatus(400);
        out.println("Error retrieving hash object");
        return;
      }

      result.put("hash", rs.getString("hash"));
      result.put("title", rs.getString("title"));
      result.put("author", rs.getString("author"));
      // result.put("path", rs.getString("path"));
      // result.put("rating", rs.getInt("rating"));

      // Update the lastAccessed field
      st.executeUpdate(
          "UPDATE hash_objects SET lastAccessed=CURRENT_TIMESTAMP() WHERE id='" + id + "'");

      response.setContentType("application/json");

      response.setCharacterEncoding("UTF-8");
      out.println(result);
    } catch (ClassNotFoundException e) {
      System.err.println("Could not connect to driver!");
      System.err.println(e.getMessage());

    } catch (SQLException ex) {
      System.err.println(
          "SQLException: "
              + ex.getMessage()
              + ", SQLState: "
              + ex.getSQLState()
              + "VendorError: "
              + ex.getErrorCode());
    } catch (JSONException ex) {
      ex.printStackTrace();
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException ex) {
          ex.printStackTrace();
        }
      }
      if (st != null) {
        try {
          st.close();
        } catch (SQLException ex) {
          ex.printStackTrace();
        }
      }
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException ex) {
          ex.printStackTrace();
        }
      }
    }
  }