public void testEmbeddedGlobalAvg() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(avg("addresses.number")) .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals(152d, (Double) list.get(0)[0], 0.0001d); }
public void testGlobalAvg() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(avg("amount")) .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals(173.0892d, (Double) list.get(0)[0], 0.0001d); }
public void testAvgNull() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(avg("age")) .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals(22.0, list.get(0)[0]); // only non-null "age"s were used in the average }
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 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 testEmbeddedAvg() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(property("surname"), avg("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(156d, (Double) list.get(0)[1], 0.0001d); assertEquals(150d, (Double) list.get(1)[1], 0.0001d); assertNull(list.get(2)[1]); }
@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]); }
public void testHavingWithMax() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(property("accountId"), max("amount")) .groupBy("accountId") .having(avg("amount")).lt(150) .orderBy("accountId") .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(2, list.get(0)[0]); assertEquals(149.0d, (Double) list.get(0)[1], 0.0001d); }
public void testOrderByInAggregationQueryMustAcceptRepeatedProperty() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(avg("age"), property("name")) .having("name").gt("A") .groupBy("name") .having(max("addresses.street")).gt("A") .orderBy(min("addresses.street")) .build(); List<Object[]> list = q.list(); assertEquals(2, list.size()); assertEquals(2, list.get(0).length); assertNull(list.get(0)[0]); assertEquals("Spider", list.get(0)[1]); assertEquals(22.0, list.get(1)[0]); assertEquals("John", list.get(1)[1]); }