JdbcMessageRepository extends AbstractMessageRepository implements MessageRepository { @Override public int countUnread(Label label) { String where; if (label == null) { where = ""; } else { where = "id IN (SELECT message_id FROM Message_Label WHERE label_id=" + label.getId() + ") AND "; } where += "id IN (SELECT message_id FROM Message_Label WHERE label_id IN (" + "SELECT id FROM Label WHERE type = '" + Label.Type.UNREAD.name() + "'))"; try ( Connection connection = config.getConnection(); Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT count(*) FROM Message WHERE " + where) ) { if (rs.next()) { return rs.getInt(1); } } catch (SQLException e) { LOG.error(e.getMessage(), e); } return 0; } JdbcMessageRepository(JdbcConfig config); @Override int countUnread(Label label); @Override void save(Plaintext message); @Override void remove(Plaintext message);  }
@Test public void ensureUnreadMessagesCanBeFoundForAllLabels() { int unread = repo.countUnread(null); assertThat(unread, is(2)); }
@Test public void ensureUnreadMessagesCanBeFoundByLabel() { int unread = repo.countUnread(inbox); assertThat(unread, is(1)); }