Java Code Examples for com.j256.ormlite.stmt.QueryBuilder#query()

public List<ElementEvaluation> getElementsEvaluation(ListeDesElementsEvaluation listeDesElementsEvaluation) {
    DatabaseHelper dbHelper = new DatabaseHelper(context);
    List<ElementEvaluation> elementEvaluationList = null;
    try {
        Dao<ElementEvaluation, String> elementsEvaluationDao = dbHelper.getDao(ElementEvaluation.class);
        QueryBuilder<ElementEvaluation, String> builder = elementsEvaluationDao.queryBuilder();

        Where where = builder.where();
        where.eq("listeDesElementsEvaluation_id", listeDesElementsEvaluation);

        elementEvaluationList = builder.query();

    } catch (SQLException e) {
        Log.e("SQL Exception", e.getMessage());
    }
    return elementEvaluationList;
}
 

/**
 * 返回完整Focus List
 * 
 * @param module
 * @return
 */
public List<FocusItemInfo> getFocusList()
{
	List<FocusItemInfo> koItemInfoList = null;
	try
	{
		QueryBuilder<FocusItemInfo, Long> queryBuilder = mFocusItemInfoDao.queryBuilder();
		// queryBuilder.groupBy("category");
		queryBuilder.orderBy("order_by", true);
		koItemInfoList = queryBuilder.query();
	}
	catch (SQLException e)
	{
		e.printStackTrace();
	}
	
	return koItemInfoList;
}
 

/**
 * Query by the unique column values
 * 
 * @param constraintName
 *            constraint name
 * @param constraintType
 *            constraint type
 * @param value
 *            value
 * @return data column constraints
 * @throws SQLException
 *             upon failure
 */
public DataColumnConstraints queryByUnique(String constraintName,
		DataColumnConstraintType constraintType, String value)
		throws SQLException {

	DataColumnConstraints constraint = null;

	QueryBuilder<DataColumnConstraints, Void> qb = queryBuilder();
	setUniqueWhere(qb.where(), constraintName, constraintType, value);
	List<DataColumnConstraints> constraints = qb.query();
	if (!constraints.isEmpty()) {

		if (constraints.size() > 1) {
			throw new GeoPackageException("More than one "
					+ DataColumnConstraints.class.getSimpleName()
					+ " was found for unique constraint. Name: "
					+ constraintName + ", Type: " + constraintType
					+ ", Value: " + value);
		}

		constraint = constraints.get(0);
	}

	return constraint;
}
 

/**
 * Query by extension name, table name, and column name
 * 
 * @param extensionName
 *            extension name
 * @param tableName
 *            table name
 * @param columnName
 *            column name
 * @return extensions
 * @throws SQLException
 *             upon failure
 */
public Extensions queryByExtension(String extensionName, String tableName,
		String columnName) throws SQLException {

	QueryBuilder<Extensions, Void> qb = queryBuilder();

	setUniqueWhere(qb.where(), extensionName, true, tableName, true,
			columnName);

	List<Extensions> extensions = qb.query();

	Extensions extension = null;
	if (extensions.size() > 1) {
		throw new GeoPackageException("More than one "
				+ Extensions.class.getSimpleName()
				+ " existed for unique combination of Extension Name: "
				+ extensionName + ", Table Name: " + tableName
				+ ", Column Name: " + columnName);
	} else if (extensions.size() == 1) {
		extension = extensions.get(0);
	}

	return extension;
}
 

/**
 * 分页查询,并按列排序
 *
 * @param orderColumn 排序列名
 * @param ascending   true为升序,false为降序
 * @param offset      搜索下标
 * @param count       搜索条数
 * @return 分页查询后的数据集
 */
public List<T> queryForPagesByOrder(String orderColumn, boolean ascending, Long offset, Long count) throws SQLException {
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    Where where = queryBuilder.where();
    where.isNotNull(orderColumn);
    queryBuilder.orderBy(orderColumn, ascending);
    queryBuilder.offset(offset);
    queryBuilder.limit(count);
    return queryBuilder.query();
}
 

/**
 * Query by the metadata parent ids
 * 
 * @param parentId
 *            parent id
 * @return metadata references
 * @throws SQLException
 *             upon failure
 */
public List<MetadataReference> queryByMetadataParent(long parentId)
		throws SQLException {

	QueryBuilder<MetadataReference, Void> qb = queryBuilder();
	qb.where().eq(MetadataReference.COLUMN_PARENT_ID, parentId);
	List<MetadataReference> metadataReferences = qb.query();

	return metadataReferences;
}
 

/**
 * 查询列名不等于指定值的记录
 *
 * @param columnName 列名
 * @param value      指定值
 */
public List<T> queryNotEqualsByColumnName(String columnName, Object value) throws SQLException {
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    Where where = queryBuilder.where();
    where.or(where.gt(columnName, value), where.lt(columnName, value));
    return queryBuilder.query();
}
 

