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

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

public List<TempTarget> getTemptargetsDataFromTime(long from, long to, boolean ascending) {
    try {
        Dao<TempTarget, Long> daoTempTargets = getDaoTempTargets();
        List<TempTarget> tempTargets;
        QueryBuilder<TempTarget, Long> queryBuilder = daoTempTargets.queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.between("date", from, to);
        PreparedQuery<TempTarget> preparedQuery = queryBuilder.prepare();
        tempTargets = daoTempTargets.query(preparedQuery);
        return tempTargets;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<TempTarget>();
}
 

public List<TDD> getTDDsForLastXDays(int days) {
    List<TDD> tddList;
    GregorianCalendar gc = new GregorianCalendar();
    gc.add(Calendar.DAY_OF_YEAR, (-1) * days);

    try {
        QueryBuilder<TDD, String> queryBuilder = getDaoTDD().queryBuilder();
        queryBuilder.orderBy("date", false);
        Where<TDD, String> where = queryBuilder.where();
        where.ge("date", gc.getTimeInMillis());
        PreparedQuery<TDD> preparedQuery = queryBuilder.prepare();
        tddList = getDaoTDD().query(preparedQuery);
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
        tddList = new ArrayList<>();
    }
    return tddList;
}
 

public List<CareportalEvent> getCareportalEvents(long start, long end, boolean ascending) {
    try {
        List<CareportalEvent> careportalEvents;
        QueryBuilder<CareportalEvent, Long> queryBuilder = getDaoCareportalEvents().queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.between("date", start, end).and().isNotNull("json").and().isNotNull("eventType");
        PreparedQuery<CareportalEvent> preparedQuery = queryBuilder.prepare();
        careportalEvents = getDaoCareportalEvents().query(preparedQuery);
        careportalEvents = preprocessOpenAPSOfflineEvents(careportalEvents);
        return careportalEvents;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<>();
}
 

public List<BgReading> getBgreadingsDataFromTime(long start, long end, boolean ascending) {
    try {
        Dao<BgReading, Long> daoBgreadings = getDaoBgReadings();
        List<BgReading> bgReadings;
        QueryBuilder<BgReading, Long> queryBuilder = daoBgreadings.queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.between("date", start, end).and().ge("value", 39).and().eq("isValid", true);
        PreparedQuery<BgReading> preparedQuery = queryBuilder.prepare();
        bgReadings = daoBgreadings.query(preparedQuery);
        return bgReadings;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<>();
}
 

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

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

/**
 * Returns the record for the given id, null if none, throws RuntimeException
 * if multiple records with the same pump id exist.
 */
@Nullable
public Treatment getPumpRecordById(long pumpId) {
    try {
        QueryBuilder<Treatment, Long> queryBuilder = getDao().queryBuilder();
        Where where = queryBuilder.where();
        where.eq("pumpId", pumpId);
        queryBuilder.orderBy("date", true);

        List<Treatment> result = getDao().query(queryBuilder.prepare());
        if (result.isEmpty())
            return null;
        if (result.size() > 1)
            log.warn("Multiple records with the same pump id found (returning first one): " + result.toString());
        return result.get(0);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}
 

public List<Treatment> getTreatmentDataFromTime(long mills, boolean ascending) {
    try {
        Dao<Treatment, Long> daoTreatments = getDao();
        List<Treatment> treatments;
        QueryBuilder<Treatment, Long> queryBuilder = daoTreatments.queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.ge("date", mills);
        PreparedQuery<Treatment> preparedQuery = queryBuilder.prepare();
        treatments = daoTreatments.query(preparedQuery);
        return treatments;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<>();
}
 

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

/**
 * 查询所有通知信息[越新的数据下标越小]
 */
public List<InviteBean> queryAllSortByStamp()
{
    List<InviteBean> list = null;
    try
    {
        QueryBuilder<InviteBean, Integer> queryBuilder = getDao().queryBuilder();
        queryBuilder.orderBy(InviteDbConfig.STAMP, false);
        list = getDao().query(queryBuilder.prepare());
    } catch (SQLException e)
    {
        KLog.e(TAG + "InviteDao.queryAllSortByStamp fail:" + e.toString());
    }
    return list;
}
 

/**
 * 排序查询小于指定值的所有记录
 *
 * @param orderColumn 小于的列
 * @param limitValue  小于的值
 * @param ascending   true为升序,false为降序
 * @return 查询结果
 */
public List<T> queryLeByOrder(String orderColumn, Object limitValue, boolean ascending) throws SQLException {
    List<T> list = null;
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    Where where = queryBuilder.where();
    where.le(orderColumn, limitValue);
    queryBuilder.orderBy(orderColumn, ascending);
    return queryBuilder.query();
}
 

/**
 * 根据模块返回搜索结果
 * 
 * @param module
 * @return
 */
public List<AlbumInfo> queryByCategory(String category)
{
	List<AlbumInfo> albumInfoList = null;
	try
	{
		QueryBuilder<AlbumInfo, Long> queryBuilder = mAlbumDao.queryBuilder();
		queryBuilder.orderBy("orderby", true);
		albumInfoList = queryBuilder.where().eq("category", category).query();
	}
	catch (SQLException e)
	{
		e.printStackTrace();
	}
	
	if (encrypt_mode)
	{
		if (albumInfoList == null || albumInfoList.isEmpty())
		{
			return null;
		}

		int size = albumInfoList.size();
		for (int i = 0; i < size; i++)
		{
			albumInfoList.get(i).setCoverurl(SecurityTu123.decodeImageUrl(albumInfoList.get(i).getCoverurl()));
		}
	}
	
	return albumInfoList;
}
 

public List<TemporaryBasal> getTemporaryBasalsDataFromTime(long mills, boolean ascending) {
    try {
        List<TemporaryBasal> tempbasals;
        QueryBuilder<TemporaryBasal, Long> queryBuilder = getDaoTemporaryBasal().queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.ge("date", mills);
        PreparedQuery<TemporaryBasal> preparedQuery = queryBuilder.prepare();
        tempbasals = getDaoTemporaryBasal().query(preparedQuery);
        return tempbasals;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<TemporaryBasal>();
}
 

public List<TemporaryBasal> getTemporaryBasalsDataFromTime(long from, long to, boolean ascending) {
    try {
        List<TemporaryBasal> tempbasals;
        QueryBuilder<TemporaryBasal, Long> queryBuilder = getDaoTemporaryBasal().queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.between("date", from, to);
        PreparedQuery<TemporaryBasal> preparedQuery = queryBuilder.prepare();
        tempbasals = getDaoTemporaryBasal().query(preparedQuery);
        return tempbasals;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<TemporaryBasal>();
}
 

@SuppressWarnings({"rawtypes", "unchecked"})
void updateUI() {
    AppCompatActivity activity = (AppCompatActivity) getActivity();

    try {
        DatabaseHelper dbHelper = new DatabaseHelper(activity);

        // Création du queryBuilder, permettant de lister les employés par leur nom de service
        QueryBuilder<FicheEmploye, String> queryBuilder = (QueryBuilder<FicheEmploye, String>) dbHelper.getDao(FicheEmploye.class).queryBuilder();

        queryBuilder.orderBy("Service", true);
        PreparedQuery<FicheEmploye> preparedQuery = queryBuilder.prepare();
        List<FicheEmploye> listEmployes = dbHelper.getDao(FicheEmploye.class).query(preparedQuery);

        // Si le contenu n'est pas vide, l'ajouter au listDataHeader et listDataChild
        if (listEmployes.size() > 0) {
            String nomService = "";
            String previousNomService = "";
            listDataHeader.clear();

            ArrayList<FicheEmploye> listEmployesOfService = new ArrayList<>();
            // Pour le premier élément dans la liste
            FicheEmploye employe = listEmployes.get(0);
            nomService = employe.Service;
            listDataHeader.add(nomService);
            listEmployesOfService.add(employe);
            previousNomService = nomService;
            // Pour les prochains éléments dans la liste
            for (int i = 1; i < listEmployes.size(); i++) {
                employe = listEmployes.get(i);
                nomService = employe.Service;
                if (!listDataHeader.contains(nomService)) {
                    listDataHeader.add(nomService);
                    Collections.sort(listEmployesOfService, new Comparator<FicheEmploye>() {
                        @Override
                        public int compare(FicheEmploye f1, FicheEmploye f2) {
                            return f1.Nom.compareTo(f2.Nom);
                        }
                    });
                    listDataChild.put(previousNomService, listEmployesOfService);
                    listEmployesOfService = new ArrayList<>();
                    previousNomService = nomService;
                }
                listEmployesOfService.add(employe);
            }
            // Pour les derniers éléments dans la liste
            listDataChild.put(previousNomService, listEmployesOfService);

            if (activity != null) {
                activity.runOnUiThread(new Runnable() {
                    public void run() {
                        listAdapter = new ExpandableListAdapter(getActivity(),
                                listDataHeader, listDataChild);
                        expListView.setAdapter(listAdapter);
                        listAdapter.notifyDataSetChanged();
                    }
                });
            }

            // Rétablissement du filtre de recherche
            CharSequence searchText = searchView.getQuery();
            if (searchText.length() != 0)
                onQueryTextChange(searchText.toString());

            // Si le contenu est vide, télécharger le bottin
        } else { // Le contenu est vide.
            afficherRafraichissementEtRechargerBottin();
        }
    } catch (Exception e) {
        Log.e("BD FicheEmploye", "" + e.getMessage());
    }
}
 

private PreparedQuery<Location> buildQuery(Dao<Location, Long> dao, int filterId) throws SQLException {
      QueryBuilder<Location, Long> qb = dao.queryBuilder();
      Calendar c = Calendar.getInstance();
String subtitle = "";
String footerText = "All people have been returned";

      if (filterId == getResources().getInteger(R.integer.time_filter_last_month)) {
          subtitle = "Last Month";
          footerText = "End of results for Last Month filter";
          c.add(Calendar.MONTH, -1);
      } else if (filterId == getResources().getInteger(R.integer.time_filter_last_week)) {
          subtitle = "Last Week";
          footerText = "End of results for Last Week filter";
          c.add(Calendar.DAY_OF_MONTH, -7);
      } else if (filterId == getResources().getInteger(R.integer.time_filter_last_24_hours)) {
          subtitle = "Last 24 Hours";
          footerText = "End of results for Last 24 Hours filter";
          c.add(Calendar.HOUR, -24);
      } else if (filterId == getResources().getInteger(R.integer.time_filter_today)) {
          subtitle = "Since Midnight";
          footerText = "End of results for Today filter";
          c.set(Calendar.HOUR_OF_DAY, 0);
          c.set(Calendar.MINUTE, 0);
          c.set(Calendar.SECOND, 0);
          c.set(Calendar.MILLISECOND, 0);
      }  else if (filterId == getResources().getInteger(R.integer.time_filter_custom)) {
	String customFilterTimeUnit = getCustomTimeUnit();
	int customTimeNumber = getCustomTimeNumber();

	subtitle = "Last " + customTimeNumber + " " + customFilterTimeUnit;
	footerText = "End of results for custom filter";
	switch (customFilterTimeUnit) {
		case "Hours":
			c.add(Calendar.HOUR, -1 * customTimeNumber);
			break;
		case "Days":
			c.add(Calendar.DAY_OF_MONTH, -1 * customTimeNumber);
			break;
		case "Months":
			c.add(Calendar.MONTH, -1 * customTimeNumber);
			break;
		default:
			c.add(Calendar.MINUTE, -1 * customTimeNumber);
			break;
	}

} else {
          // no filter
          c.setTime(new Date(0));
      }

      TextView footerTextView = footer.findViewById(R.id.footer_text);
      footerTextView.setText(footerText);
User currentUser = null;
try {
	currentUser = UserHelper.getInstance(context).readCurrentUser();
} catch (UserException e) {
	e.printStackTrace();
}

Where<Location, Long> where = qb.where().gt("timestamp", c.getTime());
if (currentUser != null) {
	where
              .and()
              .ne("user_id", currentUser.getId())
              .and()
              .eq("event_id", currentUser.getUserLocal().getCurrentEvent().getId());
      }

qb.orderBy("timestamp", false);

      ((AppCompatActivity) getActivity()).getSupportActionBar().setSubtitle(subtitle);

      return qb.prepare();
  }
 

/**
 * 排序查询指定条件下，大于指定值的所有记录
 *
 * @param orderColumn 大于的列
 * @param limitValue  大于的值
 * @param columnName  查询条件列名
 * @param value       查询条件值
 * @param ascending   true为升序,false为降序
 * @return
 */
public List<T> queryGeByOrder(String orderColumn, Object limitValue, String columnName, Object value, boolean ascending) throws SQLException {
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    Where where = queryBuilder.where();
    where.eq(columnName, value);
    where.and().ge(orderColumn, limitValue);
    queryBuilder.orderBy(orderColumn, ascending);
    return queryBuilder.query();
}