Java Code Examples for com.j256.ormlite.stmt.QueryBuilder

public ArrayList<Pin> getPinsBeforeMaxId(long maxId) throws SQLException {
    ArrayList<Pin> result = new ArrayList<Pin>();

    QueryBuilder<Pin, Integer> queryBuilder = getPinsDAO().queryBuilder()
            .orderBy(DatabaseHelper.FIELD_ID, false)
            .limit(Huaban.PAGE_SIZE);

    PreparedQuery<Pin> query;
    if (maxId > 0) {
        query = queryBuilder
                .where()
                .lt(DatabaseHelper.FIELD_ID, maxId)
                .prepare();
    } else {
        query = queryBuilder.prepare();
    }

    result.addAll(getPinsDAO().query(query));
    return result;
}
 

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

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

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<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 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 static void addNew(Manager manager,
    AlertPredicate alertType, DBProcessedAudit audit, DBIdentity userToAlert, long minTimestampMs) throws SQLException {
  // These must be unique on (secretId, userId, alertType)  
  // e.g. If I share, then unshare, then re-share something 
  // with a user, only one of these events should ever be added.
  
  QueryBuilder<DBFutureAlert,Integer> builder = manager.futureAlertDao.queryBuilder();
  builder.where().eq(INACTIVE, false)
      .and().eq(ALERT_TYPE, new SelectArg(alertType))
      .and().eq(USER_ID_TO_ALERT, userToAlert.getId());
  
  builder.setCountOf(true);
  if (manager.futureAlertDao.countOf(builder.prepare()) > 0) {
    logger.info("Trying to add new alert for {}, {}. Existing alert already exists. Ignoring",
        alertType, userToAlert.getId());
  } else {
    logger.info("Inserting future alert for {}, {}.",
        alertType, userToAlert.getId());
    DBFutureAlert a = new DBFutureAlert();
    a.userIdToAlert = userToAlert.getId();
    a.transactionId = audit.getTransactionId();
    a.enqueueTimestampMs = minTimestampMs;
    a.alertType = alertType;
    manager.futureAlertDao.create(a);
  }
}
 

/**
 * Query by extension name, table name, and column name
 * 
 * @param extensionName
 *            extension name
 * @param tableName
 *            table name
 * @param columnName
 *            column name
 * @return extensions
 * @throws SQLException
 *             upon failure
 */
public Extensions queryByExtension(String extensionName, String tableName,
		String columnName) throws SQLException {

	QueryBuilder<Extensions, Void> qb = queryBuilder();

	setUniqueWhere(qb.where(), extensionName, true, tableName, true,
			columnName);

	List<Extensions> extensions = qb.query();

	Extensions extension = null;
	if (extensions.size() > 1) {
		throw new GeoPackageException("More than one "
				+ Extensions.class.getSimpleName()
				+ " existed for unique combination of Extension Name: "
				+ extensionName + ", Table Name: " + tableName
				+ ", Column Name: " + columnName);
	} else if (extensions.size() == 1) {
		extension = extensions.get(0);
	}

	return extension;
}
 

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

@Test
public void testPrepareStatementUpdateValueString() throws Exception {
	Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
	Foo foo = new Foo();
	String stuff = "dqedqdq";
	foo.stuff = stuff;
	assertEquals(1, fooDao.create(foo));

	QueryBuilder<Foo, Integer> stmtb = fooDao.queryBuilder();
	stmtb.where().eq(Foo.STUFF_FIELD_NAME, stuff);
	List<Foo> results = fooDao.query(stmtb.prepare());
	assertEquals(1, results.size());

	UpdateBuilder<Foo, Integer> updateb = fooDao.updateBuilder();
	String newStuff = "fepojefpjo";
	updateb.updateColumnValue(Foo.STUFF_FIELD_NAME, newStuff);
	assertEquals(1, fooDao.update(updateb.prepare()));

	results = fooDao.query(stmtb.prepare());
	assertEquals(0, results.size());
}
 

