/**
* ** 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);
}
}
/* 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;
}
/**
* 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;
}
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;
}
/* 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;
}
/**
* 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();
}
}
/**
* 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()
+ ")");
}
/**
* 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
+ ")");
}
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;
}
/**
* 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();
}
}
/*
* 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;
}
public MessageDatabase() {
con = DBConnection.getSharedConnection();
}
/**
* ** 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());
}
}
