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

@Override
public List<Measurable> searchFullText(DSLContext dsl, EntitySearchOptions options) {
    Condition entityLifecycleCondition = MEASURABLE.ENTITY_LIFECYCLE_STATUS.in(options.entityLifecycleStatuses());

    Field<Double> rank = DSL
            .field("ts_rank_cd(to_tsvector({0}), plainto_tsquery({1}))",
                    Double.class,
                    DSL.lower(MEASURABLE.DESCRIPTION),
                    DSL.inline(options.searchQuery().toLowerCase()));

    return dsl
            .select(MEASURABLE.fields())
            .select(rank)
            .from(MEASURABLE)
            .where(rank.greaterThan(Double.MIN_VALUE))
            .and(entityLifecycleCondition)
            .orderBy(rank.desc())
            .limit(options.limit())
            .fetch(MeasurableDao.TO_DOMAIN_MAPPER);
}
 

@Override
public List<ChangeInitiative> searchFullText(DSLContext dsl, EntitySearchOptions options) {

    Field<Double> rank = DSL
            .field("ts_rank_cd(to_tsvector({0} || ' ' || coalesce({1}, '')), plainto_tsquery({2}))",
                    Double.class,
                    DSL.lower(CHANGE_INITIATIVE.DESCRIPTION),
                    DSL.lower(CHANGE_INITIATIVE.EXTERNAL_ID),
                    DSL.inline(options.searchQuery().toLowerCase()));

    return dsl
            .select(CHANGE_INITIATIVE.fields())
            .select(rank)
            .from(CHANGE_INITIATIVE)
            .where(rank.greaterThan(Double.MIN_VALUE))
            .orderBy(rank.desc())
            .limit(options.limit())
            .fetch(ChangeInitiativeDao.TO_DOMAIN_MAPPER);
}
 

private QueryPart delegate(Configuration configuration) {
	switch (configuration.dialect().family().toString()) {
	case "MYSQL":
	case "POSTGRES":
		return DSL.field("ST_Equals({0}, {1})", Boolean.class, field1, field2);
	case "SQLSERVER":
		return DSL.field("{0}.STEquals({1})", Boolean.class, field1, field2);
	default:
		throw new UnsupportedOperationException("Dialect not supported");
	}
}
 

public DatabaseSummaryStatistics calculateStatsForAppSelector(Select<Record1<Long>> appIdSelector) {

        Field<String> dbmsVendorInner = DSL.field("dbms_vendor_inner", String.class);
        Field<String> environmentInner = DSL.field("environment_inner", String.class);
        Field<String> eolStatusInner = DSL.field("eol_status_inner", String.class);

        // de-duplicate records, as one database can be linked to multiple apps
        Result<? extends Record> dbInfo = dsl.selectDistinct(
                    DATABASE_INFORMATION.DATABASE_NAME,
                    DATABASE_INFORMATION.INSTANCE_NAME,
                    DATABASE_INFORMATION.ENVIRONMENT.as(environmentInner),
                    DATABASE_INFORMATION.DBMS_VENDOR.as(dbmsVendorInner),
                    DATABASE_INFORMATION.DBMS_NAME,
                    DATABASE_INFORMATION.DBMS_VERSION,
                    mkEndOfLifeStatusDerivedField(DATABASE_INFORMATION.END_OF_LIFE_DATE).as(eolStatusInner))
                .from(DATABASE_INFORMATION)
                .innerJoin(APPLICATION)
                    .on(APPLICATION.ASSET_CODE.eq(DATABASE_INFORMATION.ASSET_CODE))
                .where(APPLICATION.ID.in(appIdSelector))
                .fetch();

        return ImmutableDatabaseSummaryStatistics.builder()
                .vendorCounts(calculateStringTallies(dbInfo, dbmsVendorInner))
                .environmentCounts(calculateStringTallies(dbInfo, environmentInner))
                .endOfLifeStatusCounts(calculateStringTallies(dbInfo, eolStatusInner))
                .build();

    }
 

