org.jooq.DSLContext Java Examples

@Test
public void testHsqlDb() throws SQLException {
    Connection conn = DbHelper.objectDb();

    DSLContext dsl = DSL
            .using(new DefaultConfiguration().set(conn)
                    .set(SQLDialect.HSQLDB)
                    .set(SfmRecordMapperProviderFactory.newInstance().ignorePropertyNotFound().newProvider()));

    List<Issue318> list = dsl.select()
            .from("issue318").fetchInto(Issue318.class);

    assertEquals(1, list.size());

    Issue318 value = list.get(0);

    assertTrue(Math.abs(value.getT().atZone(ZoneId.systemDefault()).toInstant().toEpochMilli() - System.currentTimeMillis()) < 10000);
    assertNotNull(value.getId());

}
 

private AttributeChangeRecord mkDataTypeChange(DSLContext dsl, ChangeUnit cu, PhysicalFlow flow, String name) {
    List<DataType> allDataTypes = dsl.selectFrom(DATA_TYPE)
            .fetch(DataTypeDao.TO_DOMAIN);

    List<DataType> newDataTypes = randomPick(allDataTypes, randomIntBetween(1, 5));
    String json = "["
            + joinUsing(newDataTypes, d -> String.format("{\"dataTypeId\": %s}", d.id().get()), ",")
            + "]";

    AttributeChangeRecord record = dsl.newRecord(ATTRIBUTE_CHANGE);
    record.setChangeUnitId(cu.id().get());
    record.setType("json");
    record.setOldValue("[]");
    record.setNewValue(json);
    record.setName(name);
    record.setLastUpdatedAt(DateTimeUtilities.nowUtcTimestamp());
    record.setLastUpdatedBy("admin");
    record.setProvenance(SAMPLE_DATA_PROVENANCE);
    return record;
}
 

private CommonTableExpression<Record7<Long, Long, Long, Long, String, Timestamp, Integer>>
    withRecursiveExpression(DSLContext sql, Long postId) {
    return name(PCS).fields("id", "root_id", "post_id", "parent_id", "review", "created_on", "score")
        .as(sql.select(
            POST_COMMENT.ID, POST_COMMENT.ID, POST_COMMENT.POST_ID, POST_COMMENT.PARENT_ID, POST_COMMENT.REVIEW,
            POST_COMMENT.CREATED_ON, POST_COMMENT.SCORE)
        .from(POST_COMMENT)
        .where(POST_COMMENT.POST_ID.eq(postId).and(POST_COMMENT.PARENT_ID.isNull()))
        .unionAll(
            sql.select(
                POST_COMMENT.ID, field(name("post_comment_score", "root_id"), Long.class),
                POST_COMMENT.POST_ID, POST_COMMENT.PARENT_ID, POST_COMMENT.REVIEW, POST_COMMENT.CREATED_ON,
                POST_COMMENT.SCORE)
            .from(POST_COMMENT)
            .innerJoin(table(name(PCS)))
            .on(POST_COMMENT.PARENT_ID.eq(field(name(PCS, "id"), Long.class)))
        )
    );
}
 

public CompletionStage<Result> update(final String queries) {
    return getRandomWorlds(queryCount(queries)).thenApplyAsync(worlds -> {
        final Random random = ThreadLocalRandom.current();
        for (final WorldRecord world : worlds) {
            world.setRandomnumber((random.nextInt(10000) + 1));
        }

        final int batchSize = 25;
        final int batches = ((worlds.size() / batchSize) + 1);
        this.db.withConnection(connection -> {
            final DSLContext sql = DSL.using(connection, DIALECT);
            for ( int i = 0 ; i < batches ; ++i ) {
                sql.batchUpdate(worlds.subList(i * batchSize, Math.min((i + 1) * batchSize, worlds.size()))).execute();
            }
            return null;
        });

        return ok(worlds.formatJSON(JSON_FORMAT)).as(JSON);
    }, dbEc);
}
 

