示例#1
1
  /**
   * Tests the implementation of getCharacterStream(long pos, long length).
   *
   * @throws Exception
   */
  public void testGetCharacterStreamLong() throws Exception {
    String str1 = "This is a test String. This is a test String";

    Reader r1 = new java.io.StringReader(str1);

    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, r1);
    ps.execute();
    ps.close();

    Statement st = createStatement();

    ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
    rs.next();
    Clob clob = rs.getClob(1);

    Reader r_1 = clob.getCharacterStream(2L, 5L);
    String str2 = str1.substring(1, 6);
    Reader r_2 = new java.io.StringReader(str2);

    assertEquals(r_2, r_1);

    rs.close();
    st.close();
  }
示例#2
0
  /**
   * This method queries the database to get the Invoice Number associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public long getInvID(String inv_no) {
    String query;
    long invID = 0;
    Statement stmt = null;
    ResultSet rs = null;

    query = "select inv_id from inv where inv_no=" + inv_no;

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        invID = rs.getLong(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvview: The Invoice ID not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return (invID);
  }
示例#3
0
  /**
   * This method queries the database to get the Invoice Number associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getInvno(String invid) {
    String query;
    String invno = "";
    Statement stmt = null;
    ResultSet rs = null;

    query = "select inv_no from rhcc_inv where rhcc_inv_id=" + invid;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        invno = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return invno;
  }
示例#4
0
  /**
   * This method queries the database to get the SPIN associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getSPINname(String invid) {
    String query;
    String spinnm = "";
    Statement stmt = null;
    ResultSet rs = null;

    //		query =  "select slc_nm from usw_co,inv where uc_id=uc_id_fk and inv_id="+invid;
    query =
        "select srv_provr_nm from srv_provr,rhcc_inv where spin = rhcc_inv.spin_fk and rhcc_inv_id="
            + invid;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        spinnm = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: The SPIN Name not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return spinnm;
  }
示例#5
0
  /**
   * This method queries the database to get the sequence for the BP_ID.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getBpid() {
    String query;
    String bpid = "";
    Statement stmt = null;
    ResultSet rs = null;

    query = "select bp_id_seq.nextval from dual ";

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        bpid = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Sequence Value for BP_ID not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return bpid;
  }
示例#6
0
  /**
   * This method queries the database to validate the passed in FRN for the selected Funding Year
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean validateFRN(String frn, String year) {
    String query;
    boolean validfrn = false;
    Statement stmt = null;
    ResultSet rs = null;

    query = "select wo_id from wrk_ordr where wrk_ordr_no='" + frn + "' and yr_fk=" + year;

    USFEnv.getLog().writeCrit("RhccDinvview: FRN query " + query, this, null);
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        validfrn = true;
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: FRN not valid for the Year ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return false;
  }
示例#7
0
  /**
   * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
   *
   * @param request servlet request
   * @param response servlet response
   * @throws ServletException if a servlet-specific error occurs
   * @throws IOException if an I/O error occurs
   */
  protected void processRequest(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    String username = request.getParameter("username");
    String password = request.getParameter("password");
    Statement stmt;
    ResultSet rs;
    Connection con = null;

    try {
      Class.forName("com.mysql.jdbc.Driver");
      String connectionUrl = "jdbc:mysql://localhost/myflickr?" + "user=root&password=123456";
      con = DriverManager.getConnection(connectionUrl);

      if (con != null) {
        System.out.println("connected to mysql");
      }
    } catch (SQLException e) {
      System.out.println("SQL Exception: " + e.toString());
    } catch (ClassNotFoundException cE) {
      System.out.println("Class Not Found Exception: " + cE.toString());
    }

    try {
      stmt = con.createStatement();

      System.out.println("SELECT * FROM flickrusers WHERE name='" + username + "'");
      rs = stmt.executeQuery("SELECT * FROM flickrusers WHERE name='" + username + "'");

      while (rs.next()) {

        if (rs.getObject(1).toString().equals(username)) {

          out.println("<h1>To username pou epileksate uparxei hdh</h1>");
          out.println("<a href=\"project3.html\">parakalw dokimaste kapoio allo.</a>");

          stmt.close();
          rs.close();
          return;
        }
      }
      stmt.close();
      rs.close();

      stmt = con.createStatement();

      if (!stmt.execute("INSERT INTO flickrusers VALUES('" + username + "', '" + password + "')")) {
        out.println("<h1>Your registration is completed  " + username + "</h1>");
        out.println("<a href=\"index.jsp\">go to the login menu</a>");
        registerListener.Register(username);
      } else {
        out.println("<h1>To username pou epileksate uparxei hdh</h1>");
        out.println("<a href=\"project3.html\">Register</a>");
      }
    } catch (SQLException e) {
      throw new ServletException("Servlet Could not display records.", e);
    }
  }
