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

@Override
protected boolean internalProcess(AlerterContext context) throws SQLException {
  // get all actions that user did after joining.
  if (System.currentTimeMillis() < TIME_TO_WAIT_MS + context.dbAlertObject.enqueueTimestampMs) {
    context.dbAlertObject.nextCheckTimestampMs = context.dbAlertObject.enqueueTimestampMs + TIME_TO_WAIT_MS;
    return false;
  }
  QueryBuilder<DBProcessedAudit,Integer> builder = getBuilder(context, CANCEL_ALERT_ACTIONS, AlertUserType.ACTOR);

  // for some reason limit(int) is deprecated, WTF.
  builder.limit(1L);
  builder.setCountOf(true);
  
  if (context.manager.processedAuditDao.countOf(builder.prepare()) == 0) {
    // no such actions have happened. we should trigger the alert.
    logger.warn("should send unimplemented email for idle user");
    // TODO: send idle user email
  }
  return true;
}
 

public List<DanaRHistoryRecord> getDanaRHistoryRecordsByType(byte type) {
    List<DanaRHistoryRecord> historyList;
    try {
        QueryBuilder<DanaRHistoryRecord, String> queryBuilder = getDaoDanaRHistory().queryBuilder();
        queryBuilder.orderBy("recordDate", false);
        Where where = queryBuilder.where();
        where.eq("recordCode", type);
        queryBuilder.limit(200L);
        PreparedQuery<DanaRHistoryRecord> preparedQuery = queryBuilder.prepare();
        historyList = getDaoDanaRHistory().query(preparedQuery);
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
        historyList = new ArrayList<>();
    }
    return historyList;
}
 

