public void testSubstr() { testSubstrInternal(2, 0); testSubstrInternal(2, 2); testSubstrInternal(3, 4); String literal = "ABC/DEF"; StringProperty prefix = StringProperty.literal(literal.substring(0, literal.indexOf('/') + 1), "prefix"); StringProperty full = StringProperty.literal(literal, "full"); Field<String> fullField = Field.field(full.getName()); Field<String> prefixField = Field.field(prefix.getName()); SquidCursor<?> cursor = database.query(null, Query.select(Function.substr(fullField, Function.add(Function.length(prefixField), 1))) .from(Query.select(full, prefix).as("subquery"))); try { assertTrue(cursor.moveToFirst()); assertEquals("DEF", cursor.getString(0)); } 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 testMath() { Function<Integer> addition = Function.add(1, 2, 3, 4, 5); IntegerProperty sum = IntegerProperty.fromFunction(addition, "sum"); testMath(sum, 15); Function<Integer> subtraction = Function.subtract(100, 30, 20); IntegerProperty difference = IntegerProperty.fromFunction(subtraction, "difference"); testMath(difference, 50); Function<Integer> multiplcation = Function.multiply(1, 2, 3, 4, 5); IntegerProperty product = IntegerProperty.fromFunction(multiplcation, "product"); testMath(product, 120); Function<Integer> division = Function.divide(1000, 10, 5); IntegerProperty quotient = IntegerProperty.fromFunction(division, "quotient"); testMath(quotient, 20); Function<Integer> modulus = Function.modulo(512, 9); IntegerProperty remainder = IntegerProperty.fromFunction(modulus, "remainder"); testMath(remainder, 8); Function<Integer> bitAnd = Function.bitwiseAnd(0xcafe0000, 0xba00, 0xbe); IntegerProperty and = IntegerProperty.fromFunction(bitAnd, "bitAnd"); testMath(and, 0); Function<Integer> bitOr = Function.bitwiseOr(0xcafe0000, 0xba00, 0xbe); IntegerProperty or = IntegerProperty.fromFunction(bitOr, "bitOr"); testMath(or, 0xcafebabe); }
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(); } }