default boolean deleteRatingsForCategory(DSLContext dsl,
                                         long category) {
    Condition sampleMeasurableCondition = MEASURABLE.MEASURABLE_CATEGORY_ID.eq(category)
            .and(MEASURABLE.PROVENANCE.eq(SAMPLE_DATA_PROVENANCE));

    List<Long> mIds = dsl
            .select(MEASURABLE.ID)
            .from(MEASURABLE)
            .where(sampleMeasurableCondition)
            .fetch(MEASURABLE.ID);

    dsl.deleteFrom(MEASURABLE_RATING)
            .where(MEASURABLE_RATING.MEASURABLE_ID.in(mIds));

    dsl.deleteFrom(MEASURABLE)
            .where(sampleMeasurableCondition)
            .execute();

    return true;
}
 

public static void main(String[] args) throws ParseException {

        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(DIConfiguration.class);
        DSLContext dsl = ctx.getBean(DSLContext.class);
        LogicalFlowDao dao = ctx.getBean(LogicalFlowDao.class);
        LogicalFlowService service = ctx.getBean(LogicalFlowService.class);
        ApplicationIdSelectorFactory factory = new ApplicationIdSelectorFactory();

        IdSelectionOptions options = IdSelectionOptions.mkOpts(
                mkRef(EntityKind.PERSON, 262508),
                HierarchyQueryScope.CHILDREN);

        for (int i = 0; i < 5; i++) {
            List<LogicalFlow> flows = FunctionUtilities.time("Get flows", () -> service.findBySelector(options));
        }
    }
 