@Test
public void testLimitOffset() throws Exception {
	if (databaseType == null || !databaseType.isOffsetSqlSupported()) {
		return;
	}
	final Dao<Foo, Object> dao = createDao(Foo.class, true);
	final int numPages = 10;
	final int numPerPage = 10;
	final List<Foo> foos = new ArrayList<Foo>();
	dao.callBatchTasks(new Callable<Void>() {
		@Override
		public Void call() throws Exception {
			for (int i = 0; i < numPages + numPerPage; i++) {
				Foo foo = new Foo();
				foos.add(foo);
				assertEquals(1, dao.create(foo));
			}
			return null;
		}
	});
	QueryBuilder<Foo, Object> qb = dao.queryBuilder();
	for (int pageC = 0; pageC < numPages; pageC++) {
		qb.limit((long) numPerPage);
		int offset = pageC * numPerPage;
		qb.offset((long) offset);
		List<Foo> results = qb.query();
		for (int i = 0; i < results.size(); i++) {
			assertEquals(foos.get(offset + i), results.get(i));
		}
	}
}
 

@Test
public void testSelectRawNotQuery() throws Exception {
	Dao<Foo, String> dao = createDao(Foo.class, true);
	Foo foo = new Foo();
	assertEquals(1, dao.create(foo));
	QueryBuilder<Foo, String> qb = dao.queryBuilder();
	qb.selectRaw("COUNTOF(*)");
	try {
		qb.query();
	} catch (SQLException e) {
		// expected
	}
}
 

/**
 * Get by contents data type
 * 
 * @param type
 *            contents data type
 * @return contents ids
 */
public List<ContentsId> getIds(String type) {

	List<ContentsId> contentsIds = null;

	ContentsDao contentsDao = geoPackage.getContentsDao();

	try {

		if (contentsIdDao.isTableExists()) {

			QueryBuilder<Contents, String> contentsQueryBuilder = contentsDao
					.queryBuilder();
			QueryBuilder<ContentsId, Long> contentsIdQueryBuilder = contentsIdDao
					.queryBuilder();

			contentsQueryBuilder.where().eq(Contents.COLUMN_DATA_TYPE,
					type);
			contentsIdQueryBuilder.join(contentsQueryBuilder);

			contentsIds = contentsIdQueryBuilder.query();

		} else {
			contentsIds = new ArrayList<>();
		}
	} catch (SQLException e) {
		throw new GeoPackageException(
				"Failed to query for contents id by contents data type. GeoPackage: "
						+ geoPackage.getName() + ", Type: " + type,
				e);
	}

	return contentsIds;
}
 

protected void getQueryIdsForOrg(MitroRequestContext context, int orgId, Set<Integer> userIds, Set<Integer> secretIds, Set<Integer> groupIds)
    throws MitroServletException, SQLException {
  // TODO: We need to create an API for orgs and clean this up.
  @SuppressWarnings("deprecation")
  AuthenticatedDB userDb = AuthenticatedDB.deprecatedNew(context.manager, context.requestor);
  if (!userDb.isOrganizationAdmin(orgId)) {
    throw new MitroServletException("Not org or no access");
  }

  RPC.GetOrganizationStateResponse out = GetOrganizationState.doOperation(context,  orgId);

  userIds.clear();

  // TODO: change GetOrganizationState query to return the user id column.
  if (!out.members.isEmpty()) {
    QueryBuilder<DBIdentity, Integer> query = context.manager.identityDao.queryBuilder();
    query.selectColumns(DBIdentity.ID_NAME);
    Where<DBIdentity, Integer> where = query.where();
    where.in(DBIdentity.NAME_FIELD_NAME, Manager.makeSelectArgsFromList(out.members));

    for (DBIdentity identity : query.query()) {
      userIds.add(identity.getId());
    }
  }

  secretIds.clear();
  secretIds.addAll(out.orgSecretsToPath.keySet());
  secretIds.addAll(out.orphanedSecretsToPath.keySet());

  groupIds.clear();
  groupIds.addAll(out.groups.keySet());
}
 

