com.j256.ormlite.stmt.PreparedQuery Java Examples

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

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

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

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

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

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

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

/**
 * 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 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, 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 getLastMessageWithTempByDialogId(List<Long> dialogOccupantsList) {
    Message message = null;

    try {
        QueryBuilder<Message, Long> queryBuilder = dao.queryBuilder();
        queryBuilder.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;
}
 

/**
 * Delete the TableIndex matching the prepared query, cascading
 * 
 * @param preparedDelete
 *            prepared query
 * @return rows deleted
 * @throws SQLException
 *             upon deletion failure
 */
public int deleteCascade(PreparedQuery<TableIndex> preparedDelete)
		throws SQLException {
	int count = 0;
	if (preparedDelete != null) {
		List<TableIndex> tableIndexList = query(preparedDelete);
		count = deleteCascade(tableIndexList);
	}
	return count;
}
 

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

/**
 * Example of created a query with a ? argument using the {@link SelectArg} object. You then can set the value of
 * this object at a later time.
 */
private void useSelectArgFeature() throws Exception {

	String name1 = "foo";
	String name2 = "bar";
	String name3 = "baz";
	assertEquals(1, accountDao.create(new Account(name1)));
	assertEquals(1, accountDao.create(new Account(name2)));
	assertEquals(1, accountDao.create(new Account(name3)));

	QueryBuilder<Account, Integer> statementBuilder = accountDao.queryBuilder();
	SelectArg selectArg = new SelectArg();
	// build a query with the WHERE clause set to 'name = ?'
	statementBuilder.where().like(Account.NAME_FIELD_NAME, selectArg);
	PreparedQuery<Account> preparedQuery = statementBuilder.prepare();

	// now we can set the select arg (?) and run the query
	selectArg.setValue(name1);
	List<Account> results = accountDao.query(preparedQuery);
	assertEquals("Should have found 1 account matching our query", 1, results.size());
	assertEquals(name1, results.get(0).getName());

	selectArg.setValue(name2);
	results = accountDao.query(preparedQuery);
	assertEquals("Should have found 1 account matching our query", 1, results.size());
	assertEquals(name2, results.get(0).getName());

	selectArg.setValue(name3);
	results = accountDao.query(preparedQuery);
	assertEquals("Should have found 1 account matching our query", 1, results.size());
	assertEquals(name3, results.get(0).getName());
}
 

public List<Message> getMessagesByDialogIdAndDate(String dialogId, long createdDate, boolean moreDate){
    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);

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

    return messagesList;
}
 

/**
 * @see Dao#iterator(PreparedQuery, int)
 */
@Override
public CloseableIterator<T> iterator(PreparedQuery<T> preparedQuery, int resultFlags) {
	try {
		return dao.iterator(preparedQuery, resultFlags);
	} catch (SQLException e) {
		logMessage(e, "iterator threw exception on: " + preparedQuery);
		throw new RuntimeException(e);
	}
}
 

/**
 * {@inheritDoc}
 */
@Override
public SpatialReferenceSystem queryForFirst(
		PreparedQuery<SpatialReferenceSystem> preparedQuery)
		throws SQLException {
	SpatialReferenceSystem srs = super.queryForFirst(preparedQuery);
	setDefinition_12_063(srs);
	return srs;
}
 

/**
 * {@inheritDoc}
 */
@Override
public List<SpatialReferenceSystem> query(
		PreparedQuery<SpatialReferenceSystem> preparedQuery)
		throws SQLException {
	List<SpatialReferenceSystem> srsList = super.query(preparedQuery);
	setDefinition_12_063(srsList);
	return srsList;
}
 

public Topic getRootTopic() {
  	Topic topic = null;
try {
	Dao<Topic, String> topicDao = helper.getTopicDao();
	PreparedQuery<Topic> q = topicDao.queryBuilder().where().isNull("parentTopic_id").prepare();
	topic = topicDao.queryForFirst(q);
} catch (SQLException e) {
	e.printStackTrace();
}
return topic;
  }
 

/**
 * @see Dao#iterator(PreparedQuery)
 */
@Override
public CloseableIterator<T> iterator(PreparedQuery<T> preparedQuery) {
	try {
		return dao.iterator(preparedQuery);
	} catch (SQLException e) {
		logMessage(e, "iterator threw exception on: " + preparedQuery);
		throw new RuntimeException(e);
	}
}
 

@Override
public T queryForFirst(PreparedQuery<T> preparedQuery) throws SQLException {
	checkForInitialized();
	DatabaseConnection connection = connectionSource.getReadOnlyConnection(tableInfo.getTableName());
	try {
		return statementExecutor.queryForFirst(connection, preparedQuery, objectCache);
	} finally {
		connectionSource.releaseConnection(connection);
	}
}
 

public List<Friend> getAllByIds(List<Integer> idsList) {
    List<Friend> friendsList = Collections.emptyList();

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

    return friendsList;
}