public Map<DataTypeDirectionKey, List<Long>> logicalFlowIdsByTypeAndDirection(Select<Record1<Long>> selector) {
    checkNotNull(selector, "selector cannot be null");

    Table<Record1<Long>> sourceApp = selector.asTable("source_app");
    Table<Record1<Long>> targetApp = selector.asTable("target_app");
    Field<Long> sourceAppId = sourceApp.field(0, Long.class);
    Field<Long> targetAppId = targetApp.field(0, Long.class);
    Field<String> flowTypeCase =
            when(sourceAppId.isNotNull()
                    .and(targetAppId.isNotNull()), inline("INTRA"))
                    .when(sourceAppId.isNotNull(), inline("OUTBOUND"))
                    .otherwise(inline("INBOUND"));
    Field<String> flowType = DSL.field("flow_type", String.class);

    Condition condition = sourceAppId
            .isNotNull()
            .or(targetAppId.isNotNull())
            .and(lf.ENTITY_LIFECYCLE_STATUS.ne(REMOVED.name())
                    .and(lf.IS_REMOVED.isFalse()));

    return  dsl.select(
                lfd.DECORATOR_ENTITY_ID,
                flowTypeCase.as(flowType),
                lf.ID)
            .from(lf)
            .innerJoin(lfd)
            .on(lf.ID.eq(lfd.LOGICAL_FLOW_ID)
                    .and(lfd.DECORATOR_ENTITY_KIND.eq(inline(EntityKind.DATA_TYPE.name()))))
            .leftJoin(sourceApp)
            .on(sourceAppId.eq(lf.SOURCE_ENTITY_ID))
            .leftJoin(targetApp)
            .on(targetAppId.eq(lf.TARGET_ENTITY_ID))
            .where(dsl.renderInlined(condition))
            .fetchGroups(
                    k -> DataTypeDirectionKey.mkKey(
                            k.get(lfd.DECORATOR_ENTITY_ID),
                            FlowDirection.valueOf(k.get(flowType))),
                    v -> v.get(lf.ID));

}
 

private QueryPart delegate(Configuration configuration) {
	switch (configuration.dialect().family().toString()) {
	case "MYSQL":
	case "POSTGRES":
		return DSL.field("ST_Within({0}, {1})", Boolean.class, field1, field2);
	case "SQLSERVER":
		return DSL.field("{0}.STWithin({1})", Boolean.class, field1, field2);
	default:
		throw new UnsupportedOperationException("Dialect not supported");
	}
}
 

private QueryPart delegate(Configuration configuration) {
	switch (configuration.dialect().family().toString()) {
	case "MYSQL":
	case "POSTGRES":
		return DSL.field("ST_Disjoint({0}, {1})", Boolean.class, field1, field2);
	case "SQLSERVER":
		return DSL.field("{0}.STDisjoint({1})", Boolean.class, field1, field2);
	default:
		throw new UnsupportedOperationException("Dialect not supported");
	}
}
 

private QueryPart delegate(Configuration configuration) {
	switch (configuration.dialect().family().toString()) {
	case "MYSQL":
	case "POSTGRES":
		return DSL.field("ST_Contains({0}, {1})", Boolean.class, field1, field2);
	case "SQLSERVER":
		return DSL.field("{0}.STContains({1})", Boolean.class, field1, field2);
	default:
		throw new UnsupportedOperationException("Dialect not supported");
	}
}
 

private FieldWrapper specificOp(String op, StaTimeIntervalWrapper other) {
    Field<OffsetDateTime> dtEnd = other.getEnd();
    Field<OffsetDateTime> dtStart = other.getStart();
    String template = "(" + INTERVAL_PARAM + " " + op + " " + TIMESTAMP_PARAM + ")";
    Field<OffsetDateTime> newStart = DSL.field(template, OffsetDateTime.class, duration, dtStart);
    Field<OffsetDateTime> newEnd = DSL.field(template, OffsetDateTime.class, duration, dtEnd);
    return new StaTimeIntervalWrapper(newStart, newEnd);
}
 

