@Override public QueryBuilder groupBy(String... attributePath) { return getQueryBuilder().groupBy(attributePath); }
@Override public QueryBuilder groupBy(String... attributePath) { return getQueryBuilder().groupBy(attributePath); }
@Test(expectedExceptions = ParsingException.class, expectedExceptionsMessageRegExp = "ISPN014021: Queries containing grouping and aggregation functions must use projections.") public void testGroupBy5() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .groupBy("name") .build(); q.list(); }
public void testGroupingAndAggregationOnSameField() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(count("surname")) .groupBy("surname") .build(); List<Object[]> list = q.list(); assertEquals(3, list.size()); assertEquals(1, list.get(0).length); assertEquals(1L, list.get(0)[0]); assertEquals(1L, list.get(1)[0]); assertEquals(1L, list.get(2)[0]); }
public void testGroupBy4() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(max("addresses.postCode")) .groupBy("name") .orderBy("name") .build(); List<Object[]> list = q.list(); assertEquals(2, list.size()); assertEquals(1, list.get(0).length); assertEquals("X1234", list.get(0)[0]); assertEquals(1, list.get(1).length); assertEquals("ZZ", list.get(1)[0]); }
public void testMaxString() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(max("surname")) .groupBy("name") .orderBy("name") .build(); List<Object[]> list = q.list(); assertEquals(2, list.size()); assertEquals(1, list.get(0).length); assertEquals(1, list.get(1).length); assertEquals("Doe", list.get(0)[0]); assertEquals("Woman", list.get(1)[0]); }
public void testEmbeddedMaxString() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(max("addresses.postCode")) .groupBy("name") .orderBy("name") .build(); List<Object[]> list = q.list(); assertEquals(2, list.size()); assertEquals(1, list.get(0).length); assertEquals("X1234", list.get(0)[0]); assertEquals(1, list.get(1).length); assertEquals("ZZ", list.get(1)[0]); }
public void testAvg() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(property("accountId"), avg("amount")) .groupBy("accountId") .orderBy("accountId") .build(); List<Object[]> list = q.list(); assertEquals(2, list.size()); assertEquals(2, list.get(0).length); assertEquals(2, list.get(1).length); assertEquals(1107.6666d, (Double) list.get(0)[1], 0.0001d); assertEquals(120.18867d, (Double) list.get(1)[1], 0.0001d); }
public void testAggregateDate() throws Exception { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(count("date"), min("date")) .having("description").eq("Hotel") .groupBy("id") .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(2, list.get(0).length); assertEquals(1L, list.get(0)[0]); assertEquals(java.util.Date.class, list.get(0)[1].getClass()); assertEquals(makeDate("2013-02-27"), list.get(0)[1]); }
@Test(expectedExceptions = ParsingException.class, expectedExceptionsMessageRegExp = "ISPN014026: The expression 'surname' must be part of an aggregate function or it should be included in the GROUP BY clause") public void testGroupBy3() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select("name") .groupBy("name") .orderBy("surname") .build(); q.list(); }
@Test(expectedExceptions = ParsingException.class, expectedExceptionsMessageRegExp = "ISPN014023: Using the multi-valued property path 'addresses.street' in the GROUP BY clause is not currently supported") public void testGroupByMustNotAcceptRepeatedProperty() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(min("name")) .groupBy("addresses.street") .build(); q.list(); }
public void testGroupingWithFilter() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select("name") .having("name").eq("John") .groupBy("name") .having("name").eq("John") .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals("John", list.get(0)[0]); }
public void testDateGrouping2() throws Exception { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(count("date"), min("date")) .having("description").eq("Hotel") .groupBy("id") .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(2, list.get(0).length); assertEquals(1L, list.get(0)[0]); assertEquals(makeDate("2013-02-27"), list.get(0)[1]); }
public void testDateGrouping3() throws Exception { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(min("date"), count("date")) .having("description").eq("Hotel") .groupBy("id") .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(2, list.get(0).length); assertEquals(makeDate("2013-02-27"), list.get(0)[0]); assertEquals(1L, list.get(0)[1]); }
public void testHavingWithSum() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(property("accountId"), sum("amount")) .groupBy("accountId") .having(sum("amount")).gt(3324) .orderBy("accountId") .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(2, list.get(0)[0]); assertEquals(6370.0d, (Double) list.get(0)[1], 0.0001d); }
public void testHavingWithMin() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(property("accountId"), min("amount")) .groupBy("accountId") .having(min("amount")).lt(10) .orderBy("accountId") .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(2, list.get(0)[0]); assertEquals(5.0d, (Double) list.get(0)[1], 0.0001d); }
public void testHavingWithAvg() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(property("accountId"), avg("amount")) .groupBy("accountId") .having(avg("amount")).lt(130.0) .orderBy("accountId") .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(2, list.get(0)[0]); assertEquals(120.188679d, (Double) list.get(0)[1], 0.0001d); }
public void testParamWithGroupBy() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(property("accountId"), property("date"), sum("amount")) .groupBy("accountId", "date") .having(sum("amount")).gt(param("param")) .build(); q.setParameter("param", 1801); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(3, list.get(0).length); assertEquals(6225d, list.get(0)[2]); }
public void testCompareDoubleWithInt() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(sum("amount")) .groupBy("accountId") .having(sum("amount")).gt(50000) .build(); List<Object[]> list = q.list(); assertEquals(0, list.size()); }
public void testCompareLongWithInt() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(sum("age")) .groupBy("name") .having(sum("age")).gt(50000) .build(); List<Object[]> list = q.list(); assertEquals(0, list.size()); }