Java Code Examples for com.j256.ormlite.stmt.Where#eq()

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

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

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

/**
 * Set the unique column criteria in the where clause
 * 
 * @param where
 *            where clause
 * @param extensionName
 *            extension name
 * @param queryTableName
 *            query table name
 * @param tableName
 *            table name
 * @param queryColumnName
 *            query column name
 * @param columnName
 *            column name
 * @throws SQLException
 */
private void setUniqueWhere(Where<Extensions, Void> where,
		String extensionName, boolean queryTableName, String tableName,
		boolean queryColumnName, String columnName) throws SQLException {

	where.eq(Extensions.COLUMN_EXTENSION_NAME, extensionName);

	if (queryTableName) {
		if (tableName == null) {
			where.and().isNull(Extensions.COLUMN_TABLE_NAME);
		} else {
			where.and().eq(Extensions.COLUMN_TABLE_NAME, tableName);
		}
	}

	if (queryColumnName) {
		if (columnName == null) {
			where.and().isNull(Extensions.COLUMN_COLUMN_NAME);
		} else {
			where.and().eq(Extensions.COLUMN_COLUMN_NAME, columnName);
		}
	}

}
 

@Test
public void testUseOfOrInt() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);
	assertEquals(0, dao.countOf());
	Foo foo1 = new Foo();
	int val = 1231231;
	foo1.val = val;
	assertEquals(1, dao.create(foo1));
	Foo foo2 = new Foo();
	foo2.val = val + 1;
	assertEquals(1, dao.create(foo2));

	Where<Foo, Integer> where = dao.queryBuilder().where();
	where.eq(Foo.ID_COLUMN_NAME, foo1.id);
	where.eq(Foo.ID_COLUMN_NAME, foo2.id);
	where.eq(Foo.VAL_COLUMN_NAME, val);
	where.eq(Foo.VAL_COLUMN_NAME, val + 1);
	where.or(4);

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

public ExtendedBolus findExtendedBolusById(String _id) {
    try {
        QueryBuilder<ExtendedBolus, Long> queryBuilder = null;
        queryBuilder = getDaoExtendedBolus().queryBuilder();
        Where where = queryBuilder.where();
        where.eq("_id", _id);
        PreparedQuery<ExtendedBolus> preparedQuery = queryBuilder.prepare();
        List<ExtendedBolus> list = getDaoExtendedBolus().query(preparedQuery);

        if (list.size() == 1) {
            return list.get(0);
        } else {
            return null;
        }
    } 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;
}
 

/**
 * Deletes marks in DB that doesn't exist on API
 *
 * @param
 */
private void deleteExpiredListeDesElementsEvaluation(String id) {
    DatabaseHelper dbHelper = new DatabaseHelper(context);

    try {

        Dao<ListeDesElementsEvaluation, String> listeDesElementsEvaluationDao = dbHelper.getDao(ListeDesElementsEvaluation.class);
        ListeDesElementsEvaluation listeDesElementsEvaluation = listeDesElementsEvaluationDao.queryForId(id);

        if (listeDesElementsEvaluation != null) {
            Dao<ElementEvaluation, String> elementsEvaluationDao = dbHelper.getDao(ElementEvaluation.class);
            DeleteBuilder<ElementEvaluation, String> deleteBuilder = elementsEvaluationDao.deleteBuilder();

            Where where = deleteBuilder.where();
            where.eq("listeDesElementsEvaluation_id", listeDesElementsEvaluation);

            deleteBuilder.delete();
        }

        listeDesElementsEvaluationDao.deleteById(id);


    } catch (SQLException e) {
        e.printStackTrace();
    }
}
 

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

@Test
public void testUseOfOrInt() 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.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);
	where.or(4);

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

@Override
public Where<Message, Object> modWhere(Where<Message, Object> where)
        throws SQLException {

    // see if recipient matches any items in comma delimited string
    where.eq(Message.TYPE_FIELD, MessageType.PRIVATE_MESSAGE);

    return where;
}
 

