private void testReusableQueryWithInCriterionInternal(Collection<String> collection, Query query, String... list) { collection.clear(); collection.addAll(Arrays.asList(list)); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(collection.size(), cursor.getCount()); while (cursor.moveToNext()) { String name = cursor.get(Employee.NAME); assertTrue(collection.contains(name)); } } finally { cursor.close(); } }
private void scanCursor(SquidCursor<Thing> cursor, String formatString) { for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) { int i = cursor.getPosition(); assertEquals(String.format(formatString, i), cursor.get(Thing.FOO)); } }
private void testInQuery(List<String> expectedNames, Query query) { SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(expectedNames.size(), cursor.getCount()); for (String name : expectedNames) { cursor.moveToNext(); assertEquals(name, cursor.get(Employee.NAME)); } } finally { cursor.close(); } }
public void testSelectDistinct() { Query query = Query.selectDistinct(Employee.MANAGER_ID).orderBy(Employee.MANAGER_ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(4, cursor.getCount()); cursor.moveToFirst(); assertEquals(Long.valueOf(0), cursor.get(Employee.MANAGER_ID)); cursor.moveToNext(); assertEquals(Long.valueOf(1), cursor.get(Employee.MANAGER_ID)); cursor.moveToNext(); assertEquals(Long.valueOf(2), cursor.get(Employee.MANAGER_ID)); cursor.moveToNext(); assertEquals(Long.valueOf(5), cursor.get(Employee.MANAGER_ID)); } finally { cursor.close(); } }
private void testBetween(List<Long> expectedIds, boolean useNotBetween) { SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID).where(useNotBetween ? Employee.ID.notBetween(2, 5) : Employee.ID.between(2, 5)).orderBy(Employee.ID.asc())); try { assertEquals(expectedIds.size(), cursor.getCount()); for (Long id : expectedIds) { cursor.moveToNext(); assertEquals(id.longValue(), cursor.get(Employee.ID).longValue()); } } finally { cursor.close(); } }
public void testLikeSubquery() { insertBasicTestModel("Sam 1", "A", System.currentTimeMillis() - 5); insertBasicTestModel("Sam 2", "B", System.currentTimeMillis() - 4); insertBasicTestModel("Sam 3", "C", System.currentTimeMillis() - 3); insertBasicTestModel("Bla 1", "D", System.currentTimeMillis() - 2); insertBasicTestModel("Bla 2", "E", System.currentTimeMillis() - 1); Function<String> substr = Function.substr(TestModel.FIRST_NAME, 1, 3); Function<String> strConcat = Function.strConcat(substr, "%"); Query likeFirstName = Query.select().where(TestModel.FIRST_NAME.like( Query.select(strConcat).from(TestModel.TABLE).where(TestModel.ID.eq(1)))).orderBy(TestModel.ID.asc()); SquidCursor<TestModel> cursor = database.query(TestModel.class, likeFirstName); try { assertEquals(3, cursor.getCount()); int index = 1; while (cursor.moveToNext()) { assertEquals("Sam " + index, cursor.get(TestModel.FIRST_NAME)); index++; } } finally { cursor.close(); } }
private void testGlob(List<Employee> expected, boolean useNotGlob) { SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID, Employee.NAME).where(useNotGlob ? Employee.NAME.notGlob("b*") : Employee.NAME.glob("b*")) .orderBy(Employee.ID.asc())); try { assertEquals(expected.size(), cursor.getCount()); for (Employee e : expected) { cursor.moveToNext(); assertEquals(e.getRowId(), cursor.get(Employee.ID).longValue()); assertEquals(e.getName(), cursor.get(Employee.NAME)); } } finally { cursor.close(); } }
public void testUnionAll() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .unionAll(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(4, cursor.getCount()); cursor.moveToFirst(); assertEquals(cookieMonster, new Employee(cursor)); cursor.moveToNext(); assertEquals(cookieMonster, new Employee(cursor)); cursor.moveToNext(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(oscar, new Employee(cursor)); } finally { cursor.close(); } }
public void testRawOrderBy() { String sortOrder = COL_GIVEN_NAME + " ASC"; ContentProviderQueryBuilder builder = getBuilder(); Query query = builder.setDataSource(TestModel.TABLE).build(null, null, null, sortOrder); CompiledStatement compiled = query.compile(database.getCompileContext()); verifyCompiledSqlArgs(compiled, 0); SquidCursor<TestModel> cursor = null; try { cursor = database.query(TestModel.class, query); assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals(model3, buildModelFromCursor(cursor)); cursor.moveToNext(); assertEquals(model2, buildModelFromCursor(cursor)); cursor.moveToNext(); assertEquals(model1, buildModelFromCursor(cursor)); } finally { if (cursor != null) { cursor.close(); } } }
public void testDefaultOrderBy() { ContentProviderQueryBuilder builder = getBuilder(); builder.setDefaultOrder(TestModel.LUCKY_NUMBER.desc()); Query query = builder.setDataSource(TestModel.TABLE).build(null, null, null, null); CompiledStatement compiled = query.compile(database.getCompileContext()); verifyCompiledSqlArgs(compiled, 0); SquidCursor<TestModel> cursor = null; try { cursor = database.query(TestModel.class, query); assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals(model2, buildModelFromCursor(cursor)); cursor.moveToNext(); assertEquals(model1, buildModelFromCursor(cursor)); cursor.moveToNext(); assertEquals(model3, buildModelFromCursor(cursor)); } finally { if (cursor != null) { cursor.close(); } } }
public void testUnion() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .union(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals(cookieMonster, new Employee(cursor)); cursor.moveToNext(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(oscar, new Employee(cursor)); } finally { cursor.close(); } }
public void testSelectFromView() { View view = View.fromQuery(Query.select(Employee.PROPERTIES) .from(Employee.TABLE).where(Employee.MANAGER_ID.eq(bigBird.getRowId())), "bigBirdsEmployees"); database.tryCreateView(view); Query fromView = Query.fromView(view).orderBy(view.qualifyField(Employee.ID).asc()); SquidCursor<Employee> cursor = database.query(Employee.class, fromView); try { assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals("cookieMonster", cursor.get(Employee.NAME)); cursor.moveToNext(); assertEquals("elmo", cursor.get(Employee.NAME)); cursor.moveToNext(); assertEquals("oscar", cursor.get(Employee.NAME)); } finally { cursor.close(); } }
public void testBooleanFunctionOnCriterion() { BooleanProperty onCriterion = BooleanProperty .fromFunction(Function.caseWhen(TestModel.FIRST_NAME.eq("Sam")), "firstNameSam"); SquidCursor<TestModel> cursor = database .query(TestModel.class, Query.select(onCriterion).orderBy(Order.asc(TestModel.ID))); try { cursor.moveToFirst(); assertTrue(cursor.get(onCriterion)); cursor.moveToNext(); assertFalse(cursor.get(onCriterion)); } finally { cursor.close(); } }
public void testReverseOrder() { long max = database.countAll(Employee.class); SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID).orderBy(Employee.ID.asc().reverse())); try { assertEquals(max, cursor.getCount()); assertTrue(max > 0); while (cursor.moveToNext()) { long nextId = cursor.get(Employee.ID); if (nextId > max) { fail("IDs not in reverse order"); } max = nextId; } } finally { cursor.close(); } }
public void testQueryAsFunction() { Table qualifiedTable = Employee.TABLE.as("e1"); Query subquery = Query.select(Function.add(qualifiedTable.qualifyField(Employee.ID), 1)) .from(qualifiedTable).where(Employee.ID.eq(qualifiedTable.qualifyField(Employee.ID))); Function<Long> fromQuery = subquery.asFunction(); LongProperty idPlus1 = LongProperty.fromFunction(fromQuery, "idPlus1"); Query baseQuery = Query.select(Employee.ID, idPlus1); SquidCursor<Employee> cursor = database.query(Employee.class, baseQuery); try { assertEquals(database.countAll(Employee.class), cursor.getCount()); while (cursor.moveToNext()) { assertEquals(cursor.get(Employee.ID) + 1, cursor.get(idPlus1).longValue()); } } finally { cursor.close(); } }
public void testBasicSelectFromSubquery() { SquidCursor<TestSubqueryModel> cursor = null; try { cursor = database.query(TestSubqueryModel.class, Query.select().from(TestSubqueryModel.SUBQUERY)); assertEquals(2, cursor.getCount()); cursor.moveToFirst(); TestSubqueryModel model = new TestSubqueryModel(cursor); assertEquals(t1.getRowId(), model.getTestModelId().longValue()); assertEquals(e1.getRowId(), model.getEmployeeModelId().longValue()); assertEquals(t1.getFirstName(), model.getTestName()); assertEquals(t1.getSomeEnum(), model.getTestEnum()); assertEquals(e1.getName(), model.getEmployeeName()); assertEquals(e1.getName().toUpperCase(), model.getUppercaseName()); cursor.moveToNext(); model.readPropertiesFromCursor(cursor); assertEquals(t2.getRowId(), model.getTestModelId().longValue()); assertEquals(e2.getRowId(), model.getEmployeeModelId().longValue()); assertEquals(t2.getFirstName(), model.getTestName()); assertEquals(e2.getName(), model.getEmployeeName()); assertEquals(e2.getName().toUpperCase(), model.getUppercaseName()); } finally { if (cursor != null) { cursor.close(); } } }
public void testCoalesce() { model2.setFirstName(null); // coalesce should find last name database.persist(model2); model3.setFirstName(null).setLastName(null); // coalesce should find fallback name database.persist(model3); final String FALLBACK_NAME = "Squid"; Function<String> coalesce = Function.coalesce(TestModel.FIRST_NAME, TestModel.LAST_NAME, FALLBACK_NAME); StringProperty modelName = StringProperty.fromFunction(coalesce, "name"); // select *, coalesce(firstName, lastName, 'Squid') as name from testModel order by _id asc; SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select(TestModel.PROPERTIES) .selectMore(modelName).orderBy(TestModel.ID.asc())); assertEquals(3, cursor.getCount()); try { cursor.moveToFirst(); assertEquals(model1.getFirstName(), cursor.get(modelName)); cursor.moveToNext(); assertEquals(model2.getLastName(), cursor.get(modelName)); cursor.moveToNext(); assertEquals(FALLBACK_NAME, cursor.get(modelName)); } finally { cursor.close(); } }
public void testSubqueryJoin() { StringProperty managerName = Employee.NAME.as("managerName"); Query query = Query .fromSubquery(Query.select(Employee.MANAGER_ID).from(Employee.TABLE).groupBy(Employee.MANAGER_ID), "subquery"); query.selectMore(managerName); query.join(Join.inner(Employee.TABLE, query.getTable().qualifyField(Employee.MANAGER_ID).eq(Employee.ID))) .orderBy(Employee.MANAGER_ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals("bigBird", cursor.get(managerName)); cursor.moveToNext(); assertEquals("cookieMonster", cursor.get(managerName)); cursor.moveToNext(); assertEquals("bert", cursor.get(managerName)); } finally { cursor.close(); } }
public void testExcept() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .except(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(2, cursor.getCount()); cursor.moveToFirst(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(oscar, new Employee(cursor)); } finally { cursor.close(); } }
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(); } }