public void testAggregateCount() { TestModel model1 = insertBasicTestModel(); TestModel model2 = new TestModel().setFirstName(model1.getFirstName()).setLastName("Smith"); database.persist(model2); IntegerProperty groupCount = IntegerProperty.countProperty(TestModel.FIRST_NAME, false); Query query = Query.select(TestModel.PROPERTIES).selectMore(groupCount).groupBy(TestModel.FIRST_NAME); SquidCursor<TestModel> groupedCursor = database.query(TestModel.class, query); try { groupedCursor.moveToFirst(); assertEquals(1, groupedCursor.getCount()); assertEquals(2, groupedCursor.get(groupCount).intValue()); } finally { groupedCursor.close(); } }
/** * Count the number of rows matching a given {@link Criterion}. Use null to count all rows. * * @param modelClass the model class corresponding to the table * @param criterion the criterion to match * @return the number of rows matching the given criterion */ public int count(Class<? extends AbstractModel> modelClass, Criterion criterion) { Property.IntegerProperty countProperty = Property.IntegerProperty.countProperty(); Query query = Query.select(countProperty); if (criterion != null) { query.where(criterion); } query = inferTableForQuery(modelClass, query); CompiledStatement compiled = query.compile(getCompileContext()); acquireNonExclusiveLock(); try { return (int) getDatabase().simpleQueryForLong(compiled.sql, compiled.sqlArgs); } finally { releaseNonExclusiveLock(); } }
public void testLimitAndOffsetWithExpressions() { // limit = 1 + (count(*) / 4), offset = count(*) / 2 Field<Integer> limit = Function.add(1, Function.divide( Query.select(IntegerProperty.countProperty()).from(Employee.TABLE).asFunction(), 4)); Field<Integer> offset = Function.divide( Query.select(IntegerProperty.countProperty()).from(Employee.TABLE).asFunction(), 2); Query query = Query.select().orderBy(Employee.NAME.asc()).limit(limit, offset); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(2, cursor.getCount()); cursor.moveToFirst(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(ernie, new Employee(cursor)); } finally { cursor.close(); } }
public void testFunctionOnAmbiguousColumnName() { IntegerProperty happyCount = IntegerProperty.countProperty(Employee.IS_HAPPY, false); Query test = Query.select(TestModel.ID, TestModel.FIRST_NAME, TestModel.IS_HAPPY, happyCount) .join(Join.inner(Employee.TABLE, Employee.IS_HAPPY.eq(TestModel.IS_HAPPY))); // just test that the query compiles with the function database.query(TestModel.class, test); }
/** * Count the number of rows matching a given {@link Criterion}. Use null to count all rows. * * @param modelClass the model class corresponding to the table * @param criterion the criterion to match * @return the number of rows matching the given criterion */ public int count(Class<? extends AbstractModel> modelClass, Criterion criterion) { Property.IntegerProperty countProperty = Property.IntegerProperty.countProperty(); Query query = Query.select(countProperty); if (criterion != null) { query.where(criterion); } query = inferTableForQuery(modelClass, query); CompiledStatement compiled = query.compile(getCompileContext()); acquireNonExclusiveLock(); try { return (int) getDatabase().simpleQueryForLong(compiled.sql, compiled.sqlArgs); } finally { releaseNonExclusiveLock(); } }