@Override public FilterConditionEndContext having(String attributePath) { return having(Expression.property(attributePath)); }
@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 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 testCount() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(property("surname"), count("age")) .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(1L, list.get(0)[1]); assertEquals(0L, list.get(1)[1]); assertEquals(0L, list.get(2)[1]); }
@Test(expectedExceptions = IllegalStateException.class, expectedExceptionsMessageRegExp = "ISPN014825: Query parameter 'param2' was not set") public void testMissingParamWithParameterMap() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .having("name").eq(param("param1")) .and().having("gender").eq(param("param2")) .build(); Map<String, Object> parameterMap = new HashMap<>(1); parameterMap.put("param1", "John"); q.setParameters(parameterMap); q.list(); }
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]); }
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 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 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 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 testMax() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getTransactionImplClass()) .select(property("accountId"), max("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(1800d, list.get(0)[1]); assertEquals(149d, list.get(1)[1]); }
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]); }
@Override public long getSize( StoreKey storeKey ) { Query query = queryFactory.from( NfcConcreteResourceWrapper.class ) .select( Expression.count( "path" ) ) .having( "location" ) .eq( storeKey.toString() ) .toBuilder() .build(); List<Object> result = query.list(); Object[] count = (Object[]) result.get( 0 ); return (Long) count[0]; }
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 testGlobalMaxString() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(max("name")) .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals("Spider", 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); }
public void testGlobalMinString() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(min("name")) .build(); List<Object[]> list = q.list(); assertEquals(1, list.size()); assertEquals(1, list.get(0).length); assertEquals("John", list.get(0)[0]); }
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 testCountNull3() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .select(property("name"), count("salutation")) .groupBy("name") .orderBy("name") .build(); List<Object[]> list = q.list(); assertEquals(2, list.size()); assertEquals(2, list.get(0).length); assertEquals("John", list.get(0)[0]); assertEquals(0L, list.get(0)[1]); assertEquals(2, list.get(1).length); assertEquals("Spider", list.get(1)[0]); assertEquals(2L, list.get(1)[1]); }
@Test(expectedExceptions = IllegalArgumentException.class, expectedExceptionsMessageRegExp = "ISPN014806: No parameters named '\\[param2\\]' were found") public void testUnknownParamWithParameterMap() { QueryFactory qf = getQueryFactory(); Query q = qf.from(getModelFactory().getUserImplClass()) .having("name").eq(param("param1")) .build(); Map<String, Object> parameterMap = new HashMap<>(1); parameterMap.put("param2", User.Gender.MALE); q.setParameters(parameterMap); }