DBQueries { public static java.sql.ResultSet getDistinctKeysAndCounts(boolean sortByKeyDescending, final String tableName, final Connection connection){ final String selectSql = String.format("SELECT DISTINCT value, COUNT(value) AS valueCount FROM cqtbl_%s GROUP BY (value) %s", tableName, sortByKeyDescending ? "ORDER BY value DESC" : ""); Statement statement = null; try{ statement = connection.createStatement(); return statement.executeQuery(selectSql); }catch(Exception e){ DBUtils.closeQuietly(statement); throw new IllegalStateException("Unable to look up index entries and counts.", e); } }  static void createIndexTable(final String tableName, final Class<K> objectKeyClass, final Class<A> valueClass, final Connection connection); static boolean indexTableExists(final String tableName, final Connection connection); static void createIndexOnTable(final String tableName, final Connection connection); static void suspendSyncAndJournaling(final Connection connection); static void setSyncAndJournaling(final Connection connection, final SQLiteConfig.SynchronousMode pragmaSynchronous, final SQLiteConfig.JournalMode pragmaJournalMode); static SQLiteConfig.SynchronousMode getPragmaSynchronousOrNull(final Connection connection); static SQLiteConfig.JournalMode getPragmaJournalModeOrNull(final Connection connection); static void dropIndexOnTable(final String tableName, final Connection connection); static void dropIndexTable(final String tableName, final Connection connection); static void clearIndexTable(final String tableName, final Connection connection); static void compactDatabase(final Connection connection); static void expandDatabase(final Connection connection, long numBytes); static long getDatabaseSize(final Connection connection); static int bulkAdd(Iterable<Row<K, A>> rows, final String tableName, final Connection connection); static int bulkRemove(Iterable<K> objectKeys, final String tableName, final Connection connection); static int count(final Query<O> query, final String tableName, final Connection connection); static int countDistinct(final Query<O> query, final String tableName, final Connection connection); static java.sql.ResultSet search(final Query<O> query, final String tableName, final Connection connection); static java.sql.ResultSet getDistinctKeys(final Query<O> query, boolean descending, final String tableName, final Connection connection); static java.sql.ResultSet getKeysAndValues(final Query<O> query, boolean descending, final String tableName, final Connection connection); static int getCountOfDistinctKeys(final String tableName, final Connection connection); static java.sql.ResultSet getDistinctKeysAndCounts(boolean sortByKeyDescending, final String tableName, final Connection connection); static java.sql.ResultSet getAllIndexEntries(final String tableName, final Connection connection); static java.sql.ResultSet getIndexEntryByObjectKey(final K key , final String tableName, final Connection connection); static boolean contains(final K objectKey, final Query<O> query, final String tableName, final Connection connection);  }
@Test public void getDistinctKeysAndCounts(){ Connection connection = null; ResultSet resultSet = null; try { ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true); initWithTestData(connectionManager); connection = connectionManager.getConnection(null, noQueryOptions()); resultSet = DBQueries.getDistinctKeysAndCounts(false, NAME, connection); Map<String, Integer> resultSetToMap = resultSetToMap(resultSet); assertEquals(3, resultSetToMap.size()); assertEquals(new Integer(2), resultSetToMap.get("abs")); assertEquals(new Integer(1), resultSetToMap.get("airbags")); assertEquals(new Integer(1), resultSetToMap.get("gps")); }finally { DBUtils.closeQuietly(resultSet); DBUtils.closeQuietly(connection); } }
@Test public void getDistinctKeysAndCounts_SortByKeyDescending(){ Connection connection = null; ResultSet resultSet = null; try { ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true); initWithTestData(connectionManager); connection = connectionManager.getConnection(null, noQueryOptions()); resultSet = DBQueries.getDistinctKeysAndCounts(true, NAME, connection); Map<String, Integer> resultSetToMap = resultSetToMap(resultSet); assertEquals(3, resultSetToMap.size()); Iterator<Map.Entry<String, Integer>> entriesIterator = resultSetToMap.entrySet().iterator(); Map.Entry entry = entriesIterator.next(); assertEquals("gps", entry.getKey()); assertEquals(1, entry.getValue()); entry = entriesIterator.next(); assertEquals("airbags", entry.getKey()); assertEquals(1, entry.getValue()); entry = entriesIterator.next(); assertEquals("abs", entry.getKey()); assertEquals(2, entry.getValue()); }finally { DBUtils.closeQuietly(resultSet); DBUtils.closeQuietly(connection); } }