public List<Message> getMessagesByDialogId(String dialogId) {
    List<Message> messagesList = new ArrayList<>();

    try {
        QueryBuilder<Message, 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<Message> preparedQuery = messageQueryBuilder.prepare();
        messagesList = dao.query(preparedQuery);
    } catch (SQLException e) {
        ErrorUtils.logError(e);
    }

    return messagesList;
}
 

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

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

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

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

public List<UserVideo> getUserVideos(User user, List<Video> videos) {
	if (user == null) {
		return null;
	}
	List<UserVideo> result = null;
	List<String> ids = new ArrayList<String>(videos.size());
	for (Video v : videos) {
		ids.add(v.getReadable_id());
	}
	try {
		userVideoDao = getUserVideoDao();
		QueryBuilder<UserVideo, Integer> q = userVideoDao.queryBuilder();
		q.where().in("video_id", ids);
		q.where().eq("user_id", user.getNickname());
		result = userVideoDao.query(q.prepare());
	} catch (SQLException e) {
		e.printStackTrace();
		result = new ArrayList<UserVideo>();
	}
	return result;
}
 

/**
 * 根据手机号模糊查询匹配的用户数据
 *
 * @param phone 手机号
 * @return 模糊匹配结果
 */
public List<UserBean> queryUsersLikePhone(String phone)
{
    List<UserBean> resultList = null;
    try
    {
        QueryBuilder<UserBean, Integer> queryBuilder = getDao().queryBuilder();
        queryBuilder.orderBy(UserDbConfig.FIRST_CHAR, true).orderBy(UserDbConfig.FULL_SPELL, true);
        queryBuilder.where().like(UserDbConfig.PHONE, "%" + phone + "%");
        resultList = getDao().query(queryBuilder.prepare());
    } catch (SQLException e)
    {
        KLog.e(TAG + " UserDao.queryUsersLikePhone fail : " + e.toString());
    }
    return resultList;
}
 

/**
 * 返回最高得分
 * 
 * @return
 */
public GameRanking getTopScore()
{
	QueryBuilder<GameRanking, ?> qb = getDao().queryBuilder();
	try
	{
		// qb.where().eq("user_name", userName);
		qb.orderBy("score", false);
		return qb.queryForFirst();
	}
	catch (SQLException e)
	{
		e.printStackTrace();
	}

	return null;
}
 

/**
 * 根据模块返回搜索结果
 * 
 * @param module
 * @return
 */
public List<FocusItemInfo> queryByModule(String module)
{
	List<FocusItemInfo> koItemInfoList = null;
	try
	{
		QueryBuilder<FocusItemInfo, Long> queryBuilder = mFocusItemInfoDao.queryBuilder();
		koItemInfoList = queryBuilder.where().eq("module", module).query();
	}
	catch (SQLException e)
	{
		e.printStackTrace();
	}
	
	return koItemInfoList;
}
 

@Test
public void testCountOfPrepared() throws Exception {
	Dao<Foo, String> dao = createDao(Foo.class, true);
	assertEquals(0, dao.countOf());
	Foo foo = new Foo();
	int id1 = 1;
	foo.id = id1;
	assertEquals(1, dao.create(foo));
	foo.id = 2;
	assertEquals(1, dao.create(foo));
	assertEquals(2, dao.countOf());

	QueryBuilder<Foo, String> qb = dao.queryBuilder();
	qb.setCountOf(true).where().eq(Foo.ID_FIELD_NAME, id1);
	assertEquals(1, dao.countOf(qb.prepare()));
}
 

@Override
public void onDataSetChanged() {
    Log.i("ZULIP_WIDGET", "onDataSetChanged() = Data reloaded");
    QueryBuilder<Message, Object> queryBuilder = ZulipApp.get().getDao(Message.class).queryBuilder();
    String filter;
    filter = setupWhere();
    if (!filter.equals("")) {
        queryBuilder.where().raw(filter);
    }

    try {
        messageList = queryBuilder.query();
    } catch (SQLException e) {
        ZLog.logException(e);
    }
}
 

@Override
public List<T> getAllSorted(String sortedColumn, boolean ascending) {
    List<T> objectsList = Collections.emptyList();

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

    return objectsList;
}