org.jooq.impl.DSL Java Examples
The following examples show how to use
org.jooq.impl.DSL.
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: RatingSchemeDAO.java From waltz with Apache License 2.0 | 6 votes |
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()); }
Example #2
Source File: V15__CreateOidcUsers.java From bouncr with Eclipse Public License 1.0 | 6 votes |
@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); } }
Example #3
Source File: AssetCostDao.java From waltz with Apache License 2.0 | 6 votes |
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())); }
Example #4
Source File: V21__CreateUserProfiles.java From bouncr with Eclipse Public License 1.0 | 6 votes |
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); } }
Example #5
Source File: MeasurableRatingDao.java From waltz with Apache License 2.0 | 6 votes |
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; } }
Example #6
Source File: UserTest.java From testing_security_development_enterprise_systems with GNU Lesser General Public License v3.0 | 6 votes |
@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. */ }
Example #7
Source File: SecretSeriesDAO.java From keywhiz with Apache License 2.0 | 6 votes |
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(); } }); }
Example #8
Source File: PhysicalSpecDecoratorDao.java From waltz with Apache License 2.0 | 6 votes |
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(); }
Example #9
Source File: AssetCostStatsDao.java From waltz with Apache License 2.0 | 6 votes |
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()); }
Example #10
Source File: FlowSummaryWithTypesAndPhysicalsExport.java From waltz with Apache License 2.0 | 6 votes |
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()); }
Example #11
Source File: ApplicationIdSelectorFactory.java From waltz with Apache License 2.0 | 5 votes |
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); }
Example #12
Source File: JooqRepository.java From datashare with GNU Affero General Public License v3.0 | 5 votes |
@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(); }
Example #13
Source File: DataTypeIdSelectorFactory.java From waltz with Apache License 2.0 | 5 votes |
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())); }
Example #14
Source File: ContainerSchemaDefinition.java From hadoop-ozone with Apache License 2.0 | 5 votes |
/** * 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(); }
Example #15
Source File: GeomFromText.java From java-crud-api with MIT License | 5 votes |
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"); } }
Example #16
Source File: PhysicalSpecDefnFieldIdSelectorFactory.java From waltz with Apache License 2.0 | 5 votes |
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)); }
Example #17
Source File: IsValid.java From java-crud-api with MIT License | 5 votes |
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"); } }
Example #18
Source File: V1__CreateUserLicense.java From bouncr with Eclipse Public License 1.0 | 5 votes |
@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); } }
Example #19
Source File: PgExpressionHandler.java From FROST-Server with GNU Lesser General Public License v3.0 | 5 votes |
@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()); }
Example #20
Source File: Contains.java From java-crud-api with MIT License | 5 votes |
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"); } }
Example #21
Source File: QueryIntegrationTest.java From tutorials with MIT License | 5 votes |
@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())); }
Example #22
Source File: AppGroupMemberDao.java From waltz with Apache License 2.0 | 5 votes |
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; }
Example #23
Source File: SurveyInstanceRecipientDao.java From waltz with Apache License 2.0 | 5 votes |
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); }
Example #24
Source File: SqlSelector.java From SqlFaker with Apache License 2.0 | 5 votes |
/** * 选择表中的一列数据 * @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); }
Example #25
Source File: ChangeInitiativeDao.java From waltz with Apache License 2.0 | 5 votes |
@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); }
Example #26
Source File: MeasurableIdSelectorFactory.java From waltz with Apache License 2.0 | 5 votes |
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())); }
Example #27
Source File: ObjectToJsonObjectBinding.java From vertx-jooq with MIT License | 5 votes |
@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"); } }
Example #28
Source File: ScopedContext.java From JOOQ with Apache License 2.0 | 5 votes |
public DSLContext getDSLContext() { if (dslContext == null) { Configuration defaultConfiguration = new DefaultConfiguration().set(getConnection()) .set(SQLDialect.MYSQL); dslContext = DSL.using(defaultConfiguration); } return dslContext; }
Example #29
Source File: SurveyInstanceDao.java From waltz with Apache License 2.0 | 5 votes |
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()); }
Example #30
Source File: PgExpressionHandler.java From FROST-Server with GNU Lesser General Public License v3.0 | 5 votes |
@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()); }