com.j256.ormlite.stmt.Where Java Examples

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<BgReading> getAllBgreadingsDataFromTime(long mills, 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.ge("date", mills);
        PreparedQuery<BgReading> preparedQuery = queryBuilder.prepare();
        bgReadings = daoBgreadings.query(preparedQuery);
        return bgReadings;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<BgReading>();
}
 

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

private CloseableIterator<Location> iterator() throws SQLException {
	Dao<Location, Long> dao = DaoStore.getInstance(context).getLocationDao();
	QueryBuilder<Location, Long> query = dao.queryBuilder();
	Where<? extends Temporal, Long> where = query.where().ge("timestamp", locationCollection.getLatestDate());
	User currentUser = null;
	try {
		currentUser = UserHelper.getInstance(context.getApplicationContext()).readCurrentUser();
	} catch (UserException e) {
		e.printStackTrace();
	}
	if (currentUser != null) {
		where.and().ne("user_id", currentUser.getId()).and().eq("event_id", currentUser.getUserLocal().getCurrentEvent().getId());
	}
	if (filter != null) {
		filter.and(where);
	}
	query.orderBy("timestamp", false);


	return dao.iterator(query.prepare());
}
 

private QueryBuilder<Location, Long> getQuery(User user) throws SQLException {
	Dao<Location, Long> dao = DaoStore.getInstance(context).getLocationDao();
	QueryBuilder<Location, Long> query = dao.queryBuilder();

	Where<? extends Temporal, Long> where = query.where();
	if (user != null) {
		where.eq("user_id", user.getId()).and().eq("event_id", user.getUserLocal().getCurrentEvent().getId());
	}

	if (filter != null) {
		filter.and(where);
	}

	query.orderBy("timestamp", false);

	return query;
}
 

private CloseableIterator<Observation> iterator() throws SQLException {
    Dao<Observation, Long> dao = DaoStore.getInstance(context).getObservationDao();
    QueryBuilder<Observation, Long> query = dao.queryBuilder();
    Where<Observation, Long> where = query
            .orderBy("timestamp", false)
            .where()
            .ge("last_modified", observationCollection.getLatestDate())
            .and()
            .eq("event_id", currentEventId);

    for (Filter filter : filters) {
        QueryBuilder<?, ?> filterQuery = filter.query();
        if (filterQuery != null) {
            query.join(filterQuery);
        }

        filter.and(where);
    }

    return dao.iterator(query.prepare());
}
 

