Java SelectQuery.addOrderBy方法代码示例

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@SuppressWarnings("unchecked")
private SelectQuery addConditions(SelectQuery selectQuery, TransactionQueryForm form) {
    if (form.isTransactionPkSet()) {
        selectQuery.addConditions(TRANSACTION.TRANSACTION_PK.eq(form.getTransactionPk()));
    }

    if (form.isChargeBoxIdSet()) {
        selectQuery.addConditions(CONNECTOR.CHARGE_BOX_ID.eq(form.getChargeBoxId()));
    }

    if (form.isOcppIdTagSet()) {
        selectQuery.addConditions(TRANSACTION.ID_TAG.eq(form.getOcppIdTag()));
    }

    if (form.getType() == TransactionQueryForm.QueryType.ACTIVE) {
        selectQuery.addConditions(TRANSACTION.STOP_TIMESTAMP.isNull());
    }

    processType(selectQuery, form);

    // Default order
    selectQuery.addOrderBy(TRANSACTION.TRANSACTION_PK.desc());

    return selectQuery;
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
private SelectQuery<R> getQuery(Sort sort) {
    SelectQuery<R> query = dsl.selectFrom(table).getQuery();
    // Do not sort if specified sort condition.
    if (sort == null) {
        return query;
    }
    for (Sort.Order order : sort) {
        // It's currently only allowed column name of lowercase.
        Field<?> field = table.field(name(LOWER_CAMEL.to(LOWER_UNDERSCORE, order.getProperty())));
        if (field == null) {
            // TODO Consider later that can't find the field which has sort condition.
            continue;
        }
        SortField<?> sortField;
        if (order.getDirection() == Sort.Direction.ASC) {
            sortField = field.asc();
        } else {
            sortField = field.desc();
        }
        query.addOrderBy(sortField);
    }
    return query;
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
public ImmutableList<SecretSeries> getSecretSeries(@Nullable Long expireMaxTime, Group group) {
  SelectQuery<Record> select = dslContext
      .select().from(SECRETS).join(SECRETS_CONTENT).on(SECRETS.CURRENT.equal(SECRETS_CONTENT.ID))
      .where(SECRETS.CURRENT.isNotNull()).getQuery();

  if (expireMaxTime != null && expireMaxTime > 0) {
    select.addOrderBy(SECRETS_CONTENT.EXPIRY.asc().nullsLast());
    long now = System.currentTimeMillis() / 1000L;
    select.addConditions(SECRETS_CONTENT.EXPIRY.greaterThan(now));
    select.addConditions(SECRETS_CONTENT.EXPIRY.lessOrEqual(expireMaxTime));
  }

  if (group != null) {
    select.addJoin(ACCESSGRANTS, SECRETS.ID.eq(ACCESSGRANTS.SECRETID));
    select.addJoin(GROUPS, GROUPS.ID.eq(ACCESSGRANTS.GROUPID));
    select.addConditions(GROUPS.NAME.eq(group.getName()));
  }
  List<SecretSeries> r = select.fetchInto(SECRETS).map(secretSeriesMapper);
  return ImmutableList.copyOf(r);
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
public ImmutableList<SecretSeries> getSecretSeriesBatched(int idx, int num, boolean newestFirst) {
  SelectQuery<Record> select = dslContext
      .select()
      .from(SECRETS)
      .join(SECRETS_CONTENT)
      .on(SECRETS.CURRENT.equal(SECRETS_CONTENT.ID))
      .where(SECRETS.CURRENT.isNotNull())
      .getQuery();
  if (newestFirst) {
    select.addOrderBy(SECRETS.CREATEDAT.desc());
  } else {
    select.addOrderBy(SECRETS.CREATEDAT.asc());
  }
  select.addLimit(idx, num);

  List<SecretSeries> r = select.fetchInto(SECRETS).map(secretSeriesMapper);
  return ImmutableList.copyOf(r);
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@Override
public List<QuestionView> getQuestions(final QuestionsOptions options) {
    final Integer itemsPerPage = SecurityUtils.getItemsPerPage();
    final SelectQuery<Record> questionSelect = getQuestionSelect(options);

    if (options.isOwnOrPublic()) {
        questionSelect.addConditions(
                QUESTIONS.PRIVATE.eq(false).or(QUESTIONS.USER_ID.eq(getUserId(options.getCurrentUser())))
        );
    }

    if (StringUtils.isNotEmpty(options.getProblemCode())) {
        questionSelect.addConditions(QUESTIONS.TASK_ID.eq(getProblemId(options.getProblemCode())));
    }

    questionSelect.addOrderBy(QUESTIONS.QUESTION_ID.desc());
    questionSelect.addLimit(options.getPage() * itemsPerPage, itemsPerPage);
    return questionSelect.fetch(getQuestionMapper(QuestionContent.WITHOUT_CONTENT));
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
protected void addSort(SchemaFactory schemaFactory, String type, Sort sort, SelectQuery<?> query) {
    if ( sort == null ) {
        return;
    }

    TableField<?, Object> sortField = JooqUtils.getTableField(getMetaDataManager(), type, sort.getName());
    if ( sortField == null ) {
        return;
    }

    switch (sort.getOrderEnum()) {
    case DESC:
        query.addOrderBy(sortField.desc());
    default:
        query.addOrderBy(sortField.asc());
    }
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@Override
public ProblemListView getProblems(@Nonnull final ProblemsOptions problemsOptions) {
    final Integer itemsPerPage = SecurityUtils.getItemsPerPage();

    final SelectQuery<Record> query = jooq.selectQuery();
    query.addSelect(TASKS.TASK_ID, TASKS.NAME, TASKS.CODE);
    query.addFrom(TASKS);

    if (!Strings.isNullOrEmpty(problemsOptions.getQuery())) {
        final String lowerQuery = problemsOptions.getQuery().toLowerCase();
        query.addConditions(TASKS.CODE.lower().contains(lowerQuery)
                                      .or(TASKS.NAME.lower().contains(lowerQuery)));
    }

    if (!Strings.isNullOrEmpty(problemsOptions.getAvailableForGroup())) {
        query.addConditions(TASKS.TASK_ID.notIn(
                jooq.select(CONTESTS_TASKS.TASK_ID)
                    .from(CONTESTS_TASKS)
                    .join(CONTESTS).on(CONTESTS.CONTEST_ID.eq(CONTESTS_TASKS.CONTEST_ID))
                    .where(CONTESTS.CODE.eq(problemsOptions.getAvailableForGroup()))
        ));
    }

    query.addOrderBy(TASKS.CODE, TASKS.NAME);

    query.addLimit(problemsOptions.getPage() * itemsPerPage, itemsPerPage);

    List<ProblemView> problems =
            query.fetch()
                 .map(record -> {
                     final ProblemView problem = new ProblemView();
                     problem.setCode(record.getValue(TASKS.CODE));
                     problem.setName(record.getValue(TASKS.NAME));
                     return problem;
                 });
    return new ProblemListView(problems);
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
protected void addSort(SchemaFactory schemaFactory, String type, Sort sort, SelectQuery<?> query) {
    if (sort != null) {
        TableField<?, Object> sortField = JooqUtils.getTableField(metaDataManager, type, sort.getName());
        if (sortField == null) {
            return;
        }

        switch (sort.getOrderEnum()) {
            case DESC:
                query.addOrderBy(sortField.desc());
                break;
            default:
                query.addOrderBy(sortField.asc());
        }
    }

    TableField<?, Object> idSort = JooqUtils.getTableField(metaDataManager, type, ObjectMetaDataManager.ID_FIELD);
    if (idSort == null) {
        return;
    }

    if (sort != null) {
        switch (sort.getOrderEnum()) {
            case DESC:
                query.addOrderBy(idSort.desc());
                break;
            default:
                query.addOrderBy(idSort.asc());
        }
    }
    else {
        query.addOrderBy(idSort.asc());
    }
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@SuppressWarnings("unchecked")
private Result<Record5<Integer, String, String, String, DateTime>> getOverviewInternal(ChargePointQueryForm form) {
    SelectQuery selectQuery = ctx.selectQuery();
    selectQuery.addFrom(CHARGE_BOX);
    selectQuery.addSelect(
            CHARGE_BOX.CHARGE_BOX_PK,
            CHARGE_BOX.CHARGE_BOX_ID,
            CHARGE_BOX.DESCRIPTION,
            CHARGE_BOX.OCPP_PROTOCOL,
            CHARGE_BOX.LAST_HEARTBEAT_TIMESTAMP
    );

    if (form.isSetOcppVersion()) {

        // http://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html
        selectQuery.addConditions(CHARGE_BOX.OCPP_PROTOCOL.like(form.getOcppVersion().getValue() + "_"));
    }

    if (form.isSetDescription()) {
        selectQuery.addConditions(includes(CHARGE_BOX.DESCRIPTION, form.getDescription()));
    }

    if (form.isSetChargeBoxId()) {
        selectQuery.addConditions(includes(CHARGE_BOX.CHARGE_BOX_ID, form.getChargeBoxId()));
    }

    switch (form.getHeartbeatPeriod()) {
        case ALL:
            break;

        case TODAY:
            selectQuery.addConditions(
                    date(CHARGE_BOX.LAST_HEARTBEAT_TIMESTAMP).eq(date(DateTime.now()))
            );
            break;

        case YESTERDAY:
            selectQuery.addConditions(
                    date(CHARGE_BOX.LAST_HEARTBEAT_TIMESTAMP).eq(date(DateTime.now().minusDays(1)))
            );
            break;

        case EARLIER:
            selectQuery.addConditions(
                    date(CHARGE_BOX.LAST_HEARTBEAT_TIMESTAMP).lessThan(date(DateTime.now().minusDays(1)))
            );
            break;

        default:
            throw new SteveException("Unknown enum type");
    }

    // Default order
    selectQuery.addOrderBy(CHARGE_BOX.CHARGE_BOX_PK.asc());

    return selectQuery.fetch();
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@Override
@SuppressWarnings("unchecked")
public List<Reservation> getReservations(ReservationQueryForm form) {
    SelectQuery selectQuery = ctx.selectQuery();
    selectQuery.addFrom(RESERVATION);
    selectQuery.addJoin(OCPP_TAG, OCPP_TAG.ID_TAG.eq(RESERVATION.ID_TAG));
    selectQuery.addJoin(CONNECTOR, CONNECTOR.CONNECTOR_PK.eq(RESERVATION.CONNECTOR_PK));
    selectQuery.addJoin(CHARGE_BOX, CONNECTOR.CHARGE_BOX_ID.eq(CHARGE_BOX.CHARGE_BOX_ID));

    selectQuery.addSelect(
            RESERVATION.RESERVATION_PK,
            RESERVATION.TRANSACTION_PK,
            OCPP_TAG.OCPP_TAG_PK,
            CHARGE_BOX.CHARGE_BOX_PK,
            OCPP_TAG.ID_TAG,
            CHARGE_BOX.CHARGE_BOX_ID,
            RESERVATION.START_DATETIME,
            RESERVATION.EXPIRY_DATETIME,
            RESERVATION.STATUS,
            CONNECTOR.CONNECTOR_ID
    );

    if (form.isChargeBoxIdSet()) {
        selectQuery.addConditions(CHARGE_BOX.CHARGE_BOX_ID.eq(form.getChargeBoxId()));
    }

    if (form.isOcppIdTagSet()) {
        selectQuery.addConditions(RESERVATION.ID_TAG.eq(form.getOcppIdTag()));
    }

    if (form.isStatusSet()) {
        selectQuery.addConditions(RESERVATION.STATUS.eq(form.getStatus().name()));
    }

    processType(selectQuery, form);

    // Default order
    selectQuery.addOrderBy(RESERVATION.EXPIRY_DATETIME.asc());

    return selectQuery.fetch().map(new ReservationMapper());
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@Override
public List<String> getTags(final String query) {

    final SelectQuery<Record> selectQuery = jooq.selectQuery();

    selectQuery.addSelect(TAGS.NAME);
    selectQuery.addFrom(TAGS);

    if (!Strings.isNullOrEmpty(query)) {
        selectQuery.addConditions(TAGS.NAME.lower().contains(query.toLowerCase()));
    }

    selectQuery.addOrderBy(TAGS.NAME.asc());

    return selectQuery.fetch().map(record -> record.getValue(TAGS.NAME));
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
private SelectQuery<Record> getQuestionSelect(final QuestionsOptions options) {
    final SelectQuery<Record> query = jooq.selectQuery();
    final Field<?> answersCountField = DSL.selectCount()
                                     .from(QUESTIONS_ANSWERS)
                                     .where(QUESTIONS_ANSWERS.QUESTION_ID.eq(QUESTIONS.QUESTION_ID))
                                     .asField("answersCount");

    query.addSelect(USERS.LOGIN,
                    PROFILES.FIRST_NAME,
                    PROFILES.LAST_NAME,
                    PROFILES.NICKNAME,
                    CONTESTS.CODE,
                    TASKS.CODE,
                    QUESTIONS.QUESTION_ID,
                    QUESTIONS.STATUS,
                    QUESTIONS.QUESTION,
                    QUESTIONS.CREATED,
                    QUESTIONS.PRIVATE,
                    answersCountField);
    query.addFrom(QUESTIONS);
    query.addJoin(TASKS, JoinType.LEFT_OUTER_JOIN, TASKS.TASK_ID.eq(QUESTIONS.TASK_ID));
    query.addJoin(CONTESTS, JoinType.LEFT_OUTER_JOIN, CONTESTS.CONTEST_ID.eq(QUESTIONS.CONTEST_ID));
    query.addJoin(USERS, USERS.USER_ID.eq(QUESTIONS.USER_ID));
    query.addJoin(PROFILES, USERS.USER_ID.eq(PROFILES.USER_ID));

    if (options != null) {
        final Field<Integer> questionIdField =
                DSL.field("split_part({0}, '|', 1)::integer", Integer.class, EVENTS.ARGUMENTS);
        final Field<Object> hasNotificationField =
                DSL.selectCount()
                   .from(NOTIFICATIONS)
                   .join(EVENTS)
                   .using(NOTIFICATIONS.EVENT_ID)
                   .where(NOTIFICATIONS.USER_ID.eq(JooqDaoUtils.getUserId(options.getCurrentUser())))
                   .and(NOTIFICATIONS.STATUS.eq(NotifyStatus.unread))
                   .and(questionIdField.eq(QUESTIONS.QUESTION_ID))
                   .asField("hasNotification");

        query.addSelect(hasNotificationField);
        query.addOrderBy(hasNotificationField.desc());

        if (!Strings.isNullOrEmpty(options.getQuery())) {
            final String lowerCaseQuery = options.getQuery().toLowerCase();
            query.addConditions(QUESTIONS.QUESTION.lower().contains(lowerCaseQuery)
                                                  .or(USERS.LOGIN.lower().contains(lowerCaseQuery))
                                                  .or(PROFILES.NICKNAME.lower().contains(lowerCaseQuery))
                                                  .or(TASKS.CODE.lower().contains(lowerCaseQuery))
                                                  .or(CONTESTS.CODE.lower().contains(lowerCaseQuery)));
        }
    }

    return query;
}