private QueryPart delegate(Configuration configuration) {
	switch (configuration.dialect().family().toString()) {
	case "MYSQL":
	case "POSTGRES":
		return DSL.field("ST_Intersects({0}, {1})", Boolean.class, field1, field2);
	case "SQLSERVER":
		return DSL.field("{0}.STIntersects({1})", Boolean.class, field1, field2);
	default:
		throw new UnsupportedOperationException("Dialect not supported");
	}
}
 

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

    Condition condition = LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_ID.in(dataTypeSelector)
            .and(LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_KIND.eq(EntityKind.DATA_TYPE.name()));

    Field<Long> appId = DSL.field("app_id", Long.class);

    Condition applicationConditions = mkApplicationConditions(options);

    SelectConditionStep<Record1<Long>> sources = selectLogicalFlowAppsByDataType(
            LOGICAL_FLOW.SOURCE_ENTITY_ID.as(appId),
            condition
                    .and(LOGICAL_FLOW.SOURCE_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
                    .and(applicationConditions),
            LOGICAL_FLOW.SOURCE_ENTITY_ID);

    SelectConditionStep<Record1<Long>> targets = selectLogicalFlowAppsByDataType(
            LOGICAL_FLOW.TARGET_ENTITY_ID.as(appId),
            condition
                    .and(LOGICAL_FLOW.TARGET_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
                    .and(applicationConditions),
            LOGICAL_FLOW.TARGET_ENTITY_ID);

    return DSL
            .selectDistinct(appId)
            .from(sources.union(targets).asTable());
}
 

private QueryPart delegate(Configuration configuration) {
	switch (configuration.dialect().family().toString()) {
	case "MYSQL":
	case "POSTGRES":
		return DSL.field("ST_Touches({0}, {1})", Boolean.class, field1, field2);
	case "SQLSERVER":
		return DSL.field("{0}.STTouches({1})", Boolean.class, field1, field2);
	default:
		throw new UnsupportedOperationException("Dialect not supported");
	}
}
 

private QueryPart delegate(Configuration configuration) {
	switch (configuration.dialect().family().toString()) {
	case "MYSQL":
	case "POSTGRES":
		return DSL.field("ST_IsClosed({0})", Boolean.class, field);
	case "SQLSERVER":
		return DSL.field("{0}.STIsClosed()", Boolean.class, field);
	default:
		throw new UnsupportedOperationException("Dialect not supported");
	}
}
 

private QueryPart delegate(Configuration configuration) {
	switch (configuration.dialect().family().toString()) {
	case "MYSQL":
	case "POSTGRES":
		return DSL.field("ST_GeomFromText({0})", byte[].class, field);
	case "SQLSERVER":
		return DSL.field("{0}.STGeomFromText(0)", byte[].class, field);
	default:
		throw new UnsupportedOperationException("Dialect not supported");
	}
}
 

@Override
public FieldWrapper visit(LineStringConstant node) {
    Geometry geom = fromGeoJsonConstant(node);
    return new SimpleFieldWrapper(DSL.field(ST_GEOM_FROM_EWKT, Geometry.class, geom.asText()));
}
 

public SimpleFieldWrapper(Condition condition) {
    this.field = DSL.field(condition);
    this.condition = condition;
}
 

private FieldWrapper specificOp(String op, StaDurationWrapper other) {
    String template = "(" + INTERVAL_PARAM + " " + op + " " + INTERVAL_PARAM + ")";
    Field<OffsetDateTime> expression = DSL.field(template, OffsetDateTime.class, this.duration, other.duration);
    return new StaDateTimeWrapper(expression);
}
 

/**
 * Like {@link #arrayAgg}, but uses {@code array_remove} to eliminate
 * SQL {@code NULL} values from the result.
 *
 * @param field the field to be aggregated
 * @param <T> the type of the field
 * @return a {@link Field} representing the array aggregate
 *
 * @see <a href="http://www.postgresql.org/docs/9.3/static/functions-aggregate.html"></a>
 */
@Support({SQLDialect.POSTGRES})
public static <T> Field<T[]> arrayAggNoNulls(Field<T> field) {
    return DSL.field("array_remove(array_agg({0}), NULL)", field.getDataType().getArrayType(), field);
}
 

/**
 * Joins a set of string values using the given delimiter.
 *
 * @param field the field to be concatenated
 * @param delimiter the separating delimiter
 * @return a {@link Field} representing the joined string
 */
@Support({SQLDialect.POSTGRES})
public static Field<String> stringAgg(Field<String> field, String delimiter) {
    return DSL.field("string_agg({0}, {1})", field.getDataType(), field, DSL.val(delimiter));
}
 

/**
 * Create a new StaDuration by taking the difference between the two given
 * timestamps.
 *
 * @param ts1 The first timestamp.
 * @param ts2 The second timestamp, to be subtracted from the first.
 */
public StaDurationWrapper(final Field<OffsetDateTime> ts1, final Field<OffsetDateTime> ts2) {
    String template = "(" + TIMESTAMP_PARAM + " - " + TIMESTAMP_PARAM + ")";
    this.duration = DSL.field(template, String.class, ts1, ts2);
}