@Nullable
public CareportalEvent getLastCareportalEvent(String event) {
    try {
        List<CareportalEvent> careportalEvents;
        QueryBuilder<CareportalEvent, Long> queryBuilder = getDaoCareportalEvents().queryBuilder();
        queryBuilder.orderBy("date", false);
        Where where = queryBuilder.where();
        where.eq("eventType", event).and().isNotNull("json");
        queryBuilder.limit(1L);
        PreparedQuery<CareportalEvent> preparedQuery = queryBuilder.prepare();
        careportalEvents = getDaoCareportalEvents().query(preparedQuery);
        if (careportalEvents.size() == 1)
            return careportalEvents.get(0);
        else
            return null;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return null;
}
 

public List<ProfileSwitch> getProfileSwitchData(long from, boolean ascending) {
    try {
        Dao<ProfileSwitch, Long> daoProfileSwitch = getDaoProfileSwitch();
        List<ProfileSwitch> profileSwitches;
        QueryBuilder<ProfileSwitch, Long> queryBuilder = daoProfileSwitch.queryBuilder();
        queryBuilder.orderBy("date", ascending);
        queryBuilder.limit(100L);
        Where where = queryBuilder.where();
        where.ge("date", from);
        PreparedQuery<ProfileSwitch> preparedQuery = queryBuilder.prepare();
        profileSwitches = daoProfileSwitch.query(preparedQuery);
        //add last one without duration
        ProfileSwitch last = getLastProfileSwitchWithoutDuration();
        if (last != null) {
            if (!profileSwitches.contains(last))
                profileSwitches.add(last);
        }
        return profileSwitches;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<>();
}
 

@Nullable
private ProfileSwitch getLastProfileSwitchWithoutDuration() {
    try {
        Dao<ProfileSwitch, Long> daoProfileSwitch = getDaoProfileSwitch();
        List<ProfileSwitch> profileSwitches;
        QueryBuilder<ProfileSwitch, Long> queryBuilder = daoProfileSwitch.queryBuilder();
        queryBuilder.orderBy("date", false);
        queryBuilder.limit(1L);
        Where where = queryBuilder.where();
        where.eq("durationInMinutes", 0);
        PreparedQuery<ProfileSwitch> preparedQuery = queryBuilder.prepare();
        profileSwitches = daoProfileSwitch.query(preparedQuery);
        if (profileSwitches.size() > 0)
            return profileSwitches.get(0);
        else
            return null;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return null;
}
 

public List<ProfileSwitch> getProfileSwitchEventsFromTime(long mills, boolean ascending) {
    try {
        Dao<ProfileSwitch, Long> daoProfileSwitch = getDaoProfileSwitch();
        List<ProfileSwitch> profileSwitches;
        QueryBuilder<ProfileSwitch, Long> queryBuilder = daoProfileSwitch.queryBuilder();
        queryBuilder.orderBy("date", ascending);
        queryBuilder.limit(100L);
        Where where = queryBuilder.where();
        where.ge("date", mills);
        PreparedQuery<ProfileSwitch> preparedQuery = queryBuilder.prepare();
        profileSwitches = daoProfileSwitch.query(preparedQuery);
        return profileSwitches;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<>();
}
 

public List<ProfileSwitch> getProfileSwitchEventsFromTime(long from, long to, boolean ascending) {
    try {
        Dao<ProfileSwitch, Long> daoProfileSwitch = getDaoProfileSwitch();
        List<ProfileSwitch> profileSwitches;
        QueryBuilder<ProfileSwitch, Long> queryBuilder = daoProfileSwitch.queryBuilder();
        queryBuilder.orderBy("date", ascending);
        queryBuilder.limit(100L);
        Where where = queryBuilder.where();
        where.between("date", from, to);
        PreparedQuery<ProfileSwitch> preparedQuery = queryBuilder.prepare();
        profileSwitches = daoProfileSwitch.query(preparedQuery);
        return profileSwitches;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<>();
}
 

@Override
@Test
public void testLimitFormat() throws Exception {
	if (connectionSource == null) {
		return;
	}
	Dao<StringId, String> dao;
	try {
		connectionSource.setDatabaseType(databaseType);
		dao = createDao(StringId.class, false);
	} finally {
		connectionSource.setDatabaseType(new H2DatabaseType());
	}
	QueryBuilder<StringId, String> qb = dao.queryBuilder();
	long limit = 1232;
	qb.limit(limit);
	String query = qb.prepareStatementString();
	assertTrue(query + " should start with stuff", query.startsWith("SELECT TOP " + limit + " "));
}
 

/**
 * 排行榜, 按积分排序
 * 
 * @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;
}
 

@Override
@Test
public void testLimitFormat() throws Exception {
	connectionSource.setDatabaseType(databaseType);
	BaseDaoImpl<StringId, String> dao = new BaseDaoImpl<StringId, String>(connectionSource, StringId.class) {
	};
	dao.initialize();
	QueryBuilder<StringId, String> qb = dao.queryBuilder();
	long limit = 1232;
	qb.limit(limit);
	String query = qb.prepareStatementString();
	assertTrue(query + " should start with stuff", query.startsWith("SELECT LIMIT 0 " + limit + " "));
}
 

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);
    }
}
 

@Test
public void testLimitFormat() throws Exception {
	if (connectionSource == null) {
		return;
	}
	if (!databaseType.isLimitSqlSupported()) {
		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);
	String query = qb.prepareStatementString();
	assertTrue(query + " should contain LIMIT", query.contains(" LIMIT " + limit + " "));
}
 

@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 + " "));
}
 

@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));
		}
	}
}
 

public void deleteDbRequestbyMongoId(String action, String id) {
    try {
        QueryBuilder<DbRequest, String> queryBuilder = getDaoDbRequest().queryBuilder();
        Where where = queryBuilder.where();
        where.eq("_id", id).and().eq("action", action);
        queryBuilder.limit(10L);
        PreparedQuery<DbRequest> preparedQuery = queryBuilder.prepare();
        List<DbRequest> dbList = getDaoDbRequest().query(preparedQuery);
        for (DbRequest r : dbList) {
            delete(r);
        }
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
}
 

public List<TDD> getTDDs() {
    List<TDD> tddList;
    try {
        QueryBuilder<TDD, String> queryBuilder = getDaoTDD().queryBuilder();
        queryBuilder.orderBy("date", false);
        queryBuilder.limit(10L);
        PreparedQuery<TDD> preparedQuery = queryBuilder.prepare();
        tddList = getDaoTDD().query(preparedQuery);
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
        tddList = new ArrayList<>();
    }
    return tddList;
}
 

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

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

}