public List<Message> loadMessages(int minId, int maxId) throws SQLException {
List<Message> messages = new LinkedList<Message>();
// prepare the statement
//
String query = "select * from Message where id >= ? and id <= ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setInt(1, minId);
statement.setInt(2, maxId);
ResultSet set = statement.executeQuery();
Message msg = null;
while (set.next()) {
msg = new Message();
msg.setId(set.getInt("id"));
msg.setContent(set.getString("content"));
msg.setFormattedContent(set.getString("formatted_content"));
msg.setPublishDate(set.getString("publishDate"));
msg.setUrl(set.getString("url"));
messages.add(msg);
}
set.close();
statement.close();
return messages;
}
public List<Message> loadMessages(int[] ids) {
try {
List<Message> messages = new LinkedList<Message>();
// prepare the statement
//
Statement statement = connection.createStatement();
String query = "select * from Message where ";
for (int i = 0; i < ids.length; i++) {
query += "id = " + ids[i];
if (i != (ids.length - 1)) {
query += " or ";
}
}
ResultSet set = statement.executeQuery(query);
Message msg = null;
while (set.next()) {
msg = new Message();
msg.setId(set.getInt("id"));
msg.setContent(set.getString("content"));
msg.setFormattedContent(set.getString("formatted_content"));
msg.setPublishDate(set.getString("publishDate"));
msg.setUrl(set.getString("url"));
messages.add(msg);
}
set.close();
statement.close();
return messages;
} catch (SQLException e) {
e.printStackTrace();
}
return new LinkedList<Message>();
}
public Message loadMessage(String url) {
// thread null case
//
if (url == null) return null;
try {
// prepare the statement
//
String query = "select * from Message where url like ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, url);
ResultSet set = statement.executeQuery();
Message msg = null;
if (set.next()) {
msg = new Message();
msg.setId(set.getInt("id"));
msg.setContent(set.getString("content"));
msg.setFormattedContent(set.getString("formatted_content"));
msg.setPublishDate(set.getString("publishDate"));
msg.setUrl(set.getString("url"));
// TODO Load the message thread and user
}
set.close();
statement.close();
return msg;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public List<Message> loadThreadMessages(int threadId) {
try {
List<Message> messages = new LinkedList<Message>();
// prepare the statement
//
String query = "select * from Message where threadID = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setInt(1, threadId);
ResultSet set = statement.executeQuery();
Message msg = null;
while (set.next()) {
msg = new Message();
msg.setId(set.getInt("id"));
msg.setContent(set.getString("content"));
msg.setFormattedContent(set.getString("formatted_content"));
msg.setPublishDate(set.getString("publishDate"));
msg.setUrl(set.getString("url"));
messages.add(msg);
}
set.close();
statement.close();
return messages;
} catch (SQLException e) {
e.printStackTrace();
}
return new LinkedList<Message>();
}
public boolean insertMessage(Message message) throws SQLException {
// threat null case
//
if (message == null) return false;
// check if message exists
//
if (this.loadMessage(message.getUrl()) != null) {
return false;
}
// make sure the message can be inserted
//
if (message.getUser() == null)
throw new IllegalArgumentException("Message's user cannot be null when inserting");
if (message.getMessageThread() == null)
throw new IllegalArgumentException("Message's thread cannot be null when inserting");
//
// persist the words
//
this.insertWords(this.getRegularWords(message.getFormattedContent()));
//
// persist the message
//
String query =
"insert into Message(threadID,userID,publishDate,content,formatted_content,url,parentID) values(?,?,?,?,?,?,?);";
PreparedStatement statement =
connection.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
statement.setInt(1, message.getMessageThread().getId());
statement.setInt(2, message.getUser().getId());
statement.setString(3, message.getPublishDate());
statement.setString(4, message.getContent());
statement.setString(5, message.getFormattedContent());
statement.setString(6, message.getUrl());
if (message.getParent() != null) {
Message msg = this.loadMessage(message.getParent().getUrl());
if (msg != null) statement.setInt(7, msg.getId());
else statement.setInt(7, -1);
} else statement.setInt(7, -1);
statement.execute();
ResultSet set = statement.getGeneratedKeys();
if (!set.next()) {
System.out.println("!! Failed to read auto generated keys !!");
} else {
message.setId(set.getInt(1));
}
set.close();
statement.close();
System.out.println("Inserted a new message:");
System.out.println("\t> url : " + message.getUrl());
// System.out.println ("\t> date : " + message.getPublishDate());
// System.out.println ("\t> content : " + message.getFormattedContent());
// this.calculateMessageProperties(message.getId());
return true;
}