Example #1
1
 static void attributeMean() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     Statement meanQuery = con.createStatement();
     ResultSet mean = meanQuery.executeQuery("select AVG(sal) from employ where sal IS NOT NULL");
     if (mean.next()) {
       fill = con.createStatement();
       fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + mean.getInt(1) + ")");
     }
     /*fill = con.createStatement();
     fill.executeUpdate("UPDATE employ set sal = COALESCE(sal,AVG(sal))");*/
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println("Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
  /**
   * 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");
    }
  }
  /**
   * 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");
    }
  }
Example #4
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());
      }
    }
  }
Example #5
0
  private static boolean connectToDatabase() {
    String databaseConnectionInfo =
        "jdbc:mysql://localhost/authorities?user=authorities&password=authorities&useUnicode=yes&characterEncoding=UTF-8";
    if (authoritiesConn == null) {
      try {
        authoritiesConn = DriverManager.getConnection(databaseConnectionInfo);
        getPreferredAuthorByOriginalNameStmt =
            authoritiesConn.prepareStatement(
                "SELECT viafId, normalizedName from preferred_authors where originalName = ?",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        getPreferredAuthorByNormalizedNameStmt =
            authoritiesConn.prepareStatement(
                "SELECT viafId, normalizedName from preferred_authors where originalName = ?",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        getPreferredAuthorByAlternateNameStmt =
            authoritiesConn.prepareStatement(
                "SELECT preferred_authors.viafId, normalizedName FROM `preferred_authors` inner join alternate_authors on preferred_authors.viafId = alternate_authors.viafId where alternateName = ?",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);

        return true;
      } catch (Exception e) {
        logger.error("Error connecting to authorities database");
        return false;
      }
    }
    return true;
  }
  private static void dropCourse(Connection conn, String stu_no) throws SQLException {
    String cno = readEntry("Course number: ");
    String sno = readEntry("Section number : ");

    String chks =
        "SELECT g.Section_identifier FROM SECTION s, GRADE_REPORT g where s.Section_identifier=g.Section_identifier AND s.Course_number='"
            + cno
            + "' AND g.Section_identifier="
            + sno
            + " AND s.Semester='Spring' AND s.Year=TO_DATE('2007','YYYY') AND g.Student_number="
            + stu_no;
    Statement s3 = conn.createStatement();
    ResultSet r3 = s3.executeQuery(chks);

    if (r3.next()) {
      String del =
          "DELETE FROM GRADE_REPORT WHERE Student_number="
              + stu_no
              + " AND Section_identifier="
              + sno;
      Statement s4 = conn.createStatement();
      try {
        s4.executeUpdate(del);
        System.out.println("Course dropped");
      } catch (SQLException e) {
        System.out.println("Cannot drop a course from table");
      }
    } else {
      System.out.println("Enrollment data not found. Check the input and try again.");
    }
  }
  public void executeBatch(String sql, Object args[][]) throws SQLException, FileNotFoundException {
    PreparedStatement prep = connection.prepareStatement(sql);
    int i;
    for (Object[] a : args) {
      i = 1;
      for (Object b : a) {
        if (b instanceof String) {
          prep.setString(i, (String) b);
        }
        if (b instanceof Integer) {
          prep.setInt(i, (Integer) b);
        }
        if (b instanceof File) {
          FileInputStream fis = new FileInputStream((File) b);
          prep.setBinaryStream(i, fis);
        }
        if (b instanceof FileInputStream) {
          prep.setBinaryStream(i, (InputStream) b);
        }
        if (b instanceof BufferedImage) {
          byte[] buffer =
              ((DataBufferByte) ((BufferedImage) b).getRaster().getDataBuffer()).getData();
          InputStream ist = new ByteArrayInputStream(buffer);
          prep.setBinaryStream(i, ist);
        }
        i++;
      }
      prep.addBatch();
    }

    connection.setAutoCommit(false);
    prep.executeBatch();
    connection.setAutoCommit(true);
  }
  /**
   * 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");
    }
  }
Example #9
0
  public static void recieveTrafficRates(String IP, float upload, float download) {
    System.out.println("Insertion of Data into Database!");
    Connection con = null;
    String url = "jdbc:mysql://localhost:3306/";
    String dbName = "traffic_report";
    String driverName = "com.mysql.jdbc.Driver";
    String userName = "******";
    String password = "******";
    String IP_address = IP;

    float up = upload, down = download;

    try {
      Class.forName(driverName).newInstance();
      con = DriverManager.getConnection(url + dbName, userName, password);
      try {
        Statement st = con.createStatement();

        int val =
            st.executeUpdate(
                "INSERT current_traffic VALUES('" + IP + "','" + up + "','" + down + "')");

        System.out.println("Data Entry process successfully!");
      } catch (SQLException s) {
        System.out.println("Table all ready exists!" + s);
      }
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  //
  // 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();
  }
Example #11
0
 static void specificAttributeMean() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     fill = con.createStatement();
     fill.executeUpdate(
         "UPDATE employ a set sal = (select AVG(sal) from employ where sal IS NOT NULL and grade = (select grade from employ b where sal IS NULL and a.grade=b.grade and ROWNUM <= 1)) where sal IS NULL");
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
Example #12
0
 static void globalConstant() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     fill = con.createStatement();
     fill.executeUpdate("UPDATE employ set sal = " + GlobalConstant + " where sal IS NULL");
     // fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + GlobalConstant + ")");
     // System.out.println("Entered..!");
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println("Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
Example #13
0
 static void probableValue() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     Statement freqQuery = con.createStatement();
     ResultSet freq =
         freqQuery.executeQuery(
             "select sal,count(*) as total from employ group by sal order by total desc");
     if (freq.next()) {
       fill = con.createStatement();
       fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + freq.getInt(1) + ")");
     }
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
Example #14
0
  public List<Cocinero> getAllCocinero() {
    List ll = new LinkedList();
    Statement st;
    ResultSet rs;
    String url = "jdbc:mysql://localhost:3306/food";
    String user = "******";
    String pass = "******";
    String driver = "com.mysql.jdbc.Driver";

    try (Connection connection = DriverManager.getConnection(url, user, pass)) {
      Class.forName(driver);
      st = connection.createStatement();
      String recordQuery = ("Select * from cocinero");
      rs = st.executeQuery(recordQuery);
      while (rs.next()) {
        Integer idcoc = rs.getInt("idCocinero");
        String years = rs.getString("AñosServicio");
        String idemp = rs.getString("Empleados_idEmpleados");
        Integer idepp = Integer.parseInt(idemp);
        ll.add(new Cocinero(idcoc, years, idepp));
      }

    } catch (ClassNotFoundException | SQLException ex) {
      Logger.getLogger(MenuEmpleados.class.getName()).log(Level.SEVERE, null, ex);
    }
    return ll;
  }
  public int update(ValueObject obj) throws SQLException {

    if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
      throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : update() ");
    }
    PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;

    Connection conn = null;
    PreparedStatement ps = null;
    int result = 0;

    StringBuffer sb = new StringBuffer();
    sb.append("update PT_KICA_ERR_LOG  set ")
        .append("U_D_FLAG = ")
        .append(toDB(entity.getU_D_FLAG()))
        .append(",")
        .append("YYYYMMDD = ")
        .append(toDB(entity.getYYYYMMDD()))
        .append(",")
        .append("TRANSHOUR = ")
        .append(toDB(entity.getTRANSHOUR()))
        .append(",")
        .append("FILENAME = ")
        .append(toDB(entity.getFILENAME()))
        .append(",")
        .append("ERRLOG = ")
        .append(toDB(entity.getERRLOG()))
        .append(",")
        .append("RESULT_FLAG = ")
        .append(toDB(entity.getRESULT_FLAG()))
        .append(",")
        .append("UPD_DT = ")
        .append(toDB(entity.getUPD_DT()))
        .append(",")
        .append("INS_DT = ")
        .append(toDB(entity.getINS_DT()))
        .append(" where  1=1 ");

    sb.append(" and SEQ = ").append(toDB(entity.getSEQ()));

    KJFLog.sql(sb.toString());

    try {

      conn = this.getConnection();
      ps = conn.prepareStatement(sb.toString());

      int i = 1;

      result = ps.executeUpdate();

    } catch (SQLException e) {
      throw e;
    } finally {
      if (ps != null) ps.close();
      this.release(conn);
    }

    return result;
  }
  /**
   * 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");
    }
  }
  public int delete(ValueObject obj) throws SQLException {

    if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
      throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : delete() ");
    }
    PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;

    Connection conn = null;
    PreparedStatement ps = null;
    int result = 0;

    StringBuffer sb = new StringBuffer();
    sb.append("delete from PT_KICA_ERR_LOG  where  1=1")
        .append(" and SEQ = ")
        .append(toDB(entity.getSEQ()));

    KJFLog.sql(sb.toString());

    try {

      conn = this.getConnection();
      ps = conn.prepareStatement(sb.toString());

      result = ps.executeUpdate();

    } catch (SQLException e) {
      throw e;
    } finally {
      if (ps != null) ps.close();
      this.release(conn);
    }

    return result;
  }
  /**
   * 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;
  }
Example #19
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();
    }
  }
Example #20
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();
    }
  }
Example #21
0
  // Get a list of images that have been added, but not yet added into
  // the database.
  protected void doFlush() {
    SpyDB pdb = getDB();
    Vector v = null;
    try {
      Connection db = pdb.getConn();
      Statement st = db.createStatement();
      String query = "select * from upload_log where stored is null";
      ResultSet rs = st.executeQuery(query);
      v = new Vector();
      while (rs.next()) {
        v.addElement(rs.getString("photo_id"));
      }
    } catch (Exception e) {
      // Do nothing, we'll try again later.
    } finally {
      pdb.freeDBConn();
    }

    // Got the vector, now store the actual images.  This is done so
    // that we don't hold the database connection open whlie we're
    // making the list *and* getting another database connection to act
    // on it.
    if (v != null) {
      try {
        for (int i = 0; i < v.size(); i++) {
          String stmp = (String) v.elementAt(i);
          storeImage(Integer.valueOf(stmp).intValue());
        }
      } catch (Exception e) {
        // Don't care, we'll try again soon.
      }
    }
  }
  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();
  }
  /**
   * 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");
    }
  }
 private void handleKeepAlive() {
   Connection c = null;
   try {
     netCode = ois.readInt();
     c = checkConnectionNetCode();
     if (c != null && c.getState() == Connection.STATE_CONNECTED) {
       oos.writeInt(ACK_KEEP_ALIVE);
       oos.flush();
       System.out.println("->ACK_KEEP_ALIVE"); // $NON-NLS-1$
     } else {
       System.out.println("->NOT_CONNECTED"); // $NON-NLS-1$
       oos.writeInt(NOT_CONNECTED);
       oos.flush();
     }
   } catch (IOException e) {
     System.out.println("handleKeepAlive: " + e.getMessage()); // $NON-NLS-1$
     if (c != null) {
       c.setState(Connection.STATE_DISCONNECTED);
       System.out.println(
           "Connection closed with "
               + c.getRemoteAddress()
               + //$NON-NLS-1$
               ":"
               + c.getRemotePort()); // $NON-NLS-1$
     }
   }
 }
Example #25
0
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {

    PrintWriter writer = response.getWriter();
    String noteName = request.getParameter("noteName");
    String note = request.getParameter("note");

    try {
      Class.forName("com.mysql.jdbc.Driver");
      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/evernoteDB");

      Statement st = con.createStatement();

      st.executeUpdate(
          "INSERT INTO note (noteName, note) VALUES ('" + noteName + "','" + note + "')");

      response.sendRedirect("/userNotes.jsp");
    } catch (ClassNotFoundException e) {
      writer.println("Couldn't load database driver: " + e.getMessage());
    } catch (SQLException e) {
      writer.println("SQLException caught: " + e.getMessage());
    } catch (Exception e) {
      writer.println(e);
    }
  }
Example #26
0
  public static void main(String args[]) {
    int myport = 9090;
    Socket s;
    ServerSocket ss;
    BufferedReader fromSoc, fromKbd;
    PrintStream toSoc;
    String line, msg;
    try {
      s = new Socket("169.254.63.10", 6016);
      System.out.println("enter line");
      System.out.println("connected");
      toSoc = new PrintStream(s.getOutputStream());

      toSoc.println(myport);
      String url =
          "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=D://db.mdb; DriverID=22;READONLY=true;";

      Connection con = DriverManager.getConnection(url, "", "");
      Statement st = con.createStatement();
      String ip = "169.254.63.10";
      int port = 6016;
      String video = "rooney";

      st.executeUpdate(
          " insert into p3 values('" + ip + "' , '" + port + "' , '" + video + "' );  ");

      fun();

    } catch (Exception e) {

      System.out.println("exception" + e);
    }
  }
Example #27
0
  /* to search the DB table for req. video*/
  boolean search(String file) {

    boolean found = false;

    Connection con = null;
    String url =
        "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};  DBQ=D://db.mdb; DriverID=22;READONLY=true;";

    try {
      con = DriverManager.getConnection(url, "", "");
      try {
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT video FROM p3");
        while (rs.next()) {
          String colvalue = rs.getString("video");
          if (colvalue.equalsIgnoreCase(file)) found = true;
        }

      } catch (SQLException s) {
        System.out.println("SQL statement is not executed!");
      }
    } catch (Exception e) {
      System.out.println(e);
    }
    return found;
  }
Example #28
0
 /**
  * Start the application
  *
  * @throws IOException if there is a problem with connection
  * @throws InterruptedException if thread was interrupted
  */
 private void start() throws IOException, InterruptedException {
   Connection c = new Connection(myHost.getHostName(), myHost.getPort());
   try {
     configureKnownHosts(c);
     c.connect(new HostKeyVerifier());
     authenticate(c);
     final Session s = c.openSession();
     try {
       s.execCommand(myCommand);
       // Note that stdin is not being waited using semaphore.
       // Instead, the SSH process waits for remote process exit
       // if remote process exited, none is interested in stdin
       // anyway.
       forward("stdin", s.getStdin(), System.in, false);
       forward("stdout", System.out, s.getStdout(), true);
       forward("stderr", System.err, s.getStderr(), true);
       myForwardCompleted.acquire(2); // wait only for stderr and stdout
       s.waitForCondition(ChannelCondition.EXIT_STATUS, Long.MAX_VALUE);
       Integer exitStatus = s.getExitStatus();
       if (exitStatus == null) {
         // broken exit status
         exitStatus = 1;
       }
       System.exit(exitStatus.intValue() == 0 ? myExitCode : exitStatus.intValue());
     } finally {
       s.close();
     }
   } finally {
     c.close();
   }
 }
Example #29
0
  public static void main(String args[]) {
    String fileToSend = "C:\\test1.txt";
    while (true) {
      // Maak lokale variabels eerst aan.
      // Dit is nodig om deze ook te gebruiken buiten de "try catch" blok.
      ServerSocket welcomeSocket = null;
      Socket connectionSocket = null;
      BufferedOutputStream outToClient = null;

      try {
        // poort waar gebruiker kan op connecteren
        welcomeSocket = new ServerSocket(3248);
        // Tot er een gebruiker gevonden is zal .accept() blijven wachten
        // Nadat er iemand geconnecteerd is zal er een nieuwe socket aangemaakt worden waarmee deze
        // met elkaar communiceren
        connectionSocket = welcomeSocket.accept();
        // buffer voor data communicatie tussen server en client
        outToClient = new BufferedOutputStream(connectionSocket.getOutputStream());
      } catch (IOException ex) {
        System.out.println("IOException1");
      } // if an I/O error occurs when opening the socket.

      if (outToClient
          != null) // wanneer buffer correct is aangemaakt zal deze niet een null waarde hebben
      {
        // gemaakte variabele doorgeven aan thread
        Connection c = new Connection(connectionSocket, outToClient, fileToSend);
        // thread starten
        c.start();
      }
    }
  }
Example #30
-1
  public List<Empleado> getAllEmpleados() {
    List ll = new LinkedList();
    Statement st;
    ResultSet rs;
    String url = "jdbc:mysql://localhost:3306/food";
    String user = "******";
    String pass = "******";
    String driver = "com.mysql.jdbc.Driver";

    try (Connection connection = DriverManager.getConnection(url, user, pass)) {
      Class.forName(driver);
      st = connection.createStatement();
      String recordQuery = ("Select * from empleados");
      rs = st.executeQuery(recordQuery);
      while (rs.next()) {
        Integer id = rs.getInt("idEmpleados");
        String name = rs.getString("Nombre");
        String telf = rs.getString("Telefono");
        ll.add(new Empleado(id, name, telf));
        /// System.out.println(id +","+ name +","+ apellido +","+ cedula +","+ telf +"
        // "+direccion+"");
      }

    } catch (ClassNotFoundException | SQLException ex) {
      Logger.getLogger(MenuEmpleados.class.getName()).log(Level.SEVERE, null, ex);
    }
    return ll;
  }