Esempio n. 1
0
 /**
  * ** Deletes the record corresponding to the Primary Key of thie DBRecordKey. ** @param
  * altIndexName The alternate index name, or null to delete the primary index ** @param
  * whereKeyType WHERE key type: Full, PartialFirst, PartialAll
  */
 protected void _delete(String altIndexName, int whereKeyType) // boolean fullKeyReq)
     throws SQLException, DBException {
   // DBDelete: DELETE FROM <table> WHERE <where>
   DBDelete ddel = new DBDelete(this.getFactory());
   ddel.setWhere(this._getWhereClause(altIndexName, whereKeyType));
   // Print.logInfo("DBDelete: " + ddel);
   DBConnection dbc = null;
   try {
     dbc = DBConnection.getDefaultConnection();
     dbc.executeUpdate(ddel.toString());
   } finally {
     DBConnection.release(dbc);
   }
 }
Esempio n. 2
0
  /* return list of all Devices within the specified DeviceGroup (NOT SCALABLE BEYOND A FEW HUNDRED GROUPS) */
  public static java.util.List<String> getUsersForGroup(String acctId, String groupId)
      throws DBException {

    /* valid account/groupId? */
    if (StringTools.isBlank(acctId)) {
      return null;
    } else if (StringTools.isBlank(groupId)) {
      return null;
    }

    /* get db selector */
    DBSelect dsel = GroupList._getUserListSelect(acctId, groupId);
    if (dsel == null) {
      return null;
    }

    /* read users for group */
    java.util.List<String> usrList = new Vector<String>();
    DBConnection dbc = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      dbc = DBConnection.getDefaultConnection();
      stmt = dbc.execute(dsel.toString());
      rs = stmt.getResultSet();
      while (rs.next()) {
        String usrId = rs.getString(GroupList.FLD_userID);
        usrList.add(usrId);
      }
    } catch (SQLException sqe) {
      throw new DBException("Get Group GroupeList", sqe);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (Throwable t) {
        }
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (Throwable t) {
        }
      }
      DBConnection.release(dbc);
    }

    /* return list */
    return usrList;
  }
Esempio n. 3
0
  /**
   * Prepares the statement used to delete this object from the database.
   *
   * @param conn the database connection
   * @return the delete statement.
   * @exception java.sql.SQLException if an error occurs.
   */
  public PreparedStatement getDeleteStatement(DBConnection conn) throws SQLException {

    String sql = "delete from PersonalProfile \n" + "where " + getOIdColumnName() + " = ?";
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setBigDecimal(1, getOId().toBigDecimal());
    return stmt;
  }
