public void testOrderBySum() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(sum("age")) .orderBy(sum("age")) .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals(22L, list.get(0)[0]); }
@Test(expectedExceptions = ParsingException.class, expectedExceptionsMessageRegExp = "ISPN028515: Cannot have aggregate functions in the WHERE clause : SUM.") public void testGroupBy7() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(sum("age")) .having(sum("age")).gt(10) .build(); List<Object[]> list = q.list(); assertEquals(2, list.size()); assertEquals(3, list.get(0).length); assertEquals(3, list.get(1).length); assertEquals(1500d, (Double) list.get(0)[2], 0.0001d); assertEquals(45d, (Double) list.get(1)[2], 0.0001d); }
@Test(expectedExceptions = IllegalStateException.class, expectedExceptionsMessageRegExp = "Aggregation SUM cannot be applied to property of type java.lang.String") public void testGroupBy6() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(sum("name")) .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals(2, list.get(0)[0]); }
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 testEmbeddedGlobalSum() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(sum("addresses.number")) .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals(456L, list.get(0)[0]); }
public void testGlobalSum() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(sum("amount")) .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals(9693d, (Double) list.get(0)[0], 0.0001d); }
public void testTwoPhaseGroupingAndAggregationOnSameField() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(count("surname"), sum("addresses.number")) .groupBy("surname") .orderBy("surname") .build(); List<Object[]> list = q.list(); assertEquals(3, list.size()); assertEquals(2, list.get(0).length); assertEquals(1L, list.get(0)[0]); assertEquals(156L, list.get(0)[1]); assertEquals(1L, list.get(1)[0]); assertEquals(300L, list.get(1)[1]); assertEquals(1L, list.get(2)[0]); assertNull(list.get(2)[1]); }
public void testGroupBy2() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(sum("age")) .groupBy("name") .orderBy("name") .build(); List<Object[]> list = q.list(); assertEquals(2, list.size()); assertEquals(1, list.get(0).length); assertEquals(22L, list.get(0)[0]); assertEquals(1, list.get(1).length); assertNull(list.get(1)[0]); }
public void testSum() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(sum("age")) .groupBy("name") .orderBy("name") .build(); List<Object[]> list = q.list(); assertEquals(2, list.size()); assertEquals(1, list.get(0).length); assertEquals(22L, list.get(0)[0]); assertEquals(1, list.get(1).length); assertNull(list.get(1)[0]); }
public void testEmbeddedSum() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(property("surname"), sum("addresses.number")) .groupBy("surname") .orderBy("surname") .build(); List<Object[]> list = q.list(); assertEquals(3, list.size()); assertEquals(2, list.get(0).length); assertEquals(2, list.get(1).length); assertEquals(2, list.get(2).length); assertEquals(156L, list.get(0)[1]); assertEquals(300L, list.get(1)[1]); assertNull(list.get(2)[1]); }
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]); }
@Test public void testComplexQuery() throws Exception { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(avg("amount"), sum("amount"), count("date"), min("date"), max("accountId")) .having("isDebit").eq(param("param")) .orderBy(avg("amount"), SortOrder.DESC).orderBy(count("date"), SortOrder.DESC) .orderBy(max("amount"), SortOrder.ASC) .build(); q.setParameter("param", true); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(5, list.get(0).length); assertEquals(143.50909d, (Double) list.get(0)[0], 0.0001d); assertEquals(7893d, (Double) list.get(0)[1], 0.0001d); assertEquals(55L, list.get(0)[2]); assertEquals(java.util.Date.class, list.get(0)[3].getClass()); assertEquals(makeDate("2013-01-01"), list.get(0)[3]); assertEquals(2, list.get(0)[4]); }