Java 类org.jooq.impl.DSL 实例源码

@Override
public void saveTaxCodes(final Iterable<EasyTaxTaxCode> taxCodes) throws SQLException {
    final DateTime now = new DateTime();
    execute(dataSource.getConnection(), new WithConnectionCallback<Void>() {
        @Override
        public Void withConnection(final Connection conn) throws SQLException {
            DSL.using(conn, dialect, settings).transaction(new TransactionalRunnable() {
                @Override
                public void run(final Configuration configuration) throws Exception {
                    final DSLContext dslContext = DSL.using(configuration);
                    for (EasyTaxTaxCode taxCode : taxCodes) {
                        DateTime date = taxCode.getCreatedDate() != null
                                ? taxCode.getCreatedDate()
                                : now;
                        saveTaxCodeInternal(taxCode, date, dslContext);
                    }
                }
            });
            return null;
        }
    });
}

public void setUp() throws Exception {
  connection = DriverManager.getConnection("jdbc:hsqldb:mem:myDb");
  context = DSL.using(connection, SQLDialect.HSQLDB, new Settings().withRenderNameStyle(
      RenderNameStyle.AS_IS));

  final List<Field<String>> fields = getFields();

  context.createTable(relationName)
      .columns(fields)
      .execute();

  try (InputStream in = resourceClass.getResourceAsStream(csvPath)) {
    final Loader<Record> loader = context.loadInto(table(name(relationName)))
        .loadCSV(in)
        .fields(fields)
        .execute();

    assertThat(loader.errors()).isEmpty();
  }
}

private void init(Connection conn) {
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
    Result<Record> coresAttributes = create.select().from(MD_CLASS_ATTRIBUTES)
    .join(MD_CLASSES).on(MD_CLASS_ATTRIBUTES.CLASS_ID.eq(MD_CLASSES.CLASS_ID))
    .where(MD_CLASSES.CLASS_NAME.like("bom%Compute"))
    .and(MD_CLASS_ATTRIBUTES.ATTRIBUTE_NAME.eq("cores")).fetch();

    for (Record coresAttribute : coresAttributes) {
        coresAttributeIds.add(coresAttribute.getValue(MD_CLASS_ATTRIBUTES.ATTRIBUTE_ID));
    }

    create = DSL.using(conn, SQLDialect.POSTGRES);
    Result<Record> computeClasses = create.select().from(MD_CLASSES)
            .where(MD_CLASSES.CLASS_NAME.like("bom%Compute")).fetch();
    for (Record computeClass : computeClasses) {
        computeClassIds.add(computeClass.get(MD_CLASSES.CLASS_ID));
    }
    log.info("cached compute class ids: " + computeClassIds);
    log.info("cached compute cores attribute ids: " + coresAttributeIds);
}

@Provides
@Singleton
static DSLContext dbContext(
    DataSource dataSource, @ForDatabase ListeningExecutorService dbExecutor) {
  Configuration configuration =
      new DefaultConfiguration()
          .set(dbExecutor)
          .set(SQLDialect.MYSQL)
          .set(new Settings().withRenderSchema(false))
          .set(new DataSourceConnectionProvider(dataSource))
          .set(DatabaseUtil.sfmRecordMapperProvider());
  DSLContext ctx = DSL.using(configuration);
  // Eagerly trigger JOOQ classinit for better startup performance.
  ctx.select().from("curio_server_framework_init").getSQL();
  return ctx;
}

@Override
public void addTaxation(final EasyTaxTaxation taxation) throws SQLException {
    final String invoiceItemIdTaxMappingJson = encodeInvoiceItemIdTaxMapping(
            taxation.getKbInvoiceId(), taxation.getInvoiceItemIds());
    execute(dataSource.getConnection(), new WithConnectionCallback<Void>() {
        @Override
        public Void withConnection(final Connection conn) throws SQLException {
            DSL.using(conn, dialect, settings)
                    .insertInto(EASYTAX_TAXATIONS, EASYTAX_TAXATIONS.KB_TENANT_ID,
                            EASYTAX_TAXATIONS.KB_ACCOUNT_ID, EASYTAX_TAXATIONS.KB_INVOICE_ID,
                            EASYTAX_TAXATIONS.KB_INVOICE_ITEM_IDS, EASYTAX_TAXATIONS.TOTAL_TAX,
                            EASYTAX_TAXATIONS.CREATED_DATE)
                    .values(taxation.getKbTenantId().toString(),
                            taxation.getKbAccountId().toString(),
                            taxation.getKbInvoiceId().toString(), invoiceItemIdTaxMappingJson,
                            taxation.getTotalTax(), taxation.getCreatedDate())
                    .execute();
            return null;
        }
    });
}

