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();
}
}
}
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;
}
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();
}
}
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;
}
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();
}
}
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;
}
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;
}
}
}
/**
* 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);
}
}
}
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();
}
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;
}
}
}
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;
}
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;
}
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;
}
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;
}
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);
}
/**
* 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;
}
/**
* 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;
}
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;
}
