Java SelectQuery.addFrom方法代码示例

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@Override
public Collection<SpareWrapper> getSpares(String label, Boolean flag, Integer numFromInclusive, Integer numToInclusive) throws Exception {
    SelectQuery<Record> query = jooq.selectQuery();
    query.addFrom(Spares.SPARES);

    if (label != null) {
        query.addConditions(Spares.SPARES.LABEL.eq(label));
    }

    if (flag != null) {
        query.addConditions(Spares.SPARES.FLAG.eq(flag));
    }

    if (numFromInclusive != null) {

        if (numToInclusive == null) {
            query.addConditions(Spares.SPARES.NUM.eq(numFromInclusive));
        } else {
            query.addConditions(Spares.SPARES.NUM.ge(numFromInclusive));
            query.addConditions(Spares.SPARES.NUM.le(numToInclusive));
        }
    }

    return query.fetch(SpareWrapper::of);
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@SuppressWarnings("unchecked")
private
SelectQuery<Record8<Integer, String, Integer, String, DateTime, String, DateTime, String>>
getInternalCSV(TransactionQueryForm form) {

    SelectQuery selectQuery = ctx.selectQuery();
    selectQuery.addFrom(TRANSACTION);
    selectQuery.addJoin(CONNECTOR, TRANSACTION.CONNECTOR_PK.eq(CONNECTOR.CONNECTOR_PK));
    selectQuery.addSelect(
            TRANSACTION.TRANSACTION_PK,
            CONNECTOR.CHARGE_BOX_ID,
            CONNECTOR.CONNECTOR_ID,
            TRANSACTION.ID_TAG,
            TRANSACTION.START_TIMESTAMP,
            TRANSACTION.START_VALUE,
            TRANSACTION.STOP_TIMESTAMP,
            TRANSACTION.STOP_VALUE
    );

    return addConditions(selectQuery, form);
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
/**
 * Difference from getInternalCSV:
 * Joins with CHARGE_BOX and OCPP_TAG tables, selects CHARGE_BOX_PK and OCPP_TAG_PK additionally
 */
@SuppressWarnings("unchecked")
private
SelectQuery<Record10<Integer, String, Integer, String, DateTime, String, DateTime, String, Integer, Integer>>
getInternal(TransactionQueryForm form) {

    SelectQuery selectQuery = ctx.selectQuery();
    selectQuery.addFrom(TRANSACTION);
    selectQuery.addJoin(CONNECTOR, TRANSACTION.CONNECTOR_PK.eq(CONNECTOR.CONNECTOR_PK));
    selectQuery.addJoin(CHARGE_BOX, CHARGE_BOX.CHARGE_BOX_ID.eq(CONNECTOR.CHARGE_BOX_ID));
    selectQuery.addJoin(OCPP_TAG, OCPP_TAG.ID_TAG.eq(TRANSACTION.ID_TAG));
    selectQuery.addSelect(
            TRANSACTION.TRANSACTION_PK,
            CONNECTOR.CHARGE_BOX_ID,
            CONNECTOR.CONNECTOR_ID,
            TRANSACTION.ID_TAG,
            TRANSACTION.START_TIMESTAMP,
            TRANSACTION.START_VALUE,
            TRANSACTION.STOP_TIMESTAMP,
            TRANSACTION.STOP_VALUE,
            CHARGE_BOX.CHARGE_BOX_PK,
            OCPP_TAG.OCPP_TAG_PK
    );

    return addConditions(selectQuery, form);
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@Override
public List<? extends ProjectMember> getProjectMembersByIdentity(long projectId, Set<Identity> identities) {
    Condition allMembers = DSL.falseCondition();
    for (Identity identity : identities) {
        allMembers = allMembers.or(PROJECT_MEMBER.EXTERNAL_ID.eq(identity.getExternalId())
                .and(PROJECT_MEMBER.EXTERNAL_ID_TYPE.eq(identity.getExternalIdType()))
                .and(PROJECT_MEMBER.REMOVED.isNull())
                .and(PROJECT_MEMBER.STATE.eq(CommonStatesConstants.ACTIVE))
                .and(PROJECT_MEMBER.PROJECT_ID.eq(projectId)));
    }
    SelectQuery<Record> query = create().selectQuery();
    query.addFrom(PROJECT_MEMBER);
    query.addConditions(allMembers);
    query.setDistinct(true);
    return query.fetchInto(PROJECT_MEMBER);
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@SuppressWarnings("unchecked")
@Override
protected <T> List<T> getListByRelationshipMap(Object obj, MapRelationship rel) {
    Class<UpdatableRecord<?>> typeClass = JooqUtils.getRecordClass(schemaFactory, rel.getObjectType());

    String mappingType = schemaFactory.getSchemaName(rel.getMappingType());
    String fromType = schemaFactory.getSchemaName(rel.getObjectType());

    TableField<?, Object> fieldFrom = JooqUtils.getTableField(getMetaDataManager(), fromType, ObjectMetaDataManager.ID_FIELD);
    TableField<?, Object> mappingTo = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getOtherRelationship().getPropertyName());
    TableField<?, Object> mappingOther = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getPropertyName());
    TableField<?, Object> mappingRemoved = JooqUtils.getTableField(getMetaDataManager(), mappingType, ObjectMetaDataManager.REMOVED_FIELD);

    Table<?> table = JooqUtils.getTable(schemaFactory, typeClass);
    Table<?> mapTable = JooqUtils.getTable(schemaFactory, rel.getMappingType());

    SelectQuery<?> query = create().selectQuery();
    query.addFrom(table);
    query.addSelect(table.fields());
    query.addJoin(mapTable, fieldFrom.eq(mappingTo)
            .and(mappingRemoved == null ? DSL.trueCondition() : mappingRemoved.isNull())
            .and(mappingOther.eq(ObjectUtils.getId(obj))));

    return (List<T>)query.fetchInto(typeClass);
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@SuppressWarnings("unchecked")
private Result<Record7<Integer, Integer, String, String, String, String, String>> getOverviewInternal(UserQueryForm form) {
    SelectQuery selectQuery = ctx.selectQuery();
    selectQuery.addFrom(USER);
    selectQuery.addJoin(OCPP_TAG, JoinType.LEFT_OUTER_JOIN, USER.OCPP_TAG_PK.eq(OCPP_TAG.OCPP_TAG_PK));
    selectQuery.addSelect(
            USER.USER_PK,
            USER.OCPP_TAG_PK,
            OCPP_TAG.ID_TAG,
            USER.FIRST_NAME,
            USER.LAST_NAME,
            USER.PHONE,
            USER.E_MAIL
    );

    if (form.isSetUserPk()) {
        selectQuery.addConditions(USER.USER_PK.eq(form.getUserPk()));
    }

    if (form.isSetOcppIdTag()) {
        selectQuery.addConditions(includes(OCPP_TAG.ID_TAG, form.getOcppIdTag()));
    }

    if (form.isSetEmail()) {
        selectQuery.addConditions(includes(USER.E_MAIL, form.getEmail()));
    }

    if (form.isSetName()) {

        // Concatenate the two columns and search within the resulting representation
        // for flexibility, since the user can search by first or last name, or both.
        Field<String> joinedField = DSL.concat(USER.FIRST_NAME, USER.LAST_NAME);

        // Find a matching sequence anywhere within the concatenated representation
        selectQuery.addConditions(includes(joinedField, form.getName()));
    }

    return selectQuery.fetch();
}
 

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包/类
public Object list(SchemaFactory schemaFactory, String type, Map<Object, Object> criteria, ListOptions options) {
    Class<?> clz = getClass(schemaFactory, type, criteria, true);
    if (clz == null) {
        return null;
    }

    /* Use core schema, parent may not be authorized */
    type = objectManager.getSchemaFactory().getSchemaName(clz);
    Table<?> table = JooqUtils.getTableFromRecordClass(clz);
    Sort sort = options == null ? null : options.getSort();
    Pagination pagination = options == null ? null : options.getPagination();

    if (table == null)
        return null;

    SelectQuery<?> query = create().selectQuery();
    query.addFrom(table);
    addSort(schemaFactory, type, sort, query);

    addConditions(schemaFactory, query, type, table, criteria);
    addLimit(schemaFactory, type, pagination, query);

    List<?> result = query.fetch();
    processPaginationResult(result, pagination);

    return result;
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
public List<ProtocolRecord> match(MetaProtocolRecord metaProtocol,
                                  JobRecord job) {
    SelectQuery<Record> selectQuery = model.create()
                                           .selectQuery();
    selectQuery.addDistinctOn(PROTOCOL.fields());
    selectQuery.addFrom(PROTOCOL);

    addServiceMask(metaProtocol, job, selectQuery);
    addStatusMask(metaProtocol, job, selectQuery);

    addMask(selectQuery, metaProtocol.getDeliverFrom(),
            job.getDeliverFrom(), PROTOCOL.DELIVER_FROM,
            ExistentialDomain.Location);
    addMask(selectQuery, metaProtocol.getDeliverTo(), job.getDeliverTo(),
            PROTOCOL.DELIVER_TO, ExistentialDomain.Location);

    addMask(selectQuery, metaProtocol.getProduct(), job.getProduct(),
            PROTOCOL.PRODUCT, ExistentialDomain.Product);

    addMask(selectQuery, metaProtocol.getRequester(), job.getRequester(),
            PROTOCOL.REQUESTER, ExistentialDomain.Agency);

    addMask(selectQuery, metaProtocol.getAssignTo(), job.getAssignTo(),
            PROTOCOL.ASSIGN_TO, ExistentialDomain.Agency);

    addMask(selectQuery, metaProtocol.getQuantityUnit(),
            job.getQuantityUnit(), PROTOCOL.QUANTITY_UNIT,
            ExistentialDomain.Unit);

    return selectQuery.fetch()
                      .into(ProtocolRecord.class);
}
 

import org.jooq.SelectQuery; //导入方法依赖的package包/类
@Override
@SuppressWarnings("unchecked")
public List<Overview> getOverview(OcppTagQueryForm form) {
    SelectQuery selectQuery = ctx.selectQuery();
    selectQuery.addFrom(OCPP_TAG);

    OcppTag parentTable = OCPP_TAG.as("parent");

    selectQuery.addSelect(
            OCPP_TAG.OCPP_TAG_PK,
            parentTable.OCPP_TAG_PK,
            OCPP_TAG.ID_TAG,
            OCPP_TAG.PARENT_ID_TAG,
            OCPP_TAG.EXPIRY_DATE,
            OCPP_TAG.IN_TRANSACTION,
            OCPP_TAG.BLOCKED
    );

    selectQuery.addJoin(parentTable, JoinType.LEFT_OUTER_JOIN, parentTable.ID_TAG.eq(OCPP_TAG.PARENT_ID_TAG));

    if (form.isIdTagSet()) {
        selectQuery.addConditions(OCPP_TAG.ID_TAG.eq(form.getIdTag()));
    }

    if (form.isParentIdTagSet()) {
        selectQuery.addConditions(OCPP_TAG.PARENT_ID_TAG.eq(form.getParentIdTag()));
    }

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

        case TRUE:
            selectQuery.addConditions(OCPP_TAG.EXPIRY_DATE.lessOrEqual(CustomDSL.utcTimestamp()));
            break;

        case FALSE:
            selectQuery.addConditions(
                    OCPP_TAG.EXPIRY_DATE.isNull().or(OCPP_TAG.EXPIRY_DATE.greaterThan(CustomDSL.utcTimestamp()))
            );
            break;

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

    processBooleanType(selectQuery, OCPP_TAG.IN_TRANSACTION, form.getInTransaction());
    processBooleanType(selectQuery, OCPP_TAG.BLOCKED, form.getBlocked());

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

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