Esempio n. 4
0
 public Vector<Message> getInboxMessages(String username, int type) {
   if (type == 4) username = "******";
   ResultSet rs =
       con.queryDB(
           "SELECT * FROM "
               + MESSAGES
               + " WHERE toName = \""
               + username
               + "\" AND messageType = "
               + type
               + " ORDER BY timeStamp DESC");
   Vector<Message> inbox = new Vector<Message>();
   try {
     while (rs.next()) {
       inbox.add(
           new Message(
               rs.getString("fromName"),
               username,
               rs.getString("message"),
               rs.getInt("messageType"),
               rs.getTimestamp("timeStamp")));
     }
   } catch (SQLException e) {
     throw new RuntimeException("SQLException in MessageDatabase::getInboxMessages");
   }
   return inbox;
 }
  public Nutzer insert(Nutzer a) {
    Connection con = DBConnection.connection();
    try {
      Statement stmt = con.createStatement();

      ResultSet rs = stmt.executeQuery("SELECT MAX(NutzerId) AS maxid " + "FROM nutzer");

      if (rs.next()) {
        a.setId(rs.getInt("maxid") + 1);

        stmt = con.createStatement();

        stmt.executeUpdate(
            "INSERT INTO nutzer (NutzerId, Email, Vorname, Nachname, Passwort)"
                + "VALUES ("
                + a.getId()
                + ",'"
                + a.getEmail()
                + "','"
                + a.getVorname()
                + "','"
                + a.getNachname()
                + "','"
                + a.getPassword()
                + "')");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return a;
  }
Esempio n. 6
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;
  }
Esempio n. 7
0
  /**
   * Prepares the statement used to insert this object into the database.
   *
   * @param conn the database connection.
   * @return the insert statement.
   * @exception java.sql.SQLException if an error occurs.
   */
  public PreparedStatement getInsertStatement(DBConnection conn) throws SQLException {
    /*
     * It would probably be better to have CoreDO implement
     * 	void addToCache(CoreDO DO) {}
     * and have each DO that has caching enabled override it as
     *      void addToCache(CoreDO DO) { cache.put( DO.getOId(), DO ); }
     * and change CoreDO to invoke addToCache()
     * when it invokes getInsertStatement().
     */

    ObjectId oid;

    PreparedStatement stmt =
        conn.prepareStatement(
            "insert into PersonalProfile ( LeafNumber, Profile, Mandatory, MinAge, MaxAge, Nationality, Sex, "
                + getOIdColumnName()
                + ", "
                + getVersionColumnName()
                + " ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )");

    param = new int[1];
    param[0] = 1;
    // writeMemberStuff uses the JDBCsetCalls.template
    // to build up the value for this tag:
    // the value is a series of calls to setPrepStmtParam_TYPE methods.
    // Those methods are defined in GenericDO.
    try {
      setPrepStmtParam_int(stmt, param, getLeafNumber());
      setPrepStmtParam_DO(stmt, param, getProfile());
      setPrepStmtParam_boolean(stmt, param, getMandatory());
      setPrepStmtParam_int(stmt, param, getMinAge());
      setPrepStmtParam_int(stmt, param, getMaxAge());
      setPrepStmtParam_DO(stmt, param, getNationality());
      setPrepStmtParam_String(stmt, param, getSex());

      /* The order of the values being inserted must match
       * the order of the columns listed in the CREATE TABLE command
       * that appears in the .sql file for this DO.
       * Since the id and version number are always the last columns
       * listed in the CREATE TABLE command, their values are added
       * to the PreparedStatement after all other values.
       */
      setPrepStmtParam_BigDecimal(stmt, param, getOId().toBigDecimal());
      setPrepStmtParam_int(stmt, param, getNewVersion());

    } catch (Exception e) {
      throw new SQLException("Data Object error: " + e.getMessage());
    }

    return stmt;
  }
  private void setamount() {

    try {
      ResultSet rst =
          DBConnection.getDBConnection()
              .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)
              .executeQuery(
                  "SELECT Amount FROM BOOKING  where Pass_No='" + combo1.getSelectedItem() + "'");
      while (rst.next()) {
        combo8.addItem(rst.getString(1));
      }
    } catch (Exception n) {
      n.printStackTrace();
    }
  }
  private void setCombo() {

    try {
      ResultSet rst =
          DBConnection.getDBConnection()
              .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)
              .executeQuery(
                  "SELECT Emp.empNo, Emp.Sname, Emp.Fname, Emp.Lname, Emp.Designation FROM Emp WHERE Emp.Designation='Booking Clerk'");
      while (rst.next()) {
        combo3.addItem(rst.getString(3));
      }
    } catch (Exception n) {
      n.printStackTrace();
    }
  }
Esempio n. 10
0
 /**
  * Puts a message in the database Note: the timestamp will be stored as the current time at which
  * this method is called.
  */
 public void storeMessage(Message message) {
   con.updateDB(
       "INSERT INTO "
           + MESSAGES
           + " VALUES ( \""
           + message.getFrom()
           + "\", \""
           + message.getTo()
           + "\", \""
           + message.getBody()
           + "\", "
           + null
           + ", "
           + message.getType()
           + ")");
 }
Esempio n. 11
0
 /**
  * Puts a message in the database Note: the timestamp will be stored as the current time at which
  * this method is called.
  */
 public void storeMessage(
     String from, String to, String message, int messageType, String timestamp) {
   con.updateDB(
       "INSERT INTO "
           + MESSAGES
           + " VALUES ( \""
           + from
           + "\", \""
           + to
           + "\", \""
           + message
           + "\", "
           + null
           + ", "
           + messageType
           + ")");
 }
Esempio n. 12
0
 public boolean containsFriendRequest(String from, String to) {
   ResultSet rs =
       con.queryDB(
           "SELECT * FROM "
               + MESSAGES
               + " WHERE fromName = \""
               + from
               + "\" AND toName = \""
               + to
               + "\" AND messageType = 2");
   try {
     if (rs.next()) return true;
   } catch (SQLException e) {
     throw new RuntimeException("SQLException in MessageDatabase::containsFriendRequest");
   }
   return false;
 }
