package uk.ac.cam.em487.fjava.tick5;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;
import uk.ac.cam.cl.fjava.messages.RelayMessage;
import uk.ac.cam.em487.fjava.tick5.FurtherJavaPreamble.Ticker;
@FurtherJavaPreamble(author = "Eduardo Munoz", date = "2011/11/19", crsid = "em487", summary = "Class to implement a database for a chat server. It stores the messages and some statistics", ticker = Ticker.D)
public class Database {
private Connection connection;
private final static Logger LOGGER = Logger.getLogger(Database.class
.getName());;
public Database(String databasePath) throws SQLException {
try {
Class.forName("org.hsqldb.jdbcDriver");
}
catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
connection = DriverManager.getConnection("jdbc:hsqldb:file:"
+ databasePath, "SA", "");
connection.setAutoCommit(false);
Statement delayStmt = connection.createStatement();
try {
// Always update data on disk
delayStmt.execute("SET WRITE_DELAY FALSE");
}
finally {
delayStmt.close();
}
Statement sqlStmt = connection.createStatement();
try {
// create database of messages
sqlStmt
.execute("CREATE TABLE messages(nick VARCHAR(255) NOT NULL,"
+ "message VARCHAR(4096) NOT NULL,timeposted BIGINT NOT NULL)");
}
catch (SQLException e) {
// this will happen when the table already exists
LOGGER
.info("The database seems to already be created. No need to do anything about it.");
}
finally {
sqlStmt.close();
}
sqlStmt = connection.createStatement();
try {
// create database for statistics
sqlStmt
.execute("CREATE TABLE statistics(key VARCHAR(255), value INT)");
// initialize values
sqlStmt
.execute("INSERT INTO statistics(key,value) VALUES ('Total messages',0)");
sqlStmt
.execute("INSERT INTO statistics(key,value) VALUES ('Total logins',0)");
}
catch (SQLException e) {
}
finally {
sqlStmt.close();
}
connection.commit();
}
public void close() throws SQLException {
connection.close();
}
public void incrementLogins() throws SQLException {
Statement sqlStmt = connection.createStatement();
try {
// create database of messages
sqlStmt
.execute("UPDATE statistics SET value = value+1 WHERE key='Total logins'");
}
finally {
sqlStmt.close();
}
connection.commit();
}
public void addMessage(RelayMessage m) throws SQLException {
Statement sqlStmt = connection.createStatement();
try {
sqlStmt
.execute("UPDATE statistics SET value = value+1 WHERE key='Total messages'");
}
finally {
sqlStmt.close();
}
String stmt = "INSERT INTO MESSAGES(nick,message,timeposted) VALUES (?,?,?)";
PreparedStatement insertMessage = connection.prepareStatement(stmt);
try {
insertMessage.setString(1, m.getFrom());
insertMessage.setString(2, m.getMessage());
insertMessage.setLong(3, m.getCreationTime().getTime());
insertMessage.executeUpdate();
}
finally {
insertMessage.close();
}
connection.commit();
}
public List<RelayMessage> getRecent() throws SQLException {
List<RelayMessage> recent = new LinkedList<RelayMessage>();
String stmt = "SELECT nick,message,timeposted FROM messages ORDER BY timeposted DESC LIMIT 10";
PreparedStatement recentMessages = connection.prepareStatement(stmt);
try {
ResultSet rs = recentMessages.executeQuery();
try {
while (rs.next()) {
String from = rs.getString(1);
String text = rs.getString(2);
Date date = new Date(rs.getLong(3));
RelayMessage rmes = new RelayMessage(from, text, date);
// always add to the head of the list, so messages in
// chronological order
recent.add(0, rmes);
}
}
finally {
rs.close();
}
}
finally {
recentMessages.close();
}
return recent;
}
public static void main(String[] args) throws SQLException {
if (args.length != 1) {
System.err
.println("Usage: java uk.ac.cam.em487.fjava.tick5.Database <database name>");
}
try {
Class.forName("org.hsqldb.jdbcDriver");
}
catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
Connection connection = DriverManager.getConnection("jdbc:hsqldb:file:"
+ args[0], "SA", "");
connection.setAutoCommit(false);
Statement delayStmt = connection.createStatement();
try {
// Always update data on disk
delayStmt.execute("SET WRITE_DELAY FALSE");
}
finally {
delayStmt.close();
}
Statement sqlStmt = connection.createStatement();
try {
sqlStmt
.execute("CREATE TABLE messages(nick VARCHAR(255) NOT NULL,"
+ "message VARCHAR(4096) NOT NULL,timeposted BIGINT NOT NULL)");
}
catch (SQLException e) {
System.out
.println("Warning: Database table \"messages\" already exists.");
}
finally {
sqlStmt.close();
}
String stmt = "INSERT INTO MESSAGES(nick,message,timeposted) VALUES (?,?,?)";
PreparedStatement insertMessage = connection.prepareStatement(stmt);
try {
insertMessage.setString(1, "Alastair");
insertMessage.setString(2, "Hello, Andy");
insertMessage.setLong(3, System.currentTimeMillis());
insertMessage.executeUpdate();
}
finally {
insertMessage.close();
}
connection.commit();
stmt = "SELECT nick,message,timeposted FROM messages "
+ "ORDER BY timeposted DESC LIMIT 10";
PreparedStatement recentMessages = connection.prepareStatement(stmt);
try {
ResultSet rs = recentMessages.executeQuery();
try {
while (rs.next())
System.out.println(rs.getString(1) + ": " + rs.getString(2)
+ " [" + rs.getLong(3) + "]");
}
finally {
rs.close();
}
}
finally {
recentMessages.close();
}
}
}