Java Code Examples for com.yahoo.squidb.data.SquidCursor#get()

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

private <T extends Number> void testMath(Property<T> property, T expectedValue) {
    SquidCursor<?> cursor = database.query(null, Query.select(property));
    try {
        cursor.moveToFirst();
        T value = cursor.get(property);
        assertEquals(expectedValue, value);
    } finally {
        cursor.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 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();
    }
}
 

private void testJoinWithUsingClauseInternal(boolean leftJoin) {
    final String separator = "|";
    final Map<Long, String> expectedResults = new HashMap<>();
    expectedResults.put(cookieMonster.getRowId(), "2|3|4");
    expectedResults.put(elmo.getRowId(), "2|3|4");
    expectedResults.put(oscar.getRowId(), "2|3|4");
    if (!leftJoin) {
        expectedResults.put(bigBird.getRowId(), "1");
        expectedResults.put(bert.getRowId(), "5");
        expectedResults.put(ernie.getRowId(), "6");
    }

    /*
     * select employees._id, employees.name, employees.managerId, subTable.subordinates as coworkers from employees
     * join (select e.managerId, group_concat(e._id, "|") as subordinates from employees as e group by e.managerId)
     * as subTable using (managerId);
     */

    Table employeesAlias = Employee.TABLE.as("e");
    LongProperty aliasedId = employeesAlias.qualifyField(Employee.ID);
    LongProperty aliasedManagerId = employeesAlias.qualifyField(Employee.MANAGER_ID);
    StringProperty subordinates = StringProperty.fromFunction(Function.groupConcat(aliasedId, separator),
            "subordinates");
    Query subquery = Query.select(aliasedManagerId, subordinates).from(employeesAlias)
            .groupBy(aliasedManagerId);

    if (leftJoin) {
        subquery.having(Function.count().gt(1));
    }

    SqlTable<?> subTable = subquery.as("subTable");
    StringProperty coworkers = subTable.qualifyField(subordinates);
    Query query = Query.select(Employee.PROPERTIES).selectMore(coworkers)
            .from(Employee.TABLE);
    if (leftJoin) {
        query.leftJoin(subTable, Employee.MANAGER_ID);
    } else {
        query.innerJoin(subTable, Employee.MANAGER_ID);
    }

    SquidCursor<Employee> cursor = database.query(Employee.class, query);

    try {
        assertEquals(6, cursor.getCount());
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            long id = cursor.get(Employee.ID);
            String coworkersList = cursor.get(coworkers);
            assertEquals(expectedResults.get(id), coworkersList);
        }
    } finally {
        cursor.close();
    }
}