Esempio n. 13
0
  /**
   * Prepares the statement used to update this object in the database.
   *
   * @param conn the database connection
   * @return the update statement.
   * @exception java.sql.SQLException if an error occurs.
   */
  public PreparedStatement getUpdateStatement(DBConnection conn) throws SQLException {

    ObjectId oid;

    PreparedStatement stmt =
        conn.prepareStatement(
            "update PersonalProfile set "
                + getVersionColumnName()
                + " = ?, LeafNumber = ?, Profile = ?, Mandatory = ?, MinAge = ?, MaxAge = ?, Nationality = ?, Sex = ? "
                + "where "
                + getOIdColumnName()
                + " = ? and "
                + getVersionColumnName()
                + " = ?");

    param = new int[1];
    param[0] = 1;
    // int[] param = {1};
    // writeMemberStuff builds up the value for this tag:
    // the value is a series of calls to setPrepStmtParam_TYPE methods.
    // Those methods are defined below.
    try {
      setPrepStmtParam_int(stmt, param, getNewVersion());
      setPrepStmtParam_int(stmt, param, getLeafNumber());
      setPrepStmtParam_DO(stmt, param, getProfile());
      setPrepStmtParam_boolean(stmt, param, getMandatory());
      setPrepStmtParam_int(stmt, param, getMinAge());
      setPrepStmtParam_int(stmt, param, getMaxAge());
      setPrepStmtParam_DO(stmt, param, getNationality());
      setPrepStmtParam_String(stmt, param, getSex());

      /* When updating a persistent object, the UPDATE_WHERE_CLAUSE tag
       * used to build the SQL WHERE clause (above) uses the
       * DO's id and version number to locate the correct row in
       * the database table.
       */
      setPrepStmtParam_BigDecimal(stmt, param, getOId().toBigDecimal());
      setPrepStmtParam_int(stmt, param, getVersion());

    } catch (Exception e) {
      throw new SQLException("Data Object error: " + e.getMessage());
    }

    return stmt;
  }
  private void setcbr() {

    try {
      ResultSet rst =
          DBConnection.getDBConnection()
              .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)
              .executeQuery(
                  "SELECT * FROM Passenger where Booked_status='Booked' and Pay_Status='Not_Paid'");
      System.out.println("Hello");
      while (rst.next()) {

        combo1.addItem(rst.getString(1));
        combo2.addItem(rst.getString(2));
      }
    } catch (Exception n) {
      n.printStackTrace();
    }
  }
  private void generator() {

    try {
      ResultSet rst =
          DBConnection.getDBConnection()
              .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)
              .executeQuery("SELECT Payment_No FROM Payment");
      text1.setText("1000");
      while (rst.next()) {
        String s;
        int number = rst.getInt(1);
        number = number + 1;

        s = "" + number;
        text1.setText(s);
        int x;
      }
    } catch (Exception n) {
      n.printStackTrace();
    }
  }
Esempio n. 16
0
 /*
  * Returns a Vector representing all messages in the user's outbox.  The most recent
  * messages are at the beginning, and the oldest ones are at the end.
  */
 public Vector<Message> getOutboxMessages(String username) {
   ResultSet rs =
       con.queryDB(
           "SELECT * FROM "
               + MESSAGES
               + " WHERE fromName = \""
               + username
               + "\" ORDER BY timeStamp DESC");
   Vector<Message> outbox = new Vector<Message>();
   try {
     while (rs.next()) {
       outbox.add(
           new Message(
               username,
               rs.getString("toName"),
               rs.getString("message"),
               rs.getInt("messageType"),
               rs.getTimestamp("timeStamp")));
     }
   } catch (SQLException e) {
     throw new RuntimeException("SQLException in MessageDatabase::getOutboxMessages");
   }
   return outbox;
 }
Esempio n. 17
0
 public MessageDatabase() {
   con = DBConnection.getSharedConnection();
 }