示例#8
0
  boolean validar(String busqueda, String Tabla, String llave_consulta, String llave_busqueda)
      throws IOException, SQLException {

    Connection con = null;
    ResultSet resultadosConsulta = null;
    Statement instruccionSQL = null;
    try {

      con = DriverManager.getConnection("jdbc:mysql://localhost/rsu_inventario", "root", "inforsu");

      // Preparamos la consulta

      instruccionSQL = con.createStatement();

      resultadosConsulta =
          instruccionSQL.executeQuery(
              "SELECT "
                  + busqueda
                  + " FROM "
                  + Tabla
                  + " WHERE "
                  + llave_consulta
                  + " = '"
                  + llave_busqueda
                  + "'");

      if (resultadosConsulta
          .next()) { // si es valido el primer reg. hay una fila, tons el usuario y su pw existen
        con.close();
        resultadosConsulta.close();
        instruccionSQL.close();
        ;
        return true;
      } // usuario validado correctamente
      else {
        con.close();
        resultadosConsulta.close();
        instruccionSQL.close();
        return false;
      } // usuario validado incorrectamente

    } catch (Exception e) {

      e.printStackTrace();
      con.close();
      resultadosConsulta.close();
      instruccionSQL.close();
      return false;
    }
  }
示例#9
0
  public void carga_default() {

    Connection con = null;
    Statement st = null;
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      con = DriverManager.getConnection("jdbc:mysql://localhost/rsu_inventario", "root", pass);
      try {
        st = con.createStatement();
        st.executeUpdate(
            "INSERT INTO ADMIN VALUES ('174183767','msX','msx','estudiante','santiago',"
                + "'2130','3030303','30303030','los valdios 3030','*****@*****.**',12345);");

      } catch (SQLException s) {

        SQL_FORM error = new SQL_FORM();
        JOptionPane.showMessageDialog(error, "error al ingresar datos");

        // SQL_FORM error = new SQL_FORM();
        JOptionPane.showMessageDialog(error, s, "alert", JOptionPane.ERROR_MESSAGE);

      } finally {
        con.close();
        st.close();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
示例#10
0
  /* return status codes for account/device */
  public static int[] getStatusCodes(String accountID, String deviceID) throws DBException {

    /* account-id specified? */
    if (StringTools.isBlank(accountID)) {
      return new int[0];
    }

    /* device-id specified? */
    if (StringTools.isBlank(deviceID)) {
      deviceID = ALL_DEVICES;
    }

    /* select */
    // DBSelect: SELECT statucCode FROM StatusCode WHERE (accountID='acct') AND (deviceID='*') ORDER
    // BY statucCode
    DBSelect<StatusCode> dsel = new DBSelect<StatusCode>(StatusCode.getFactory());
    dsel.setSelectedFields(StatusCode.FLD_statusCode);
    DBWhere dwh = dsel.createDBWhere();
    dsel.setWhere(
        dwh.WHERE_(
            dwh.AND(
                dwh.EQ(StatusCode.FLD_accountID, accountID),
                dwh.EQ(StatusCode.FLD_deviceID, deviceID))));
    dsel.setOrderByFields(StatusCode.FLD_statusCode);

    /* get list */
    java.util.List<Integer> codeList = new Vector<Integer>();
    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = DBConnection.getDefaultConnection().execute(dsel.toString());
      rs = stmt.getResultSet();
      while (rs.next()) {
        int code = rs.getInt(StatusCode.FLD_statusCode);
        codeList.add(new Integer(code));
      }
    } catch (SQLException sqe) {
      throw new DBException("Getting StatusCode List", sqe);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (Throwable t) {
        }
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (Throwable t) {
        }
      }
    }

    /* return array of status codes */
    int codeListInt[] = new int[codeList.size()];
    for (int i = 0; i < codeListInt.length; i++) {
      codeListInt[i] = codeList.get(i).intValue();
    }
    return codeListInt;
  }
  //
  // Examine BLOBs and CLOBs.
  //
  private void vetLargeObjects(
      Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
      throws Exception {
    Statement stmt = conn.createStatement();

    stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))");
    stmt.execute(
        "INSERT INTO t (id, b, c) VALUES (1, "
            + "CAST ("
            + TestUtil.stringToHexLiteral("101010001101")
            + "AS BLOB(10)), CAST ('hello' AS CLOB(10)))");

    ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t");

    rs.next();

    Blob blob = rs.getBlob(2);
    Clob clob = rs.getClob(3);

    vetObject(blob, unsupportedList, notUnderstoodList);
    vetObject(clob, unsupportedList, notUnderstoodList);

    stmt.close();
    conn.rollback();
  }