@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("groups"))
                .column(field("group_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("description", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("group_id")),
                        constraint().unique(field("name"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

public static void main(String[] args) {
    AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(DIConfiguration.class);
    DSLContext dsl = ctx.getBean(DSLContext.class);


    long categoryId = 1L;

    SelectConditionStep<Record> qry = dsl
            .select(MEASURABLE.NAME, MEASURABLE.DESCRIPTION, MEASURABLE.ID, MEASURABLE.PARENT_ID, MEASURABLE.EXTERNAL_ID)
            .select(INVOLVEMENT_KIND.NAME)
            .select(PERSON.DISPLAY_NAME)
            .from(MEASURABLE)
            .leftJoin(INVOLVEMENT)
            .on(INVOLVEMENT.ENTITY_ID.eq(MEASURABLE.ID).and(INVOLVEMENT.ENTITY_KIND.eq(EntityKind.MEASURABLE.name())))
            .leftJoin(INVOLVEMENT_KIND)
            .on(INVOLVEMENT_KIND.ID.eq(INVOLVEMENT.KIND_ID))
            .leftJoin(PERSON)
            .on(PERSON.EMPLOYEE_ID.eq(INVOLVEMENT.EMPLOYEE_ID))
            .where(MEASURABLE.MEASURABLE_CATEGORY_ID.eq(categoryId));

    qry.fetch().forEach(System.out::println);

}
 

public Delete buildDelete(PathElementEntitySet set) {
    gatherData();

    DSLContext dslContext = pm.getDslContext();
    final StaMainTable<J> table = tableCollection.getTablesByType().get(set.getEntityType());
    if (table == null) {
        throw new AssertionError("Don't know how to delete" + set.getEntityType().name(), new IllegalArgumentException("Unknown type for delete"));
    }

    SelectConditionStep<Record1<J>> idSelect = DSL.select(queryState.getSqlMainIdField())
            .from(queryState.getSqlFrom())
            .where(queryState.getSqlWhere());

    DeleteConditionStep<? extends Record> delete = dslContext
            .deleteFrom(table)
            .where(
                    table.getId().in(idSelect)
            );

    if (LOGGER.isTraceEnabled()) {
        LOGGER.trace(GENERATED_SQL, delete.getSQL(ParamType.INDEXED));
    }
    return delete;
}
 

private List<String> getActiveClouds(Platform platform, Connection conn) {
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
    List<String> clouds = new ArrayList<>();
    Result<Record> consumesRecords = create.select().from(CM_CI_RELATIONS)
            .join(MD_RELATIONS).on(MD_RELATIONS.RELATION_ID.eq(CM_CI_RELATIONS.RELATION_ID))
            .join(CM_CI_RELATION_ATTRIBUTES).on(CM_CI_RELATION_ATTRIBUTES.CI_RELATION_ID.eq(CM_CI_RELATIONS.CI_RELATION_ID))
            .where(CM_CI_RELATIONS.FROM_CI_ID.eq(platform.getId()))
            .and(CM_CI_RELATION_ATTRIBUTES.DF_ATTRIBUTE_VALUE.eq("active"))
            .fetch();
    for (Record r : consumesRecords) {
        String comments = r.getValue(CM_CI_RELATIONS.COMMENTS);
        String cloudName = comments.split(":")[1];
        cloudName = cloudName.split("\"")[1];
        clouds.add(cloudName);
    }
    return clouds;
}
 

private void linkExistingObservations(Datastream d, PostgresPersistenceManager<J> pm, DSLContext dslContext, J dsId) throws NoSuchEntityException {
    for (Observation o : d.getObservations()) {
        if (o.getId() == null || !entityFactories.entityExists(pm, o)) {
            throw new NoSuchEntityException(EntityType.OBSERVATION.entityName + NO_ID_OR_NOT_FOUND);
        }
        J obsId = (J) o.getId().getValue();
        AbstractTableObservations<J> tableObs = entityFactories.tableCollection.getTableObservations();
        long oCount = dslContext.update(tableObs)
                .set(tableObs.getDatastreamId(), dsId)
                .where(tableObs.getId().eq(obsId))
                .execute();
        if (oCount > 0) {
            LOGGER.debug("Assigned datastream {} to Observation {}.", dsId, obsId);
        }
    }
}
 

public void end() {
 DSLContext jooqFactory = threadFactory.get();
 DefaultConnectionProvider conn = threadConnection.get();
  // Let's not penalize users for calling end() multiple times.
  if (null == jooqFactory) {
    return;
  }

  try {
    logger.debug("Closing JDBC connection");
    conn.acquire().close();
  } catch (SQLException e) {
    throw new RuntimeException(e);
  }
  threadFactory.remove();
  threadConnection.remove();
}
 

private int updateParentIdsUsingExtIds(DSLContext tx, long categoryId) {
    // clear existing parent ids
    dsl.update(MEASURABLE)
            .setNull(MEASURABLE.PARENT_ID)
            .where(MEASURABLE.MEASURABLE_CATEGORY_ID.eq(categoryId))
            .execute();

    Measurable c = MEASURABLE.as("c");
    Measurable p = MEASURABLE.as("p");
    return tx
            .update(c)
            .set(c.PARENT_ID, DSL
                    .select(p.ID)
                    .from(p)
                    .where(p.EXTERNAL_ID.eq(c.EXTERNAL_PARENT_ID))
                    .and(p.MEASURABLE_CATEGORY_ID.eq(categoryId)))
            .where(c.MEASURABLE_CATEGORY_ID.eq(categoryId))
            .execute();
}
 

private void createProfileVerification(Connection connection) throws SQLException {
    DSLContext create = DSL.using(connection);

    try (Statement stmt = connection.createStatement()) {
        String ddl = create.createTable(table("user_profile_verifications"))
                .column(field("user_profile_field_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("code", SQLDataType.VARCHAR(255).nullable(false)))
                .column(field("expires_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_profile_field_id"), field("user_id")),
                        constraint().foreignKey(field("user_profile_field_id"))
                                .references(table("user_profile_fields"), field("user_profile_field_id")).onDeleteCascade(),
                        constraint().foreignKey(field("user_id"))
                                .references(table("users"), field("user_id")).onDeleteCascade()
                )
                .getSQL();
        stmt.execute(ddl);
    }
}
 

@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("role_permissions"))
                .column(field("role_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("permission_id", SQLDataType.BIGINT.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("role_id"), field("permission_id")),
                        constraint().foreignKey(field("role_id")).references(table("roles"), field("role_id")).onDeleteCascade(),
                        constraint().foreignKey(field("permission_id")).references(table("permissions"), field("permission_id")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

@Override
public boolean remove(ApplicationContext ctx) {
    DSLContext dsl = getDsl(ctx);
    log("---removing demo records");
    dsl.deleteFrom(PHYSICAL_FLOW_PARTICIPANT)
            .where(PHYSICAL_FLOW_PARTICIPANT.PROVENANCE.eq(SAMPLE_DATA_PROVENANCE))
            .execute();
    return false;
}
 

static void deleteAll(@NotNull DSLContext context) {
    context.deleteFrom(JAXMOLECULARPROFILE).execute();
    context.deleteFrom(JAXTHERAPY).execute();
    context.deleteFrom(JAXINDICATION).execute();
    context.deleteFrom(JAXREFERENCE).execute();

    context.deleteFrom(JAX).execute();
}
 

private int removeNoLongerNeederCapabilities(DSLContext tx, Set<String> noLongerNeededIds) {
    return tx
            .update(MEASURABLE)
            .set(MEASURABLE.ENTITY_LIFECYCLE_STATUS, EntityLifecycleStatus.REMOVED.name())
            .where(MEASURABLE.ID.in(noLongerNeededIds))
            .execute();
}
 

public Retriever getRetriever(RequestContext requestContext) {
    Settings settings = new Settings()
            .withStatementType(StatementType.STATIC_STATEMENT);
    DSLContext create = DSL.using(DB.getDataSource(db), SQLDialect.DEFAULT, settings);
    return new SQLBasedRetriever(config, setCursorKey,
            limit, offset, selectSqlKey, matchFields,
            greaterFields, lessFields, matchFieldTypes, greaterFieldTypes, lessFieldTypes,
            create, selectFields, table, orderByFields, renameMap, requestContext, injector);
}
 

static void deleteAll(@NotNull DSLContext context) {
    context.deleteFrom(CGITRANSCRIPT).execute();
    context.deleteFrom(CGIINDIVIDUALMUTATION).execute();
    context.deleteFrom(CGIGDNA).execute();
    context.deleteFrom(CGICDNA).execute();
    context.deleteFrom(CGIINFO).execute();
    context.deleteFrom(CGIREGION).execute();
    context.deleteFrom(CGISTRAND).execute();

    context.deleteFrom(CGI).execute();
}
 

public static FullPost create(DSLContext ctx, int appId, FullPost fullPost) {
    Post post = postFromFull(fullPost);
    Post created = postDao.insertReturning(ctx, post);
    Set<String> tags = fullPost.getTags();
    List<PostTag> postTags = PostTags.findPostTagsByName(ctx, appId, tags);
    PostTags.linkTagsToPost(ctx, appId, created.getPostId(), postTags);
    return buildFullPost(created);
}
 

public RecordUnmapperBuilder(
        ClassMeta<E> classMeta,
        MapperConfig<JooqFieldKey, ?> mapperConfig,
        final Configuration configuration) {
    this(classMeta, mapperConfig, new DSLContextProvider() {
        @Override
        public DSLContext provide() {
            return DSL.using(configuration);
        }
    });
}
 

public DSLContext get() {
  DSLContext factory = threadFactory.get();
  if(null == factory) {
    throw new IllegalStateException("Requested Factory outside work unit. "
            + "Try calling UnitOfWork.begin() first, use @Transactional annotation"
            + "or use a PersistFilter if you are inside a servlet environment.");
  }

  return factory;
}
 

public static void main(String[] args) throws ParseException {

        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(DIConfiguration.class);
        DSLContext dsl = ctx.getBean(DSLContext.class);
        LogicalFlowService service = ctx.getBean(LogicalFlowService.class);

        IdSelectionOptions options = IdSelectionOptions.mkOpts(
                EntityReference.mkRef(EntityKind.ORG_UNIT, 50L),
                HierarchyQueryScope.CHILDREN);

        service.calculateStats(options);
        System.out.println("--done");

    }
 

public static void registerDataTypes(DSLContext dsl) {
	SQLDialect dialect = dsl.dialect();
	switch (dialect.family().toString()) {
	case "MYSQL":
	case "POSTGRES":
	case "SQLSERVER":
		DefaultDataType.getDefaultDataType(SQLDialect.DEFAULT, "geometry");
		break;
	}
}
 

@Override
public List<Application> searchFullText(DSLContext dsl, EntitySearchOptions options) {
    List<String> terms = mkTerms(options.searchQuery());

    return dsl
            .selectFrom(APPLICATION)
            .where(JooqUtilities.MSSQL.mkContainsPrefix(terms))
            .and(APPLICATION.ENTITY_LIFECYCLE_STATUS.in(options.entityLifecycleStatuses()))
            .limit(options.limit())
            .fetch(ApplicationDao.TO_DOMAIN_MAPPER);
}
 

@Bean
public SpringLiquibase springLiquibase(DataSource dataSource, DSLContext dsl) throws SQLException {
    dsl.createSchemaIfNotExists("test").execute();
    SpringLiquibase liquibase = new SpringLiquibase();

    // we want to drop the database if it was created before to have immutable version
    liquibase.setDropFirst(true);

    liquibase.setDataSource(dataSource);
    liquibase.setDefaultSchema("test");
    liquibase.setChangeLog("file:../waltz-data/src/main/ddl/liquibase/db.changelog-master.xml");
    return liquibase;
}
 

@Inject public GroupDAOFactory(DSLContext jooq, @Readonly DSLContext readonlyJooq,
    GroupMapper groupMapper, ObjectMapper mapper) {
  this.jooq = jooq;
  this.readonlyJooq = readonlyJooq;
  this.groupMapper = groupMapper;
  this.mapper = mapper;
}
 

public static void main(String[] args) {
    AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(DIConfiguration.class);
    DSLContext dsl = ctx.getBean(DSLContext.class);
    UserRoleService userRoleService = ctx.getBean(UserRoleService.class);

    Set<String> defaultRoles = SetUtilities.asSet(
            SystemRole.BOOKMARK_EDITOR.name(),
            SystemRole.LOGICAL_DATA_FLOW_EDITOR.name(),
            SystemRole.LINEAGE_EDITOR.name());

    Set<String> mustHaveRoles = SetUtilities.asSet(
            SystemRole.TAXONOMY_EDITOR.name(),
            SystemRole.CAPABILITY_EDITOR.name(),
            SystemRole.RATING_EDITOR.name());

    InputStream inputStream = BulkRoleAssign.class.getClassLoader().getResourceAsStream("bulk-role-assign-example.txt");
    Set<Tuple2<String, Set<String>>> updates = IOUtilities
            .streamLines(inputStream)
            .map(d -> d.toLowerCase().trim())
            .map(d -> Tuple.tuple(d, userRoleService.getUserRoles(d)))
            .map(t -> t.map2(existingRoles -> union(existingRoles, defaultRoles, mustHaveRoles)))
            .collect(Collectors.toSet());

    System.out.printf("About to update: %d user-role mappings\n", updates.size());
    updates.forEach(t -> userRoleService.updateRoles("admin", t.v1, t.v2));
    System.out.println("Finished updating mappings");
}
 

static void deleteAll(@NotNull DSLContext context) {
    // First delete the tables dependent on BRCA
    context.deleteFrom(BRCAANNOTATION1000GENOMES).execute();
    context.deleteFrom(BRCAANNOTATIONBIC).execute();
    context.deleteFrom(BRCAANNOTATIONCLINVAR).execute();
    context.deleteFrom(BRCAANNOTATIONENIGMA).execute();
    context.deleteFrom(BRCAANNOTATIONESP).execute();
    context.deleteFrom(BRCAANNOTATIONEXAC).execute();
    context.deleteFrom(BRCAANNOTATIONEXLOVD).execute();
    context.deleteFrom(BRCAANNOTATIONLOVD).execute();

    // Then delete the main object
    context.deleteFrom(BRCA).execute();
}