Java Code Examples for org.jooq.impl.DSL#trueCondition()

/**
 * 动态 jooq sql
 */
@Test
public void dynmaicSql2() {

    Condition condition = DSL.trueCondition();

    if (true) {
        condition = condition.and(User.USER.NAME.like("%李%"));
    }

    if (true) {
        condition = condition.andNot(User.USER.SEX.eq((byte) 2));
    }

    Result<UserRecord> recordResult = dslContext.selectFrom(User.USER).
            where(condition).
            fetch();

    log.info(recordResult.format());

}
 

/**
 * Returns the flow in the database that matches the parameter based on all attributes except possibly id
 *
 * @param flow the physical flow to match against
 * @return matching flow or null
 */
public PhysicalFlow matchPhysicalFlow(PhysicalFlow flow) {

    Condition idCondition = flow.id().isPresent()
            ? PHYSICAL_FLOW.ID.eq(flow.id().get())
            : DSL.trueCondition();

    return dsl.selectFrom(PHYSICAL_FLOW)
            .where(PHYSICAL_FLOW.LOGICAL_FLOW_ID.eq(flow.logicalFlowId()))
            .and(PHYSICAL_FLOW.SPECIFICATION_ID.eq(flow.specificationId()))
            .and(PHYSICAL_FLOW.BASIS_OFFSET.eq(flow.basisOffset()))
            .and(PHYSICAL_FLOW.FREQUENCY.eq(flow.frequency().name()))
            .and(PHYSICAL_FLOW.TRANSPORT.eq(flow.transport()))
            .and(PHYSICAL_FLOW.CRITICALITY.eq(flow.criticality().name()))
            .and(idCondition)
            .fetchOne(TO_DOMAIN_MAPPER);
}
 

public List<AttestationInstance> findForEntityByRecipient(AttestEntityCommand command, String userId, boolean unattestedOnly) {
    Condition maybeUnattestedOnlyCondition = unattestedOnly
            ? ATTESTATION_INSTANCE.ATTESTED_AT.isNull()
            : DSL.trueCondition();

    return dsl
            .select(ATTESTATION_INSTANCE.fields())
            .select(ENTITY_NAME_FIELD)
            .from(ATTESTATION_INSTANCE)
            .innerJoin(ATTESTATION_RUN)
            .on(ATTESTATION_INSTANCE.ATTESTATION_RUN_ID.eq(ATTESTATION_RUN.ID))
            .innerJoin(ATTESTATION_INSTANCE_RECIPIENT)
            .on(ATTESTATION_INSTANCE_RECIPIENT.ATTESTATION_INSTANCE_ID.eq(ATTESTATION_INSTANCE.ID))
            .where(ATTESTATION_INSTANCE_RECIPIENT.USER_ID.eq(userId))
            .and(ATTESTATION_RUN.ATTESTED_ENTITY_KIND.eq(command.attestedEntityKind().name())
            .and(ATTESTATION_INSTANCE.PARENT_ENTITY_ID.eq(command.entityReference().id()))
            .and(ATTESTATION_INSTANCE.PARENT_ENTITY_KIND.eq(command.entityReference().kind().name())))
            .and(maybeUnattestedOnlyCondition)
            .fetch(TO_DOMAIN_MAPPER);
}
 

private List<Person> executeWithCondition(EntitySearchOptions options, Condition condition) {
    boolean showRemoved = options
            .entityLifecycleStatuses()
            .contains(EntityLifecycleStatus.REMOVED);

    Condition maybeFilterRemoved = showRemoved
            ? DSL.trueCondition()  // match anything
            : PERSON.IS_REMOVED.isFalse();

    return dsl
            .select(PERSON.fields())
            .from(PERSON)
            .where(condition)
            .and(maybeFilterRemoved)
            .limit(options.limit())
            .fetch(PersonDao.personMapper);
}
 