示例#12
0
  /**
   * Test that <code>Clob.getCharacterStream(long,long)</code> works on CLOBs that are streamed from
   * store. (DERBY-2891)
   */
  public void testGetCharacterStreamLongOnLargeClob() throws Exception {
    getConnection().setAutoCommit(false);

    // create large (>32k) clob that can be read from store
    final int size = 33000;
    StringBuilder sb = new StringBuilder(size);
    for (int i = 0; i < size; i += 10) {
      sb.append("1234567890");
    }

    final int id = BlobClobTestSetup.getID();
    PreparedStatement ps =
        prepareStatement("insert into blobclob(id, clobdata) values (?,cast(? as clob))");
    ps.setInt(1, id);
    ps.setString(2, sb.toString());
    ps.executeUpdate();
    ps.close();

    Statement s = createStatement();
    ResultSet rs = s.executeQuery("select clobdata from blobclob where id = " + id);
    assertTrue(rs.next());
    Clob c = rs.getClob(1);

    // request a small region of the clob
    BufferedReader r = new BufferedReader(c.getCharacterStream(4L, 3L));
    assertEquals("456", r.readLine());

    r.close();
    c.free();
    rs.close();
    s.close();
    rollback();
  }
  private static void addCourse(Connection conn, String stu_no) throws SQLException {
    String cno = readEntry("Course number : ");
    String sno = readEntry("Section number : ");

    String course_query =
        "SELECT Section_identifier FROM SECTION WHERE Section_identifier="
            + sno
            + " AND Course_number='"
            + cno
            + "' AND Semester='Spring' AND YEAR=TO_DATE('2007','YYYY')";
    // System.out.println(course_query);
    Statement s1 = conn.createStatement();
    ResultSet r1 = s1.executeQuery(course_query);
    if (r1.next()) {
      String addc =
          "INSERT INTO GRADE_REPORT (Student_number, Section_identifier, Grade) VALUES ("
              + stu_no
              + ", "
              + sno
              + ", 'NULL')";
      Statement s2 = conn.createStatement();
      System.out.println(addc);
      try {
        s2.executeUpdate(addc);
        System.out.println("Enrollment successfull");
      } catch (SQLException e) {
        System.out.println("Cannot add course into table");
      }
    } else {
      System.out.println("Course not found");
    }
    s1.close();
  }
