com.j256.ormlite.stmt.PreparedQuery Java Examples

@Test
public void testFieldIndex() throws Exception {
	Dao<Index, Integer> dao = createDao(Index.class, true);
	Index index1 = new Index();
	String stuff = "doepqjdpqdq";
	index1.stuff = stuff;
	assertEquals(1, dao.create(index1));
	Index index2 = dao.queryForId(index1.id);
	assertNotNull(index2);
	assertEquals(index1.id, index2.id);
	assertEquals(stuff, index2.stuff);
	// this should work
	assertEquals(1, dao.create(index1));

	PreparedQuery<Index> query = dao.queryBuilder().where().eq("stuff", index1.stuff).prepare();
	List<Index> results = dao.query(query);
	assertNotNull(results);
	assertEquals(2, results.size());
	assertEquals(stuff, results.get(0).stuff);
	assertEquals(stuff, results.get(1).stuff);
}
 

public List<BgReading> getBgreadingsDataFromTime(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).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<>();
}
 

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

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<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<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 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 List<DialogNotification> getDialogNotificationsByDialogId(String dialogId) {
    List<DialogNotification> dialogNotificationsList = new ArrayList<>();

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

        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<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 void deleteCareportalEventById(String _id) {
    try {
        QueryBuilder<CareportalEvent, Long> queryBuilder;
        queryBuilder = getDaoCareportalEvents().queryBuilder();
        Where where = queryBuilder.where();
        where.eq("_id", _id);
        PreparedQuery<CareportalEvent> preparedQuery = queryBuilder.prepare();
        List<CareportalEvent> list = getDaoCareportalEvents().query(preparedQuery);

        if (list.size() == 1) {
            CareportalEvent record = list.get(0);
            if (L.isEnabled(L.DATABASE))
                log.debug("Removing CareportalEvent record from database: " + record.toString());
            delete(record);
        } else {
            if (L.isEnabled(L.DATABASE))
                log.debug("CareportalEvent not found database: " + _id);
        }
    } catch (SQLException e) {
        log.error("Unhandled exception", 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;
}
 

/**
 * 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 DialogOccupant getDialogOccupantForPrivateChat(int userId) {
    DialogOccupant dialogOccupant = null;

    try {
        QueryBuilder<DialogOccupant, Long> dialogOccupantQueryBuilder = dao.queryBuilder();
        dialogOccupantQueryBuilder.where().eq(QMUserColumns.ID, userId);

        QueryBuilder<Dialog, Long> dialogQueryBuilder = dialogDao.queryBuilder();
        dialogQueryBuilder.where().eq(Dialog.Column.TYPE, Dialog.Type.PRIVATE);

        dialogOccupantQueryBuilder.join(dialogQueryBuilder);

        PreparedQuery<DialogOccupant> preparedQuery = dialogOccupantQueryBuilder.prepare();
        dialogOccupant = dao.queryForFirst(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return dialogOccupant;
}
 

private Cursor obtainCursor(PreparedQuery<Observation> query, Dao<Observation, Long> oDao) throws SQLException {
	Cursor c = null;
	CloseableIterator<Observation> iterator = oDao.iterator(query);

	// get the raw results which can be cast under Android
	AndroidDatabaseResults results = (AndroidDatabaseResults) iterator.getRawResults();
	c = results.getRawCursor();
	if (c.moveToLast()) {
		long oldestTime = c.getLong(c.getColumnIndex("last_modified"));
		Log.i(LOG_NAME, "last modified is: " + c.getLong(c.getColumnIndex("last_modified")));
		Log.i(LOG_NAME, "querying again in: " + (oldestTime - requeryTime)/60000 + " minutes");
		if (queryUpdateHandle != null) {
			queryUpdateHandle.cancel(true);
		}
		queryUpdateHandle = scheduler.schedule(new Runnable() {
			public void run() {
				updateFilter();
			}
		}, oldestTime - requeryTime, TimeUnit.MILLISECONDS);
		c.moveToFirst();
	}
	return c;
}
 

/**
 * Build our query for Post objects that match a User.
 */
private PreparedQuery<Post> makePostsForUserQuery() throws SQLException {
	// build our inner query for UserPost objects
	QueryBuilder<UserPost, Integer> userPostQb = userPostDao.queryBuilder();
	// just select the post-id field
	userPostQb.selectColumns(UserPost.POST_ID_FIELD_NAME);
	SelectArg userSelectArg = new SelectArg();
	// you could also just pass in user1 here
	userPostQb.where().eq(UserPost.USER_ID_FIELD_NAME, userSelectArg);

	// build our outer query for Post objects
	QueryBuilder<Post, Integer> postQb = postDao.queryBuilder();
	// where the id matches in the post-id from the inner query
	postQb.where().in(Post.ID_FIELD_NAME, userPostQb);
	return postQb.prepare();
}
 

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

@Override
public Observable<DataType> rxQueryForFirst(final PreparedQuery<DataType> preparedQuery) {
    final Func0<Observable<DataType>> loFunc = () -> {
        try {
            return Observable.just(queryForFirst(preparedQuery));
        } catch (SQLException e) {
            return Observable.error(e);
        }
    };
    return Observable.defer(loFunc);
}
 

@Override
public Observable<List<DataType>> rxQuery(final PreparedQuery<DataType> preparedQuery) {
    final Func0<Observable<List<DataType>>> loFunc = () -> {
        try {
            return Observable.just(query(preparedQuery));
        } catch (SQLException e) {
            return Observable.error(e);
        }
    };
    return Observable.defer(loFunc);
}
 

@Override
public List<T> getByColumn(String column, String value) {
    List<T> objectsList  = Collections.emptyList();

    try {
        QueryBuilder<T, ID> queryBuilder = dao.queryBuilder();
        queryBuilder.where().eq(column, value);
        PreparedQuery<T> preparedQuery = queryBuilder.prepare();
        objectsList= dao.query(preparedQuery);
    } catch (SQLException e) {
        com.quickblox.q_municate_base_cache.utils.ErrorUtils.logError(e);
    }

    return objectsList;
}
 

@Override
public Observable<CloseableIterator<DataType>> rxIterator(final PreparedQuery<DataType> preparedQuery, final int resultFlags) {
    final Func0<Observable<CloseableIterator<DataType>>> loFunc = () -> {
        try {
            return Observable.just(iterator(preparedQuery, resultFlags));
        } catch (SQLException e) {
            return Observable.error(e);
        }
    };
    return Observable.defer(loFunc);
}
 

@Override
public Observable<Long> rxCountOf(final PreparedQuery<DataType> preparedQuery) {
    final Func0<Observable<Long>> loFunc = () -> {
        try {
            return Observable.just(countOf(preparedQuery));
        } catch (SQLException e) {
            return Observable.error(e);
        }
    };
    return Observable.defer(loFunc);
}
 

/**
 * 获取满足指定条件的记录数
 *
 * @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);
}
 

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

@Override
public List<QMUser> getByColumn(String column, Collection<String> values) {
    List<QMUser> usersList  = Collections.emptyList();

    try {
        QueryBuilder<QMUser, Long> queryBuilder = dao.queryBuilder();
        queryBuilder.where().in(column, values);
        PreparedQuery<QMUser> preparedQuery = queryBuilder.prepare();
        usersList = dao.query(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return usersList;
}
 

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

@Test(expected = RuntimeException.class)
public void testCountOfPreparedThrow() throws Exception {
	@SuppressWarnings("unchecked")
	Dao<Foo, String> dao = (Dao<Foo, String>) createMock(Dao.class);
	RuntimeExceptionDao<Foo, String> rtDao = new RuntimeExceptionDao<Foo, String>(dao);
	@SuppressWarnings("unchecked")
	PreparedQuery<Foo> prepared = (PreparedQuery<Foo>) createMock(PreparedQuery.class);
	expect(dao.countOf(prepared)).andThrow(new SQLException("Testing catch"));
	replay(dao);
	rtDao.countOf(prepared);
	verify(dao);
}
 

public List<QMUser> getUsersByIDs(Collection<Integer> idsList) {
    List<QMUser> usersList  = Collections.emptyList();

    try {
        QueryBuilder<QMUser, Long> queryBuilder = dao.queryBuilder();
        queryBuilder.where().in(ID, idsList);
        PreparedQuery<QMUser> preparedQuery = queryBuilder.prepare();
        usersList = dao.query(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return usersList;
}
 

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