From ae3d3a1620cf75f0b5f1f74c108d977681d00f57 Mon Sep 17 00:00:00 2001 From: James Moger Date: Wed, 6 Apr 2016 12:28:32 -0400 Subject: Add methods to return group by field counts --- src/main/java/com/iciql/Query.java | 51 ++++++++++++++++ src/main/java/com/iciql/QueryWhere.java | 8 +++ src/main/java/com/iciql/ValueCount.java | 71 ++++++++++++++++++++++ src/test/java/com/iciql/test/ModelsTest.java | 44 ++++++++++++++ src/test/java/com/iciql/test/PrimitivesTest.java | 19 ++++++ .../com/iciql/test/models/PrimitivesModel.java | 4 ++ 6 files changed, 197 insertions(+) create mode 100644 src/main/java/com/iciql/ValueCount.java (limited to 'src') 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 { } } + public List> selectCount(X x) { + return selectCount(x, false); + } + + public List> selectCountDesc(X x) { + return selectCount(x, true); + } + + List> selectCount(X x, boolean desc) { + List> list = Utils.newArrayList(); + SelectColumn 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> 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(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 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 { return query.selectCount(); } + public List> selectCount(X x) { + return query.selectCount(x, false); + } + + public List> 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 implements Serializable, Comparable> { + + 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 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> 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> 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> 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> 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 models = PrimitivesModel.getList(); + Collections.reverse(models); + // insert them in reverse order + db.insertAll(models); + + PrimitivesModel primitives = new PrimitivesModel(); + List> 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; -- cgit v1.2.3