Esempio n. 18
0
  /**
   * ** Returns true if the specified key attribute exists in the table ** @param altIndexName The
   * alternate index name, or null to use the primary index ** @param whereKeyType The partial key
   * match type ** @return True if the specified key attribute exists in the table, false otherwise
   */
  protected boolean _exists(String altIndexName, int whereKeyType)
      throws SQLException, DBException {

    /* key fields */
    boolean usePrimaryKey = StringTools.isBlank(altIndexName);
    DBField kfld[] = usePrimaryKey ? this.getKeyFields() : this.getAltKeyFields(altIndexName);
    if (ListTools.isEmpty(kfld)) {
      throw new DBException("No keys found!");
    }

    /* check last key for "auto_increment" */
    if (whereKeyType == DBWhere.KEY_FULL) {
      DBField lastField = kfld[kfld.length - 1];
      if (lastField.isAutoIncrement()
          && !this.getFieldValues().hasFieldValue(lastField.getName())) {
        // full key requested and last key is auto_increment, which is missing
        return false;
      }
    }

    // DBSelect: SELECT <Keys> FROM <TableName> <KeyWhere>
    String firstKey = kfld[0].getName();
    DBSelect<gDBR> dsel = new DBSelect<gDBR>(this.getFactory());
    dsel.setSelectedFields(firstKey);
    dsel.setWhere(this._getWhereClause(altIndexName, whereKeyType));

    /* get keyed record */
    DBConnection dbc = null;
    Statement stmt = null;
    ResultSet rs = null;
    boolean exists = false;
    try {
      dbc = DBConnection.getDefaultConnection();
      stmt = dbc.execute(dsel.toString()); // may throw DBException
      rs = stmt.getResultSet();
      exists = rs.next();
    } catch (SQLException sqe) {
      if (sqe.getErrorCode() == DBFactory.SQLERR_TABLE_NOTLOCKED) {
        // MySQL: This case has been seen on rare occasions.  Not sure what causes it.
        Print.logError("SQL Lock Error: " + sqe);
        Print.logError("Hackery! Forcing lock on table: " + this.getTableName());
        if (DBProvider.lockTableForRead(this.getTableName(), true)) { // may throw DBException
          stmt = dbc.execute(dsel.toString()); // may throw SQLException, DBException
          rs = stmt.getResultSet(); // SQLException
          exists = rs.next(); // SQLException
          DBProvider.unlockTables(); // DBException
        }
      } else {
        throw sqe;
      }
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (Throwable t) {
        }
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (Throwable t) {
        }
      }
      DBConnection.release(dbc);
    }

    return exists;
  }
  void profedit(String cid) throws Exception {

    try {
      Connection conn = DBConnection.ConnectDB();

      while (true) {

        // System.out.println(cid);
        // list all exercises in this course
        PreparedStatement stmt0 =
            conn.prepareStatement("SELECT eid, hwname FROM exercise WHERE cid = ?");
        stmt0.setString(1, cid);
        ResultSet rs0 = stmt0.executeQuery();
        int i = 1;
        String[] profselect = new String[20];
        int[] alleid = new int[20];
        while (rs0.next()) {
          String hwname = rs0.getString("hwname");
          alleid[i] = rs0.getInt("eid");
          profselect[i] = hwname; // professor's selection is stored
          System.out.printf(" %d. %s\n", i, hwname);
          i++;
        }

        System.out.println(" 0. Back");

        Scanner scan = new Scanner(System.in);
        int Choice = -1;
        // get user choice of which Exercise
        System.out.println("Please enter the corresponding number:");

        while (true) {
          Choice = scan.nextInt();
          if (checkint(Choice, 0, i - 1)) break;
        }

        if (Choice == 0) {
          System.out.print('\u000C');
          break; // go back
        } else {
          // display chosen Exercise
          System.out.print('\u000C');
          String chosename = profselect[Choice];
          System.out.println("You chose to update " + chosename);

          int eid = alleid[Choice];

          PreparedStatement stmt1 = conn.prepareStatement("SELECT * FROM Exercise WHERE eid=?");
          stmt1.setInt(1, eid);
          ResultSet rs1 = stmt1.executeQuery();
          rs1.next();
          String startdate = rs1.getString("startdate");
          String enddate = rs1.getString("enddate");
          int newallowattempt = rs1.getInt("allowattempt");
          int newscorescheme = rs1.getInt("scorescheme");
          int newnumofquestion = rs1.getInt("numofquestion");
          int newcorrectpoint = rs1.getInt("correctpoint");
          int newincorrectpoint = rs1.getInt("incorrectpoint");
          int newbasedon = rs1.getInt("basedon");

          while (true) {
            // display options the professor can modify
            System.out.println(
                "Choose what to update:\n  1. Start date\n  2. End date\n  3. Number of attempts\n  "
                    + "4. Score selection\n  5. Question numbers\n  6. Correct answer points\n  7. Incorrect answer points\n  8. Based on\n  0. Back");
            Choice = -1;
            while (true) {
              Choice = scan.nextInt();
              if (checkint(Choice, 0, 8)) break;
            }

            /*
                	 	1. Start date
            2. End date
            3. Number of attempts
            4. Score selection
            5. Question numbers
            6. Correct answer points
            7. Incorrect answer points
            8. Based on
                	 */

            Scanner scan1 = new Scanner(System.in);

            if (Choice == 0) {
              System.out.print('\u000C');
              break;
            } else if (Choice == 1) {
              System.out.printf(
                  "Origionally Start Date:%s\nPlease enter new Start Date(yyyy-mm-dd):", startdate);
              while (true) {
                startdate = scan1.nextLine();
                if (checkdate(startdate)) break;
              }
              startdate = startdate + " 00:00:00.0";

            } else if (Choice == 2) {
              System.out.printf(
                  "Origionally End Date:%s\nPlease enter new End Date(yyyy-mm-dd):", enddate);
              while (true) {
                enddate = scan1.nextLine();
                if (checkdate(enddate)) break;
              }
              enddate = enddate + " 00:00:00.0";

            } else if (Choice == 3) {
              System.out.printf(
                  "Origionally Number of attempts:%d\nPlease enter new Number of attempts:",
                  newallowattempt);
              newallowattempt = scan1.nextInt();
            } else if (Choice == 4) {
              String[] sele = new String[5];
              sele[1] = "first attempt";
              sele[2] = "last attempt";
              sele[3] = "average";
              sele[4] = "max";
              System.out.printf(
                  "Origionally Score selection:%s\nPlease enter new Score selection(1.first attempt 2.last attempt 3.average 4.max):",
                  sele[newscorescheme]);
              while (true) {
                newscorescheme = scan.nextInt();
                if (checkint(newscorescheme, 1, 4)) {
                  // give professor choice whether to update all the scores of students
                  System.out.println(
                      "Do you want to update all the students' scores? 1. Yes 2. No");
                  int updatechoice = scan.nextInt();
                  if (checkint(updatechoice, 1, 2)) {
                    if (updatechoice == 1) {
                      ProfUpdateScore pupdate = new ProfUpdateScore();
                      pupdate.profupdate(eid, newscorescheme);
                      break;
                    }
                  }
                  break;
                }
              }
            } else if (Choice == 5) {
              System.out.printf(
                  "Origionally Question numbers:%d\nPlease enter new Question numbers:",
                  newnumofquestion);
              newnumofquestion = scan1.nextInt();
            } else if (Choice == 6) {
              System.out.printf(
                  "Origionally Correct answer points:%d\nPlease enter new Correct answer points:",
                  newcorrectpoint);
              newcorrectpoint = scan1.nextInt();
            } else if (Choice == 7) {
              System.out.printf(
                  "Origionally Incorrect answer points:%d\nPlease enter new Incorrect answer points:",
                  newincorrectpoint);
              newincorrectpoint = scan1.nextInt();
            } else if (Choice == 8) {
              String[] basedt = new String[4];
              if (cid.equals("CSC440")) {
                basedt[1] = "Database Fundamentals";
                basedt[2] = "ER Design";
                basedt[3] = "Security and Authorization";
                System.out.printf(
                    "Origionally Based on: %s\nPlease enter new Base on:\n  1. Database Fundamentals\n  2. ER Design\n  3. Security and Authorization\n",
                    basedt[newbasedon]);
              } else {
                basedt[1] = "Binary search trees and Btrees";
                basedt[2] = "Hashing";
                basedt[3] = "Files and indexing and other topics";
                System.out.printf(
                    "Origionally Based on: %s\nPlease enter new Base on:\n  1. Binary search trees and Btrees\n  2.Hashing  3.Files and indexing and other topics\n",
                    basedt[newbasedon]);
              }
              while (true) {
                newbasedon = scan.nextInt();
                if (checkint(newbasedon, 1, 3)) break;
              }
            }

            // System.out.println(enddate);

            String query =
                "UPDATE Exercise SET startdate = timestamp'"
                    + startdate
                    + "', enddate = timestamp'"
                    + enddate
                    + "',"
                    + " correctpoint = "
                    + newcorrectpoint
                    + ",incorrectpoint = "
                    + newincorrectpoint
                    + ", scorescheme = "
                    + newscorescheme
                    + ", "
                    + "allowattempt = "
                    + newallowattempt
                    + ", numofquestion = "
                    + newnumofquestion
                    + ", basedon = "
                    + newbasedon
                    + " WHERE eid = "
                    + eid
                    + " ";

            // edit homework
            Statement stmt3 = conn.createStatement();

            // debug
            // System.out.println("query:"+query);

            if (stmt3.executeUpdate(query) == 1) {
              // System.out.print('\u000C');
              System.out.println("Update Successful!");

            } else {
              // System.out.print('\u000C');
              System.out.println("Update Failure!Please try again.");
            }
          }
        }
      }
      conn.close();

    } catch (Exception e) {
      System.out.println("" + e.getMessage());
    }
  }