/**
* @param index
* @throws SQLException
*/
public void remove(int index) throws SQLException {
Connection connection = MySQLDatabase.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(DB_DELETE_REQUEST);
preparedStatement.setInt(1, index);
preparedStatement.executeUpdate();
MySQLDatabase.releaseConnection(connection);
}
/**
* Loads details of radio-button-selected paper into the user interface to allow editing
*
* @return ArrayList<String> list of fields describing the paper to be edited
*/
public ArrayList<String> editPaper() throws DLException {
ArrayList<ArrayList<String>> paper;
ArrayList<String> returnArray = new ArrayList();
try {
MySQLDatabase db = MySQLDatabase.getInstance();
ArrayList values = new ArrayList();
values.add(this.id);
paper = db.getData("SELECT title, abstract, citation FROM papers WHERE id = ?", values);
ArrayList<ArrayList<String>> keywords =
db.getData("SELECT keyword FROM paper_keywords WHERE id = ?", values);
for (int i = 0; i < paper.get(0).size(); i++) {
returnArray.add(paper.get(0).get(i));
}
String keyword = "";
for (int i = 0; i < keywords.size(); i++) {
for (int j = 0; j < keywords.get(0).size(); j++) {
keyword = keyword + keywords.get(i).get(j) + ",";
}
}
returnArray.add(keyword);
} catch (RuntimeException e) {
throw new DLException(
e,
"Unix time: " + String.valueOf(System.currentTimeMillis() / 1000),
"Error in editPaper() of Faculty");
}
return returnArray;
}
public static void main(String[] args) {
MySQLDatabase mysql = new MySQLDatabase();
LinkedList<DataPoint> res = mysql.query("select * from Wordcount where id = 4");
for (int i = 0; i < res.size(); i++) {
System.out.println(res.toString());
}
mysql.close();
}
/**
* @return amount of requests
* @throws SQLException
*/
public Integer getAmountOfRequests() throws SQLException {
Connection c = MySQLDatabase.getConnection();
PreparedStatement s = c.prepareStatement(DB_AMOUNT_REQUESTS);
ResultSet rs = s.executeQuery();
int amount = 0;
if (rs.next()) {
amount = rs.getInt(ColumnNames.AMOUNT);
}
MySQLDatabase.releaseConnection(c);
return amount;
}
/**
* @param index
* @return get different between dates in database
* @throws SQLException
*/
public Integer getDateDiff(int index) throws SQLException {
Connection c = MySQLDatabase.getConnection();
PreparedStatement s = c.prepareStatement(DB_GET_DATE_DIFF);
s.setInt(1, index);
ResultSet rs = s.executeQuery();
if (rs.next()) {
return rs.getInt(ColumnNames.DIFF);
}
MySQLDatabase.releaseConnection(c);
return null;
}
/**
* @return list of requests
* @throws SQLException
*/
public List<RequestRoom> getAll() throws SQLException {
Connection c = MySQLDatabase.getConnection();
PreparedStatement s = c.prepareStatement(DB_GET_ALL_REQUESTS);
ResultSet rs = s.executeQuery();
List<RequestRoom> result = new ArrayList<>();
while (rs.next()) {
result.add(getRequestRoom(rs));
}
MySQLDatabase.releaseConnection(c);
return result;
}
/**
* @param from
* @param amount
* @return list of requests
* @throws SQLException
*/
public List<RequestRoom> getRequests(int from, int amount) throws SQLException {
Connection c = MySQLDatabase.getConnection();
String str = DB_GET_ALL_REQUESTS + " limit " + from + ", " + amount;
PreparedStatement s = c.prepareStatement(str);
ResultSet rs = s.executeQuery();
List<RequestRoom> result = new ArrayList<>();
while (rs.next()) {
result.add(getRequestRoom(rs));
}
MySQLDatabase.releaseConnection(c);
return result;
}
/**
* @param request
* @throws SQLException
*/
public void add(RequestRoom request) throws SQLException {
Connection connection = MySQLDatabase.getConnection();
String arrival = "cast(N'" + request.getArrivalDate() + "' as DateTime)";
String departure = "cast(N'" + request.getDepartureDate() + "' as DateTime)";
String url = String.format(DB_INSERT_REQUEST, arrival, departure);
PreparedStatement preparedStatement = connection.prepareStatement(url);
preparedStatement.setInt(1, request.getId());
preparedStatement.setInt(2, request.getAmountOfBed());
preparedStatement.setString(3, request.getClassOfRoom());
preparedStatement.setInt(4, request.getClient().getUser().getId());
preparedStatement.executeUpdate();
MySQLDatabase.releaseConnection(connection);
}
/**
* Adds a paper to the papers table, adds keywords to paper_keywords table
*
* @param keywords
* @param facultyId
* @throws DLException
*/
public void addPaper(String keywords, int facultyId) throws DLException {
MySQLDatabase db = MySQLDatabase.getInstance();
try {
ArrayList values = new ArrayList();
values.add(this.id);
values.add(title);
values.add(pAbstract);
values.add(citation);
ArrayList authorship = new ArrayList();
authorship.add(facultyId);
authorship.add(this.id);
ArrayList keywordList = new ArrayList();
String[] kws = keywords.split(",");
keywordList.add(this.id);
keywordList.add(kws[0]);
db.startTrans();
db.setData("INSERT INTO papers(id, title, abstract, citation) VALUES(?, ?, ?, ?);", values);
db.setData("INSERT INTO authorship(facultyId, paperId) VALUES(?, ?)", authorship);
for (String kw : kws) {
keywordList.set(1, kw);
db.setData("INSERT INTO paper_keywords VALUES(?, ?);", keywordList);
}
db.endTrans();
} catch (RuntimeException e) {
throw new DLException(
e,
"Unix time: " + String.valueOf(System.currentTimeMillis() / 1000),
"Error in addPaper() of Papers");
}
}
/**
* Updates the papers table to reflect updated details for a paper. Updates keywords in
* paper_keywords.
*
* @param title
* @param pAbstract
* @param citation
* @param keywords
* @param facultyId
* @throws DLException
*/
public void save(String title, String pAbstract, String citation, String keywords, int facultyId)
throws DLException {
MySQLDatabase db = MySQLDatabase.getInstance();
try {
ArrayList values = new ArrayList();
values.add(title);
values.add(pAbstract);
values.add(citation);
values.add(this.id);
ArrayList oldKeywords = new ArrayList();
oldKeywords.add(this.id);
String[] kws = keywords.split(",");
ArrayList keywordList = new ArrayList();
keywordList.add(this.id);
keywordList.add(kws[0]);
db.startTrans();
db.setData("UPDATE papers SET title = ?, abstract = ?, citation = ? WHERE id = ?;", values);
db.setData("DELETE FROM paper_keywords WHERE id = ?", oldKeywords);
for (String kw : kws) {
keywordList.set(1, kw);
db.setData("INSERT INTO paper_keywords VALUES(?, ?);", keywordList);
}
db.endTrans();
} catch (RuntimeException e) {
throw new DLException(
e,
"Unix time: " + String.valueOf(System.currentTimeMillis() / 1000),
"Error in save() of Papers");
}
}
/**
* @param id
* @return object RequestRoom
* @throws SQLException
*/
public RequestRoom get(Integer id) throws SQLException {
Connection c = MySQLDatabase.getConnection();
PreparedStatement s = c.prepareStatement(DB_GET_REQUEST);
s.setInt(1, id);
ResultSet rs = s.executeQuery();
RequestRoom requestRoom = new RequestRoom();
if (rs.next()) {
requestRoom.setId(id);
requestRoom.setClassOfRoom(rs.getString(ColumnNames.CLASS));
requestRoom.setAmountOfBed(rs.getInt(ColumnNames.AMOUNT_OF_BED));
requestRoom.setDepartureDate(rs.getDate(ColumnNames.DEPARTURE_DATE));
requestRoom.setArrivalDate(rs.getDate(ColumnNames.ARRIVAL_DATE));
Client client = new Client();
User user = new User();
user.setId(rs.getInt(ColumnNames.USER_ID));
client.setUser(user);
requestRoom.setClient(client);
}
MySQLDatabase.releaseConnection(c);
return requestRoom;
}
