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>();
}