/** * Create a Function that returns 1 (true) if the criterion passes or 0 (false) otherwise */ public static Function<Integer> caseWhen(Criterion when) { return caseWhen(when, TRUE, FALSE); }
/** * Construct an IntegerProperty represented by the expression "COUNT(field)" or "COUNT(DISTINCT field)" * * @param field the field to count * @param distinct whether to count distinct values only or not */ public static IntegerProperty countProperty(Field<?> field, boolean distinct) { Function<Integer> function = distinct ? Function.countDistinct(field) : Function.count(field); return fromFunction(function, "count"); }
@Override protected void appendQualifiedExpression(SqlBuilder builder, boolean forSqlValidation) { appendFunctionExpression(builder, forSqlValidation); }
public Query getOrderedTasksWithTags() { Function<Long> unixNow = Function.multiply(1000, Function.functionWithArguments("strftime", "%s", "now")); Function<Long> sinceCompletion = Function.subtract(unixNow, Task.COMPLETION_DATE); return getTasksWithTagsQuery(Task.COMPLETION_DATE.eq(0) .or(sinceCompletion.lt(60000 * 5))) .orderBy(Function.caseWhen(Task.DUE_DATE.neq(0)).desc(), Task.DUE_DATE.asc()); }
public void testCaseWhen() { final String PASS = "PASS"; final String FAIL = "FAIL"; Function<String> caseWhen = Function.caseExpr("Hello") .when("a", FAIL) .when("b", FAIL) caseWhen = Function.caseExpr("Hello") .when("a", FAIL) .when("b", FAIL) caseWhen = Function.caseExpr(Field.NULL) .when(null, FAIL) .when(Field.NULL, FAIL) caseWhen = Function.caseWhen(IntegerProperty.literal(1, null).gt(2), FAIL) .when(IntegerProperty.literal(3, null).gt(0), PASS) .elseExpr(FAIL) caseWhen = Function.caseWhen(TestModel.LUCKY_NUMBER.gt(0), "positive") .when(TestModel.LUCKY_NUMBER.lt(0), "negative") .elseExpr("zero") caseWhen = Function.caseExpr(TestModel.LUCKY_NUMBER) .when(0, "zero") .when(7, "default") Function<Integer> longNameFunc = Function.caseWhen(Function.length(TestModel.FIRST_NAME).gt(6)); IntegerProperty hasLongName = IntegerProperty.fromFunction(longNameFunc, "hasLongName"); assertExpectedValues(Query.select(hasLongName).from(TestModel.TABLE).orderBy(TestModel.ID.asc()), hasLongName,
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) subquery.having(Function.count().gt(1));
public void testEqualsAndHashCode() { LongProperty test1 = new LongProperty( new TableModelName(TestModel.class, TestModel.TABLE.getName()), "testCol"); LongProperty test2 = new LongProperty( new TableModelName(TestModel.class, TestModel.TABLE.getName()), "testCol"); assertEquals(test1, test2); assertEquals(test1.hashCode(), test2.hashCode()); StringProperty test3 = new StringProperty( new TableModelName(TestModel.class, TestModel.TABLE.getName()), "testCol"); StringProperty test4 = new StringProperty( new TableModelName(TestModel.class, TestModel.TABLE.getName()), "testCol", "DEFAULT 'A'"); assertEquals(test3, test4); assertEquals(test3.hashCode(), test4.hashCode()); Function<Integer> func1 = Function.count(); Function<Integer> func2 = Function.rawFunction("COUNT(*)"); assertEquals(func1, func2); assertEquals(func1.hashCode(), func2.hashCode()); IntegerProperty test5 = Property.IntegerProperty.fromFunction(func1, "count"); IntegerProperty test6 = Property.IntegerProperty.fromFunction(func2, "count"); assertEquals(test5, test6); assertEquals(test5.hashCode(), test6.hashCode()); }
public void testBoundArgumentsWorkInHavingClause() { Query query = Query.select(Employee.PROPERTIES) .groupBy(Employee.MANAGER_ID) .having(Function.count(Employee.MANAGER_ID).gt(2)); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(1, cursor.getCount()); cursor.moveToFirst(); assertEquals(bigBird.getRowId(), cursor.get(Employee.MANAGER_ID).longValue()); } finally { cursor.close(); } }
/** * Construct an IntegerProperty represented by the expression "COUNT(1)" */ public static IntegerProperty countProperty() { return fromFunction(Function.count(), "count"); }
/** * Sort the results by the value of the {@link Field}, ordered by values in the array. This is a bit of a hack. * * @param field the Field to order by * @param order values for the specified field, in the order they should appear in the result set */ public static <T> Order byArray(Field<T> field, T[] order) { if (order == null || order.length == 0) { return Order.asc("0"); } CaseBuilder caseBuilder = Function.caseExpr(field); for (int i = 0; i < order.length; i++) { caseBuilder.when(order[i], i); } caseBuilder.elseExpr(order.length); return Order.asc(caseBuilder.end()); }
public void testLiteralCriterions() { // null and not null evaluate to false assertEquals(0, database.count(Employee.class, Criterion.literal(null))); assertEquals(0, database.count(Employee.class, Criterion.literal(null).negate())); // numeric literal; values other than 0 (including negative) evaluate to true assertEquals(0, database.count(Employee.class, Criterion.literal(0))); assertEquals(6, database.count(Employee.class, Criterion.literal(10))); assertEquals(6, database.count(Employee.class, Criterion.literal(-10))); assertEquals(6, database.count(Employee.class, Criterion.literal(0).negate())); assertEquals(0, database.count(Employee.class, Criterion.literal(10).negate())); // text literal; SQLite will try to coerce to a number assertEquals(0, database.count(Employee.class, Criterion.literal("sqlite"))); assertEquals(6, database.count(Employee.class, Criterion.literal("sqlite").negate())); assertEquals(6, database.count(Employee.class, Criterion.literal("1sqlite"))); // coerces to 1 assertEquals(0, database.count(Employee.class, Criterion.literal("1sqlite").negate())); // numeric column Criterion isHappyCriterion = Employee.IS_HAPPY.asCriterion(); assertEquals(5, database.count(Employee.class, isHappyCriterion)); assertEquals(1, database.count(Employee.class, isHappyCriterion.negate())); // text column Criterion nameCriterion = Employee.NAME.asCriterion(); assertEquals(0, database.count(Employee.class, nameCriterion)); assertEquals(6, database.count(Employee.class, nameCriterion.negate())); // function Criterion f = Function.functionWithArguments("length", "sqlite").asCriterion(); assertEquals(6, database.count(Employee.class, f)); assertEquals(0, database.count(Employee.class, f.negate())); }
@Override protected String expressionForComparison() { if (function != null) { return function.expressionForComparison(); } return super.expressionForComparison(); }
@Override protected void appendQualifiedExpression(SqlBuilder builder, boolean forSqlValidation) { if (function != null) { function.appendToSqlBuilder(builder, forSqlValidation); } else { super.appendQualifiedExpression(builder, forSqlValidation); } }
/** * @return the expression for the function as it would be compiled with the given CompileContext */ public String getExpression(CompileContext forCompileContext) { SqlBuilder builder = new SqlBuilder(forCompileContext, false); appendQualifiedExpression(builder, false); return builder.getSqlString(); }
/** * Return this query wrapped in a Function object, making it suitable for inclusion in another SELECT clause as a * subquery or for constructing {@link Criterion}s. Note: the query must have exactly one column in its * result set (i.e. one field in the SELECT clause) for this to be valid SQL. * * @return a {@link Function} from this query */ public <T> Function<T> asFunction() { return Function.fromQuery(this); }
/** * Construct an IntegerProperty represented by the expression "COUNT(1)" */ public static IntegerProperty countProperty() { return fromFunction(Function.count(), "count"); }
/** * Sort the results by the value of the {@link Field}, ordered by values in the array. This is a bit of a hack. * * @param field the Field to order by * @param order values for the specified field, in the order they should appear in the result set */ public static <T> Order byArray(Field<T> field, T[] order) { if (order == null || order.length == 0) { return Order.asc("0"); } CaseBuilder caseBuilder = Function.caseExpr(field); for (int i = 0; i < order.length; i++) { caseBuilder.when(order[i], i); } caseBuilder.elseExpr(order.length); return Order.asc(caseBuilder.end()); }
@Override protected String expressionForComparison() { if (function != null) { return function.expressionForComparison(); } return super.expressionForComparison(); }
@Override protected void appendQualifiedExpression(SqlBuilder builder, boolean forSqlValidation) { if (function != null) { function.appendToSqlBuilder(builder, forSqlValidation); } else { super.appendQualifiedExpression(builder, forSqlValidation); } }
/** * @return the expression for the function as it would be compiled with the given CompileContext */ public String getExpression(CompileContext forCompileContext) { SqlBuilder builder = new SqlBuilder(forCompileContext, false); appendQualifiedExpression(builder, false); return builder.getSqlString(); }