/**
 * Set the foreign key column criteria in the where clause
 * 
 * @param where
 *            where clause
 * @param fileId
 *            file id
 * @param parentId
 *            parent id
 * @throws SQLException
 */
private void setFkWhere(Where<MetadataReference, Void> where, long fileId,
		Long parentId) throws SQLException {

	where.eq(MetadataReference.COLUMN_FILE_ID, fileId);
	if (parentId == null) {
		where.and().isNull(MetadataReference.COLUMN_PARENT_ID);
	} else {
		where.and().eq(MetadataReference.COLUMN_PARENT_ID, parentId);
	}

}
 

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

public boolean createOrUpdate(TempTarget tempTarget) {
    try {
        TempTarget old;
        tempTarget.date = roundDateToSec(tempTarget.date);

        if (tempTarget.source == Source.NIGHTSCOUT) {
            old = getDaoTempTargets().queryForId(tempTarget.date);
            if (old != null) {
                if (!old.isEqual(tempTarget)) {
                    getDaoTempTargets().delete(old); // need to delete/create because date may change too
                    old.copyFrom(tempTarget);
                    getDaoTempTargets().create(old);
                    if (L.isEnabled(L.DATABASE))
                        log.debug("TEMPTARGET: Updating record by date from: " + Source.getString(tempTarget.source) + " " + old.toString());
                    scheduleTemporaryTargetChange();
                    return true;
                }
                return false;
            }
            // find by NS _id
            if (tempTarget._id != null) {
                QueryBuilder<TempTarget, Long> queryBuilder = getDaoTempTargets().queryBuilder();
                Where where = queryBuilder.where();
                where.eq("_id", tempTarget._id);
                PreparedQuery<TempTarget> preparedQuery = queryBuilder.prepare();
                List<TempTarget> trList = getDaoTempTargets().query(preparedQuery);
                if (trList.size() > 0) {
                    old = trList.get(0);
                    if (!old.isEqual(tempTarget)) {
                        getDaoTempTargets().delete(old); // need to delete/create because date may change too
                        old.copyFrom(tempTarget);
                        getDaoTempTargets().create(old);
                        if (L.isEnabled(L.DATABASE))
                            log.debug("TEMPTARGET: Updating record by _id from: " + Source.getString(tempTarget.source) + " " + old.toString());
                        scheduleTemporaryTargetChange();
                        return true;
                    }
                }
            }
            getDaoTempTargets().create(tempTarget);
            if (L.isEnabled(L.DATABASE))
                log.debug("TEMPTARGET: New record from: " + Source.getString(tempTarget.source) + " " + tempTarget.toString());
            scheduleTemporaryTargetChange();
            return true;
        }
        if (tempTarget.source == Source.USER) {
            getDaoTempTargets().create(tempTarget);
            if (L.isEnabled(L.DATABASE))
                log.debug("TEMPTARGET: New record from: " + Source.getString(tempTarget.source) + " " + tempTarget.toString());
            scheduleTemporaryTargetChange();
            return true;
        }
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return false;
}
 

@Override
public Where<Message, Object> modWhere(Where<Message, Object> where)
        throws SQLException {
    where.eq(Message.MESSAGE_STAR_FIELD, new SelectArg(isStarred));
    return where;
}
 

public static List<OrgNode> getDirtyNodes(OrgFile file) throws SQLException {
    Where<OrgNode, Integer> builder = queryBuilder().orderBy(OrgNode.LINENUMBER_FIELD_NAME, false).where();
    builder.eq(OrgNode.FILE_FIELD_NAME, file);
    builder.and().ne(OrgNode.STATE_FIELD_NAME, OrgNode.State.Clean);
    return builder.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();

}
 

/**
 * 排序查询指定条件下，大于指定值的所有记录
 *
 * @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();
}