/**
 * Returns the amount of ins or combs for the given condition.
 * in == true returns all "ins" / false all "combs"
 */
private int getVoteAmount(final Condition cond) {
    return DSL.using(jooqConfig).
            select(DSL.count()).
            from(VOTE_TABLE).
            where(cond).fetchOne(DSL.count());
}

/**
 * Returns the amount of ins or combs for the given condition.
 * in == true returns all "ins" / false all "combs"
 */
private int getVoteAmount(final Condition cond, final boolean in) {
    return DSL.using(jooqConfig).
            select(DSL.count()).
            from(VOTE_TABLE).
            where(cond).
            and(VOTE_TABLE.UP.eq(in)).fetchOne(DSL.count());
}

/**
 * Returns all content-votes for the given news article.
 */
private List<ContentVote> getContentVotes(final long contentId, final boolean up) {
    return DSL.using(jooqConfig).
            select().
            from(VOTE_TABLE).
            where(VOTE_TABLE.CONTENT_ID.eq(contentId)).
            and(VOTE_TABLE.UP.eq(up)).fetchInto(ContentVote.class);
}

/**
 * Returns the user id for the given username.
 * @param username the username to find the {@link User}.
 * @return the id of the {@link User}.
 */
private long getIdForUsername(final String username) {
    final Record1<Long> idRecord = DSL.using(jooqConfig).
            select(TABLE.ID).
            from(TABLE).
            where(TABLE.USERNAME.eq(username)).
            fetchOne();

    if(idRecord == null) {
        return 0;
    }

    return idRecord.getValue(TABLE.ID);
}

/**
 * Returns the {@link News} from the database.
 * The result isn't sorted. This method can be used for reindexing.
 * @param start start index of the results
 * @param amount the amount of {@link News}s to return after the start index.
 * @return {@link List} containing {@link News}
 */
public List<News> getNews(final int start, final int amount) {
    return DSL.using(jooqConfig).
            select().
            from(TABLE_CONTENT.join(TABLE_NEWS, JoinType.JOIN).
                    on(TABLE_CONTENT.ID.eq(TABLE_NEWS.CONTENT_ID))).
            limit(start, amount).
            fetchInto(News.class);
}

/**
     * Checks if a {@link News} already exists for the given link. If one exists
     * then the {@link Defaults} of this are returned. If not then <code>null</code>
     * will be returned.
     *
     * @param news the {@link News} to find a {@link News} with the same link or name.
     * @return {@link Defaults} or <code>null</code>
     */
    private Defaults getDefaultsForNews(final News news) {
        final Defaults result = DSL.using(jooqConfig).
                select(TABLE_CONTENT.ID, TABLE_CONTENT.PUBLISH_DATE, TABLE_NEWS.NEWS_GROUP_ID,
                        TABLE_CONTENT.TITLE, TABLE_CONTENT.TEXT, TABLE_NEWS.IMAGE_URL,
                        TABLE_NEWS.IMAGE_WIDTH, TABLE_NEWS.IMAGE_HEIGHT, TABLE_NEWS.LINK).
                from(TABLE_CONTENT.join(TABLE_NEWS, JoinType.JOIN).
                        on(TABLE_CONTENT.ID.eq(TABLE_NEWS.CONTENT_ID))).
                where(TABLE_NEWS.LINK.eq(news.getLink())).
                    or(TABLE_CONTENT.TITLE.eq(news.getTitle()).
                        and(TABLE_CONTENT.PROVIDER_ID.eq(news.getProviderId())).
                        and(TABLE_CONTENT.PUBLISH_DATE.between(getDate(news, PUBLISHDATE_DELTA),
                                getDate(news, -PUBLISHDATE_DELTA)))).
                fetchOneInto(Defaults.class);

        // no news found -> exit with null.
/*      if(result == null) {
            return null;
        }

        final Defaults defaults = new Defaults();
        defaults.setId(id); = result.getValue(TABLE_NEWS.CONTENT_ID);
        defaults.publishDate = result.getValue(TABLE_CONTENT.PUBLISH_DATE);
        defaults.newsGroupId = result.getValue(TABLE_NEWS.NEWS_GROUP_ID);
*/
        return result;
    }

/**
 * Returns the greatest news id.
 * Can be used to get the auto incremented value after a {@link News} was inserted.
 */