private Select<Record1<Long>> mkForDataType(IdSelectionOptions options) {
    Select<Record1<Long>> dataTypeSelector = dataTypeIdSelectorFactory.apply(options);

    Condition supplierNotRemoved =  SUPPLIER_APP.IS_REMOVED.isFalse();
    Condition consumerNotRemoved =  CONSUMER_APP.IS_REMOVED.isFalse();

    Condition appKindFilterConditions = options.filters().omitApplicationKinds().isEmpty()
            ? DSL.trueCondition()
            : SUPPLIER_APP.KIND.notIn(options.filters().omitApplicationKinds())
                .or(CONSUMER_APP.KIND.notIn(options.filters().omitApplicationKinds()));

    return DSL.select(LOGICAL_FLOW_DECORATOR.LOGICAL_FLOW_ID)
            .from(LOGICAL_FLOW_DECORATOR)
            .innerJoin(LOGICAL_FLOW).on(LOGICAL_FLOW.ID.eq(LOGICAL_FLOW_DECORATOR.LOGICAL_FLOW_ID))
            .innerJoin(SUPPLIER_APP)
                .on(LOGICAL_FLOW.SOURCE_ENTITY_ID.eq(SUPPLIER_APP.ID)
                    .and(LOGICAL_FLOW.SOURCE_ENTITY_KIND.eq(EntityKind.APPLICATION.name())))
            .innerJoin(CONSUMER_APP)
                .on(LOGICAL_FLOW.TARGET_ENTITY_ID.eq(CONSUMER_APP.ID)
                    .and(LOGICAL_FLOW.TARGET_ENTITY_KIND.eq(EntityKind.APPLICATION.name())))
            .where(LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_ID.in(dataTypeSelector)
                .and(LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_KIND.eq(EntityKind.DATA_TYPE.name())))
            .and(LOGICAL_NOT_REMOVED)
            .and(supplierNotRemoved)
            .and(consumerNotRemoved)
            .and(appKindFilterConditions);
}
 

private Condition getCondition(Request request) {
    Optional<Long> categoryId = getCategoryId(request);
    List<Long> relationshipKindIds = getRelationshipKinds(request);

    Condition categoryCondition = categoryId
            .map(MEASURABLE.MEASURABLE_CATEGORY_ID::eq)
            .orElse(DSL.trueCondition());

    Condition relKindCondition = (relationshipKindIds.isEmpty())
            ? DSL.trueCondition()
            : RELATIONSHIP_KIND.ID.in(relationshipKindIds);

    return categoryCondition.and(relKindCondition);
}
 

/**
 * Finds the total number of rows in the table
 *
 * @return number of records in table
 */
private int getRecordsCount(Object lowerBoundKey) throws SQLException
{
  Condition condition = DSL.trueCondition();
  if (getWhereCondition() != null) {
    condition = condition.and(getWhereCondition());
  }

  if (isPollerPartition && lowerBoundKey != null) {
    condition = andLowerBoundKeyCondition(condition, lowerBoundKey);
  }

  int recordsCount = dslContext.select(DSL.count()).from(getTableName()).where(condition).fetchOne(0, int.class);
  return recordsCount;
}
 

/**
 * Helper function returns a range query based on the bounds passed<br>
 */
protected String buildRangeQuery(Object lowerBoundKey, int offset, int limit)
{
  Condition condition = DSL.trueCondition();
  if (getWhereCondition() != null) {
    condition = condition.and(getWhereCondition());
  }

  if (isPollerPartition && lowerBoundKey != null) {
    condition = andLowerBoundKeyCondition(condition, lowerBoundKey);
  }

  String sqlQuery;
  if (getColumnsExpression() != null) {
    Collection<Field<?>> columns = new ArrayList<>();
    for (String column : getColumnsExpression().split(",")) {
      columns.add(field(column));
    }
    sqlQuery = dslContext.select(columns).from(getTableName()).where(condition)
        .orderBy(field(getKey())).limit(limit).offset(offset).getSQL(ParamType.INLINED);
  } else {
    sqlQuery = dslContext.select().from(getTableName()).where(condition).orderBy(field(getKey())).limit(limit)
        .offset(offset).getSQL(ParamType.INLINED);
  }
  LOG.info("DSL Query: " + sqlQuery);
  return sqlQuery;
}
 