示例#14
0
  public String checkValidLogin(String myUserName, String myPW) {

    try {
      Class.forName(javaSQLDriverPath);
      Connection conn =
          (Connection) DriverManager.getConnection(ConnectionPath, ConnectionUser, ConnectionPW);
      Statement st = conn.createStatement();
      String query = "Select * from User";

      ResultSet rs = st.executeQuery(query);
      while (rs.next()) {
        // return rs.getString("Username");
        if (myUserName.equals(rs.getString("Username"))) {
          if (myPW.equals(rs.getString("Password"))) {
            setUserVariables(myUserName);
            return "success";
          } else {
            return "wrongPassword";
          }
        }
      }

      rs.close();
      st.close();
      conn.close();

      return "userNotFound";
    } catch (Exception e) {

      return e.getMessage();
    }
  }
  /**
   * Gets a list of courses that belongs to an instructor. Throws InvalidDBRequestException if any
   * error occured to the database connection.
   *
   * @param name the instructor's user name
   * @return a vector containing the list of courses
   * @throws InvalidDBRequestException
   */
  public Vector getCourseList(String name) throws InvalidDBRequestException {
    Vector courseList = new Vector();

    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      // get the course list
      rs =
          stmt.executeQuery(
              "select course_num, course_name from course where instructor = '"
                  + name
                  + "' order by course_num");
      while (rs.next()) courseList.add(rs.getString(1) + " - " + rs.getString(2));

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in getCourseList: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Server Error");
    }

    return courseList;
  }
  /**
   * Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
   * student's directory which relates to the course. Caution: vizualisation file will be deleted
   * eventhough it also relates to another course if the student is also registered to that course.
   * (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
   * error occured during deletion, or other exception occured.
   *
   * @param username student's user name
   * @param courseID course id (course number + instructor name)
   * @throws InvalidDBRequestException
   */
  public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      int count = 0;

      // check if student registered to the course
      rs =
          stmt.executeQuery(
              "select * from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '******'");
      if (!rs.next())
        throw new InvalidDBRequestException("Student is not registered to the course");

      // remove student from the course
      count =
          stmt.executeUpdate(
              "delete from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '******'");
      if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");

      // delete the quiz visualization files
      rs =
          stmt.executeQuery(
              "select distinct unique_id, s.test_name from scores s, courseTest t "
                  + "where s.test_name = t.test_name "
                  + "and course_id = '"
                  + courseID
                  + "' "
                  + "and user_login = '******'");
      while (rs.next()) {
        deleteVisualization(rs.getString(1), username, rs.getString(2));
        count =
            stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addstudent: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
  /**
   * Deletes an instructor from the database. Deletes the instructor's courses by invoking the
   * deleteCourse method. Throws InvalidDBRequestException if instructor not in the database or
   * other database connection problems.
   *
   * @see deleteCourse
   * @param instructor instructor's user name
   * @throws InvalidDBRequestException
   */
  public void deleteInstructor(String instructor) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      int count;

      // delete the instructor's courses
      ResultSet rs =
          stmt.executeQuery(
              "select course_num from course where instructor = '" + instructor + "'");
      while (rs.next()) deleteCourse(rs.getString(1).trim(), instructor);

      // delete the instructor's record
      count = stmt.executeUpdate("delete from instructor where login ='******'");

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
  /**
   * Deletes students who are older than a certain number of years and not registered to any course.
   *
   * @param year students older than year are candidates to be deleted
   * @throws InvalidDBRequestException
   */
  public void deleteOldStudent(int year) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();

      // query all student who have been in the database longer than a number of years and not
      // registered to any course
      ResultSet rs =
          stmt.executeQuery(
              "select login, count(course_id) "
                  + "from student s left join courseRoster r on login = user_login "
                  + "where date_entered < SUBDATE(now(), INTERVAL "
                  + new String().valueOf(year).trim()
                  + " YEAR) "
                  + "group by login, date_entered");
      // delete them
      while (rs.next()) if (rs.getInt(2) == 0) purgeStudent(rs.getString(1).trim());

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
示例#19
0
  private synchronized void init() throws SQLException {
    if (isClosed) return;

    // do tables exists?
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(TABLE_NAMES_SELECT_STMT);

    ArrayList<String> missingTables = new ArrayList(TABLES.keySet());
    while (rs.next()) {
      String tableName = rs.getString("name");
      missingTables.remove(tableName);
    }

    for (String missingTable : missingTables) {
      try {
        Statement createStmt = conn.createStatement();
        // System.out.println("Adding table "+ missingTable);
        createStmt.executeUpdate(TABLES.get(missingTable));
        createStmt.close();

      } catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
      }
    }
  }
  /**
   * Check the user's name and password and verify that the user is an instructor. Throws
   * InvalidDBRequestException if user is not an instructor, wrong password, or if any error occured
   * to the database connection.
   *
   * @param name user's user name
   * @param pass user's password
   * @throws InvalidDBRequestException
   */
  public void instructorLogin(String name, String pass) throws InvalidDBRequestException {
    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      // check if instructor
      rs = stmt.executeQuery("select password from instructor where login = '******'");
      if (!rs.next()) {
        if (debug) System.out.println("User not found in the instructor table");
        throw new InvalidDBRequestException("Instructor not registered");
      }

      // check password
      if (!rs.getString(1).equals(pass)) {
        if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"Invalid SQL in instructor login: "******"???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Server Error");
    }
  }
