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

@Override
public List<HistoryItem> historyItems(
        @NonNull HistoryPortion historyPortion
) {
    try {
        long portion = historyPortion.pageSize();
        int page = historyPortion.page();

        QueryBuilder<HistoryItem, ?> queryBuilder = getDao(HistoryItem.class)
                .queryBuilder()
                .limit(portion);
        if (page != -1L) {
            queryBuilder.offset(page * portion);
        }
        return queryBuilder.query();
    } catch (SQLException e) {
        L.e(e);
    }
    return new ArrayList<>();
}
 

/**
 * 排行榜, 按积分排序
 * 
 * @param offset
 * @param count
 * @return
 */
public List<GameRanking> getScoreListOrderByScore(long offset, long count)
{
	QueryBuilder<GameRanking, ?> qb = getDao().queryBuilder();
	try
	{
		// qb.where().eq("user_name", userName);
		qb.offset(offset);
		qb.limit(count);
		qb.orderBy("score", true);
		return qb.query();
	}
	catch (SQLException e)
	{
		e.printStackTrace();
	}

	return null;
}
 

/**
 * 排行榜, 按时间排序
 * 
 * @param offset
 * @param count
 * @return
 */
public List<GameRanking> getScoreListOrderByDate(long offset, long count)
{
	QueryBuilder<GameRanking, ?> qb = getDao().queryBuilder();
	try
	{
		// qb.where().eq("user_name", userName);
		qb.offset(offset);
		qb.limit(count);
		qb.orderBy("record_date", false);

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

	return null;
}
 

public static <T> T getNthRow(Dao<T, ?> dao, long row) {
    try {
        QueryBuilder<T, ?> queryBuilder = dao.queryBuilder();
        queryBuilder.limit(1L);
        queryBuilder.offset(row);
        return queryBuilder.query().get(0);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}
 

/**
 * 分页查询,并按列排序
 *
 * @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();
}
 

/**
 * 按列排序后分页查询
 *
 * @param map         查询的列条件
 * @param offset      查询的下标
 * @param count       查询的条数
 * @param orderColumn 排序的列
 * @param ascending   升序或降序,true为升序,false为降序
 * @return
 */
public List<T> queryForPagesByOrder(Map<String, Object> map, String orderColumn, boolean ascending, Long offset, Long count) throws SQLException {
    List<T> list = null;
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    Where where = queryBuilder.where();
    queryBuilder.orderBy(orderColumn, ascending);
    queryBuilder.offset(offset);
    queryBuilder.limit(count);
    where.isNotNull("id");
    for (Map.Entry<String, Object> entry : map.entrySet()) {
        where.and().eq(entry.getKey(), entry.getValue());
    }
    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 testLimitOffsetFormat() throws Exception {
	if (connectionSource == null) {
		return;
	}
	TableInfo<StringId, String> tableInfo = new TableInfo<StringId, String>(databaseType, StringId.class);
	QueryBuilder<StringId, String> qb = new QueryBuilder<StringId, String>(databaseType, tableInfo, null);
	long limit = 1232;
	qb.limit(limit);
	long offset = 171;
	qb.offset(offset);
	String query = qb.prepareStatementString();
	assertTrue(query + " should contain LIMIT", query.contains(" LIMIT " + offset + "," + limit + " "));
}
 

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

}