public DialogNotification getLastDialogNotificationByDialogId(List<Long> dialogOccupantsList) {
    DialogNotification dialogNotification = null;

    try {
        QueryBuilder<DialogNotification, Long> queryBuilder = dao.queryBuilder();
        Where<DialogNotification, Long> where = queryBuilder.where();
        where.in(DialogOccupant.Column.ID, dialogOccupantsList);
        queryBuilder.orderBy(DialogNotification.Column.CREATED_DATE, false);
        PreparedQuery<DialogNotification> preparedQuery = queryBuilder.prepare();
        dialogNotification = dao.queryForFirst(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return dialogNotification;
}
 

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

/**
 * finds treatment by its NS Id.
 *
 * @param _id
 * @return
 */
@Nullable
public Treatment findByNSId(String _id) {
    try {
        Dao<Treatment, Long> daoTreatments = getDao();
        QueryBuilder<Treatment, Long> queryBuilder = daoTreatments.queryBuilder();
        Where where = queryBuilder.where();
        where.eq("_id", _id);
        queryBuilder.limit(10L);
        PreparedQuery<Treatment> preparedQuery = queryBuilder.prepare();
        List<Treatment> trList = daoTreatments.query(preparedQuery);
        if (trList.size() != 1) {
            //log.debug("Treatment findTreatmentById query size: " + trList.size());
            return null;
        } else {
            //log.debug("Treatment findTreatmentById found: " + trList.get(0).log());
            return trList.get(0);
        }
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return null;
}
 

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

public List<Treatment> getTreatmentDataFromTime(long from, long to, 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.between("date", from, to);
        PreparedQuery<Treatment> preparedQuery = queryBuilder.prepare();
        treatments = daoTreatments.query(preparedQuery);
        return treatments;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<>();
}
 

public Message getLastMessageByDialogId(List<Long> dialogOccupantsList) {
    Message message = null;

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

    return message;
}
 

public List<TempTarget> getTemptargetsDataFromTime(long mills, 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.ge("date", mills);
        PreparedQuery<TempTarget> preparedQuery = queryBuilder.prepare();
        tempTargets = daoTempTargets.query(preparedQuery);
        return tempTargets;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<TempTarget>();
}
 

@SuppressWarnings("unchecked")
@Test
public void testUseOfOrMany() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);
	assertEquals(0, dao.countOf());
	Foo foo = new Foo();
	int id = 1;
	foo.id = id;
	int val = 1231231;
	foo.val = val;
	assertEquals(1, dao.create(foo));
	int notId = id + 1;
	foo.id = notId;
	foo.val = val + 1;
	assertEquals(1, dao.create(foo));

	Where<Foo, Integer> where = dao.queryBuilder().where();
	where.or(where.eq(Foo.ID_FIELD_NAME, id), where.eq(Foo.ID_FIELD_NAME, notId),
			where.eq(Foo.VAL_FIELD_NAME, val + 1), where.eq(Foo.VAL_FIELD_NAME, val + 1));

	List<Foo> results = where.query();
	assertEquals(2, results.size());
	assertEquals(id, results.get(0).id);
	assertEquals(notId, results.get(1).id);
}
 

public TempTarget findTempTargetById(String _id) {
    try {
        QueryBuilder<TempTarget, Long> queryBuilder = getDaoTempTargets().queryBuilder();
        Where where = queryBuilder.where();
        where.eq("_id", _id);
        PreparedQuery<TempTarget> preparedQuery = queryBuilder.prepare();
        List<TempTarget> list = getDaoTempTargets().query(preparedQuery);

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

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 void updateDanaRHistoryRecordId(JSONObject trJson) {
    try {
        QueryBuilder<DanaRHistoryRecord, String> queryBuilder = getDaoDanaRHistory().queryBuilder();
        Where where = queryBuilder.where();
        where.ge("bytes", trJson.get(DanaRNSHistorySync.DANARSIGNATURE));
        PreparedQuery<DanaRHistoryRecord> preparedQuery = queryBuilder.prepare();
        List<DanaRHistoryRecord> list = getDaoDanaRHistory().query(preparedQuery);
        if (list.size() == 0) {
            // Record does not exists. Ignore
        } else if (list.size() == 1) {
            DanaRHistoryRecord record = list.get(0);
            if (record._id == null || !record._id.equals(trJson.getString("_id"))) {
                if (L.isEnabled(L.DATABASE))
                    log.debug("Updating _id in DanaR history database: " + trJson.getString("_id"));
                record._id = trJson.getString("_id");
                getDaoDanaRHistory().update(record);
            } else {
                // already set
            }
        }
    } catch (SQLException | JSONException e) {
        log.error("Unhandled exception: " + trJson.toString(), e);
    }
}
 

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

public TemporaryBasal findTempBasalByPumpId(Long pumpId) {
    try {
        QueryBuilder<TemporaryBasal, Long> queryBuilder = null;
        queryBuilder = getDaoTemporaryBasal().queryBuilder();
        queryBuilder.orderBy("date", false);
        Where where = queryBuilder.where();
        where.eq("pumpId", pumpId);
        PreparedQuery<TemporaryBasal> preparedQuery = queryBuilder.prepare();
        List<TemporaryBasal> list = getDaoTemporaryBasal().query(preparedQuery);

        if (list.size() > 0)
            return list.get(0);
        else
            return null;

    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return null;
}
 

public DialogNotification getDialogNotificationByDialogId(boolean firstMessage, List<Long> dialogOccupantsList) {
    DialogNotification dialogNotification = null;

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

    return dialogNotification;
}
 

@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<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(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<CareportalEvent> getCareportalEventsFromTime(long mills, String type, 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().eq("eventType", type).and().isNotNull("json");
        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<>();
}
 

public List<DialogOccupant> getActualDialogOccupantsByIds(String dialogId, List<Integer> userIdsList) {
    List<DialogOccupant> dialogOccupantsList = Collections.emptyList();

    try {
        QueryBuilder<DialogOccupant, Long> queryBuilder = dao.queryBuilder();
        Where<DialogOccupant, Long> where = queryBuilder.where();
        where.and(
                where.in(QMUserColumns.ID, userIdsList),
                where.eq(DialogOccupant.Column.STATUS, DialogOccupant.Status.ACTUAL),
                where.eq(Dialog.Column.ID, dialogId)
        );
        PreparedQuery<DialogOccupant> preparedQuery = queryBuilder.prepare();
        dialogOccupantsList = dao.query(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return dialogOccupantsList;
}
 

public List<DialogOccupant> getActualDialogOccupantsByDialog(String dialogId) {
    List<DialogOccupant> dialogOccupantsList = Collections.emptyList();

    try {
        QueryBuilder<DialogOccupant, Long> queryBuilder = dao.queryBuilder();
        Where<DialogOccupant, Long> where = queryBuilder.where();
        where.and(
                where.eq(DialogOccupant.Column.STATUS, DialogOccupant.Status.ACTUAL),
                where.eq(Dialog.Column.ID, dialogId)
        );
        PreparedQuery<DialogOccupant> preparedQuery = queryBuilder.prepare();
        dialogOccupantsList = dao.query(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return dialogOccupantsList;
}
 

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