示例#1
0
  public static void main(String[] args) {
    try {
      // 测试DBConnectionManager.java
      DBConnectionManager dbp = DBConnectionManager.getInstance();
      DBConnectionManager dbp2 = DBConnectionManager.getInstance();
      Connection conn = dbp.getConnection("localserver");
      log.debug("Loading:serverId:" + " Connection:co:" + conn);
      dbp.freeConnection("localserver", conn);
      Connection conne = dbp.getConnection("localserver");
      log.debug("Loading:serverId:" + " Connection:co:" + conne);
      dbp.freeConnection("localserver", conne);
      Connection conn3 = dbp.getConnection("localserver");
      log.debug("Loading:serverId:" + " Connection:co:" + conn3);
      dbp.freeConnection("localserver", conn3);

      //			//测试config.java
      //			Config config = new Config();
      //			log.debug(config.getProperty("company"));
      //			config.setProperty("company", "FIELDSCHINA");
      //			for(int i = 0; i < 100; i++){
      //				log.debug(config.getProperty("company"));
      //				try {
      //					Thread.sleep(1000);
      //				} catch (InterruptedException e) {
      //					// TODO Auto-generated catch block
      //					e.printStackTrace();
      //				}
      //			}
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  public StudentResult getAllResult(String usn, int sem) {
    ArrayList<Integer> intern = new ArrayList<Integer>();
    ArrayList<Integer> extern = new ArrayList<Integer>();
    ArrayList<Integer> ttl = new ArrayList<Integer>();
    ArrayList<String> remrks = new ArrayList<String>();
    StudentResult studentresult = null;
    String query =
        "select internals,externals,Total,remarks from studentresult where studentusn=? and sem=?";
    Connection con = DBConnectionManager.getConnection();
    try {

      PreparedStatement ps = con.prepareStatement(query);
      ps.setString(1, usn);
      ps.setInt(2, sem);
      ResultSet rs = ps.executeQuery();
      while (rs != null && rs.next()) {
        int ia1, ia2, total;
        String rmk;
        ia1 = rs.getInt(1);
        intern.add(ia1);
        ia2 = rs.getInt(2);
        extern.add(ia2);
        total = rs.getInt(3);
        ttl.add(total);
        rmk = rs.getString(4);
        remrks.add(rmk);
      }
      studentresult = new StudentResult(intern, extern, ttl, remrks);

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return studentresult;
  }
  public void updateResult(
      String usn,
      int sem,
      ArrayList<String> code,
      ArrayList<Integer> internal,
      ArrayList<Integer> external,
      ArrayList<Integer> total,
      ArrayList<String> remark) {
    String sql =
        "update studentresult set internals=?, Externals =?,Total=?,remarks=? where subcode=? and studentusn=? and sem=?";
    Connection con = DBConnectionManager.getConnection();
    try {
      PreparedStatement ps = con.prepareStatement(sql);
      for (int i = 0; i < code.size(); i++) {
        ps.setInt(1, internal.get(i));
        ps.setInt(2, external.get(i));
        ps.setInt(3, total.get(i));
        ps.setString(4, remark.get(i));
        ps.setString(5, code.get(i));
        ps.setString(6, usn);
        ps.setInt(7, sem);
        ps.executeUpdate();
      }

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
 public void updateAttendance(
     String usn,
     int sem,
     ArrayList<String> code,
     ArrayList<Integer> list,
     ArrayList<Integer> alist,
     ArrayList<Float> percent) {
   String sql =
       "update attendance set Total_classes=?,classes_attended=?,percentage=? where subcode=? and studentusn=? and sem=?";
   Connection con = DBConnectionManager.getConnection();
   try {
     PreparedStatement ps = con.prepareStatement(sql);
     for (int i = 0; i < code.size(); i++) {
       ps.setInt(1, list.get(i));
       ps.setInt(2, alist.get(i));
       ps.setFloat(3, percent.get(i));
       ps.setString(4, code.get(i));
       ps.setString(5, usn);
       ps.setInt(6, sem);
       ps.executeUpdate();
     }
   } catch (SQLException e) {
     e.printStackTrace();
   } finally {
     try {
       con.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }
  public String getAttendanceUSN(String usN, int sem) {
    String usn = null;
    String sql = "select studentusn from attendance where studentusn=? and sem=?";
    Connection con = DBConnectionManager.getConnection();

    try {
      PreparedStatement ps = con.prepareStatement(sql);
      ps.setString(1, usN);
      ps.setInt(2, sem);
      ResultSet rs = ps.executeQuery();
      while (rs != null && rs.next()) {
        usn = rs.getString(1);
      }
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return usn;
  }
  public void insertResult(
      String usn,
      int sem,
      ArrayList<String> code,
      ArrayList<Integer> list,
      ArrayList<Integer> avg) {
    String sql =
        "insert into studentresult (studentusn,sem,subcode,internals,externals) values(?,?,?,?,?)";
    Connection con = DBConnectionManager.getConnection();
    try {
      PreparedStatement ps = con.prepareStatement(sql);
      for (int i = 0; i < code.size(); i++) {
        ps.setString(1, usn);
        ps.setInt(2, sem);
        ps.setString(3, code.get(i));
        ps.setInt(4, avg.get(i));
        ps.setInt(5, list.get(i));
        ps.execute();
      }

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
 public SubjectCode insertCode(int sem, String dept) {
   Connection con = DBConnectionManager.getConnection();
   String sql = "select subcode,iAMaxmarks,extmaxmarks from subjectcode where sem=? and dept=?";
   ArrayList<String> code = new ArrayList<String>();
   ArrayList<Integer> iAMax = new ArrayList<Integer>();
   ArrayList<Integer> extMax = new ArrayList<Integer>();
   SubjectCode subcode = null;
   try {
     PreparedStatement ps = con.prepareStatement(sql);
     ps.setInt(1, sem);
     ps.setString(2, dept);
     ResultSet rs = ps.executeQuery();
     while (rs != null && rs.next()) {
       String subCode;
       subCode = rs.getString(1);
       code.add(subCode);
       int iA, ext;
       iA = rs.getInt(2);
       iAMax.add(iA);
       ext = rs.getInt(3);
       extMax.add(ext);
     }
     subcode = new SubjectCode(code, iAMax, extMax);
     rs.close();
   } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   }
   return subcode;
 }
  public StudentIA getallIA(String usn, int sem) {
    ArrayList<Integer> iA1 = new ArrayList<Integer>();
    ArrayList<Integer> iA2 = new ArrayList<Integer>();
    ArrayList<Integer> iA3 = new ArrayList<Integer>();
    ArrayList<Float> avg = new ArrayList<Float>();
    StudentIA studentIA = null;
    String query = "select IA1,IA2,IA3,Average from studentiatable where student_usn=?and sem=?";
    Connection con = DBConnectionManager.getConnection();
    try {

      PreparedStatement ps = con.prepareStatement(query);
      ps.setString(1, usn);
      ps.setInt(2, sem);
      ResultSet rs = ps.executeQuery();
      while (rs != null && rs.next()) {
        int ia1, ia2, ia3;
        float average;
        ia1 = rs.getInt(1);
        iA1.add(ia1);
        ia2 = rs.getInt(2);
        iA2.add(ia2);
        ia3 = rs.getInt(3);
        iA3.add(ia3);
        average = rs.getInt(4);
        avg.add(average);
      }

      studentIA = new StudentIA(iA1, iA2, iA3, avg);
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return studentIA;
  }
  public void insertAttendance(
      String usn,
      int sem,
      ArrayList<String> code,
      ArrayList<Integer> list,
      ArrayList<Integer> alist) {
    String sql =
        "insert into attendance (studentusn,sem,subcode,Total_classes,classes_attended) values(?,?,?,?,?)";
    Connection con = DBConnectionManager.getConnection();

    try {
      PreparedStatement ps = con.prepareStatement(sql);
      for (int i = 0; i < code.size(); i++) {
        ps.setString(1, usn);
        ps.setInt(2, sem);
        ps.setString(3, code.get(i));
        ps.setInt(4, list.get(i));
        ps.setInt(5, alist.get(i));
        ps.execute();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
示例#10
0
  public ArrayList<String> getStudentinfo(String usn) {
    Connection con = DBConnectionManager.getConnection();
    String subcode;

    String sql = "select name,dob,address,phone_no,email_id from studentinfo where usn=?";
    ArrayList<String> info = new ArrayList<String>();
    try {

      PreparedStatement ps = con.prepareStatement(sql);
      ps.setString(1, usn);
      ResultSet rs = ps.executeQuery();
      while (rs != null && rs.next()) {

        subcode = rs.getString(1);
        info.add(subcode);
        subcode = rs.getString(2);
        info.add(subcode);
        subcode = rs.getString(3);
        info.add(subcode);
        subcode = rs.getString(4);
        info.add(subcode);
        subcode = rs.getString(5);
        info.add(subcode);
      }
      rs.close();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return info;
  }
示例#11
0
  public void updateIA(
      String usn,
      int sem,
      ArrayList<Integer> iA1,
      ArrayList<Integer> iA2,
      ArrayList<Integer> iA3,
      ArrayList<Float> avg,
      ArrayList<String> code) {
    String sql =
        "update studentiatable set ia1 =?,ia2=?,ia3=?,average=? where subcode=? and student_usn=? and sem=?";
    Connection con = DBConnectionManager.getConnection();
    try {
      PreparedStatement ps = con.prepareStatement(sql);
      for (int i = 0; i < code.size(); i++) {
        ps.setInt(1, iA1.get(i));
        ps.setInt(2, iA2.get(i));
        ps.setInt(3, iA3.get(i));
        ps.setFloat(4, avg.get(i));
        ps.setString(5, code.get(i));
        ps.setString(6, usn);
        ps.setInt(7, sem);
        ps.executeUpdate();
      }

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
示例#12
0
  public StudentAttendance getAllAttendance(String usn, int sem) {
    ArrayList<Integer> totalclass = new ArrayList<Integer>();
    ArrayList<Integer> present = new ArrayList<Integer>();
    ArrayList<Float> percent = new ArrayList<Float>();
    System.out.println(usn);
    StudentAttendance attend = null;
    String query =
        "select Total_classes,Classes_attended,percentage from attendance where studentusn=? and sem=?";
    Connection con = DBConnectionManager.getConnection();
    try {

      PreparedStatement ps = con.prepareStatement(query);
      ps.setString(1, usn);
      ps.setInt(2, sem);
      ResultSet rs = ps.executeQuery();
      while (rs != null && rs.next()) {
        int ttlclass, ttlpresent;
        float ttlpercent;
        ttlclass = rs.getInt(1);
        totalclass.add(ttlclass);
        ttlpresent = rs.getInt(2);
        present.add(ttlpresent);
        ttlpercent = rs.getFloat(3);
        percent.add(ttlpercent);
      }
      attend = new StudentAttendance(totalclass, present, percent);
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return attend;
  }
示例#13
0
  public void insertIA(
      String usn,
      int sem,
      ArrayList<Integer> iA1,
      ArrayList<Integer> iA2,
      ArrayList<Integer> iA3,
      ArrayList<Float> avg,
      ArrayList<String> code) {

    String sql =
        "insert into studentiatable ( ia1,ia2,ia3,average,subcode,student_usn,sem) values (?,?,?,?,?,?,?)";
    Connection con = DBConnectionManager.getConnection();
    try {
      PreparedStatement ps = con.prepareStatement(sql);
      for (int i = 0; i < code.size(); i++) {
        ps.setInt(1, iA1.get(i));
        ps.setInt(2, iA2.get(i));
        ps.setInt(3, iA3.get(i));
        ps.setFloat(4, avg.get(i));
        ps.setString(5, code.get(i));
        ps.setString(6, usn);
        ps.setInt(7, sem);
        ps.executeUpdate();
      }

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
示例#14
0
  public static ArrayList<HotelFacilitiesEntities> retrieveByMemberID(String membershipNo) {
    HotelFacilitiesEntities hf1 = null;
    Statement stmt = null;
    String searchQuery =
        "select * from hotelfacilities where membershipNo = '" + membershipNo + "'";
    ArrayList<HotelFacilitiesEntities> hfList = new ArrayList<HotelFacilitiesEntities>();
    try {
      // connect to DB
      currentCon = DBConnectionManager.getConnection();
      stmt = currentCon.createStatement();
      rs = stmt.executeQuery(searchQuery);
      while (rs.next()) {

        String type = rs.getString("type");
        String date = rs.getString("date");
        String name = rs.getString("name");
        String startTime = rs.getString("startTime");
        String endTime = rs.getString("endTime");

        hf1 = new HotelFacilitiesEntities();
        hf1.setType(type);
        hf1.setDate(date);
        hf1.setName(name);
        hf1.setStartTime(startTime);
        hf1.setEndTime(endTime);

        hfList.add(hf1);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }

    return hfList;
  }
示例#15
0
  public static void hotelFacilities(HotelFacilitiesEntities hf) {

    Statement stmt = null;

    // get the last member ID
    try {

      // query for inserting into the table
      currentCon = DBConnectionManager.getConnection();
      stmt = currentCon.createStatement();
      String query =
          "insert into hotelfacilities(membershipNo,status,endTime,startTime,name,date,type) values(?,?,?,?,?,?,?)";
      pstmt = currentCon.prepareStatement(query);
      // inserting values

      pstmt.setString(1, hf.getMembershipNo());
      pstmt.setString(2, "true");
      pstmt.setString(3, hf.getEndTime());
      pstmt.setString(4, hf.getStartTime());
      pstmt.setString(5, hf.getName());
      pstmt.setString(6, hf.getDate());
      pstmt.setString(7, hf.getType());

      pstmt.executeUpdate();

    } catch (Exception ex) {

      System.out.println("Registration failed: An Exception has occurred! " + ex);
    }

    // exception handling
    finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (Exception e) {
        }
        rs = null;
      }

      if (stmt != null) {
        try {
          stmt.close();
        } catch (Exception e) {
        }
        stmt = null;
      }

      if (currentCon != null) {
        try {
          currentCon.close();
        } catch (Exception e) {
        }

        currentCon = null;
      }
    }
  }
示例#16
0
  /**
   * Determine whether or a not a User with the supplied researcherID exists
   *
   * @param username The researcherID to test
   * @return true if the user exists, false if not
   * @throws SQLException if a database error was encountered
   */
  public static boolean userExists(int researcherID) throws SQLException {
    boolean returnVal = false;

    // Get our connection to the database.
    Connection conn = DBConnectionManager.getConnection("yrc");
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      stmt = conn.prepareStatement("SELECT researcherID FROM tblUsers WHERE researcherID = ?");
      stmt.setInt(1, researcherID);

      rs = stmt.executeQuery();

      // No rows returned.
      if (!rs.next()) {
        returnVal = false;
      } else {
        returnVal = true;
      }

      rs.close();
      rs = null;

      stmt.close();
      stmt = null;

      conn.close();
      conn = null;
    } finally {

      // Always make sure result sets and statements are closed,
      // and the connection is returned to the pool
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {;
        }
        rs = null;
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {;
        }
        stmt = null;
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {;
        }
        conn = null;
      }
    }

    return returnVal;
  }
 /**
  * 获得数据库连接
  *
  * @return : 一个连接
  */
 public void getConnection() {
   if (conn == null) {
     try {
       conn = dbManager.getConnection();
     } catch (SQLException ex) {
       Debug.getAppErrLogger().error("取数据库连接错误!", ex);
     }
   }
 }
示例#18
0
  public static boolean checkGuest(GuestEntities guest) {

    Statement stmt = null;
    String searchQuery = "Select * From guest where username = '******'";
    boolean ableToCreate = false;
    // get the last member ID
    try {

      currentCon = DBConnectionManager.getConnection();
      stmt = currentCon.createStatement();
      rs = stmt.executeQuery(searchQuery);
      boolean more = rs.next();

      if (!more) {
        ableToCreate = true;
      } else {
        ableToCreate = false;
      }

    } catch (Exception ex) {

      System.out.println("Registration failed: An Exception has occurred! " + ex);
    }

    // exception handling
    finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (Exception e) {
        }
        rs = null;
      }

      if (stmt != null) {
        try {
          stmt.close();
        } catch (Exception e) {
        }
        stmt = null;
      }

      if (currentCon != null) {
        try {
          currentCon.close();
        } catch (Exception e) {
        }

        currentCon = null;
      }
    }
    return ableToCreate;
  }
  /**
   * This method is called by a service requesting a database connection and returns null if
   * connection could not be obtained.
   *
   * @param String sDatabaseName, the name of the database to create a connection to.
   * @return DBConnection, a connection to the spcified database, or null if the connection could
   *     not be made.
   */
  public DBConnection requestConnection(String sDatabaseName) {

    // If the database has not been opened with connections, open it
    if (!htDatabases.containsKey(sDatabaseName)) {
      if (!openProject(sDatabaseName)) return null; // could not get connection
    }

    // returns the ste of connections
    DBConnectionManager conSet = (DBConnectionManager) htDatabases.get(sDatabaseName);

    // returns the free connection
    return conSet.getConnection();
  }
示例#20
0
  public static void hideData(String membershipNo) {

    Statement stmt = null;

    // get the last member ID
    try {

      // query for inserting into the table
      currentCon = DBConnectionManager.getConnection();
      stmt = currentCon.createStatement();
      String query =
          "update hotelfacilities set status = 'false' where membershipNo = '" + membershipNo + "'";
      pstmt = currentCon.prepareStatement(query);

      pstmt.executeUpdate();

    } catch (Exception ex) {

      System.out.println("Registration failed: An Exception has occurred! " + ex);
    }

    // exception handling
    finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (Exception e) {
        }
        rs = null;
      }

      if (stmt != null) {
        try {
          stmt.close();
        } catch (Exception e) {
        }
        stmt = null;
      }

      if (currentCon != null) {
        try {
          currentCon.close();
        } catch (Exception e) {
        }

        currentCon = null;
      }
    }
  }
示例#21
0
  public String getStudentIAUSN(String usn, int sem) {
    String uSN = null;
    String query = "select distinct student_usn from studentiatable where student_usn=? and sem=?";
    Connection con = DBConnectionManager.getConnection();
    try {

      PreparedStatement ps1 = con.prepareStatement(query);
      ps1.setString(1, usn);
      ps1.setInt(2, sem);
      ResultSet rs = ps1.executeQuery();
      while (rs != null && rs.next()) {
        uSN = rs.getString(1);
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return uSN;
  }
示例#22
0
  public int getSem(String usn) {
    int sem = 0;
    Connection con = DBConnectionManager.getConnection();
    String sql = "select distinct sem from studentinfo where usn=?  ";
    try {
      PreparedStatement ps = con.prepareStatement(sql);
      ps.setString(1, usn);
      ResultSet rs = ps.executeQuery();

      while (rs != null && rs.next()) {
        sem = rs.getInt(1);
      }
      rs.close();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return sem;
  }
示例#23
0
 public ArrayList<Integer> getAverage(String usn, int sem) {
   Connection con = DBConnectionManager.getConnection();
   String sql = "select average from studentIAtable where student_usn=? and sem=?";
   ArrayList<Integer> avg = new ArrayList<Integer>();
   try {
     PreparedStatement ps = con.prepareStatement(sql);
     ps.setString(1, usn);
     ps.setInt(2, sem);
     ResultSet rs = ps.executeQuery();
     while (rs != null && rs.next()) {
       int ia;
       ia = rs.getInt(1);
       avg.add(ia);
     }
     rs.close();
   } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   }
   return avg;
 }
示例#24
0
  public String getDept(String usn) {
    String dept = null;
    Vector<String> departmnt = new Vector<String>();
    Connection con = DBConnectionManager.getConnection();
    String sql = "select dept from studentinfo where usn=?";
    try {
      PreparedStatement ps = con.prepareStatement(sql);
      ps.setString(1, usn);
      ResultSet rs = ps.executeQuery();

      while (rs != null && rs.next()) {
        dept = rs.getString(1);
      }
      rs.close();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

    return dept;
  }
示例#25
0
  public DefaultTableModel getStudentResult(String usn, int sem) {
    Vector<String> columnNames = new Vector<String>();
    columnNames.add("Subcode");
    columnNames.add("Internals");
    columnNames.add("Externals");
    columnNames.add("Total");
    columnNames.add("Remarks");
    String sql =
        "select subcode,Internals,Externals,Total,Remarks from studentresult where studentusn =? and sem=?";
    Connection con = DBConnectionManager.getConnection();
    Vector<String> row;
    Vector<Vector<String>> data = new Vector<Vector<String>>();

    try {
      PreparedStatement ps = con.prepareStatement(sql);
      ps.setString(1, usn);
      ps.setInt(2, sem);
      ResultSet rs = ps.executeQuery();
      while (rs != null && rs.next()) {
        row = new Vector<String>();
        row.add(rs.getString(1));
        row.add(String.valueOf(rs.getInt(2)));
        row.add(String.valueOf(rs.getInt(3)));
        row.add(String.valueOf(rs.getInt(4)));
        row.add(rs.getString(5));
        data.add(row);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        con.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
    return new DefaultTableModel(data, columnNames);
  }
示例#26
0
  /**
   * Determine whether or a not a Researcher with the supplied email exists
   *
   * @param email The email to test
   * @return The researcher ID of the researcher if it exists, -1 if it doesn't
   * @throws SQLException if a database error was encountered
   */
  public static int emailExists(String email) throws SQLException {
    int returnVal = -1;

    if (email == null || email.equals("")) {
      return -1;
    }

    // Get our connection to the database.
    Connection conn = DBConnectionManager.getConnection("yrc");
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      stmt =
          conn.prepareStatement(
              "SELECT researcherID FROM tblResearchers WHERE researcherEmail = ?");
      stmt.setString(1, email);

      rs = stmt.executeQuery();

      // No rows returned.
      if (!rs.next()) {
        returnVal = -1;
      } else {
        returnVal = rs.getInt("researcherID");
      }

      rs.close();
      rs = null;

      stmt.close();
      stmt = null;

      conn.close();
      conn = null;
    } finally {

      // Always make sure result sets and statements are closed,
      // and the connection is returned to the pool
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {;
        }
        rs = null;
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {;
        }
        stmt = null;
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {;
        }
        conn = null;
      }
    }

    return returnVal;
  }
示例#27
0
  /**
   * Get a populated User object corresponding to a username.
   *
   * @param username The username to test
   * @return The User object corresponding to that username.
   * @throws NoSuchUserException if that username does not exist.
   * @throws SQLException if a database error was encountered.
   */
  public static User getUser(String username) throws NoSuchUserException, SQLException {
    // The User to return
    User theUser;

    // Make sure the username isn't null
    if (username == null) {
      throw new NoSuchUserException("got null for username in UserUtils.getUser");
    }

    // Get our connection to the database.
    Connection conn = DBConnectionManager.getConnection("yrc");
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      stmt = conn.prepareStatement("SELECT researcherID FROM tblUsers WHERE username = ?");
      stmt.setString(1, username);

      rs = stmt.executeQuery();

      // No rows returned.
      if (!rs.next()) {
        throw new NoSuchUserException("Username not found.");
      }

      theUser = new User();

      try {
        theUser.load(rs.getInt("researcherID"));
      } catch (InvalidIDException e) {
        throw new NoSuchUserException(
            "Somehow, we got an invalid ID ("
                + rs.getInt("researcherID")
                + ") after we got the ID from the username...  This can't be good.");
      }

      rs.close();
      rs = null;

      stmt.close();
      stmt = null;

      conn.close();
      conn = null;
    } finally {

      // Always make sure result sets and statements are closed,
      // and the connection is returned to the pool
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {;
        }
        rs = null;
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {;
        }
        stmt = null;
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {;
        }
        conn = null;
      }
    }

    return theUser;
  }
示例#28
0
  public static GuestEntities guestRegister(GuestEntities guest) {

    Statement stmt = null;
    int maxId = 0;
    int currentId;
    // get the last member ID
    try {
      currentCon = DBConnectionManager.getConnection();
      stmt = currentCon.createStatement();
      String getMax = "select * from guest";
      rs1 = stmt.executeQuery(getMax);
      while (rs1.next()) {
        String membershipNo = rs1.getString("membershipNo");
        currentId = Integer.parseInt(membershipNo);
        if (currentId > maxId) {
          maxId = currentId;
        }
      }

      int nextId = maxId + 1;
      DecimalFormat df = new DecimalFormat("000000000");
      String newMembershipNo;
      newMembershipNo = df.format(nextId).toString();
      guest.setMembershipNo(newMembershipNo);
      currentCon = DBConnectionManager.getConnection();
      stmt = currentCon.createStatement();
      // query for inserting into the table
      String query =
          "insert into guest(userName, password, firstName, lastName, membershipNo, gender, nationality, dob, address, occupation, membership, points, roomNumber, cost) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
      pstmt = currentCon.prepareStatement(query);
      // inserting values
      pstmt.setString(1, guest.getUserName());
      pstmt.setString(2, guest.getPassword());
      pstmt.setString(3, guest.getFirstName());
      pstmt.setString(4, guest.getLastName());
      pstmt.setString(5, newMembershipNo);
      pstmt.setString(6, guest.getGender());
      pstmt.setString(7, guest.getNationality());
      pstmt.setString(8, guest.getDob());
      pstmt.setString(9, guest.getAddress());
      pstmt.setString(10, guest.getOccupation());
      pstmt.setString(11, guest.getMembership());
      pstmt.setInt(12, guest.getPoints());
      pstmt.setInt(13, guest.getRoomNo());
      pstmt.setDouble(14, guest.getCost());
      pstmt.executeUpdate();

    } catch (Exception ex) {

      System.out.println("Registration failed: An Exception has occurred! " + ex);
    }

    // exception handling
    finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (Exception e) {
        }
        rs = null;
      }

      if (stmt != null) {
        try {
          stmt.close();
        } catch (Exception e) {
        }
        stmt = null;
      }

      if (currentCon != null) {
        try {
          currentCon.close();
        } catch (Exception e) {
        }

        currentCon = null;
      }
    }
    return guest;
  }