private long getLastId() {
    final Record1<Long> result = DSL.using(jooqConfig).
            select(TABLE_CONTENT.ID).
            from(TABLE_CONTENT).
            orderBy(TABLE_CONTENT.ID.desc()).
            limit(0, 1).
            fetchOne();

    return result == null ? 0 : result.getValue(TABLE_CONTENT.ID);
}

static Stream<Arguments> incompleteMapOfFilterMultipleKeys() {
    return Stream.of(
        Arguments.of(
            ImmutableMap.of("key1", "value1", "key2", "12:25:30", "key3", "2017-05-17T12:25:30"),
            Arrays.asList(Filter.of("key1", "missingKey", v1 -> "val1", v2 -> "val2", (val1, val2) -> DSL.trueCondition()), Filter.of("key3", DSL::trueCondition)))
    );
}

/**
 * Returns all {@link User}s who are flying with the given {@link User}.
 */
public List<User> getWithFlyingsOf(final long userId) {
    return DSL.using(jooqConfig).
        select(USER_TABLE.fields()).
        from(USER_TABLE).
        join(FLY_WITH_TABLE).
            on(USER_TABLE.ID.eq(FLY_WITH_TABLE.USER_ID)).
        where(FLY_WITH_TABLE.FLY_WITH_ID.eq(userId)).
        fetchInto(User.class);
}

/**
 * Returns all {@link User} which the given {@link User} is flying with.
 */
public List<User> getFlyWiths(final long userId) {
    return DSL.using(jooqConfig).
            select(USER_TABLE.fields()).
            from(USER_TABLE).
            join(FLY_WITH_TABLE).
                on(USER_TABLE.ID.eq(FLY_WITH_TABLE.FLY_WITH_ID)).
            where(FLY_WITH_TABLE.USER_ID.eq(userId)).
            fetchInto(User.class);
}

@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("user_locks"))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("locked_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_id")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id"))
                ).getSQL();
        stmt.execute(ddl);
    }
}

private void populatePlatform(Connection conn, Platform platform) {
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
    Result<Record> computes = create.select().from(CM_CI)
            .join(NS_NAMESPACES).on(NS_NAMESPACES.NS_ID.eq(CM_CI.NS_ID))
            .join(CM_CI_ATTRIBUTES).on(CM_CI_ATTRIBUTES.CI_ID.eq(CM_CI.CI_ID))
            .where(NS_NAMESPACES.NS_PATH.eq(platform.getPath())
            .and(CM_CI.CLASS_ID.in(computeClassIds))
            .and(CM_CI_ATTRIBUTES.ATTRIBUTE_ID.in(coresAttributeIds)))
            .fetch();

    platform.setTotalComputes(computes.size());
    int totalCores = 0;
    if (platform.getTotalComputes() > 0) {
        for (Record compute : computes) {
            totalCores += Integer.parseInt(compute.get(CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE));
        }
    }
    platform.setTotalCores(totalCores);

    //Now query platform ci attributes and set to the object
    Result<Record> platformAttributes = create.select().from(CM_CI_ATTRIBUTES)
            .join(MD_CLASS_ATTRIBUTES).on(MD_CLASS_ATTRIBUTES.ATTRIBUTE_ID.eq(CM_CI_ATTRIBUTES.ATTRIBUTE_ID))
            .where(CM_CI_ATTRIBUTES.CI_ID.eq(platform.getId()))
            .fetch();

    for ( Record attribute : platformAttributes ) {
        String attributeName = attribute.getValue(MD_CLASS_ATTRIBUTES.ATTRIBUTE_NAME);
        if (attributeName.equalsIgnoreCase("source")) {
            platform.setSource(attribute.getValue(CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE));
        } else if (attributeName.equalsIgnoreCase("pack")) {
            platform.setPack(attribute.getValue(CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE));
        }
    }
}

@Before
public void clear() throws IOException {
    IndexTestUtil.deleteAllDocuments(NewsIndexType.getInstance());
    DSL.using(con).delete(new CategoryPreferenceTable()).execute();
    //DSL.using(con).truncate(new UserTable()).execute();
    DSL.using(con).delete(new NewsTable()).execute();
    DSL.using(con).delete(new ContentTable()).execute();
    DSL.using(con).delete(new RssFeedContentSourceTable()).execute();
    DSL.using(con).delete(new FetchHistoryTable()).execute();
    DSL.using(con).delete(new ContentSourceTable()).execute();
    DSL.using(con).delete(new CategoryTable()).execute();
}

/**
 * @return
 */
