protected <TYPE extends AbstractModel> SquidCursor<TYPE> fetchFirstItem(Class<TYPE> modelClass, Criterion criterion, Property<?>... properties) { return fetchFirstItem(modelClass, Query.select(properties).where(criterion)); }
/** * Add a {@link Criterion} to the WHERE clause of this query. Multiple calls will combine all the criterions with * AND. * * @param criterion the Criterion to add to the WHERE clause * @return this Query object, to allow chaining method calls */ public Query where(Criterion criterion) { if (criterion == null) { return this; } if (immutable) { return fork().where(criterion); } if (criterions == null) { criterions = new ArrayList<>(); } criterions.add(criterion); invalidateCompileCache(); return this; }
public Query getTasksWithTagsQuery(Criterion filterBy) { if (filterBy == null) { return TASKS_WITH_TAGS; } // Since the query is frozen, this will create a clone with the given filter return TASKS_WITH_TAGS.where(filterBy); }
/** * 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(); } }
/** * 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; }
public void testReusableQuery() { AtomicReference<String> name = new AtomicReference<>(); Query query = Query.select().where(Employee.NAME.eq(name)); testReusableQueryInternal(name, "bigBird", query); testReusableQueryInternal(name, "cookieMonster", query); testReusableQueryInternal(name, "elmo", query); }
public void testReusableQueryWithInCriterion() { Set<String> collection = new HashSet<>(); Query query = Query.select().where(Employee.NAME.in(collection)); testReusableQueryWithInCriterionInternal(collection, query, "bigBird", "cookieMonster", "elmo"); testReusableQueryWithInCriterionInternal(collection, query, "bigBird", "cookieMonster"); testReusableQueryWithInCriterionInternal(collection, query, "oscar"); testReusableQueryWithInCriterionInternal(collection, query); }
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 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 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 testEnumResolvedUsingName() { 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, "APPLE"); }
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(); } }
@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 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 ((?))")); }
@Override public void run() { // insert into testModels (firstName, lastName) select (firstName, lastName, creationDate) 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).columns(TestModel.FIRST_NAME, TestModel.LAST_NAME) .select(query); insert.compile(database.getCompileContext()); } }, IllegalStateException.class);
public void testIntersect() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .intersect(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(1, cursor.getCount()); cursor.moveToFirst(); assertEquals(cookieMonster, new Employee(cursor)); } finally { 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 testSelectionArgsGeneration() { Query query = Query.select(TestModel.PROPERTIES) .where(TestModel.FIRST_NAME.eq("Sam") .and(TestModel.BIRTHDAY.gt(17)) .and(TestModel.LAST_NAME.neq("Smith"))); CompiledStatement compiledQuery = query.compile(database.getCompileContext()); verifyCompiledSqlArgs(compiledQuery, 3, "Sam", 17, "Smith"); }
public void testWithNonLiteralCriterion() { TestModel model = new TestModel().setFirstName("Sam").setLastName("Sam"); database.persist(model); TestModel fetch = database .fetchByQuery(TestModel.class, Query.select().where(TestModel.FIRST_NAME.eq(TestModel.LAST_NAME))); assertNotNull(fetch); assertEquals(fetch.getFirstName(), fetch.getLastName()); }
public void testChangeCursorClosesOldCursor() { TestAdapter adapter = new TestAdapter(new TestModel()); SquidCursor<TestModel> cursor1 = database.query(TestModel.class, Query.select()); adapter.swapCursor(cursor1); SquidCursor<TestModel> cursor2 = database.query(TestModel.class, Query.select().where(TestModel.ID.eq(1))); adapter.changeCursor(cursor2); assertTrue(cursor1.isClosed()); adapter.changeCursor(null); cursor2.close(); }