public List<User> getAllUser() {
    try {
        QueryBuilder<User, Integer> qb = dao.queryBuilder();
        return qb.query();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}
 

/** Returns all groups where requestor is a direct member. */
private List<DBGroup> getAllDirectlyAccessibleGroups() throws SQLException {
  QueryBuilder<DBGroup, Integer> groupQuery = getDirectlyAccessibleGroupsQuery(null);
  return groupQuery.query();
}
 

/**
 * 根据Map对应的key(列名)和value(列属性值), 返回列表数据
 * 
 * @param map
 * @return
 */
public List<Model> queryForEq(Map<String, Object> map)
{
	if (map == null || map.isEmpty())
	{
		return null;
	}

	QueryBuilder<Model, ?> queryBuilder = mRuntimeDao.queryBuilder();
	Where<Model, ?> where = null;

	Iterator<Entry<String, Object>> iterator = map.entrySet().iterator();
	Entry<String, Object> entry = null;
	boolean isFirst = true;

	try
	{
		while (iterator.hasNext())
		{
			entry = iterator.next();

			if (isFirst)
			{
				where = queryBuilder.where().eq(entry.getKey(), entry.getValue());
				isFirst = false;
			}
			else
			{
				where.and().eq(entry.getKey(), entry.getValue());
			}
		}

		return queryBuilder.query();
	}
	catch (SQLException e)
	{
		e.printStackTrace();
	}

	return null;
}
 

@SuppressWarnings({ "unchecked" })
protected List<RPC.AuditEvent> queryAuditEvents(
    Manager manager,
    Set<Integer> userIds,
    Set<Integer> secretIds,
    Set<Integer> groupIds,
    Long limit, Long offset,
    Long startTimeMs, Long endTimeMs) throws SQLException, MitroServletException {
  List<RPC.AuditEvent> events = Lists.newArrayList();

  QueryBuilder<DBProcessedAudit, Integer> query = manager.processedAuditDao.queryBuilder();
  Where<DBProcessedAudit, Integer> where = query.where();

  List<Where<DBProcessedAudit, Integer>> clauses = new ArrayList<>();
  if (!userIds.isEmpty()) {
    clauses.add(where.and(where.in(DBProcessedAudit.ACTOR_FIELD_NAME, userIds),
                          where.in(DBProcessedAudit.ACTION_FIELD_NAME, 
                              Manager.makeSelectArgsFromList(USER_ACTION_TYPES))));
  }
  if (!secretIds.isEmpty()) {
    clauses.add(where.and(where.in(DBProcessedAudit.AFFECTED_SECRET_FIELD_NAME, secretIds),
                          where.in(DBProcessedAudit.ACTION_FIELD_NAME, 
                              Manager.makeSelectArgsFromList(SECRET_ACTION_TYPES))));
  }
  if (!groupIds.isEmpty()) {
    clauses.add(where.and(where.in(DBProcessedAudit.AFFECTED_GROUP_FIELD_NAME, groupIds),
                          where.in(DBProcessedAudit.ACTION_FIELD_NAME, 
                              Manager.makeSelectArgsFromList(GROUP_ACTION_TYPES))));
  }

  if (clauses.size() > 0) {
    orWhereClauses(where, clauses);

    if (startTimeMs != null) {
      where.and(where, where.ge(DBProcessedAudit.TIMESTAMP_FIELD_NAME, startTimeMs));
    }
    if (endTimeMs != null) {
      if (startTimeMs != null && startTimeMs > endTimeMs) {
        throw new MitroServletException("start time must be before end time");
      }
      where.and(where, where.le(DBProcessedAudit.TIMESTAMP_FIELD_NAME, endTimeMs));
    }
    // offset and limit of null interpreted as no offset and no limit.
    // Boolean param of orderBy specifies DESC order.
    query.offset(offset).limit(limit).orderBy(DBProcessedAudit.TIMESTAMP_FIELD_NAME, false);
    for (DBProcessedAudit dbAudit : query.query()) {
      RPC.AuditEvent auditEvent = new RPC.AuditEvent();
      fillRPCAuditEvent(manager, dbAudit, auditEvent);
      events.add(auditEvent);
    }
  }
  return events;
}
 

/**
 * Query by extension name and table name
 * 
 * @param extensionName
 *            extension name
 * @param tableName
 *            table name
 * @return extensions
 * @throws SQLException
 *             upon failure
 */
public List<Extensions> queryByExtension(String extensionName,
		String tableName) throws SQLException {

	QueryBuilder<Extensions, Void> qb = queryBuilder();

	setUniqueWhere(qb.where(), extensionName, true, tableName, false, null);

	List<Extensions> extensions = qb.query();

	return extensions;
}
 

/**
 * Query by the metadata ids
 * 
 * @param fileId
 *            file id
 * @param parentId
 *            parent id
 * @return metadata references
 * @throws SQLException
 *             upon failure
 */
public List<MetadataReference> queryByMetadata(long fileId, Long parentId)
		throws SQLException {

	QueryBuilder<MetadataReference, Void> qb = queryBuilder();
	setFkWhere(qb.where(), fileId, parentId);
	List<MetadataReference> metadataReferences = qb.query();

	return metadataReferences;
}
 

/**
 * 查询大于某个值的记录
 *
 * @param orderColumn 排序的列
 * @param value       某个值
 * @return 大于某个值的记录
 */
public List<T> queryAllByGt(String orderColumn, Object value) throws SQLException {
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    Where where = queryBuilder.where();
    where.gt(orderColumn, value);
    return queryBuilder.query();
}
 

/**
 * 通过表列名查询
 *
 * @param columnName
 * @param value
 * @return
 */
public List<T> queryByColumnName(String columnName, Object value) throws SQLException {
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    queryBuilder.where().eq(columnName, value);
    return queryBuilder.query();

}
 

/**
 * Query by extension name
 * 
 * @param extensionName
 *            extension name
 * @return extensions
 * @throws SQLException
 *             upon failure
 */
public List<Extensions> queryByExtension(String extensionName)
		throws SQLException {

	QueryBuilder<Extensions, Void> qb = queryBuilder();

	setUniqueWhere(qb.where(), extensionName, false, null, false, null);

	List<Extensions> extensions = qb.query();

	return extensions;
}