public Connection openConnection() throws SQLException, ClassNotFoundException {
    if (checkConnection()) {
        return this.connection;
    }
    Class.forName("com.mysql.jdbc.Driver");
    this.connection = DriverManager.getConnection("jdbc:mysql://" + this.hostname + ":" + String.valueOf(this.port) + "/" + this.database, this.user, this.password);
    this.sql = DSL.using(connection, SQLDialect.MYSQL);
    return this.connection;
}

@Test
void ofCustomParserAndManuallyCallBuildConditionWithWrongTypeThrows2() {
    final FilterValue filter = Filter.of("myKey", valueToParse -> 5, value -> DSL.trueCondition());
    Assertions.assertThrows(ClassCastException.class, () -> filter.buildCondition(Collections.singletonList("whatever")));
    Assertions.assertThrows(ClassCastException.class, () -> filter.buildCondition(Collections.singletonList("54")));
    Assertions.assertEquals(DSL.trueCondition(), filter.buildCondition(Collections.singletonList(56)));
}

@Override
public void begin() {
  if (isActive()) {
    throw new IllegalStateException("UnitOfWork has already been started.");
  } else {
    DSLContext dslContext = DSL.using(configuration);
    dslContexts.set(dslContext);
  }
}

@Test
@SuppressWarnings("deprecation")
void getCondition() {
    Assertions.assertFalse(filteringJooqImpl1.getCondition(emptyMap, "any", s -> DSL.trueCondition()).isPresent());
    Assertions.assertFalse(filteringJooqImpl1.getCondition(notEmptyMap, "any", s -> DSL.trueCondition()).isPresent());
    Assertions.assertTrue(filteringJooqImpl1.getCondition(notEmptyMap, "key1", s -> DSL.trueCondition()).isPresent());
    Assertions.assertFalse(filteringJooqImpl1.getCondition(mapWithNullValues, "key1", s -> DSL.trueCondition()).isPresent());
    Assertions.assertFalse(filteringJooqImpl1.getCondition(mapWithEmptyValues, "key1", s -> DSL.trueCondition()).isPresent());
    Assertions.assertFalse(filteringJooqImpl1.getCondition(mapWithSpaceValues, "key1", s -> DSL.trueCondition()).isPresent());
}

@Test
void of2() {
    final FilterValue filterValue2 = Filter.of(
        "key", "key2",
        val1 -> "value", val2 -> "value",
        (value1, value2) -> DSL.trueCondition());
    Assertions.assertEquals(2, filterValue2.size());
    Assertions.assertNotNull(filterValue2);
}

@Test
void ofCustomParserAndManuallyCallBuildConditionWithWrongTypeThrows() {
    final FilterValue1<Integer> filter = Filter.of("myKey", valueToParse -> 5, value -> DSL.trueCondition());
    Assertions.assertThrows(ClassCastException.class, () -> filter.buildCondition(Collections.singletonList("whatever")));
    Assertions.assertThrows(ClassCastException.class, () -> filter.buildCondition(Collections.singletonList("54")));
    Assertions.assertEquals(DSL.trueCondition(), filter.buildCondition(Collections.singletonList(56)));
}

private static Condition createNTrueCondition(final int n) {
    if (n < 1)
        throw new IllegalArgumentException("Cannot have n < 1");
    Condition condition = null;
    for (int i = 0; i < n; i++) {
        if (condition == null)
            condition = DSL.trueCondition();
        else
            condition = condition.and(DSL.trueCondition());
    }
    return condition;
}

@Override
public final void migrate(Connection connection) throws Exception {
    didExecute = true;
    SQLDialect dialect = JDBCUtils.dialect(connection);
    if (SQLDialect.DEFAULT.equals(dialect))
        throw new IllegalStateException("Dialect couldn't be deducted from connection " + connection);

    Configuration configuration = new DefaultConfiguration().set(connection).set(dialect);
    DdlExecuteListener listener = new DdlExecuteListener();
    configuration.set(new DefaultExecuteListenerProvider(listener));
    DSLContext create = DSL.using(configuration);

    migrate(connection, create);
    ddlInstructionExecuted = listener.ddlInstructionExecuted();
}

@Test
public void insertExecShouldSucceed() throws InterruptedException {
    CountDownLatch latch = new CountDownLatch(1);
    dao.insertExecAsync(createSomething()).
            doOnSuccess(insertedRows -> Assert.assertEquals(1L,insertedRows.longValue())).
            flatMap(v-> dao.client().fetchOne(DSL.using(dao.configuration()).selectFrom(Tables.SOMETHING).orderBy(Tables.SOMETHING.SOMEID.desc()).limit(1),dao.jsonMapper())).
            flatMap(id -> {
                Assert.assertNotNull(id);
                return dao.deleteExecAsync(id.getSomeid());

            }).
            doOnSuccess(deletedRows -> Assert.assertEquals(1L, deletedRows.longValue())).
            subscribe(failOrCountDownSingleObserver(latch));
    await(latch);
}

