/** * Build a {@link Query} combining this object's internal state with the arguments passed. If a * {@link ProjectionMap} is set, the projection elements will be evaluated and transformed accordingly. If the * sortOrder is null or empty, the default order will be used (if one was set). * * @param projection the raw column names to be selected * @param selection a raw selection string * @param selectionArgs array of strings which substitute replaceable arguments in the selection string * @param sortOrder a raw ordering clause * @return a {@link Query} using the projection, selection, selection args, and sort order */ public Query build(String[] projection, String selection, String[] selectionArgs, String sortOrder) { Query query = Query.select(computeProjection(projection)).from(dataSource); boolean hasUserSelection = !SqlUtils.isEmpty(selection); if (hasUserSelection) { query.where(Criterion.fromRawSelection(selection, selectionArgs)); } if (!SqlUtils.isEmpty(sortOrder)) { query.orderBy(Order.fromExpression(sortOrder)); } else if (defaultOrder != null && defaultOrder.length > 0) { query.orderBy(defaultOrder); } if (strictMode && hasUserSelection) { query.requestValidation(); } return query; }
/** * Construct a new Select statement that queries all the {@link Field}s of the given subquery * * @param subquery the inner Query object * @param subqueryAlias the name to alias the subquery * @return a new Query object */ public static Query fromSubquery(Query subquery, String subqueryAlias) { SubqueryTable table = subquery.as(subqueryAlias); return Query.select(table.qualifiedFields()).from(table); }
/** * Count the number of rows matching a given {@link Criterion}. Use null to count all rows. * * @param modelClass the model class corresponding to the table * @param criterion the criterion to match * @return the number of rows matching the given criterion */ public int count(Class<? extends AbstractModel> modelClass, Criterion criterion) { Property.IntegerProperty countProperty = Property.IntegerProperty.countProperty(); Query query = Query.select(countProperty); if (criterion != null) { query.where(criterion); } query = inferTableForQuery(modelClass, query); CompiledStatement compiled = query.compile(getCompileContext()); acquireNonExclusiveLock(); try { return (int) getDatabase().simpleQueryForLong(compiled.sql, compiled.sqlArgs); } finally { releaseNonExclusiveLock(); } }
protected <TYPE extends AbstractModel> SquidCursor<TYPE> fetchFirstItem(Class<TYPE> modelClass, Query query) { boolean immutableQuery = query.isImmutable(); Field<Integer> beforeLimit = query.getLimit(); SqlTable<?> beforeTable = query.getTable(); query = query.limit(1); // If argument was frozen, we may get a new object SquidCursor<TYPE> cursor = query(modelClass, query); if (!immutableQuery) { query.from(beforeTable).limit(beforeLimit); // Reset for user } cursor.moveToFirst(); return cursor; }
/** * Set the {@link SqlTable table} this query selects from * * @param table the table to select from * @return this Query object, to allow chaining method calls */ public Query from(SqlTable<?> table) { if (immutable) { return fork().from(table); } if (this.table != table) { this.table = table; if (selectAllCache != null) { selectAllCache.clear(); } invalidateCompileCache(); } return this; }
public void testNeedsValidationUpdatedBySubqueryTable() { Query subquery = Query.select(Thing.PROPERTIES).from(Thing.TABLE).where(Criterion.literal(123)); subquery.requestValidation(); assertTrue(subquery.compile(database.getCompileContext()).sql.contains("WHERE (?)")); Query baseTestQuery = Query.select().from(Thing.TABLE).where(Thing.FOO.isNotEmpty()).freeze(); assertFalse(baseTestQuery.needsValidation()); Query testQuery = baseTestQuery.from(subquery.as("t1")); assertTrue(testQuery.compile(database.getCompileContext()).needsValidation); assertTrue(testQuery.sqlForValidation(database.getCompileContext()).contains("WHERE ((?))")); testQuery = baseTestQuery.innerJoin(subquery.as("t2"), (Criterion[]) null); assertTrue(testQuery.compile(database.getCompileContext()).needsValidation); assertTrue(testQuery.sqlForValidation(database.getCompileContext()).contains("WHERE ((?))")); testQuery = baseTestQuery.union(subquery); assertTrue(testQuery.compile(database.getCompileContext()).needsValidation); assertTrue(testQuery.sqlForValidation(database.getCompileContext()).contains("WHERE ((?))")); }
public void testNeedsValidationUpdatedByQueryFunction() { Query subquery = Query.select(Function.max(Thing.ID)).from(Thing.TABLE).where(Criterion.literal(123)); subquery.requestValidation(); assertTrue(subquery.compile(database.getCompileContext()).sql.contains("WHERE (?)")); Query baseTestQuery = Query.select().from(Thing.TABLE).where(Thing.FOO.isNotEmpty()).freeze(); assertFalse(baseTestQuery.needsValidation()); Query testQuery = baseTestQuery.selectMore(subquery.asFunction()); assertTrue(testQuery.compile(database.getCompileContext()).needsValidation); assertTrue(testQuery.sqlForValidation(database.getCompileContext()).contains("WHERE ((?))")); }
public void testValidationPropagatesToSubqueryJoinAndCompoundSelect() { Query subquery = Query.select(Thing.FOO).from(Thing.TABLE).where(Thing.BAR.gt(0)); Query joinSubquery = Query.select(Thing.BAR).from(Thing.TABLE).where(Thing.FOO.isNotEmpty()); Query compoundSubquery = Query.select(Thing.BAZ).from(Thing.TABLE).where(Thing.IS_ALIVE.isTrue()); SubqueryTable subqueryTable = subquery.as("t1"); SubqueryTable joinTable = joinSubquery.as("t2"); Query query = Query.select().from(subqueryTable).innerJoin(joinTable, (Criterion[]) null) .union(compoundSubquery); final int queryLength = query.compile(database.getCompileContext()).sql.length(); String withValidation = query.sqlForValidation(database.getCompileContext()); assertEquals(queryLength + 6, withValidation.length()); }
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 testQueryFreeze() { Query base = Query.select().from(Employee.TABLE).limit(1).freeze(); Query fork = base.limit(2); assertFalse(base == fork); assertEquals(Field.field("1"), base.getLimit()); assertEquals(Field.field("2"), fork.getLimit()); assertEquals(base.getTable(), fork.getTable()); }
StringProperty subordinates = StringProperty.fromFunction(Function.groupConcat(aliasedId, separator), "subordinates"); Query subquery = Query.select(aliasedManagerId, subordinates).from(employeesAlias) .groupBy(aliasedManagerId); 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);
public void testInCriterion() { List<String> expectedNames = Arrays.asList("bigBird", "cookieMonster"); Query query = Query.select().where(Employee.NAME.in("bigBird", "cookieMonster")).orderBy(Employee.NAME.asc()); testInQuery(expectedNames, query); query = Query.select().where(Employee.NAME.notIn("bigBird", "cookieMonster")).orderBy(Employee.NAME.asc()); testInQuery(Arrays.asList("bert", "elmo", "ernie", "oscar"), query); List<String> list = Arrays.asList("bigBird", "cookieMonster"); query = Query.select().where(Employee.NAME.in(list)).orderBy(Employee.NAME.asc()); testInQuery(expectedNames, query); // Test off-by-one error that used to occur when the in criterion wasn't the last criterion in the list query = Query.select().where(Employee.NAME.in(list).or(Field.field("1").neq(1))).orderBy(Employee.NAME.asc()); testInQuery(expectedNames, query); }
public void testDatabaseProvidedArgumentResolver() { database.useCustomArgumentBinder = true; Query query = Query.select(TestModel.SOME_ENUM).from(TestModel.TABLE) .where(TestModel.SOME_ENUM.eq(TestEnum.APPLE)); CompiledStatement compiledStatement = query.compile(database.getCompileContext()); verifyCompiledSqlArgs(compiledStatement, 1, 0); }
public void testFork() { Query base = Query.select().from(Employee.TABLE).limit(1); Query fork = base.fork().limit(2); base.limit(3); assertFalse(base == fork); assertEquals(Field.field("3"), base.getLimit()); assertEquals(Field.field("2"), fork.getLimit()); assertEquals(base.getTable(), fork.getTable()); }
/** * Add more {@link Field Fields} to be selected * * @param fields the additional Fields to be selected * @return this Query object, to allow chaining method calls */ public Query selectMore(List<Field<?>> fields) { if (immutable) { return fork().selectMore(fields); } if (!isEmpty(fields)) { if (this.fields == null) { this.fields = new ArrayList<>(fields); } else { this.fields.addAll(fields); } if (selectAllCache != null) { selectAllCache.clear(); } invalidateCompileCache(); } return this; }
@Override public void run() { // insert into testModels select luckyNumber from testModels where luckyNumber = 9; Query query = Query.select(TestModel.FIRST_NAME, TestModel.LAST_NAME, TestModel.BIRTHDAY) .from(TestModel.TABLE) .where(TestModel.LUCKY_NUMBER.eq(9)); Insert insert = Insert.into(TestModel.TABLE).select(query); insert.compile(database.getCompileContext()); } }, IllegalStateException.class);
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 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(); } }