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

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 TemporaryBasal findTempBasalById(String _id) {
    try {
        QueryBuilder<TemporaryBasal, Long> queryBuilder = null;
        queryBuilder = getDaoTemporaryBasal().queryBuilder();
        Where where = queryBuilder.where();
        where.eq("_id", _id);
        PreparedQuery<TemporaryBasal> preparedQuery = queryBuilder.prepare();
        List<TemporaryBasal> list = getDaoTemporaryBasal().query(preparedQuery);

        if (list.size() != 1) {
            return null;
        } else {
            return list.get(0);
        }
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return null;
}
 

@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<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 Message getMessageByDialogId(boolean firstMessage, List<Long> dialogOccupantsList) {
    Message message = null;

    try {
        QueryBuilder<Message, Long> queryBuilder = dao.queryBuilder();
        Where<Message, Long> where = queryBuilder.where();
        where.and(
                where.in(DialogOccupant.Column.ID, dialogOccupantsList),
                where.eq(Message.Column.STATE, State.READ)
        );
        queryBuilder.orderBy(Message.Column.CREATED_DATE, firstMessage);
        PreparedQuery<Message> preparedQuery = queryBuilder.prepare();
        message = dao.queryForFirst(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return message;
}
 

public List<CareportalEvent> getCareportalEventsFromTime(long mills, boolean ascending) {
    try {
        List<CareportalEvent> careportalEvents;
        QueryBuilder<CareportalEvent, Long> queryBuilder = getDaoCareportalEvents().queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.ge("date", mills).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<CareportalEvent> getCareportalEvents(boolean ascending) {
    try {
        List<CareportalEvent> careportalEvents;
        QueryBuilder<CareportalEvent, Long> queryBuilder = getDaoCareportalEvents().queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.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<>();
}
 

/**
 * help to build a where to query in database;;
 * @return
 */
private PreparedQuery buildWhere(String wantDay,int schoolWeek,String schoolWeekDsz) throws SQLException {
    QueryBuilder<ClassModel,Integer> queryBuilder = classDao.queryBuilder();
    queryBuilder.clear();
    Where<ClassModel,Integer> where = queryBuilder.where();
    where.and(
            where.like("day",wantDay),
            where.le("strWeek", schoolWeek),
            where.ge("endWeek", schoolWeek),
            where.or(
                    where.isNull("dsz"),
                    where.like("dsz",""),
                    where.like("dsz",schoolWeekDsz)
            )
    );
    return queryBuilder.prepare();
}
 

/**
 * help to build a where to query in database;
 *
 * @return
 */
private PreparedQuery buildWhere(String wantDay) throws SQLException {
    QueryBuilder<ClassModel,Integer> queryBuilder = classDao.queryBuilder();
    queryBuilder.clear();
    Where<ClassModel,Integer> where = queryBuilder.where();
    where.like("day",wantDay);
    return queryBuilder.prepare();
}
 

/**
 * 按列排序后分页查询
 *
 * @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 map 查询条件键值组合
 * @return
 */
public long getCount(Map<String, Object> map) throws SQLException {
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    queryBuilder.setCountOf(true);
    Where where = queryBuilder.where();
    where.isNotNull("id");
    for (Map.Entry<String, Object> entry : map.entrySet()) {
        where.and().eq(entry.getKey(), entry.getValue());
    }
    PreparedQuery<T> preparedQuery = queryBuilder.prepare();
    return ormLiteDao.countOf(preparedQuery);
}
 

/**
 * 排序查询
 *
 * @param orderColumn 排序的列
 * @param ascending   true为升序,false为降序
 * @return
 */
public List<T> queryAllByOrder(String orderColumn, boolean ascending) throws SQLException {
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    Where where = queryBuilder.where();
    where.isNotNull(orderColumn);
    queryBuilder.orderBy(orderColumn, ascending);
    return queryBuilder.query();
}
 

@Test(expected = SQLException.class)
public void testTwoResultsInSubQuery() throws Exception {
	BaseDaoImpl<ForeignFoo, Integer> foreignDao =
			new BaseDaoImpl<ForeignFoo, Integer>(connectionSource, ForeignFoo.class) {
			};
	QueryBuilder<ForeignFoo, Integer> qbInner = foreignDao.queryBuilder();
	qbInner.selectColumns(ID_COLUMN_NAME);
	QueryBuilder<ForeignFoo, Integer> qbOuter = foreignDao.queryBuilder();
	qbInner.selectColumns(FOREIGN_COLUMN_NAME);

	Where<ForeignFoo, Integer> where = qbOuter.where();
	where.in(ID_COLUMN_NAME, qbInner);
	where.prepare();
}
 

public List<DialogNotification> getDialogNotificationsByDialogIdAndDate(String dialogId, long createdDate, boolean moreDate) {
    List<DialogNotification> dialogNotificationsList = new ArrayList<>();

    try {
        QueryBuilder<DialogNotification, Long> messageQueryBuilder = dao.queryBuilder();

        Where<DialogNotification, Long> where = messageQueryBuilder.where();
        where.and(where.ne(DialogNotification.Column.STATE, State.TEMP_LOCAL),
                where.ne(DialogNotification.Column.STATE, State.TEMP_LOCAL_UNREAD),
                moreDate
                        ? where.gt(DialogNotification.Column.CREATED_DATE, createdDate)
                        : where.lt(DialogNotification.Column.CREATED_DATE, createdDate));

        QueryBuilder<DialogOccupant, Long> dialogOccupantQueryBuilder = dialogOccupantDao
                .queryBuilder();

        QueryBuilder<Dialog, Long> dialogQueryBuilder = dialogDao.queryBuilder();
        dialogQueryBuilder.where().eq(Dialog.Column.ID, dialogId);

        dialogOccupantQueryBuilder.join(dialogQueryBuilder);
        messageQueryBuilder.join(dialogOccupantQueryBuilder);

        PreparedQuery<DialogNotification> preparedQuery = messageQueryBuilder.prepare();
        dialogNotificationsList = dao.query(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return dialogNotificationsList;
}
 

public List<Message> getMessagesByDialogIdAndDate(String dialogId, long createdDate, boolean moreDate, long limit){
    List<Message> messagesList = new ArrayList<>();

    try {
        QueryBuilder<Message, Long> messageQueryBuilder = dao.queryBuilder();

        Where<Message, Long> where = messageQueryBuilder.where();
        where.and(where.ne(Message.Column.STATE, State.TEMP_LOCAL),
                where.ne(Message.Column.STATE, State.TEMP_LOCAL_UNREAD),
                moreDate
                        ? where.gt(Message.Column.CREATED_DATE, createdDate)
                        : where.lt(Message.Column.CREATED_DATE, createdDate));

        QueryBuilder<DialogOccupant, Long> dialogOccupantQueryBuilder = dialogOccupantDao.queryBuilder();

        QueryBuilder<Dialog, Long> dialogQueryBuilder = dialogDao.queryBuilder();
        dialogQueryBuilder.where().eq(Dialog.Column.ID, dialogId);

        dialogOccupantQueryBuilder.join(dialogQueryBuilder);
        messageQueryBuilder
                .join(dialogOccupantQueryBuilder)
                .orderBy(Message.Column.CREATED_DATE, false)
                .limit(limit);

        PreparedQuery<Message> preparedQuery = messageQueryBuilder.prepare();
        messagesList = dao.query(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return messagesList;
}
 

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 long getCount() throws SQLException {
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    queryBuilder.setCountOf(true);
    Where where = queryBuilder.where();
    where.isNotNull("id");
    PreparedQuery<T> preparedQuery = queryBuilder.prepare();
    return ormLiteDao.countOf(preparedQuery);
}
 

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

public boolean createOrUpdate(ProfileSwitch profileSwitch) {
    try {
        ProfileSwitch old;
        profileSwitch.date = roundDateToSec(profileSwitch.date);

        if (profileSwitch.source == Source.NIGHTSCOUT) {
            old = getDaoProfileSwitch().queryForId(profileSwitch.date);
            if (old != null) {
                if (!old.isEqual(profileSwitch)) {
                    profileSwitch.source = old.source;
                    profileSwitch.profileName = old.profileName; // preserver profileName to prevent multiple CPP extension
                    getDaoProfileSwitch().delete(old); // need to delete/create because date may change too
                    getDaoProfileSwitch().create(profileSwitch);
                    if (L.isEnabled(L.DATABASE))
                        log.debug("PROFILESWITCH: Updating record by date from: " + Source.getString(profileSwitch.source) + " " + old.toString());
                    scheduleProfileSwitchChange();
                    return true;
                }
                return false;
            }
            // find by NS _id
            if (profileSwitch._id != null) {
                QueryBuilder<ProfileSwitch, Long> queryBuilder = getDaoProfileSwitch().queryBuilder();
                Where where = queryBuilder.where();
                where.eq("_id", profileSwitch._id);
                PreparedQuery<ProfileSwitch> preparedQuery = queryBuilder.prepare();
                List<ProfileSwitch> trList = getDaoProfileSwitch().query(preparedQuery);
                if (trList.size() > 0) {
                    old = trList.get(0);
                    if (!old.isEqual(profileSwitch)) {
                        getDaoProfileSwitch().delete(old); // need to delete/create because date may change too
                        old.copyFrom(profileSwitch);
                        getDaoProfileSwitch().create(old);
                        if (L.isEnabled(L.DATABASE))
                            log.debug("PROFILESWITCH: Updating record by _id from: " + Source.getString(profileSwitch.source) + " " + old.toString());
                        scheduleProfileSwitchChange();
                        return true;
                    }
                }
            }
            // look for already added percentage from NS
            profileSwitch.profileName = PercentageSplitter.pureName(profileSwitch.profileName);
            getDaoProfileSwitch().create(profileSwitch);
            if (L.isEnabled(L.DATABASE))
                log.debug("PROFILESWITCH: New record from: " + Source.getString(profileSwitch.source) + " " + profileSwitch.toString());
            scheduleProfileSwitchChange();
            return true;
        }
        if (profileSwitch.source == Source.USER) {
            getDaoProfileSwitch().create(profileSwitch);
            if (L.isEnabled(L.DATABASE))
                log.debug("PROFILESWITCH: New record from: " + Source.getString(profileSwitch.source) + " " + profileSwitch.toString());
            scheduleProfileSwitchChange();
            return true;
        }
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return false;
}