Java Code Examples for com.yahoo.squidb.data.SquidCursor#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 testGroupConcat() {
    setUpAggregateTest();

    StringProperty firstNameConcat = StringProperty.fromFunction(
            Function.groupConcat(TestModel.FIRST_NAME), "fname_concat");
    StringProperty firstNameConcatSeparator = StringProperty.fromFunction(
            Function.groupConcat(TestModel.FIRST_NAME, "|"), "fname_concat_separator");
    StringProperty firstNameDistinct = StringProperty.fromFunction(
            Function.groupConcatDistinct(TestModel.FIRST_NAME), "fname_distinct");
    SquidCursor<TestModel> cursor = database.query(TestModel.class,
            Query.select(firstNameConcat, firstNameConcatSeparator, firstNameDistinct)
                    .groupBy(TestModel.FIRST_NAME));
    try {
        assertEquals(2, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("A,A,A", cursor.get(firstNameConcat));
        assertEquals("A|A|A", cursor.get(firstNameConcatSeparator));
        assertEquals("A", cursor.get(firstNameDistinct));
        cursor.moveToNext();
        assertEquals("B,B,B", cursor.get(firstNameConcat));
        assertEquals("B|B|B", cursor.get(firstNameConcatSeparator));
        assertEquals("B", cursor.get(firstNameDistinct));
    } finally {
        cursor.close();
    }
}
 

public void testAtomicIntegers() {
    AtomicInteger id = new AtomicInteger(1);
    Query query = Query.select(Employee.ID).where(Employee.ID.eq(id));

    SquidCursor<Employee> cursor = database.query(Employee.class, query);
    try {
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(1, cursor.get(Employee.ID).longValue());
    } finally {
        cursor.close();
    }
    id.set(2);
    cursor = database.query(Employee.class, query);
    try {
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(2, 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 testQueryResults(Criterion criterion, TestVirtualModel... expectedResults) {
    Query query = Query.select(TestVirtualModel.PROPERTIES).where(criterion);
    SquidCursor<TestVirtualModel> cursor = database.query(TestVirtualModel.class, query);
    try {
        int expectedCount = expectedResults == null ? 0 : expectedResults.length;
        assertEquals(expectedCount, cursor.getCount());
        if (expectedCount == 0) {
            return;
        }

        for (int i = 0; i < expectedCount; i++) {
            cursor.moveToPosition(i);
            assertEquals(expectedResults[i], new TestVirtualModel(cursor));
        }
    } 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();
    }
}
 

private void testCursorAdapterInternal(AbstractModel model, LongProperty idColumn, Query query,
        CursorAdapterTest test) {
    TestAdapter adapter;
    if (idColumn == null) {
        adapter = new TestAdapter(model);
    } else {
        adapter = new TestAdapter(model, idColumn);
    }

    SquidCursor<? extends AbstractModel> cursor = database.query(model.getClass(), query);
    try {
        adapter.swapCursor(cursor);
        test.testCursorAdapter(adapter);
    } finally {
        cursor.close();
    }
}
 

public void testSumAndSumDistinct() {
    setUpAggregateTest();

    IntegerProperty sum = IntegerProperty.fromFunction(
            Function.sum(TestModel.LUCKY_NUMBER), "sum");
    IntegerProperty sumDistinct = IntegerProperty.fromFunction(
            Function.sumDistinct(TestModel.LUCKY_NUMBER), "sumDistinct");
    SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select(sum, sumDistinct));
    try {
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(12, cursor.get(sum).intValue());
        assertEquals(8, cursor.get(sumDistinct).intValue());
    } finally {
        cursor.close();
    }
}
 

public void testJoinOnLiteralValue() {
    TestModel modelOne = new TestModel().setFirstName("Sam").setLastName("Bosley");
    TestModel modelTwo = new TestModel().setFirstName("Kevin").setLastName("Lim");
    TestModel modelThree = new TestModel().setFirstName("Jonathan").setLastName("Koren");

    Thing thingOne = new Thing().setFoo("Thing1").setBar(5);
    Thing thingTwo = new Thing().setFoo("Thing2").setBar(-1);
    Thing thingThree = new Thing().setFoo("Thing3").setBar(100);

    database.persist(modelOne);
    database.persist(modelTwo);
    database.persist(modelThree);
    database.persist(thingOne);
    database.persist(thingTwo);
    database.persist(thingThree);

    Query query = Query.select(TestModel.FIRST_NAME, TestModel.LAST_NAME).selectMore(Thing.FOO, Thing.BAR)
            .from(TestModel.TABLE)
            .join(Join.inner(Thing.TABLE, Thing.BAR.gt(0)));
    SquidCursor<TestModel> cursor = database.query(TestModel.class, query);
    try {
        assertEquals(6, cursor.getCount());
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            assertTrue(cursor.get(Thing.BAR) > 0);
        }
    } finally {
        cursor.close();
    }
}
 

public void testSelectLiteral() {
    StringProperty literal = StringProperty.literal("literal", "name");
    LongProperty literalLong = LongProperty.literal(12, "twelve");
    SquidCursor<?> c = database.query(null, Query.select(literal, literalLong));
    try {
        assertEquals(1, c.getCount());
        c.moveToFirst();
        assertEquals("literal", c.get(literal));
        assertEquals(12, c.get(literalLong).longValue());
    } finally {
        if (c != null) {
            c.close();
        }
    }
}
 

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();
    }
}
 