示例#21
0
 public void dispose() {
   try {
     stmt.close();
     con.close();
   } catch (SQLException e) {
   }
 }
  /**
   * Delete a student from the database. Also deletes the student's folders. Throws
   * InvalidDBRequestException if any error in database connection.
   *
   * @param username student's user name
   * @throws InvalidDBRequestException
   */
  private void purgeStudent(String username) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      int count;

      // delete from scores
      count = stmt.executeUpdate("delete from scores where user_login = '******'");

      // delete from student
      count = stmt.executeUpdate("delete from student where login = '******'");

      // delete student's folder
      File studentDir = new File("./StudentQuizzes/" + username);
      if (!(studentDir.delete())) {
        System.err.println("Error in deleting folder for student: " + username);
      }

      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
  /**
   * Query a quiz list from the database. If there is no student user name specified, the list will
   * contain all quizzes that are used in the instructor's courses. Otherwise, the list will contain
   * all quizzes that the student has taken and from the instructor's courses which the student is
   * registered. Throws InvalidDBRequestException if any error occured to the database connection.
   *
   * @param instructor instructor's user name
   * @param student student's user name. Can be empty to get a list of all quizzes in the
   *     instructor's courses.
   * @return a vector containing the list of quizzes
   * @throws InvalidDBRequestException
   */
  public Vector getQuizList(String instructor, String student) throws InvalidDBRequestException {
    Vector list = new Vector();

    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      if (!student.equals("")) {
        // get the list that contains all quizzes that the student has taken and from the
        // instructor's courses which the student is registered
        rs =
            stmt.executeQuery(
                "select courseTest.test_name, scores.start_time from scores, courseTest, course "
                    + "where courseTest.test_name = scores.test_name "
                    + "and courseTest.course_id = course.course_id "
                    + "and instructor = '"
                    + instructor
                    + "' and user_login = '******' "
                    + "order by scores.start_time");

        while (rs.next()) {
          list.add(rs.getString(1) + " <" + rs.getString(2) + ">");
        }
      } else {
        // get the list that contains all quizzes that are used in the instructor's courses
        rs =
            stmt.executeQuery(
                "select test_name from courseTest, course "
                    + "where courseTest.course_id = course.course_id "
                    + "and instructor = '"
                    + instructor
                    + "' ");

        while (rs.next()) {
          list.add(rs.getString(1));
        }
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in getQuizList: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }

    return list;
  }
