org.jooq.impl.DSL Java Examples

public Collection<RatingScheme> findAll() {
    Map<Long, Collection<RagName>> itemsByScheme = groupBy(
            d -> d.ratingSchemeId(),
            fetchItems(DSL.trueCondition()));

    return dsl
            .selectFrom(RATING_SCHEME)
            .fetch(TO_SCHEME_MAPPER)
            .stream()
            .map(s -> ImmutableRatingScheme
                    .copyOf(s)
                    .withRatings(itemsByScheme.getOrDefault(
                            s.id().get(),
                            Collections.emptyList())))
            .collect(Collectors.toList());
}
 

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("oidc_users"))
                .column(field("oidc_provider_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("oidc_sub", SQLDataType.VARCHAR(255).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("oidc_provider_id"), field("user_id")),
                        constraint().foreignKey(field("oidc_provider_id")).references(table("oidc_providers"), field("oidc_provider_id")).onDeleteCascade(),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

public List<Tuple2<Long, BigDecimal>> calculateCombinedAmountsForSelector(int year, Select<Record1<Long>> appIdSelector) {
    checkNotNull(appIdSelector, "appIdSelector cannot be null");

    Field<BigDecimal> totalAmount = DSL.sum(ASSET_COST.AMOUNT).as("total_amount");

    Condition condition = ASSET_COST.YEAR.eq(year)
            .and(APPLICATION.ID.in(appIdSelector));

    return dsl.select(APPLICATION.ID, totalAmount)
            .from(ASSET_COST)
            .innerJoin(APPLICATION)
            .on(APPLICATION.ASSET_CODE.eq(ASSET_COST.ASSET_CODE))
            .where(dsl.renderInlined(condition))
            .groupBy(APPLICATION.ID)
            .fetch(r -> Tuple.tuple(r.value1(), r.value2()));
}
 

private void createProfileFields(Connection connection) throws SQLException {
    DSLContext create = DSL.using(connection);
    try (Statement stmt = connection.createStatement()) {
        String ddl = create.createTable(table("user_profile_fields"))
                .column(field("user_profile_field_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("json_name", SQLDataType.VARCHAR(100).nullable(true)))
                .column(field("is_required", SQLDataType.BOOLEAN.nullable(false)))
                .column(field("is_identity", SQLDataType.BOOLEAN.nullable(false)))
                .column(field("regular_expression", SQLDataType.VARCHAR(255).nullable(true)))
                .column(field("min_length", SQLDataType.SMALLINT.nullable(true)))
                .column(field("max_length", SQLDataType.SMALLINT.nullable(true)))
                .column(field("needs_verification", SQLDataType.BOOLEAN.nullable(false)))
                .column(field("position", SQLDataType.TINYINT.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_profile_field_id")),
                        constraint().unique(field("name")),
                        constraint().unique(field("json_name"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

public Operation save(SaveMeasurableRatingCommand command) {
    MeasurableRatingRecord record = TO_RECORD_MAPPER.apply(command);

    boolean exists = dsl.fetchExists(DSL
            .selectFrom(MEASURABLE_RATING)
            .where(MEASURABLE_RATING.MEASURABLE_ID.eq(command.measurableId()))
            .and(MEASURABLE_RATING.ENTITY_ID.eq(command.entityReference().id()))
            .and(MEASURABLE_RATING.ENTITY_KIND.eq(command.entityReference().kind().name())));

    if (exists) {
        if (dsl.executeUpdate(record) == 0) {
            throw new NotFoundException(
                    "MR_SAVE_UPDATE_FAILED",
                    format("Could find associated record to update for rating: %s", command));
        };
        return Operation.UPDATE;
    } else {
        if (dsl.executeInsert(record) != 1) {
            throw new NotFoundException(
                    "MR_SAVE_INSERT_FAILED",
                    format("Creation of record failed: %s", command));
        };
        return Operation.ADD;
    }
}
 

@Test
public void testFindUsersWithJOOQ() {

    //Query query =     em.createQuery("select u from User u where u.address.country = 'Norway'");
    //Query query = em.createNativeQuery("select * from User where country = 'Norway'");

    DSLContext create = DSL.using(SQLDialect.H2);
    String sql = create
            .select()
            .from(table("User"))
            .where(field("country").eq("Norway"))
            .getSQL(ParamType.INLINED);

    Query query = em.createNativeQuery(sql, User.class);

    List<User> results = query.getResultList();

    assertEquals(3, results.size());

    /*
       JOOQ is a popular, easy to use DSL for writing SQL (not JPQL).
       Besides type-safety and IDE code-completion, one HUGE benefit
       is that the SQL is targeted for the specific dialect of the
       target DB.
     */
}
 

public void deleteSecretSeriesById(long id) {
  long now = OffsetDateTime.now().toEpochSecond();
  dslContext.transaction(configuration -> {
    // find the record and lock it until this transaction is complete
    SecretsRecord r = DSL.using(configuration)
        .select()
        .from(SECRETS)
        .where(SECRETS.ID.eq(id).and(SECRETS.CURRENT.isNotNull()))
        .forUpdate()
        .fetchOneInto(SECRETS);
    if (r != null) {
      DSL.using(configuration)
          .update(SECRETS)
          .set(SECRETS.NAME, transformNameForDeletion(r.getName()))
          .set(SECRETS.CURRENT, (Long) null)
          .set(SECRETS.UPDATEDAT, now)
          .where(SECRETS.ID.eq(id))
          .execute();
      DSL.using(configuration)
          .delete(ACCESSGRANTS)
          .where(ACCESSGRANTS.SECRETID.eq(id))
          .execute();
    }
  });
}
 

public int rippleDataTypesToLogicalFlows() {
    return dsl.insertInto(LOGICAL_FLOW_DECORATOR)
            .select(DSL
                    .selectDistinct(
                            PHYSICAL_FLOW.LOGICAL_FLOW_ID,
                            DSL.val(DATA_TYPE.name()),
                            PHYSICAL_SPEC_DATA_TYPE.DATA_TYPE_ID,
                            DSL.val(AuthoritativenessRating.NO_OPINION.name()),
                            DSL.val("waltz"),
                            DSL.val(Timestamp.valueOf(nowUtc())),
                            DSL.val("admin"))
                    .from(PHYSICAL_SPEC_DATA_TYPE)
                    .join(PHYSICAL_FLOW).on(PHYSICAL_SPEC_DATA_TYPE.SPECIFICATION_ID.eq(PHYSICAL_FLOW.SPECIFICATION_ID))
                    .leftJoin(LOGICAL_FLOW_DECORATOR)
                        .on(LOGICAL_FLOW_DECORATOR.LOGICAL_FLOW_ID.eq(PHYSICAL_FLOW.LOGICAL_FLOW_ID)
                            .and(LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_ID.eq(PHYSICAL_SPEC_DATA_TYPE.DATA_TYPE_ID)))
                    .where(LOGICAL_FLOW_DECORATOR.LOGICAL_FLOW_ID.isNull()))
            .execute();
}
 

public Cost calculateTotalCostByAppIdSelector(int year, Select<Record1<Long>> appIdSelector) {
    Condition optionsCondition =
            APPLICATION.ID.in(appIdSelector)
                    .and(ASSET_COST.YEAR.eq(year));

    return dsl.select(DSL.coalesce(DSL.sum(ASSET_COST.AMOUNT), BigDecimal.ZERO))
            .from(ASSET_COST)
            .innerJoin(APPLICATION)
            .on(APPLICATION.ASSET_CODE.eq(ASSET_COST.ASSET_CODE))
            .where(optionsCondition)
            .fetchOne(r -> ImmutableCost.builder()
                    .amount(r.value1())
                    .costKind(COST_KIND_TOTAL)
                    .year(year)
                    .build());
}
 

private static Select<Record1<Long>> mkAppIdSelector(ApplicationIdSelectorFactory appIdSelectorFactory) {
    EntityReference infraRef = mkRef(EntityKind.ORG_UNIT, 6811);
    EntityReference entRiskRef = mkRef(EntityKind.ORG_UNIT, 3125);
    EntityReference regCtrlRef = mkRef(EntityKind.ORG_UNIT, 2761);

    Function<EntityReference, Select<Record1<Long>>> mkOrgUnitSelector = (ref) -> DSL
            .select(ENTITY_HIERARCHY.ID)
            .from(ENTITY_HIERARCHY)
            .where(ENTITY_HIERARCHY.ANCESTOR_ID.eq(ref.id()))
            .and(ENTITY_HIERARCHY.KIND.eq(ref.kind().name()));

    Select<Record1<Long>> ouSelector = DSL.selectFrom(
            mkOrgUnitSelector.apply(infraRef)
                .unionAll(mkOrgUnitSelector.apply(entRiskRef))
                .unionAll(mkOrgUnitSelector.apply(regCtrlRef)).asTable());

    return DSL
            .select(APPLICATION.ID)
            .from(APPLICATION)
            .where(APPLICATION.ORGANISATIONAL_UNIT_ID.in(ouSelector))
            .and(APPLICATION.LIFECYCLE_PHASE.notEqual(EntityLifecycleStatus.REMOVED.name()))
            .and(APPLICATION.IS_REMOVED.isFalse());

}
 

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

    Condition applicationConditions = mkApplicationConditions(options);
    return DSL
            .selectDistinct(SCENARIO_RATING_ITEM.DOMAIN_ITEM_ID)
            .from(SCENARIO_RATING_ITEM)
            .innerJoin(APPLICATION).on(APPLICATION.ID.eq(SCENARIO_RATING_ITEM.DOMAIN_ITEM_ID))
            .and(SCENARIO_RATING_ITEM.DOMAIN_ITEM_KIND.eq(EntityKind.APPLICATION.name()))
            .where(SCENARIO_RATING_ITEM.SCENARIO_ID.eq(options.entityReference().id()))
            .and(applicationConditions);
}
 

@Override
public int unstar(Project project, User user, List<String> documentIds) {
    return DSL.using(connectionProvider, dialect).deleteFrom(DOCUMENT_USER_STAR).
            where(DOCUMENT_USER_STAR.DOC_ID.in(documentIds),
                    DOCUMENT_USER_STAR.USER_ID.eq(user.id),
                    DOCUMENT_USER_STAR.PRJ_ID.eq(project.getId())).execute();
}
 

private Select<Record1<Long>> mkForLogicalFlow(IdSelectionOptions options) {
    ensureScopeIsExact(options);
    return DSL
            .selectDistinct(LOGICAL_FLOW_DECORATOR.DECORATOR_ENTITY_ID)
            .from(LOGICAL_FLOW_DECORATOR)
            .where(LOGICAL_FLOW_DECORATOR.LOGICAL_FLOW_ID.eq(options.entityReference().id()));
}
 

/**
 * Create the Container History table.
 */
private void createContainerHistoryTable() {
  dslContext.createTableIfNotExists(CONTAINER_HISTORY_TABLE_NAME)
      .column(CONTAINER_ID, SQLDataType.BIGINT)
      .column("datanode_host", SQLDataType.VARCHAR(1024))
      .column("first_report_timestamp", SQLDataType.BIGINT)
      .column("last_report_timestamp", SQLDataType.BIGINT)
      .constraint(DSL.constraint("pk_container_id_datanode_host")
          .primaryKey(CONTAINER_ID, "datanode_host"))
      .execute();
}
 

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

private Select<Record1<Long>> mkForLogicalElement(IdSelectionOptions options) {
    ensureScopeIsExact(options);
    long logicalElementId = options.entityReference().id();
    return DSL.select(PHYSICAL_SPEC_DEFN_FIELD.ID)
            .from(PHYSICAL_SPEC_DEFN_FIELD)
            .where(PHYSICAL_SPEC_DEFN_FIELD.LOGICAL_DATA_ELEMENT_ID.eq(logicalElementId));
}
 

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

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        createUserLicense(create, stmt);
        createLastActivity(create, stmt);
    }
}
 

@Override
public FieldWrapper visit(Day node) {
    Expression param = node.getParameters().get(0);
    FieldWrapper input = param.accept(this);
    if (input instanceof TimeFieldWrapper) {
        TimeFieldWrapper timeExpression = (TimeFieldWrapper) input;
        return new SimpleFieldWrapper(DSL.extract(timeExpression.getDateTime(), DatePart.DAY));
    }
    throw new IllegalArgumentException("Day can only be used on times, not on " + input.getClass().getName());
}
 

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

@Test
public void givenValidData_whenInserting_thenSucceed() {
    dsl.insertInto(AUTHOR)
            .set(AUTHOR.ID, 4)
            .set(AUTHOR.FIRST_NAME, "Herbert")
            .set(AUTHOR.LAST_NAME, "Schildt")
            .execute();

    dsl.insertInto(BOOK)
            .set(BOOK.ID, 4)
            .set(BOOK.TITLE, "A Beginner's Guide")
            .execute();

    dsl.insertInto(AUTHOR_BOOK)
            .set(AUTHOR_BOOK.AUTHOR_ID, 4)
            .set(AUTHOR_BOOK.BOOK_ID, 4)
            .execute();

    final Result<Record3<Integer, String, Integer>> result = dsl.select(AUTHOR.ID, AUTHOR.LAST_NAME, DSL.count())
            .from(AUTHOR)
            .join(AUTHOR_BOOK).on(AUTHOR.ID.equal(AUTHOR_BOOK.AUTHOR_ID))
            .join(BOOK).on(AUTHOR_BOOK.BOOK_ID.equal(BOOK.ID))
            .groupBy(AUTHOR.LAST_NAME)
            .orderBy(AUTHOR.LAST_NAME.desc())
            .fetch();

    assertEquals(3, result.size());
    assertEquals("Sierra", result.getValue(0, AUTHOR.LAST_NAME));
    assertEquals(Integer.valueOf(2), result.getValue(0, DSL.count()));
    assertEquals("Bates", result.getValue(2, AUTHOR.LAST_NAME));
    assertEquals(Integer.valueOf(1), result.getValue(2, DSL.count()));
}
 

public boolean canUpdate(long groupId, String userId) {
    return dsl.select(DSL.value(Boolean.TRUE))
            .from(APPLICATION_GROUP_MEMBER)
            .where(APPLICATION_GROUP_MEMBER.USER_ID.eq(userId))
            .and(APPLICATION_GROUP_MEMBER.GROUP_ID.eq(groupId))
            .and(APPLICATION_GROUP_MEMBER.ROLE.eq(AppGroupMemberRole.OWNER.name()))
            .fetchOne() != null;
}
 

public boolean isPersonInstanceRecipient(long personId, long surveyInstanceId) {
    Condition recipientExists = DSL.exists(DSL.selectFrom(SURVEY_INSTANCE_RECIPIENT)
            .where(SURVEY_INSTANCE_RECIPIENT.SURVEY_INSTANCE_ID.eq(surveyInstanceId)
                    .and(SURVEY_INSTANCE_RECIPIENT.PERSON_ID.eq(personId))));

    return dsl.select(DSL.when(recipientExists, true).otherwise(false))
            .fetchOne(Record1::value1);
}
 

/**
 * 选择表中的一列数据
 * @param sql sql 语句
 * @return 表中的一列数据
 */
public List<String> selectColumn(String sql) {
    // 执行 sql 并获取查询结果
    Result<Record> records = DSL.using(url, username, password).fetch(sql);

    // 获取第 1 列的值
    return records.getValues(0, String.class);
}
 

@Override
public List<EntityReference> findByIdSelectorAsEntityReference(Select<Record1<Long>> selector) {
    checkNotNull(selector, "selector cannot be null");
    return dsl.select(CHANGE_INITIATIVE.ID, CHANGE_INITIATIVE.NAME, DSL.val(EntityKind.CHANGE_INITIATIVE.name()))
            .from(CHANGE_INITIATIVE)
            .where(CHANGE_INITIATIVE.ID.in(selector))
            .fetch(TO_ENTITY_REFERENCE);
}
 

private Select<Record1<Long>> mkForScenario(IdSelectionOptions options) {
    ensureScopeIsExact(options);
    return DSL
            .selectDistinct(SCENARIO_AXIS_ITEM.DOMAIN_ITEM_ID)
            .from(SCENARIO_AXIS_ITEM)
            .where(SCENARIO_AXIS_ITEM.SCENARIO_ID.eq(options.entityReference().id()))
            .and(SCENARIO_AXIS_ITEM.DOMAIN_ITEM_KIND.eq(EntityKind.MEASURABLE.name()));
}
 

@Override
public void sql(BindingSQLContext<JsonObject> ctx) {
    // Depending on how you generate your SQL, you may need to explicitly distinguish
    // between jOOQ generating bind variables or inlined literals. If so, use this check:
    // ctx.render().paramType() == INLINED
    RenderContext context = ctx.render().visit(DSL.val(ctx.convert(converter()).value()));
    if (SQLDialect.POSTGRES.equals(ctx.configuration().dialect().family()))
    {
        context.sql("::json");
    }
}
 

public DSLContext getDSLContext() {
    if (dslContext == null) {
        Configuration defaultConfiguration = new DefaultConfiguration().set(getConnection())
                .set(SQLDialect.MYSQL);

        dslContext = DSL.using(defaultConfiguration);
    }

    return dslContext;
}
 

private List<SurveyRunCompletionRate> calcCompletionRateForSurveyRuns(Condition surveyRunSelectionCondition) {
    Field<Integer> statCount = DSL.count(SURVEY_INSTANCE.ID).as("statCount");

    final Result<Record3<Long, String, Integer>> countsByRunAndStatus = dsl
            .select(SURVEY_INSTANCE.SURVEY_RUN_ID,
                    SURVEY_INSTANCE.STATUS,
                    statCount)
            .from(SURVEY_INSTANCE)
            .where(surveyRunSelectionCondition)
            .and(IS_ORIGINAL_INSTANCE_CONDITION)
            .groupBy(SURVEY_INSTANCE.SURVEY_RUN_ID, SURVEY_INSTANCE.STATUS)
            .fetch();

    Map<Long, ImmutableSurveyRunCompletionRate.Builder> buildersByRunId = new HashMap<>();
    countsByRunAndStatus.forEach(r -> {
        Long runId = r.get(SURVEY_INSTANCE.SURVEY_RUN_ID);
        ImmutableSurveyRunCompletionRate.Builder inProgressBuilder = buildersByRunId.getOrDefault(
                runId,
                ImmutableSurveyRunCompletionRate.builder().surveyRunId(runId));
        if (isStatTypeOf(r, SurveyInstanceStatus.NOT_STARTED)) {
            inProgressBuilder.notStartedCount(r.get(statCount));
        }
        if (isStatTypeOf(r, SurveyInstanceStatus.IN_PROGRESS)) {
            inProgressBuilder.inProgressCount(r.get(statCount));
        }
        if (isStatTypeOf(r, SurveyInstanceStatus.COMPLETED)) {
            inProgressBuilder.completedCount(r.get(statCount));
        }

        buildersByRunId.put(runId, inProgressBuilder);
    });

    return buildersByRunId
            .values()
            .stream()
            .map(ImmutableSurveyRunCompletionRate.Builder::build)
            .collect(Collectors.toList());
}
 

@Override
public FieldWrapper visit(FractionalSeconds node) {
    Expression param = node.getParameters().get(0);
    FieldWrapper input = param.accept(this);
    if (input instanceof TimeFieldWrapper) {
        TimeFieldWrapper timeExpression = (TimeFieldWrapper) input;
        return new SimpleFieldWrapper(DSL.field("(date_part('SECONDS', TIMESTAMPTZ ?) - floor(date_part('SECONDS', TIMESTAMPTZ ?)))", Double.class, timeExpression.getDateTime(), timeExpression.getDateTime()));
    }
    throw new IllegalArgumentException("FractionalSeconds can only be used on times, not on " + input.getClass().getName());
}