public void runTest() throws SQLException {
db.start(this, "Transactions");
db.openConnection();
processTransactions();
db.closeConnection();
db.end();
db.openConnection();
processTransactions();
db.logMemory(this, "Memory Usage");
db.closeConnection();
}
@Override
public void runTest() throws SQLException {
database.start(this, "Transactions");
database.openConnection();
processTransactions();
database.closeConnection();
database.end();
database.openConnection();
processTransactions();
database.logMemory(this, "Memory Usage");
database.closeConnection();
}
/**
* Oppdaterer den totale vente-tiden i ett f*g. Metoden legger til waitingtime til det som lå der
* fra før.
*
* @param subjectcode
* @param waitingtime
* @return
*/
public static boolean updateTotalWaitingTime(String subjectcode, int waitingtime) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
return false;
}
PreparedStatement ps = null;
try {
ps =
db.connection.prepareStatement(
"UPDATE queue SET total_helped = total_helped + 1,total_waitingtime = total_waitingtime + ? WHERE subject_code = ?");
ps.setInt(1, waitingtime);
ps.setString(2, subjectcode);
ps.executeUpdate();
} catch (Exception e) {
System.out.println(e);
return false;
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeConnection(db.connection);
}
return true;
}
/**
* Endrer en entry(kø-element) til aktiv tilstand(får hjelp av en lærer).
*
* @param teacher
* @param user
* @param active
*/
public static void setActive(String teacher, String user, boolean active) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
return;
}
PreparedStatement ps = null;
try {
ps =
db.connection.prepareStatement(
"UPDATE queue_list SET status = ?,teacher_username = ? WHERE person_username = ?");
ps.setBoolean(1, active);
ps.setString(2, teacher);
ps.setString(3, user);
ps.executeUpdate();
} catch (Exception e) {
System.out.println(e);
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeConnection(db.connection);
}
}
/**
* Endrer kø info til ett bestemt f*g
*
* @param code
* @param info
* @param username
* @return
*/
public static boolean endreKoInfo(String code, String info, String username) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
return false;
}
PreparedStatement ps = null;
try {
ps =
db.connection.prepareStatement(
"UPDATE queue SET info = ?,info_username = ? WHERE subject_code = ?");
ps.setString(1, info);
ps.setString(2, username);
ps.setString(3, code);
ps.executeUpdate();
} catch (Exception e) {
System.out.println(e);
return false;
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeConnection(db.connection);
}
return true;
}
/**
* Fjerner en entry
*
* @param username
* @param subjectcode
* @return
*/
public static boolean removeQueueEntry(String username) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
return false;
}
PreparedStatement ps = null;
/* sletter din entrys */
try {
ps = db.connection.prepareStatement("DELETE FROM queue_list WHERE person_username = ?");
ps.setString(1, username);
ps.executeUpdate();
} catch (Exception e) {
System.out.println(e);
return false;
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeConnection(db.connection);
}
return true;
}
/**
* Henter ut hvilken rolle en bruker har i ett bestemt f*g.
*
* @param subjectcode
* @param username
* @return
*/
public static String getSubject_access(String subjectcode, String username) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
}
PreparedStatement ps = null;
ResultSet rs = null;
String role = "student";
/* Henter hvilken access brukeren har i faget */
try {
ps =
db.connection.prepareStatement(
"SELECT r.role_name FROM roles r INNER JOIN person_sub_role psr ON psr.role_id = r.id WHERE psr.username = ? AND psr.subjectcode = ? ");
ps.setString(1, username);
ps.setString(2, subjectcode);
rs = ps.executeQuery();
if (rs.next()) {
role = rs.getString("role_name");
}
} catch (Exception e) {
System.out.println(e);
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeResSet(rs);
Cleanup.closeConnection(db.connection);
}
return role;
}
/**
* oppretter en kø dersom det ikke finnes fra før.
*
* @param subjectcode
* @return
*/
public static boolean startQueue(String subjectcode) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
return false;
}
PreparedStatement ps = null;
try {
ps = db.connection.prepareStatement("UPDATE queue SET status = true WHERE subject_code = ?");
ps.setString(1, subjectcode);
ps.executeUpdate();
} catch (Exception e) {
System.out.println(e);
return false;
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeConnection(db.connection);
}
return true;
}
/**
* Sletter alle kø-elementene fra ett f*g slik at fagets kø er tom.
*
* @param subjectcode
* @return
*/
public static boolean emptyQueue(String subjectcode) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
return false;
}
PreparedStatement ps = null;
ResultSet rs = null;
/* henter queue_id */
int queue_id = 0;
try {
ps = db.connection.prepareStatement("SELECT id FROM queue WHERE subject_code = ?");
ps.setString(1, subjectcode);
rs = ps.executeQuery();
if (rs.next()) {
queue_id = rs.getInt("id");
}
} catch (Exception e) {
System.out.println(e);
}
/* sletter alle entrys */
try {
ps = db.connection.prepareStatement("DELETE FROM queue_list WHERE queue_id = ?");
ps.setInt(1, queue_id);
ps.executeUpdate();
} catch (Exception e) {
System.out.println(e);
return false;
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeConnection(db.connection);
}
return true;
}
/**
* Legger inn en spesifikk øving i person_ass tabellen.
*
* @param username
* @param subjectcode
* @param assignment
*/
public static void godkjennOving(String username, String subjectcode, int assignment) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
}
PreparedStatement ps = null;
ResultSet rs = null;
int assignment_id = -1;
try {
/* henter ut assignemnt_id */
ps =
db.connection.prepareStatement(
"SELECT id FROM assignments WHERE subjectcode = ? AND assignmentnumber = ?");
ps.setString(1, subjectcode);
ps.setInt(2, assignment);
rs = ps.executeQuery();
if (rs.next()) {
assignment_id = rs.getInt("id");
}
/* Legger inn en øving til en person */
ps =
db.connection.prepareStatement(
"INSERT INTO person_ass(username,assignment_id) VALUES(?,?)");
ps.setString(1, username);
ps.setInt(2, assignment_id);
ps.executeUpdate();
} catch (Exception e) {
System.out.println(e);
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeResSet(rs);
Cleanup.closeConnection(db.connection);
}
}
/**
* Henter ut alle personer i ett f*g som personSimple objekter. Henter kun ut brukernavn, fornavn
* og etternavn.
*
* @param code
* @return
*/
public static ArrayList<PersonSimple> getPersonerSimple(String code) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
}
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<PersonSimple> personer = new ArrayList();
/* Henter hvilken access brukeren har i faget */
try {
ps =
db.connection.prepareStatement(
"SELECT p.username,p.firstname,p.lastname FROM person p INNER JOIN person_sub_role ps ON p.username = ps.username WHERE ps.subjectcode = ? AND role_id = ?");
ps.setString(1, code);
ps.setInt(2, 1);
rs = ps.executeQuery();
while (rs.next()) {
personer.add(
new PersonSimple(
rs.getString("username"), rs.getString("firstname"), rs.getString("lastname")));
}
} catch (Exception e) {
System.out.println(e);
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeResSet(rs);
Cleanup.closeConnection(db.connection);
}
return personer;
}
@Override
public void init(Database db, int size) throws SQLException {
this.database = db;
transactions = size * 6;
int scale = 2;
accounts = size * 30;
tellers = Math.max(accounts / 10, 1);
branches = Math.max(tellers / 10, 1);
db.start(this, "Init");
db.openConnection();
db.dropTable("BRANCHES");
db.dropTable("TELLERS");
db.dropTable("ACCOUNTS");
db.dropTable("HISTORY");
String[] create = {
"CREATE TABLE BRANCHES(BID INT NOT NULL PRIMARY KEY, "
+ "BBALANCE DECIMAL(15,2), FILLER VARCHAR(88))",
"CREATE TABLE TELLERS(TID INT NOT NULL PRIMARY KEY, "
+ "BID INT, TBALANCE DECIMAL(15,2), FILLER VARCHAR(84))",
"CREATE TABLE ACCOUNTS(AID INT NOT NULL PRIMARY KEY, "
+ "BID INT, ABALANCE DECIMAL(15,2), FILLER VARCHAR(84))",
"CREATE TABLE HISTORY(TID INT, "
+ "BID INT, AID INT, DELTA DECIMAL(15,2), HTIME DATETIME, "
+ "FILLER VARCHAR(40))"
};
for (String sql : create) {
db.update(sql);
}
PreparedStatement prep;
db.setAutoCommit(false);
int commitEvery = 1000;
prep =
db.prepare(
"INSERT INTO BRANCHES(BID, BBALANCE, FILLER) "
+ "VALUES(?, 10000.00, '"
+ FILLER
+ "')");
for (int i = 0; i < branches * scale; i++) {
prep.setInt(1, i);
db.update(prep, "insertBranches");
if (i % commitEvery == 0) {
db.commit();
}
}
db.commit();
prep =
db.prepare(
"INSERT INTO TELLERS(TID, BID, TBALANCE, FILLER) "
+ "VALUES(?, ?, 10000.00, '"
+ FILLER
+ "')");
for (int i = 0; i < tellers * scale; i++) {
prep.setInt(1, i);
prep.setInt(2, i / tellers);
db.update(prep, "insertTellers");
if (i % commitEvery == 0) {
db.commit();
}
}
db.commit();
int len = accounts * scale;
prep =
db.prepare(
"INSERT INTO ACCOUNTS(AID, BID, ABALANCE, FILLER) "
+ "VALUES(?, ?, 10000.00, '"
+ FILLER
+ "')");
for (int i = 0; i < len; i++) {
prep.setInt(1, i);
prep.setInt(2, i / accounts);
db.update(prep, "insertAccounts");
if (i % commitEvery == 0) {
db.commit();
}
}
db.commit();
db.closeConnection();
db.end();
// db.start(this, "Open/Close");
// db.openConnection();
// db.closeConnection();
// db.end();
}
/**
* Metoden som kalles ved hver "polling" all info som trengs når du ser på køen. blir metoden for
* "tung" kan eventuelt øvingsoversikt delen fjernes..(2 spørringer)
*
* @param subjectcode
* @return
*/
public static Queue getQueueBean(String username, String subjectcode) {
Queue q = new Queue();
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
}
PreparedStatement ps = null;
PreparedStatement ps2 = null;
ResultSet rs = null;
ResultSet rs2 = null;
int queue_id = 0;
String info_username = "";
/* Henter Queue info */
try {
ps = db.connection.prepareStatement("SELECT * FROM queue WHERE subject_code = ?");
ps.setString(1, subjectcode);
rs = ps.executeQuery();
if (rs.next()) {
queue_id = rs.getInt("id");
q.setSubjectName(subjectcode);
q.setQueue_info(rs.getString("info"));
q.setStatus(rs.getBoolean("status"));
q.setTotalWaitingTime(rs.getInt("total_waitingtime"));
q.setTotalHelped(rs.getInt("total_helped"));
info_username = rs.getString("info_username");
}
ps =
db.connection.prepareStatement(
"SELECT firstname,lastname FROM person WHERE username = ?");
ps.setString(1, info_username);
rs = ps.executeQuery();
if (rs.next()) {
q.setQueue_info_firstname(rs.getString("firstname"));
q.setQueue_info_lastname(rs.getString("lastname"));
}
} catch (Exception e) {
System.out.println(e);
}
/* henter alle entrys i koen */
ArrayList<queue_entry> entrys = new ArrayList();
try {
ps = db.connection.prepareStatement("SELECT * FROM queue_list WHERE queue_id = ?");
ps.setInt(1, queue_id);
rs = ps.executeQuery();
while (rs.next()) {
int queue_list_id = rs.getInt("id");
ArrayList<String> queue_list_persons = new ArrayList();
ps2 =
db.connection.prepareStatement(
"SELECT person_username FROM queue_list_person WHERE queue_list_id = ?");
ps2.setInt(1, queue_list_id);
rs2 = ps2.executeQuery();
while (rs2.next()) {
queue_list_persons.add(rs2.getString("person_username"));
}
entrys.add(
new queue_entry(
rs.getString("info"),
rs.getString("room"),
rs.getInt("table_nr"),
rs.getBoolean("status"),
rs.getString("person_username"),
rs.getString("teacher_username"),
queue_list_persons,
rs.getTimestamp("submit_time")));
}
q.setQueue_entrys(entrys);
q.setEstimatedTime();
} catch (Exception e) {
System.out.println(e);
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeResSet(rs);
Cleanup.closeConnection(db.connection);
}
return q;
}
/**
* Henter ut alle øvingene til alle personene som ligger i en queue entry.
*
* @param person
* @return
*/
public static Queue_godkjenning getListObjects(String person, String subjectcode) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
}
PreparedStatement ps = null;
ResultSet rs = null;
Queue_godkjenning Qg = new Queue_godkjenning();
ArrayList<String> personer = new ArrayList();
personer.add(person);
int queue_list_id = -1;
int queue_id = -1;
/* Henter ut info om entryen */
try {
ps =
db.connection.prepareStatement(
"SELECT ql.submit_time,ql.info,ql.room,ql.table_nr,ql.id,ql.queue_id,ql.person_username FROM queue_list ql INNER JOIN queue q ON q.id = ql.queue_id WHERE ql.person_username = ? AND q.subject_code = ?");
ps.setString(1, person);
ps.setString(2, subjectcode);
rs = ps.executeQuery();
if (rs.next()) {
Qg.setInfo(rs.getString("info"));
Qg.setRoom(rs.getString("room"));
Qg.setTable_nr(rs.getInt("table_nr"));
Qg.setTime(rs.getTimestamp("submit_time"));
queue_list_id = rs.getInt("id");
queue_id = rs.getInt("queue_id");
}
/* Henter ut antall øvinger i faget */
ps =
db.connection.prepareStatement(
"SELECT sub.assignments FROM subject sub INNER JOIN queue q ON q.subject_code = sub.subjectcode WHERE q.id = ?");
ps.setInt(1, queue_id);
rs = ps.executeQuery();
if (rs.next()) {
Qg.setNumberofassignments(rs.getInt("assignments"));
}
/* Henter ut alle personer som ligger i entryen utenom påmelder */
ps =
db.connection.prepareStatement(
"SELECT person_username FROM queue_list_person WHERE queue_list_id = ?");
ps.setInt(1, queue_list_id);
rs = ps.executeQuery();
while (rs.next()) {
personer.add(rs.getString("person_username"));
}
/* Henter ut alle øvinger til alle i entryen */
ArrayList<Assignment> ass;
Assignment ny;
for (String p : personer) {
ps =
db.connection.prepareStatement(
"SELECT ass.assignmentnumber FROM assignments ass INNER JOIN person_ass pas ON ass.id = pas.assignment_id WHERE pas.username = ? AND ass.subjectcode = ?");
ps.setString(1, p);
ps.setString(2, subjectcode);
rs = ps.executeQuery();
ass = new ArrayList();
while (rs.next()) {
ny = new Assignment();
ny.setAssignmentNumber(rs.getInt("assignmentnumber"));
ass.add(ny);
}
Qg.personer.add(new ListObject(p, ass));
}
} catch (Exception e) {
System.out.println(e);
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeResSet(rs);
Cleanup.closeConnection(db.connection);
}
return Qg;
}
/**
* Legger til en person i køen til ett f*g.
*
* @param username
* @param subjectcode
* @param info
* @return
*/
public static boolean addQueueEntry(
String username,
String subjectcode,
String info,
String room,
int table,
ArrayList<String> personer) {
Database db = new Database();
try {
db.openConnection();
} catch (Exception ex) {
System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
return false;
}
PreparedStatement ps = null;
ResultSet rs = null;
int queue_id = 0;
try {
ps = db.connection.prepareStatement("SELECT id FROM queue WHERE subject_code = ?");
ps.setString(1, subjectcode);
rs = ps.executeQuery();
if (rs.next()) {
queue_id = rs.getInt("ID");
}
} catch (Exception e) {
System.out.println(e);
}
try {
ps =
db.connection.prepareStatement(
"INSERT INTO queue_list (queue_id,info,room,table_nr,status,person_username) VALUES (?,?,?,?,?,?)");
ps.setInt(1, queue_id);
ps.setString(2, info);
ps.setString(3, room);
ps.setInt(4, table);
ps.setBoolean(5, false);
ps.setString(6, username);
ps.executeUpdate();
ps = db.connection.prepareStatement("SELECT id FROM queue_list WHERE person_username = ?");
ps.setString(1, username);
rs = ps.executeQuery();
int queue_list_id = -1;
if (rs.next()) {
queue_list_id = rs.getInt("id");
}
for (String pers : personer) {
ps =
db.connection.prepareStatement(
"INSERT INTO queue_list_person (person_username, queue_list_id) VALUES (?,?)");
ps.setString(1, pers);
ps.setInt(2, queue_list_id);
ps.executeUpdate();
}
} catch (Exception e) {
System.out.println(e);
return false;
} finally {
Cleanup.closeSentence(ps);
Cleanup.closeResSet(rs);
Cleanup.closeConnection(db.connection);
}
return true;
}