aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--CHANGELOG.md6
-rw-r--r--src/main/java/com/iciql/Query.java51
-rw-r--r--src/main/java/com/iciql/QueryWhere.java8
-rw-r--r--src/main/java/com/iciql/ValueCount.java71
-rw-r--r--src/test/java/com/iciql/test/ModelsTest.java44
-rw-r--r--src/test/java/com/iciql/test/PrimitivesTest.java19
-rw-r--r--src/test/java/com/iciql/test/models/PrimitivesModel.java4
7 files changed, 199 insertions, 4 deletions
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 4c90f09..5bca337 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -2,11 +2,9 @@
All notable changes to this project will be documented in this file.
This project adheres to [Semantic Versioning](http://semver.org/).
-### [Unreleased][unreleased]
-#### Fixed
-#### Changed
+### [2.1.0][unreleased]
#### Added
-#### Removed
+- Add methods to select counts of a group by field (*SELECT field, COUNT(*) FROM table [WHERE conditions] GROUP BY field*)
### [2.0.0] - 2016-04-04
diff --git a/src/main/java/com/iciql/Query.java b/src/main/java/com/iciql/Query.java
index d26e7b7..20d5520 100644
--- a/src/main/java/com/iciql/Query.java
+++ b/src/main/java/com/iciql/Query.java
@@ -31,6 +31,7 @@ import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
+import java.util.Collections;
import java.util.HashMap;
import java.util.IdentityHashMap;
import java.util.List;
@@ -100,6 +101,56 @@ public class Query<T> {
}
}
+ public <X> List<ValueCount<X>> selectCount(X x) {
+ return selectCount(x, false);
+ }
+
+ public <X> List<ValueCount<X>> selectCountDesc(X x) {
+ return selectCount(x, true);
+ }
+
+ <X> List<ValueCount<X>> selectCount(X x, boolean desc) {
+ List<ValueCount<X>> list = Utils.newArrayList();
+ SelectColumn<T> col = getColumnByReference(x);
+ X alias = x;
+ if (col == null) {
+ alias = getPrimitiveAliasByValue(x);
+ col = getColumnByReference(alias);
+ }
+ if (col == null) {
+ throw new IciqlException("Unmapped column reference!");
+ }
+ groupBy(alias);
+
+ SQLStatement stat = getSelectStatement(false);
+ col.appendSQL(stat);
+ stat.appendSQL(", COUNT(*)");
+ appendFromWhere(stat);
+
+ ResultSet rs = stat.executeQuery();
+ Class<? extends DataTypeAdapter<?>> typeAdapter = col.getFieldDefinition().typeAdapter;
+ Class<?> clazz = x.getClass();
+ try {
+ // SQLite returns pre-closed ResultSets for query results with 0 rows
+ if (!rs.isClosed()) {
+ while (rs.next()) {
+ X value = (X) db.getDialect().deserialize(rs, 1, clazz, typeAdapter);
+ long count = rs.getLong(2);
+ list.add(new ValueCount<X>(value, count));
+ }
+ }
+ Collections.sort(list);
+ if (desc) {
+ Collections.reverse(list);
+ }
+ } catch (Exception e) {
+ throw IciqlException.fromSQL(stat.getSQL(), e);
+ } finally {
+ JdbcUtils.closeSilently(rs, true);
+ }
+ return list;
+ }
+
public List<T> select() {
return select(false);
}
diff --git a/src/main/java/com/iciql/QueryWhere.java b/src/main/java/com/iciql/QueryWhere.java
index 16f1397..de6ecd4 100644
--- a/src/main/java/com/iciql/QueryWhere.java
+++ b/src/main/java/com/iciql/QueryWhere.java
@@ -605,4 +605,12 @@ public class QueryWhere<T> {
return query.selectCount();
}
+ public <X> List<ValueCount<X>> selectCount(X x) {
+ return query.selectCount(x, false);
+ }
+
+ public <X> List<ValueCount<X>> selectCountDesc(X x) {
+ return query.selectCount(x, true);
+ }
+
}
diff --git a/src/main/java/com/iciql/ValueCount.java b/src/main/java/com/iciql/ValueCount.java
new file mode 100644
index 0000000..dfc8570
--- /dev/null
+++ b/src/main/java/com/iciql/ValueCount.java
@@ -0,0 +1,71 @@
+/*
+ * Copyright 2016 James Moger.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package com.iciql;
+
+import java.io.Serializable;
+import java.util.Date;
+
+public class ValueCount<X> implements Serializable, Comparable<ValueCount<X>> {
+
+ private final static long serialVersionUID = 1L;
+
+ private final X value;
+ private final long count;
+
+ ValueCount(X x, long count) {
+ this.value = x;
+ this.count = count;
+ }
+
+ public X getValue() {
+ return value;
+ }
+
+ public long getCount() {
+ return count;
+ }
+
+ @Override
+ public String toString() {
+ return String.format("%s=%d", value, count);
+ }
+
+ @Override
+ public int compareTo(ValueCount<X> o) {
+ return (count < o.count) ? -1 : ((count == o.count) ? compareValue(o.value) : 1);
+ }
+
+ private int compareValue(X anotherValue) {
+ if (value == null && anotherValue == null) {
+ return 0;
+ } else if (value == null) {
+ return 1;
+ } else if (anotherValue == null) {
+ return -1;
+ }
+
+ if (Number.class.isAssignableFrom(value.getClass())) {
+ long n1 = ((Number) value).longValue();
+ long n2 = ((Number) anotherValue).longValue();
+ return (n1 < n2) ? -1 : ((n1 == n2) ? 0 : 1);
+ } else if (Date.class.isAssignableFrom(value.getClass())) {
+ return ((Date) value).compareTo((Date) anotherValue);
+ }
+
+ return value.toString().compareToIgnoreCase(anotherValue.toString());
+ }
+}
+
diff --git a/src/test/java/com/iciql/test/ModelsTest.java b/src/test/java/com/iciql/test/ModelsTest.java
index 742ed53..96a6e4a 100644
--- a/src/test/java/com/iciql/test/ModelsTest.java
+++ b/src/test/java/com/iciql/test/ModelsTest.java
@@ -19,6 +19,7 @@ package com.iciql.test;
import com.iciql.Db;
import com.iciql.DbInspector;
+import com.iciql.ValueCount;
import com.iciql.ValidationRemark;
import com.iciql.test.models.Product;
import com.iciql.test.models.ProductAnnotationOnly;
@@ -174,4 +175,47 @@ public class ModelsTest {
assertEquals(10, ids.size());
assertEquals("[10, 9, 8, 7, 6, 5, 4, 3, 2, 1]", ids.toString());
}
+
+ @Test
+ public void testGroupByCount() {
+ Product products = new Product();
+
+ List<ValueCount<String>> categories = db.from(products)
+ .selectCount(products.category);
+ assertEquals(5, categories.size());
+ assertEquals("[Meat/Poultry=1, Produce=1, Seafood=1, Beverages=2, Condiments=5]", categories.toString());
+ }
+
+ @Test
+ public void testGroupByCountDesc() {
+ Product products = new Product();
+
+ List<ValueCount<String>> categories = db.from(products)
+ .selectCountDesc(products.category);
+ assertEquals(5, categories.size());
+ assertEquals("[Condiments=5, Beverages=2, Seafood=1, Produce=1, Meat/Poultry=1]", categories.toString());
+ }
+
+ @Test
+ public void testFilteredGroupByCount() {
+ Product products = new Product();
+
+ List<ValueCount<String>> categories = db.from(products)
+ .where(products.category).isNot("Seafood")
+ .selectCount(products.category);
+ assertEquals(4, categories.size());
+ assertEquals("[Meat/Poultry=1, Produce=1, Beverages=2, Condiments=5]", categories.toString());
+ }
+
+ @Test
+ public void testFilteredGroupByCountDesc() {
+ Product products = new Product();
+
+ List<ValueCount<String>> categories = db.from(products)
+ .where(products.category).isNot("Seafood")
+ .selectCountDesc(products.category);
+ assertEquals(4, categories.size());
+ assertEquals("[Condiments=5, Beverages=2, Produce=1, Meat/Poultry=1]", categories.toString());
+ }
+
}
diff --git a/src/test/java/com/iciql/test/PrimitivesTest.java b/src/test/java/com/iciql/test/PrimitivesTest.java
index 49595a2..db36be0 100644
--- a/src/test/java/com/iciql/test/PrimitivesTest.java
+++ b/src/test/java/com/iciql/test/PrimitivesTest.java
@@ -18,6 +18,7 @@ package com.iciql.test;
import com.iciql.Db;
import com.iciql.IciqlException;
+import com.iciql.ValueCount;
import com.iciql.test.models.MultipleBoolsModel;
import com.iciql.test.models.PrimitivesModel;
import org.junit.Test;
@@ -114,4 +115,22 @@ public class PrimitivesTest {
assertEquals(models.size(), list.size());
assertEquals("[10, 9, 8, 7, 6, 5, 4, 3, 2, 1]", list.toString());
}
+
+ @Test
+ public void testPrimitiveGroupByCount() {
+ Db db = IciqlSuite.openNewDb();
+
+ // insert random models in reverse order
+ List<PrimitivesModel> models = PrimitivesModel.getList();
+ Collections.reverse(models);
+ // insert them in reverse order
+ db.insertAll(models);
+
+ PrimitivesModel primitives = new PrimitivesModel();
+ List<ValueCount<Integer>> types = db.from(primitives)
+ .selectCount(primitives.typeCode);
+ assertEquals(2, types.size());
+ assertEquals("[0=5, 1=5]", types.toString());
+ }
+
}
diff --git a/src/test/java/com/iciql/test/models/PrimitivesModel.java b/src/test/java/com/iciql/test/models/PrimitivesModel.java
index 078b565..aebf9f6 100644
--- a/src/test/java/com/iciql/test/models/PrimitivesModel.java
+++ b/src/test/java/com/iciql/test/models/PrimitivesModel.java
@@ -50,6 +50,9 @@ public class PrimitivesModel {
@IQColumn
public float myFloat;
+ @IQColumn
+ public int typeCode;
+
public PrimitivesModel() {
Random rand = new Random();
myLong = rand.nextLong();
@@ -78,6 +81,7 @@ public class PrimitivesModel {
for (int i = 1; i <= 10; i++) {
PrimitivesModel p = new PrimitivesModel();
p.myLong = i;
+ p.typeCode = i % 2;
list.add(p);
}
return list;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-22 16:19:29 +0000