public void insertMessageThread(String url, String name, MessageBoard board) throws SQLException, IllegalArgumentException { // check for null params // if (url == null || board == null) throw new IllegalArgumentException( "Failed to insert message thread (url or message board are null)"); // make sure the thread doesn't exist // if (loadMessageThread(url) != null) { return; } String query = "insert into MessageThread(name,url,boardID) values(?,?,?);"; PreparedStatement statement = connection.prepareStatement(query); statement.setString(1, name); statement.setString(2, url); statement.setInt(3, board.getId()); statement.execute(); statement.close(); // System.out.println ("Inserted a new message thread:"); // System.out.println ("\t> name : " + name); // System.out.println ("\t> url : " + url); // if (board != null) { // System.out.println ("\t> board : " + board.getName()); // } else { // log this error // } }
public int getNumInvalidMessages(String boardName) { if (boardName == null) { try { Statement s0 = connection.createStatement(); ResultSet set0 = s0.executeQuery("select count(*) from Message where length(formatted_content) < 10"); if (!set0.next()) return 0; int count = set0.getInt(1); set0.close(); s0.close(); return count; } catch (SQLException e) { e.printStackTrace(); return 0; } } else { try { MessageBoard board = this.loadMessageBoard(boardName); if (board == null) { return 0; } Statement s0 = connection.createStatement(); ResultSet set0 = s0.executeQuery( "select count(m.id) from Message m, MessageThread t " + " where length(m.formatted_content) < 10 and m.threadID = t.id and t.boardID = " + board.getId()); int count = 0; while (set0.next()) { count += Integer.parseInt(set0.getString(1)); } set0.close(); s0.close(); return count; } catch (SQLException e) { e.printStackTrace(); return 0; } } }
public int getNumUsers(String boardName) { if (boardName == null) { try { Statement s0 = connection.createStatement(); ResultSet set0 = s0.executeQuery("select count(*) from User"); if (!set0.next()) return 0; int count = set0.getInt(1); set0.close(); s0.close(); return count; } catch (SQLException e) { e.printStackTrace(); return 0; } } else { try { MessageBoard board = this.loadMessageBoard(boardName); if (board == null) { return 0; } Statement s0 = connection.createStatement(); ResultSet set0 = s0.executeQuery( "select count(u.id) from User u " + " where u.boardID = " + board.getId()); int count = 0; while (set0.next()) { count += Integer.parseInt(set0.getString(1)); } set0.close(); s0.close(); return count; } catch (SQLException e) { e.printStackTrace(); return 0; } } }
public int getNumWords(String boardName, boolean distinct) { if (distinct) { try { Statement s0 = connection.createStatement(); ResultSet set0 = s0.executeQuery("select count(*) from Word"); if (!set0.next()) return 0; int count = set0.getInt(1); set0.close(); s0.close(); return count; } catch (SQLException e) { e.printStackTrace(); return 0; } } else { try { if (boardName != null) { MessageBoard board = this.loadMessageBoard(boardName); if (board == null) { return 0; } Statement s0 = connection.createStatement(); ResultSet set0 = s0.executeQuery( "select s.propertyValue from settings s, Message m, MessageThread t " + " where propertyKey like 'num-words' and s.tableID = m.id and m.threadID = t.id and t.boardID = " + board.getId()); int count = 0; while (set0.next()) { count += Integer.parseInt(set0.getString(1)); } set0.close(); s0.close(); return count; } else { Statement s0 = connection.createStatement(); ResultSet set0 = s0.executeQuery( "select s.propertyValue from settings s " + " where propertyKey like 'num-words'"); int count = 0; while (set0.next()) { count += Integer.parseInt(set0.getString(1)); } set0.close(); s0.close(); return count; } } catch (SQLException e) { e.printStackTrace(); return 0; } } }
public List<MessageWithProperties> loadMessagesWithProperties(String boardName) throws SQLException { List<MessageWithProperties> messages = new LinkedList<MessageWithProperties>(); if (boardName == null) { // prepare the statement // String query = "select * from Message"; Statement statement = connection.createStatement(); ResultSet set = statement.executeQuery(query); MessageWithProperties msg = null; while (set.next()) { msg = new MessageWithProperties(); 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")); msg.loadProperties(connection); messages.add(msg); } set.close(); statement.close(); } else { MessageBoard board = this.loadMessageBoard(boardName); if (board == null) { return messages; } // prepare the statement // String query = "select m.* from Message m, MessageThread t where m.threadID = t.id and t.boardID = " + board.getId(); Statement statement = connection.createStatement(); ResultSet set = statement.executeQuery(query); MessageWithProperties msg = null; while (set.next()) { msg = new MessageWithProperties(); 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")); msg.loadProperties(connection); messages.add(msg); } set.close(); statement.close(); } return messages; }