예제 #1
0
 @WebMethod(operationName = "delete")
 public int delete(@WebParam(name = "qid") int qid) {
   Connection conn = null;
   PreparedStatement ps = null;
   int res = -1;
   try {
     // new com.mysql.jdbc.Driver();
     Class.forName("com.mysql.jdbc.Driver").newInstance();
     // conn =
     // DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename?user=username&password=password");
     String connectionUrl = "jdbc:mysql://localhost:3306/stackexchange";
     String connectionUser = "******";
     String connectionPassword = "";
     conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
     ps = conn.prepareStatement("delete from question where id = ?;");
     ps.setInt(1, qid);
     ps.executeUpdate();
     ps = conn.prepareStatement("delete from uservote where category = 'q' and id = ?;");
     ps.setInt(1, qid);
     res = ps.executeUpdate();
     ps = conn.prepareStatement("select id from Answer where q_id = ?");
     ps.setInt(1, qid);
     ResultSet rs = ps.executeQuery();
     while (rs.next()) {
       ps = conn.prepareStatement("delete from uservote where id = ? and category = 'a';");
       ps.setInt(1, rs.getInt("id"));
       res = ps.executeUpdate();
     }
     ps = conn.prepareStatement("delete from Answer where q_id = ?;");
     ps.setInt(1, qid);
     res = ps.executeUpdate();
   } catch (ClassNotFoundException
       | InstantiationException
       | IllegalAccessException
       | SQLException e) {
   } finally {
     try {
       if (ps != null) ps.close();
     } catch (SQLException e) {
     }
     try {
       if (conn != null) conn.close();
     } catch (SQLException e) {
     }
   }
   return res;
 }
예제 #2
0
  @WebMethod(operationName = "update")
  public int update(
      @WebParam(name = "id") int id,
      @WebParam(name = "token") String token,
      @WebParam(name = "user-agent") String ua,
      @WebParam(name = "ip") String ip,
      @WebParam(name = "topic") String topic,
      @WebParam(name = "content") String content) {

    Connection conn = null;
    PreparedStatement ps = null;
    int res = -1;
    InformationToken it = new InformationToken();
    String email = it.getEmail(token, ua, ip);
    if (it.getStatus() != 200) {
      return -1 * it.getStatus();
    } else {
      try {
        // new com.mysql.jdbc.Driver();
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        // conn =
        // DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename?user=username&password=password");
        String connectionUrl = "jdbc:mysql://localhost:3306/stackexchange";
        String connectionUser = "******";
        String connectionPassword = "";
        conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
        ps = conn.prepareStatement("select name from user where email = ?");
        ps.setString(1, email);
        ResultSet rs = ps.executeQuery();
        rs.next();
        String name = rs.getString("name");
        ps =
            conn.prepareStatement(
                "update question set name = ?, email = ?, topic = ?, content = ? where id = ?;");
        ps.setString(1, name);
        ps.setString(2, email);
        ps.setString(3, topic);
        ps.setString(4, content);
        ps.setInt(5, id);
        res = ps.executeUpdate();
      } catch (ClassNotFoundException
          | InstantiationException
          | IllegalAccessException
          | SQLException e) {

      } finally {
        try {
          if (ps != null) ps.close();
        } catch (SQLException e) {
        }
        try {
          if (conn != null) conn.close();
        } catch (SQLException e) {
        }
      }
      return res;
    }
  }
예제 #3
0
  @WebMethod(operationName = "getQuestionById")
  @WebResult(name = "Question")
  public Question getQuestionById(@WebParam(name = "qid") int qid) throws Exception {
    // TODO write your implementation code here:
    Question Q = new Question();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
      // new com.mysql.jdbc.Driver();
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      // conn =
      // DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename?user=username&password=password");
      String connectionUrl = "jdbc:mysql://localhost:3306/stackexchange";
      String connectionUser = "******";
      String connectionPassword = "";
      conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
      ps = conn.prepareStatement("select * from question where id = ?");
      ps.setInt(1, qid);
      rs = ps.executeQuery();
      while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        String topic = rs.getString("topic");
        String content = rs.getString("content");
        int vote = rs.getInt("vote");

        Q = new Question(id, name, email, topic, content, vote);
      }
    } catch (ClassNotFoundException
        | InstantiationException
        | IllegalAccessException
        | SQLException e) {
      throw e;
    } finally {
      if (rs != null) rs.close();
      if (ps != null) ps.close();
    }
    return Q;
  }