示例#24
0
  /** private method which actually will do all of our work for the sample */
  private void executeSample() {

    String query = "select anEmployee from staff2";
    try {
      Statement stmt = _con.createStatement();
      ;

      // Execute the query which will return an Employee object
      // We will cast this using the Person interface. Note the
      // Person interface class MUST be in your CLASSPATH. You
      // Do not need Employee in your CLASSPATH.
      ResultSet rs = stmt.executeQuery(query);

      output("***Using interface class\n");
      while (rs.next()) {
        Person aPerson = (Person) rs.getObject(1);
        displayMethods(aPerson.getClass());
        output(
            "The person is: "
                + aPerson.toString()
                + "\nFirst Name= "
                + aPerson.getFirstName()
                + "\nLast Name= "
                + aPerson.getLastName()
                + "\n");
      }
      // Now execute the same query, but this time we will use
      // reflection to access the class.  Again, only the interface
      // Person is required in the CLASSPATH
      rs = stmt.executeQuery(query);

      output("***Using reflection\n");
      Object theObj = null;
      while (rs.next()) {
        theObj = rs.getObject(1);
        output("The person is: " + theObj.toString() + "\n");
        Class theClass = theObj.getClass();
        displayMethods(theClass);
        Method m1 = theClass.getMethod("toString", new Class[0]);
        Method m2 = theClass.getMethod("getFirstName", new Class[0]);
        Method m3 = theClass.getMethod("getLastName", new Class[0]);
        output(
            "The person is: "
                + (Object) m1.invoke(theObj, new Object[0])
                + "\nFirst Name= "
                + (Object) m2.invoke(theObj, new Object[0])
                + "\nLast Name= "
                + (Object) m3.invoke(theObj, new Object[0])
                + "\n");
      }
      rs.close();
      stmt.close();
    } catch (SQLException sqe) {
      displaySQLEx(sqe);
    } catch (Exception e) {
      error("Unexpected exception : " + e.toString() + "\n");
      e.printStackTrace();
    }
  }
  /**
   * Obtains information on a quiz that a student has taken, which includes the student's answer to
   * the quiz, a unique id of the instance, number of questions in the quiz, number of questions
   * answered correctly, and vizualisation type of the quiz. Throws InvalidDBRequestException if
   * cannot find the record of the stuent taking the quiz, quiz is not registered in the database,
   * or if any error occured to the database connection.
   *
   * @param student student's user name
   * @param quiz quiz name
   * @param startTime the time the student started the quiz
   * @return a string tokenizer containing: answer,uniqueID, numQuestions, numCorrect, and
   *     visualType. It uses "@" as the delimiter.
   * @throws InvalidDBRequestException
   */
  public StringTokenizer getAnswerAndVisualType(String student, String quiz, String startTime)
      throws InvalidDBRequestException {
    String answer;
    String uniqueID;
    String numQuestions;
    String numCorrect;
    String visualType;
    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      // get student's answer, unique id, number of questions, and number of questions answered
      // correctly
      rs =
          stmt.executeQuery(
              "select transcript, unique_id, num_questions, num_correct from scores "
                  + "where test_name = '"
                  + quiz
                  + "' and user_login = '******' and start_time = '"
                  + startTime
                  + "'");
      if (!rs.next()) throw new InvalidDBRequestException("Student has not taken the quiz");
      else {
        answer = rs.getString(1).trim();
        uniqueID = rs.getString(2).trim();
        numQuestions = rs.getString(3).trim();
        numCorrect = rs.getString(4).trim();
      }

      // get quiz vizualisation type
      rs = stmt.executeQuery("select visual_type from test where name = '" + quiz + "' ");
      if (!rs.next())
        throw new InvalidDBRequestException(
            "Quiz was not found! Can't retrieve visualization type.");
      else {
        visualType = rs.getString(1);
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in getAnswerAndVisualType: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
    return new StringTokenizer(
        answer + "@" + uniqueID + "@" + numQuestions + "@" + numCorrect + "@" + visualType, "@");
  }
示例#26
0
  public ExptLocatorTree(Genome g) {
    super();
    try {
      java.sql.Connection c = DatabaseFactory.getConnection(ExptLocator.dbRole);
      int species = g.getSpeciesDBID();
      int genome = g.getDBID();

      Statement s = c.createStatement();
      ResultSet rs = null;

      rs =
          s.executeQuery(
              "select e.name, e.version from experiment e, exptToGenome eg where e.active=1 and "
                  + "e.id=eg.experiment and eg.genome="
                  + genome);
      while (rs.next()) {
        String name = rs.getString(1);
        String version = rs.getString(2);
        ChipChipLocator loc = new ChipChipLocator(g, name, version);
        this.addElement(loc.getTreeAddr(), loc);
      }
      rs.close();

      rs =
          s.executeQuery(
              "select ra.name, ra.version from rosettaanalysis ra, rosettaToGenome rg where "
                  + "ra.id = rg.analysis and ra.active=1 and rg.genome="
                  + genome);
      while (rs.next()) {
        String name = rs.getString(1);
        String version = rs.getString(2);
        MSPLocator msp = new MSPLocator(g, name, version);
        this.addElement(msp.getTreeAddr(), msp);
      }
      rs.close();

      rs =
          s.executeQuery(
              "select ra.name, ra.version from bayesanalysis ra, bayesToGenome rg where "
                  + "ra.id = rg.analysis and ra.active=1 and rg.genome="
                  + genome);
      while (rs.next()) {
        String name2 = rs.getString(1);
        String version2 = rs.getString(2);
        ExptLocator loc2 = new BayesLocator(g, name2, version2);
        addElement(loc2.getTreeAddr(), loc2);
      }
      rs.close();
      s.close();

      DatabaseFactory.freeConnection(c);
    } catch (SQLException se) {
      se.printStackTrace(System.err);
      throw new RuntimeException(se);
    } catch (UnknownRoleException e) {
      e.printStackTrace();
    }
  }
示例#27
0
  public void connectItems(String query)
        // PRE:  query must be initialized
        // POST: Updates the list of Items based on the query.
      {
    String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
    String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
    String user = "******"; // Username for db
    String pass = "******"; // Password for db
    Connection myConnection; // Connection obj to db
    Statement stmt; // Statement to execute a result appon
    ResultSet results; // A set containing the returned results
    int rowcount; // Num objects in the resultSet
    int i; // Index for the array

    try { // Try connection to db

      Class.forName(driver).newInstance(); // Create our db driver

      myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection

      stmt =
          myConnection.createStatement(
              ResultSet.TYPE_SCROLL_INSENSITIVE,
              ResultSet.CONCUR_UPDATABLE); // Create a new statement
      results = stmt.executeQuery(query); // Store the results of our query

      rowcount = 0;
      if (results.last()) // Go to the last result
      {
        rowcount = results.getRow();
        results.beforeFirst();
      }
      itemsArray = new Item[rowcount];

      i = 0;
      while (results.next()) // Itterate through the results set
      {
        itemsArray[i] =
            new Item(
                results.getInt("item_id"),
                results.getString("item_name"),
                results.getString("item_type"),
                results.getInt("price"),
                results.getInt("owner_id"),
                results.getString("item_path")); // Creat new Item
        i++;
      }

      results.close(); // Close the ResultSet
      stmt.close(); // Close the statement
      myConnection.close(); // Close the connection to db

    } catch (Exception e) // Cannot connect to db
    {
      System.err.println(e.toString());
      System.err.println("Error, something went horribly wrong in connectItems!");
    }
  }
  public static void main(String args[]) throws SQLException {

    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("Could not load driver");
    }

    String user, pass;
    user = readEntry("userid : ");
    pass = readEntry("password: "******"jdbc:oracle:thin:@csoracle.utdallas.edu:1521:student", user, pass);
    boolean flag = false;
    while (flag == false) {
      // Check if student data exists
      String stu_no = readEntry("\n\nEnter student number : ");
      String query = "SELECT Name FROM STUDENT WHERE Student_number = " + stu_no;
      Statement s = conn.createStatement();
      ResultSet r = s.executeQuery(query);

      if (r.next()) {
        System.out.println("Student data found");
      } else {
        System.out.println("Student not found. Please try again");
      }
      s.close();

      System.out.println(
          "\n\n\n\n\n------------------------Enrollment for Spring 2007--------------------------");
      System.out.println("1) Add a class");
      System.out.println("2) Drop a class");
      System.out.println("3) See my schedule");
      String ch = readEntry("Enter your choice : ");

      switch (ch.charAt(0)) {
        case '1':
          System.out.println("\n\n--------Add a class--------");
          addCourse(conn, stu_no);
          break;
        case '2':
          System.out.println("\n\n--------Drop a class--------");
          dropCourse(conn, stu_no);
          break;
        case '3':
          System.out.println("\n\n--------Your Schedule--------");
          viewSchedule(conn, stu_no);
          break;
        default:
          flag = true;
      }
    }
    conn.close();
  }
  private void loadNewDict(String sql) {
    Debug.getAppErrLogger().debug("------------------------------------------------load:" + sql);

    HashMap htemp = new HashMap();
    Statement state = null;
    ResultSet rs_dic = null;
    try {
      if (conn != null) {
        conn.close();
        conn = null;
      }
      getConnection();
      state = conn.createStatement();
      rs_dic = state.executeQuery(sql);
      while (rs_dic.next()) {
        String text = rs_dic.getString(1);
        String value = rs_dic.getString(2);
        if (null != text && value != null) {
          htemp.put(text.trim(), value.trim());
        }
      }
      hashCache.put(sql, htemp);
      rs_dic.close();
      state.close();
    } catch (Exception e) {
      e.printStackTrace();
      Debug.getAppErrLogger().error(e);
    } finally {
      try {
        if (rs_dic != null) {
          rs_dic.close();
          rs_dic = null;
        }
        if (state != null) {
          state.close();
          state = null;
        }
      } catch (Exception e) {
        Debug.getAppErrLogger().error("Inside DictCachService::loadNewDict() - 关闭时出错!\n" + e);
      }
      close();
    }
  }
