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

public List<ProfileSwitch> getProfileSwitchEventsFromTime(long mills, 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", mills);
        PreparedQuery<ProfileSwitch> preparedQuery = queryBuilder.prepare();
        profileSwitches = daoProfileSwitch.query(preparedQuery);
        return profileSwitches;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<>();
}
 

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<Friend> getAllSorted() {
    List<Friend> friendsList = Collections.emptyList();

    try {
        QueryBuilder<Friend, Long> friendQueryBuilder = dao.queryBuilder();

        QueryBuilder<QMUser, Long> userQueryBuilder = userDao.queryBuilder();
        userQueryBuilder.orderByRaw(QMUserColumns.FULL_NAME + " COLLATE NOCASE");

        friendQueryBuilder.join(userQueryBuilder);

        PreparedQuery<Friend> preparedQuery = friendQueryBuilder.prepare();

        friendsList = dao.query(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return friendsList;
}
 

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

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

private void prepareSearchByTitleQuery() {
    try {
        QueryBuilder<PhotoEntity, Integer> queryBuilder = mPhotosDao.queryBuilder();
        mSearchByTitleQuerySelectArg = new SelectArg();
        queryBuilder.where().like(PhotoEntity.Fields.TITLE, mSearchByTitleQuerySelectArg);
        mSearchByTitleQuery = queryBuilder.prepare();
    } catch (SQLException e) {
        Log.wtf(LOG_TAG, "Preparing of SearchByTitleQuery failed", e);
    }
}
 

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

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

    return objectsList;
}
 

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

public Dialog getByRoomJid(String roomJid) {
    Dialog dialog = null;

    try {
        QueryBuilder<Dialog, Long> queryBuilder = dao.queryBuilder();
        queryBuilder.where().eq(Dialog.Column.ROOM_JID, roomJid);
        PreparedQuery<Dialog> preparedQuery = queryBuilder.prepare();
        dialog = dao.queryForFirst(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return dialog;
}
 

public List<TemporaryBasal> getTemporaryBasalsDataFromTime(long from, long to, boolean ascending) {
    try {
        List<TemporaryBasal> tempbasals;
        QueryBuilder<TemporaryBasal, Long> queryBuilder = getDaoTemporaryBasal().queryBuilder();
        queryBuilder.orderBy("date", ascending);
        Where where = queryBuilder.where();
        where.between("date", from, to);
        PreparedQuery<TemporaryBasal> preparedQuery = queryBuilder.prepare();
        tempbasals = getDaoTemporaryBasal().query(preparedQuery);
        return tempbasals;
    } catch (SQLException e) {
        log.error("Unhandled exception", e);
    }
    return new ArrayList<TemporaryBasal>();
}
 

/**
 * Get the relations to the table, both base table and related table
 * 
 * @param table
 *            table name
 * @return extended relations
 * @throws SQLException
 *             upon failure
 */
public List<ExtendedRelation> getTableRelations(String table)
		throws SQLException {

	QueryBuilder<ExtendedRelation, Long> qb = queryBuilder();
	qb.where().like(ExtendedRelation.COLUMN_BASE_TABLE_NAME, table).or()
			.like(ExtendedRelation.COLUMN_RELATED_TABLE_NAME, table);
	PreparedQuery<ExtendedRelation> preparedQuery = qb.prepare();

	return query(preparedQuery);
}
 

public Attachment getByAttachmentType(Attachment.Type type) {
    Attachment attachment = null;

    try {
        QueryBuilder<Attachment, Long> queryBuilder = dao.queryBuilder();
        queryBuilder.where().eq(Attachment.Column.ID, type);
        PreparedQuery<Attachment> preparedQuery = queryBuilder.prepare();
        attachment = dao.queryForFirst(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return attachment;
}
 

public List<DialogOccupant> getDialogOccupantsListByDialogId(String dialogId) {
    List<DialogOccupant> dialogOccupantsList = null;

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

    return dialogOccupantsList;
}
 

public QMUser getUserRequestById(int userId) {
    UserRequest userRequest = null;

    try {
        QueryBuilder<UserRequest, Long> queryBuilder = dao.queryBuilder();
        queryBuilder.where().eq(QMUserColumns.ID, userId);
        PreparedQuery<UserRequest> preparedQuery = queryBuilder.prepare();
        userRequest = dao.queryForFirst(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return userRequest != null ? userRequest.getUser() : null;
}
 

public List<T> getSkippedSorted(long startRow, long perPage, String sortedColumn, boolean ascending) {
    List<T> objectsList = Collections.emptyList();

    try {
        QueryBuilder<T, Long> queryBuilder = dao.queryBuilder();
        queryBuilder.offset(startRow).limit(perPage);
        queryBuilder.orderBy(sortedColumn, ascending);
        PreparedQuery<T> preparedQuery = queryBuilder.prepare();
        objectsList = dao.query(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return objectsList;
}
 

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

/**
 * {@inheritDoc}
 */
@Override
public TileDao getTileDao(TileMatrixSet tileMatrixSet) {

	if (tileMatrixSet == null) {
		throw new GeoPackageException(
				"Non null " + TileMatrixSet.class.getSimpleName()
						+ " is required to create "
						+ TileDao.class.getSimpleName());
	}

	// Get the Tile Matrix collection, order by zoom level ascending & pixel
	// size descending per requirement 51
	List<TileMatrix> tileMatrices;
	try {
		TileMatrixDao tileMatrixDao = getTileMatrixDao();
		QueryBuilder<TileMatrix, TileMatrixKey> qb = tileMatrixDao
				.queryBuilder();
		qb.where().eq(TileMatrix.COLUMN_TABLE_NAME,
				tileMatrixSet.getTableName());
		qb.orderBy(TileMatrix.COLUMN_ZOOM_LEVEL, true);
		qb.orderBy(TileMatrix.COLUMN_PIXEL_X_SIZE, false);
		qb.orderBy(TileMatrix.COLUMN_PIXEL_Y_SIZE, false);
		PreparedQuery<TileMatrix> query = qb.prepare();
		tileMatrices = tileMatrixDao.query(query);
	} catch (SQLException e) {
		throw new GeoPackageException(
				"Failed to retrieve " + TileDao.class.getSimpleName()
						+ " for table name: " + tileMatrixSet.getTableName()
						+ ". Exception retrieving "
						+ TileMatrix.class.getSimpleName() + " collection.",
				e);
	}

	// Read the existing table and create the dao
	TileTableReader tableReader = new TileTableReader(
			tileMatrixSet.getTableName());
	final TileTable tileTable = tableReader.readTable(database);
	tileTable.setContents(tileMatrixSet.getContents());
	TileDao dao = new TileDao(getName(), database, tileMatrixSet,
			tileMatrices, tileTable);

	return dao;
}
 

private PreparedQuery<Observation> buildQuery(Dao<Observation, Long> oDao, int filterId) throws SQLException {
	QueryBuilder<Observation, Long> qb = oDao.queryBuilder();
	Calendar c = Calendar.getInstance();
	List<String> filters = new ArrayList<>();
	String footerText = "All observations have been returned";

	if (filterId == getResources().getInteger(R.integer.time_filter_last_month)) {
		filters.add("Last Month");
		footerText = "End of results for Last Month filter";
		c.add(Calendar.MONTH, -1);
	} else if (filterId == getResources().getInteger(R.integer.time_filter_last_week)) {
		filters.add("Last Week");
		footerText = "End of results for Last Week filter";
		c.add(Calendar.DAY_OF_MONTH, -7);
	} else if (filterId == getResources().getInteger(R.integer.time_filter_last_24_hours)) {
		filters.add("Last 24 Hours");
		footerText = "End of results for Last 24 Hours filter";
		c.add(Calendar.HOUR, -24);
	} else if (filterId == getResources().getInteger(R.integer.time_filter_today)) {
		filters.add("Since Midnight");
		footerText = "End of results for Today filter";
		c.set(Calendar.HOUR_OF_DAY, 0);
		c.set(Calendar.MINUTE, 0);
		c.set(Calendar.SECOND, 0);
		c.set(Calendar.MILLISECOND, 0);
	}  else if (filterId == getResources().getInteger(R.integer.time_filter_custom)) {
		String customFilterTimeUnit = getCustomTimeUnit();
		int customTimeNumber = getCustomTimeNumber();

		filters.add("Last " + customTimeNumber + " " + customFilterTimeUnit);
		footerText = "End of results for custom filter";
		switch (customFilterTimeUnit) {
			case "Hours":
				c.add(Calendar.HOUR, -1 * customTimeNumber);
				break;
			case "Days":
				c.add(Calendar.DAY_OF_MONTH, -1 * customTimeNumber);
				break;
			case "Months":
				c.add(Calendar.MONTH, -1 * customTimeNumber);
				break;
			default:
				c.add(Calendar.MINUTE, -1 * customTimeNumber);
				break;
		}

	} else {
		// no filter
		c.setTime(new Date(0));
	}

	requeryTime = c.getTimeInMillis();
	adapter.setFooterText(footerText);
	qb.where()
		.ne("state", State.ARCHIVE)
		.and()
		.ge("timestamp", c.getTime())
		.and()
		.eq("event_id", EventHelper.getInstance(context).getCurrentEvent().getId());

	List<String> actionFilters = new ArrayList<>();

	boolean favorites = preferences.getBoolean(getResources().getString(R.string.activeFavoritesFilterKey), false);
	if (favorites && currentUser != null) {
		Dao<ObservationFavorite, Long> observationFavoriteDao = DaoStore.getInstance(context).getObservationFavoriteDao();
		QueryBuilder<ObservationFavorite, Long> favoriteQb = observationFavoriteDao.queryBuilder();
		favoriteQb.where()
			.eq("user_id", currentUser.getRemoteId())
			.and()
			.eq("is_favorite", true);

		qb.join(favoriteQb);

		actionFilters.add("Favorites");
	}

	boolean important = preferences.getBoolean(getResources().getString(R.string.activeImportantFilterKey), false);
	if (important) {
		Dao<ObservationImportant, Long> observationImportantDao = DaoStore.getInstance(context).getObservationImportantDao();
		QueryBuilder<ObservationImportant, Long> importantQb = observationImportantDao.queryBuilder();
		importantQb.where().eq("is_important", true);

		qb.join(importantQb);

		actionFilters.add("Important");
	}

	qb.orderBy("timestamp", false);

	if (!actionFilters.isEmpty()) {
		filters.add(StringUtils.join(actionFilters, " & "));
	}

	((AppCompatActivity) getActivity()).getSupportActionBar().setSubtitle(StringUtils.join(filters, ", "));

	return qb.prepare();
}
 

/**
 * Test delete
 * 
 * @param geoPackage
 * @throws SQLException
 */
public static void testDelete(GeoPackage geoPackage) throws SQLException {

	ExtensionsDao dao = geoPackage.getExtensionsDao();

	if (dao.isTableExists()) {
		List<Extensions> results = dao.queryForAll();

		if (!results.isEmpty()) {

			// Choose random metadata
			int random = (int) (Math.random() * results.size());
			Extensions extensions = results.get(random);

			// Delete the extensions
			dao.delete(extensions);

			// Verify deleted
			Extensions queryExtensions = dao.queryByExtension(
					extensions.getExtensionName(),
					extensions.getTableName(), extensions.getColumnName());
			TestCase.assertNull(queryExtensions);

			// Choose prepared deleted
			results = dao.queryForAll();
			if (!results.isEmpty()) {

				// Choose random metadata
				random = (int) (Math.random() * results.size());
				extensions = results.get(random);

				// Find which metadata to delete
				QueryBuilder<Extensions, Void> qb = dao.queryBuilder();
				qb.where().eq(Extensions.COLUMN_EXTENSION_NAME,
						extensions.getExtensionName());
				PreparedQuery<Extensions> query = qb.prepare();
				List<Extensions> queryResults = dao.query(query);
				int count = queryResults.size();

				// Delete
				DeleteBuilder<Extensions, Void> db = dao.deleteBuilder();
				db.where().eq(Extensions.COLUMN_EXTENSION_NAME,
						extensions.getExtensionName());
				PreparedDelete<Extensions> deleteQuery = db.prepare();
				int deleted = dao.delete(deleteQuery);

				TestCase.assertEquals(count, deleted);

			}
		}
	}
}