예제 #4
0
  /**
   * @param qid
   * @return
   */
  @WebMethod(operationName = "getVote")
  public int getVote(@WebParam(name = "qid") int qid) {
    int vote = -1;
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      // new com.mysql.jdbc.Driver();
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      // conn =
      // DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename?user=username&password=password");
      String connectionUrl = "jdbc:mysql://localhost:3306/stackexchange";
      String connectionUser = "******";
      String connectionPassword = "";
      conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
      ps = conn.prepareStatement("select vote from question where id = ?");
      ps.setInt(1, qid);
      ResultSet rs = null;
      rs = ps.executeQuery();
      rs.next();
      vote = rs.getInt("vote");

    } catch (ClassNotFoundException
        | InstantiationException
        | IllegalAccessException
        | SQLException e) {
    } finally {
      try {
        if (ps != null) ps.close();
      } catch (SQLException e) {
      }
      try {
        if (conn != null) conn.close();
      } catch (SQLException e) {
      }
    }
    return vote;
  }
예제 #5
0
 @WebMethod(operationName = "getNumAnswer")
 public int getNumAnswer(@WebParam(name = "id") int id) {
   Connection conn = null;
   PreparedStatement ps = null;
   ResultSet rs = null;
   int res = -1;
   try {
     // new com.mysql.jdbc.Driver();
     Class.forName("com.mysql.jdbc.Driver").newInstance();
     // conn =
     // DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename?user=username&password=password");
     String connectionUrl = "jdbc:mysql://localhost:3306/stackexchange";
     String connectionUser = "******";
     String connectionPassword = "";
     conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
     ps = conn.prepareStatement("SELECT COUNT(id) AS numAns FROM answer where q_id = ?");
     ps.setInt(1, id);
     rs = ps.executeQuery();
     rs.next();
     res = rs.getInt("numAns");
   } catch (ClassNotFoundException
       | InstantiationException
       | IllegalAccessException
       | SQLException e) {
   } finally {
     try {
       if (ps != null) ps.close();
     } catch (SQLException e) {
     }
     try {
       if (conn != null) conn.close();
     } catch (SQLException e) {
     }
   }
   return res;
 }
예제 #6
0
  @WebMethod(operationName = "vote")
  public int vote(
      @WebParam(name = "id") int id,
      @WebParam(name = "token") String token,
      @WebParam(name = "user-agent") String ua,
      @WebParam(name = "ip") String ip,
      @WebParam(name = "value") int val) {
    Connection conn = null;
    PreparedStatement ps = null;
    int executeUpdate = -2;
    InformationToken it = new InformationToken();
    String mail = it.getEmail(token, ua, ip);
    if (it.getStatus() != 200) {
      return -1 * it.getStatus();
    }
    try {
      // new com.mysql.jdbc.Driver();
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      // conn =
      // DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename?user=username&password=password");
      String connectionUrl = "jdbc:mysql://localhost:3306/stackexchange";
      String connectionUser = "******";
      String connectionPassword = "";
      conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
      ps =
          conn.prepareStatement(
              "select * from uservote where id_mail = ? and category = 'q' and id = ?");
      ps.setString(1, mail);
      ps.setInt(2, id);
      ResultSet rs1 = ps.executeQuery();

      if (rs1.next()) {

        return -1;
      } else {
        ps = conn.prepareStatement("insert into uservote values(?,'q',?)");
        ps.setString(1, mail);
        ps.setInt(2, id);
        ps.executeUpdate();
        ps = conn.prepareStatement("select vote from question where id = ?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        rs.next();
        int currentVote = rs.getInt("vote");
        ps = conn.prepareStatement("update question set vote = ? where id = ?");
        ps.setInt(1, currentVote + val);
        ps.setInt(2, id);
        executeUpdate = ps.executeUpdate();
      }
    } catch (ClassNotFoundException
        | InstantiationException
        | IllegalAccessException
        | SQLException e) {
      return 0;
    } finally {
      try {
        if (ps != null) ps.close();
      } catch (SQLException e) {
      }
      try {
        if (conn != null) conn.close();
      } catch (SQLException e) {
      }
    }
    return executeUpdate;
  }