示例#30
0
  /**
   * This method queries the database to validate the passed in FRN for the selected Funding Year
   * has been invoiced or not
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean checkFRNinv(String frn, String year) {
    String query;
    boolean validfrn = false;
    Statement stmt = null;
    ResultSet rs = null;

    USFEnv.getLog().writeCrit("RhccDinvview: FRN  Invoiced" + frn, this, null);
    USFEnv.getLog().writeCrit("RhccDinvview: FRN  Invoiced" + year, this, null);

    query = " SELECT DISTINCT rhcc_inv_id_fk ";
    query = query + " FROM wrk_ordr, wrk_ordr_dets, wo_det_hsties ";
    query = query + " WHERE wo_id = wo_id_fk ";
    query = query + " AND wod_id = wod_id_fk ";
    query = query + " AND wdh_stat = 'P' ";
    query = query + " AND inv_stat = 'I' ";
    query = query + " AND wrk_ordr_no ='" + frn + "' ";
    query = query + " AND wrk_ordr.yr_fk =" + year;

    USFEnv.getLog().writeCrit("RhccDinvview: FRN  Invoiced" + query, this, null);

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      USFEnv.getLog().writeCrit("RhccDinvview: FRN  Invoiced" + rs.getFetchSize(), this, null);
      if (rs.next()) {
        USFEnv.getLog()
            .writeCrit("RhccDinvview: FRN  Invoiced" + rs.getString("rhcc_inv_id_fk"), this, null);
        validfrn = true;
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: FRN not Invoiced", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return false;
  }