@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("invitations"))
                .column(field("invitation_id", SQLDataType.BIGINT.identity(true)))
                .column(field("email", SQLDataType.VARCHAR(100)))
                .column(field("code", SQLDataType.VARCHAR(8)))
                .column(field("invited_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("invitation_id")),
                        constraint().unique(field("code"))
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("group_invitations"))
                .column(field("group_invitation_id", SQLDataType.BIGINT.identity(true)))
                .column(field("invitation_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("group_id", SQLDataType.BIGINT.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("group_invitation_id")),
                        constraint().foreignKey(field("invitation_id")).references(table("invitations"), field("invitation_id")),
                        constraint().foreignKey(field("group_id")).references(table("groups"), field("group_id"))
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("oidc_invitations"))
                .column(field("oidc_invitation_id", SQLDataType.BIGINT.identity(true)))
                .column(field("invitation_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("oidc_provider_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("oidc_sub", SQLDataType.VARCHAR(255).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("oidc_invitation_id")),
                        constraint().foreignKey(field("invitation_id")).references(table("invitations"), field("invitation_id")),
                        constraint().foreignKey(field("oidc_provider_id")).references(table("oidc_providers"), field("oidc_provider_id"))
                ).getSQL();
        stmt.execute(ddl);

    }
}

@Test
void ofCustomParser1Param() {
    final FilterValue1<Integer> filter = Filter.of("myKey", valueToParse -> 5, value -> DSL.trueCondition());
    final Condition condition = filter.buildCondition(Collections.singletonList(9));

    final int parserResult = filter.getKeyParser1().parser1().apply("whatever");
    Assertions.assertEquals(5, parserResult);
    Assertions.assertEquals(DSL.trueCondition(), condition);
}

@Test
void getTableFieldWorks() {
    sortingJooqImpl1.getSortAliasMapping().put("myKey", SortValue.of(DSL.field("any", Integer.class)));
    Assertions.assertNotNull(sortingJooqImpl1.getTableField("myKey"));
    Assertions.assertEquals("any", sortingJooqImpl1.getTableField("myKey").getSortField(SortOrder.ASC).getName());

}

@Test
void ofSupplierNotNull() {
    final FilterValue filter = Filter.of("myKey", DSL::trueCondition);
    final Condition condition = filter.buildCondition();
    Assertions.assertNotNull(condition);
    Assertions.assertEquals(DSL.trueCondition(), condition);
}

/**
 * Rename this table
 */
@Override
public CmOpsActionState rename(String name) {
    return new CmOpsActionState(DSL.name(name), null);
}

/**
 * Create a <code>somethingComposite</code> table reference
 */
public Somethingcomposite() {
    this(DSL.name("somethingComposite"), null);
}

/**
 * {@inheritDoc}
 */
@Override
public Somethingwithoutjson as(String alias) {
    return new Somethingwithoutjson(DSL.name(alias), this);
}

/**
 * Create a <code>kloopzcm.ns_opt</code> table reference
 */
public NsOpt() {
    this(DSL.name("ns_opt"), null);
}

/**
 * Rename this table
 */
@Override
public CmCiAttributes rename(String name) {
    return new CmCiAttributes(DSL.name(name), null);
}

/**
 * {@inheritDoc}
 */
@Override
public DjApprovalStates as(String alias) {
    return new DjApprovalStates(DSL.name(alias), this);
}

/**
 * Create an aliased <code>somethingComposite</code> table reference
 */
public Somethingcomposite(String alias) {
    this(DSL.name(alias), SOMETHINGCOMPOSITE);
}

/**
 * Create an aliased <code>kloopzcm.cms_ci_event_queue</code> table reference
 */
public CmsCiEventQueue(String alias) {
    this(DSL.name(alias), CMS_CI_EVENT_QUEUE);
}

@Test
void buildOrderByWithDuplicatesThrows() {
    sortingJooqImpl1.getSortAliasMapping().put("id", SortValue.of(DSL.field("col_id", Integer.class)));
    sortingJooqImpl1.getSortAliasMapping().put("name", SortValue.of(DSL.field("any", String.class)));
    Assertions.assertThrows(SortingApiException.class, () -> sortingJooqImpl1.buildOrderBy(Sort.by(Sort.Order.asc("id"), Sort.Order.desc("id"), Sort.Order.asc("id"))));
}