public void testBooleanFunctionPropertyConstants() {
    BooleanProperty alwaysTrue = BooleanProperty.fromFunction(Function.TRUE, "alwaysTrue");
    BooleanProperty alwaysFalse = BooleanProperty.fromFunction(Function.FALSE, "alwaysFalse");
    SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select(alwaysTrue, alwaysFalse));
    try {
        cursor.moveToFirst();
        assertTrue(cursor.get(alwaysTrue));
        assertFalse(cursor.get(alwaysFalse));
    } 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 testQueryUsingSubqueryModel() {
    Employee employee1 = new Employee().setName("Big bird");
    Employee employee2 = new Employee().setName("Elmo");
    database.persist(employee1);
    database.persist(employee2);

    ContentProviderQueryBuilder builder = new ContentProviderQueryBuilder(TestSubqueryModel.PROPERTIES,
            TestSubqueryModel.SUBQUERY);
    Query query = builder.build(null, null, null, null);

    SquidCursor<TestSubqueryModel> cursor = null;
    try {
        cursor = database.query(TestSubqueryModel.class, query);
        assertEquals(2, cursor.getCount());

        cursor.moveToFirst();
        TestSubqueryModel model = new TestSubqueryModel(cursor);
        assertEquals(model1.getRowId(), model.getTestModelId().longValue());
        assertEquals(employee1.getRowId(), model.getEmployeeModelId().longValue());
        assertEquals(model1.getFirstName(), model.getTestName());
        assertEquals(employee1.getName(), model.getEmployeeName());
        assertEquals(employee1.getName().toUpperCase(), model.getUppercaseName());

        cursor.moveToNext();
        model.readPropertiesFromCursor(cursor);
        assertEquals(model2.getRowId(), model.getTestModelId().longValue());
        assertEquals(employee2.getRowId(), model.getEmployeeModelId().longValue());
        assertEquals(model2.getFirstName(), model.getTestName());
        assertEquals(employee2.getName(), model.getEmployeeName());
        assertEquals(employee2.getName().toUpperCase(), model.getUppercaseName());
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}
 

public void testQueryBindingTypes() {
    insertBasicTestModel();
    Field<Integer> one = Field.field("1");
    SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select().where(Function.abs(one).eq(1)));
    try {
        assertEquals(1, cursor.getCount());
    } finally {
        cursor.close();
    }
}
 

public void testOrderByArray() {
    Long[] order = new Long[]{5L, 1L, 4L};
    SquidCursor<Employee> cursor = database.query(Employee.class,
            Query.select(Employee.ID).limit(order.length).orderBy(Employee.ID.byArray(order)));
    try {
        assertEquals(order.length, cursor.getCount());
        for (int i = 0; i < order.length; i++) {
            cursor.moveToPosition(i);
            assertEquals(order[i], cursor.get(Employee.ID));
        }
    } finally {
        cursor.close();
    }
}
 

public void testMax() {
    LongProperty maxId = LongProperty.fromFunction(Function.max(TestModel.ID), "maxId");
    SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select(maxId));
    try {
        cursor.moveToFirst();
        assertEquals(model3.getRowId(), cursor.get(maxId).longValue());
    } finally {
        cursor.close();
    }
}
 

public void testBindArgsProtectsInjection() {
    Query q = Query.select().where(Employee.NAME.eq("'Sam'); drop table " + Employee.TABLE.getName() + ";"));
    SquidCursor<Employee> cursor = database.query(Employee.class, q);
    try {
        assertFalse(database.countAll(Employee.class) == 0);
    } finally {
        cursor.close();
    }
}
 

public void testSimpleSubquerySelect() {
    Query query = Query.fromSubquery(Query.select(Employee.NAME).from(Employee.TABLE), "subquery");
    StringProperty name = query.getTable().qualifyField(Employee.NAME);
    query.where(name.eq("bigBird"));
    SquidCursor<Employee> cursor = database.query(Employee.class, query);
    try {
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        Employee employee = new Employee(cursor);
        assertEquals("bigBird", employee.getName());
    } finally {
        cursor.close();
    }
}
 

public void testJoinOnSameTableUsingAlias() {
    // check precondition
    int rowsWithManager = database.count(Employee.class, Employee.MANAGER_ID.gt(0));
    assertEquals(5, rowsWithManager);

    List<String> resultEmployees = new ArrayList<>(5);
    List<String> resultManagers = new ArrayList<>(5);
    resultEmployees.add(cookieMonster.getName());
    resultManagers.add(bigBird.getName());
    resultEmployees.add(elmo.getName());
    resultManagers.add(bigBird.getName());
    resultEmployees.add(oscar.getName());
    resultManagers.add(bigBird.getName());
    resultEmployees.add(bert.getName());
    resultManagers.add(cookieMonster.getName());
    resultEmployees.add(ernie.getName());
    resultManagers.add(bert.getName());

    // select employees.name as employeeName, managers.name as managerName from employees inner join employees as
    // managers on (employees.managerId = managers._id) order by managers._id ASC;
    Table managerTable = Employee.TABLE.as("managers");
    StringProperty employeeName = Employee.NAME.as("employeeName");
    StringProperty managerName = Employee.NAME.as(managerTable, "managerName");
    LongProperty managerId = managerTable.qualifyField(Employee.ID);
    Join join = Join.inner(managerTable, Employee.MANAGER_ID.eq(managerId));
    Query query = Query.select(employeeName, managerName).from(Employee.TABLE).join(join).orderBy(managerId.asc());

    SquidCursor<Employee> cursor = database.query(Employee.class, query);
    try {
        assertEquals(rowsWithManager, cursor.getCount());
        int index = 0;
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext(), index++) {
            String eName = cursor.get(employeeName);
            String mName = cursor.get(managerName);
            assertEquals(resultEmployees.get(index), eName);
            assertEquals(resultManagers.get(index), mName);
        }
    } finally {
        cursor.close();
    }
}