private Select<Record1<Long>> mkForFlowDiagram(IdSelectionOptions options) {
    ensureScopeIsExact(options);

    long diagramId = options.entityReference().id();

    Condition logicalFlowInClause = LOGICAL_FLOW.ID.in(DSL
            .select(FLOW_DIAGRAM_ENTITY.ENTITY_ID)
            .from(FLOW_DIAGRAM_ENTITY)
            .where(FLOW_DIAGRAM_ENTITY.ENTITY_KIND.eq(EntityKind.LOGICAL_DATA_FLOW.name())
                    .and(FLOW_DIAGRAM_ENTITY.DIAGRAM_ID.eq(diagramId))));

    Condition applicationConditions = DSL.trueCondition(); //mkApplicationConditions(options);

    SelectConditionStep<Record1<Long>> directlyReferencedApps = DSL
            .select(flowDiagram.ENTITY_ID)
            .from(flowDiagram)
            .innerJoin(APPLICATION)
            .on(APPLICATION.ID.eq(flowDiagram.ENTITY_ID))
            .where(flowDiagram.DIAGRAM_ID.eq(diagramId))
            .and(flowDiagram.ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
            .and(applicationConditions);

    SelectConditionStep<Record1<Long>> appsViaSourcesOfFlows = DSL
            .select(LOGICAL_FLOW.SOURCE_ENTITY_ID)
            .from(LOGICAL_FLOW)
            .innerJoin(APPLICATION)
            .on(APPLICATION.ID.eq(LOGICAL_FLOW.SOURCE_ENTITY_ID))
            .where(logicalFlowInClause)
            .and(LOGICAL_FLOW.SOURCE_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
            .and(applicationConditions);

    SelectConditionStep<Record1<Long>> appsViaTargetsOfFlows = DSL
            .select(LOGICAL_FLOW.TARGET_ENTITY_ID)
            .from(LOGICAL_FLOW)
            .innerJoin(APPLICATION)
            .on(APPLICATION.ID.eq(LOGICAL_FLOW.TARGET_ENTITY_ID))
            .where(logicalFlowInClause)
            .and(LOGICAL_FLOW.TARGET_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
            .and(applicationConditions);

    return DSL.selectFrom(
            directlyReferencedApps
                .unionAll(appsViaSourcesOfFlows)
                .unionAll(appsViaTargetsOfFlows).asTable());
}
 

public Condition getLifecycleCondition(IdSelectionOptions options) {
    return options.entityLifecycleStatuses().contains(EntityLifecycleStatus.REMOVED)
            ? DSL.trueCondition()
            : PHYSICAL_FLOW_NOT_REMOVED;
}
 

private Condition mkQueryCondition(String query, Field<String> nameField) {
    List<String> terms = mkTerms(query);
    Condition termMatcher = DSL.trueCondition();
    terms.forEach(t -> termMatcher.and(nameField.like("%" + t + "%")));
    return termMatcher;
}
 

private Condition mkHistoryDurationCondition(Duration duration) {
    if (duration == Duration.ALL) {
        return DSL.trueCondition();
    }
    return esv.CREATED_AT.gt(currentTimestamp().minus(duration.numDays()));
}
 

private Condition mkRollupOnlyCondition(boolean rollupOnly) {
    return  rollupOnly
            ? esd.ROLLUP_VISIBILITY.eq(true)
            : DSL.trueCondition();
}
 

private Condition getLifecycleCondition(IdSelectionOptions options) {
    return options.entityLifecycleStatuses().contains(EntityLifecycleStatus.REMOVED)
            ? DSL.trueCondition()
            : PHYSICAL_FLOW.IS_REMOVED.isFalse()
            .and(PHYSICAL_SPECIFICATION.IS_REMOVED.isFalse());
}
 

private SelectConditionStep<Record> prepareQuery(DSLContext dsl, IdSelectionOptions options) {

        Select<Record1<Long>> appIdSelector = applicationIdSelectorFactory.apply(options);

        Condition conditionForDataType = EntityKind.DATA_TYPE.equals(options.entityReference().kind())
                ? LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_ID.in(dataTypeIdSelectorFactory.apply(options))
                : DSL.trueCondition();

        Field<Long> sourceFlowId = LOGICAL_FLOW.ID.as("sourceFlowId");
        Field<Long> targetFlowId = LOGICAL_FLOW.ID.as("targetFlowId");

        Select<Record1<Long>> sourceAppFlows = DSL.select(sourceFlowId)
                .from(LOGICAL_FLOW)
                .innerJoin(APPLICATION)
                    .on(LOGICAL_FLOW.SOURCE_ENTITY_ID.eq(APPLICATION.ID))
                .where(LOGICAL_FLOW.SOURCE_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
                .and(APPLICATION.ID.in(appIdSelector));

        Select<Record1<Long>> targetAppFlows = DSL.select(targetFlowId)
                .from(LOGICAL_FLOW)
                .innerJoin(APPLICATION)
                    .on(LOGICAL_FLOW.TARGET_ENTITY_ID.eq(APPLICATION.ID))
                .where(LOGICAL_FLOW.TARGET_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
                .and(APPLICATION.ID.in(appIdSelector));

        Field<String> sourceAssetCodeField = DSL
                .when(LOGICAL_FLOW.SOURCE_ENTITY_KIND.eq(EntityKind.APPLICATION.name()),
                        DSL.select(APPLICATION.ASSET_CODE)
                                .from(APPLICATION)
                                .where(APPLICATION.ID.eq(LOGICAL_FLOW.SOURCE_ENTITY_ID)));

        Field<String> sourceOrgUnitNameField = DSL
                .when(LOGICAL_FLOW.SOURCE_ENTITY_KIND.eq(EntityKind.APPLICATION.name()),
                        DSL.select(ORGANISATIONAL_UNIT.NAME)
                                .from(APPLICATION)
                                .innerJoin(ORGANISATIONAL_UNIT)
                                    .on(ORGANISATIONAL_UNIT.ID.eq(APPLICATION.ORGANISATIONAL_UNIT_ID))
                                .where(APPLICATION.ID.eq(LOGICAL_FLOW.SOURCE_ENTITY_ID)));

        Field<String> targetAssetCodeField = DSL
                .when(LOGICAL_FLOW.TARGET_ENTITY_KIND.eq(EntityKind.APPLICATION.name()),
                        DSL.select(APPLICATION.ASSET_CODE)
                                .from(APPLICATION)
                                .where(APPLICATION.ID.eq(LOGICAL_FLOW.TARGET_ENTITY_ID)));

        Field<String> targetOrgUnitNameField = DSL
                .when(LOGICAL_FLOW.TARGET_ENTITY_KIND.eq(EntityKind.APPLICATION.name()),
                        DSL.select(ORGANISATIONAL_UNIT.NAME)
                                .from(APPLICATION)
                                .innerJoin(ORGANISATIONAL_UNIT)
                                    .on(ORGANISATIONAL_UNIT.ID.eq(APPLICATION.ORGANISATIONAL_UNIT_ID))
                                .where(APPLICATION.ID.eq(LOGICAL_FLOW.TARGET_ENTITY_ID)));

        return dsl
                .select(SOURCE_NAME_FIELD.as("Source"),
                        sourceAssetCodeField.as("Source Asset Code"),
                        sourceOrgUnitNameField.as("Source Org Unit"))
                .select(TARGET_NAME_FIELD.as("Target"),
                        targetAssetCodeField.as("Target Asset Code"),
                        targetOrgUnitNameField.as("Target Org Unit"))
                .select(DATA_TYPE.NAME.as("Data Type"))
                .select(ENUM_VALUE.DISPLAY_NAME.as("Authoritativeness"))
                .from(LOGICAL_FLOW)
                .leftJoin(sourceAppFlows)
                    .on(sourceFlowId.eq(LOGICAL_FLOW.ID))
                .leftJoin(targetAppFlows)
                    .on(targetFlowId.eq(LOGICAL_FLOW.ID))
                .join(LOGICAL_FLOW_DECORATOR)
                    .on(LOGICAL_FLOW_DECORATOR.LOGICAL_FLOW_ID.eq(LOGICAL_FLOW.ID)
                        .and(LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_KIND.eq(EntityKind.DATA_TYPE.name())))
                .join(DATA_TYPE)
                    .on(DATA_TYPE.ID.eq(LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_ID)
                        .and(LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_KIND.eq(EntityKind.DATA_TYPE.name())))
                .join(ENUM_VALUE)
                    .on(ENUM_VALUE.KEY.eq(LOGICAL_FLOW_DECORATOR.RATING)
                            .and(ENUM_VALUE.TYPE.eq("AuthoritativenessRating")))
                .where(LOGICAL_FLOW.ENTITY_LIFECYCLE_STATUS.ne(REMOVED.name()))
                .and(conditionForDataType)
                .and(sourceFlowId.isNotNull()
                        .or(targetFlowId.isNotNull()));

    }