Java DSLContext类代码示例

import org.jooq.DSLContext; //导入依赖的package包/类
private ValidationResult notExists(final DSLContext context, final ColumnPermutation lhs,
    final ColumnPermutation rhs) {

  final Table<Record> lhsAlias = context.select(fields(lhs))
      .from(tables(lhs))
      .where(notNull(lhs))
      .asTable();

  final int violators = context.selectCount().from(
      selectFrom(lhsAlias).whereNotExists(
          context.selectOne()
              .from(tables(rhs))
              .where(row(fields(rhs)).eq(row(lhsAlias.fields())))
      ).limit(1)
  ).fetchOne().value1();

  return new DefaultValidationResult(violators == 0);
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
@Override
public void migrate(Connection connection) throws Exception {
    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);
    }
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
private void saveTaxCodeInternal(final EasyTaxTaxCode taxCode, final DateTime date,
        final DSLContext dslContext) {
    int updateCount = dslContext.update(EASYTAX_TAX_CODES)
            .set(EASYTAX_TAX_CODES.TAX_RATE, taxCode.getTaxRate())
            .set(EASYTAX_TAX_CODES.VALID_FROM_DATE, taxCode.getValidFromDate())
            .set(EASYTAX_TAX_CODES.VALID_TO_DATE, taxCode.getValidToDate())
            .set(EASYTAX_TAX_CODES.CREATED_DATE, date)
            .where(EASYTAX_TAX_CODES.KB_TENANT_ID.equal(taxCode.getKbTenantId().toString()))
            .and(EASYTAX_TAX_CODES.TAX_ZONE.equal(taxCode.getTaxZone()))
            .and(EASYTAX_TAX_CODES.PRODUCT_NAME.equal(taxCode.getProductName()))
            .and(EASYTAX_TAX_CODES.TAX_CODE.equal(taxCode.getTaxCode()))
            .and(EASYTAX_TAX_CODES.VALID_FROM_DATE.equal(taxCode.getValidFromDate())).execute();

    if (updateCount < 1) {
        dslContext
                .insertInto(EASYTAX_TAX_CODES, EASYTAX_TAX_CODES.KB_TENANT_ID,
                        EASYTAX_TAX_CODES.TAX_ZONE, EASYTAX_TAX_CODES.PRODUCT_NAME,
                        EASYTAX_TAX_CODES.TAX_CODE, EASYTAX_TAX_CODES.TAX_RATE,
                        EASYTAX_TAX_CODES.VALID_FROM_DATE, EASYTAX_TAX_CODES.VALID_TO_DATE,
                        EASYTAX_TAX_CODES.CREATED_DATE)
                .values(taxCode.getKbTenantId().toString(), taxCode.getTaxZone(),
                        taxCode.getProductName(), taxCode.getTaxCode(), taxCode.getTaxRate(),
                        taxCode.getValidFromDate(), taxCode.getValidToDate(), date)
                .execute();
    }
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
private ValidationResult leftOuterJoin(final DSLContext context, final ColumnPermutation lhs,
    final ColumnPermutation rhs) {

  final String rhsTableName = rhs.getColumnIdentifiers().get(0).getTableIdentifier();
  final Table rhsAlias = table(name(rhsTableName)).asTable(randomAlias());
  final ColumnPermutation rhsAliasColumns = swapRelationName(rhsAlias.getName(), rhs);

  final int violators = context.selectDistinct(fields(lhs))
      .from(tables(lhs))
      .leftOuterJoin(rhsAlias)
      .on(columnsEqual(lhs, rhsAliasColumns))
      .where(isNull(rhsAliasColumns))
      .and(notNull(lhs))
      .limit(1)
      .execute();

  return new DefaultValidationResult(violators == 0);
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
@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.
     */
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
public static void createGuildSettings(DiscordBot bot, DSLContext database, Guild guild) {
    SettingsRecord record = database.newRecord(Tables.SETTINGS);

    record.setGuildid(guild.getId());
    record.setSilentcommands(false);
    record.setInvitelinkremover(false);
    record.setModlog(false);
    record.setModlogchannelid(DiscordUtils.getDefaultChannel(guild).getId());
    record.setHoldingroom(false);
    record.setHoldingroomroleid(null);
    record.setHoldingroomminutes(3);
    record.setPrefix(bot.getConfig().jim.default_prefix);
    record.setWelcomemessage(false);
    record.setMessage(DEFAULT_WELCOME_MESSAGE);
    record.setWelcomemessagechannelid(DiscordUtils.getDefaultChannel(guild).getId());
    record.setNospaceprefix(false);
    record.setStatistics(false);

    record.store();
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
@Override
public void migrate(Connection connection, DSLContext create) {
    create.createSequence("S_AUTHOR_ID").execute();

    String authorTableName = "AUTHOR";
    create.createTable(authorTableName)
            .column("ID", SQLDataType.INTEGER.nullable(false))
            .column("FIRST_NAME", SQLDataType.VARCHAR.length(50))
            .column("LAST_NAME", SQLDataType.VARCHAR.length(50).nullable(false))
            .column("DATE_OF_BIRTH", SQLDataType.DATE)
            .column("YEAR_OF_BIRTH", SQLDataType.INTEGER)
            .column("ADDRESS", SQLDataType.VARCHAR.length(50))
            .execute();

    create.alterTable(authorTableName).add(constraint("PK_T_AUTHOR").primaryKey("ID")).execute();

    String bookTableName = "BOOK";
    create.createTable(bookTableName)
            .column("ID", SQLDataType.INTEGER.nullable(false))
            .column("AUTHOR_ID", SQLDataType.INTEGER.nullable(false))
            .column("TITLE", SQLDataType.VARCHAR.length(400).nullable(false))
            .execute();

    create.alterTable(bookTableName).add(constraint("PK_T_BOOK").primaryKey("ID")).execute();
    create.alterTable(bookTableName).add(
            constraint("FK_T_BOOK_AUTHOR_ID").foreignKey("AUTHOR_ID").references(authorTableName, "ID"))
            .execute();

}
 

import org.jooq.DSLContext; //导入依赖的package包/类
@Override
public void handle(RoutingContext ctx, Server server, DiscordBot bot, DSLContext database) {
    HttpServerRequest request = ctx.request();
    HttpServerResponse response = ctx.response();

    String userId = ServerUtils.authUser(request, response, config);
    if (userId == null) {
        return;
    }

    User user = DiscordApiUtils.getUser(userId, config.jim.token);
    if (user == null) {
        response.setStatusCode(404);
        response.end();
        return;
    }

    Gson gson = new Gson();
    response.putHeader("Content-Type", "application/json");
    response.end(gson.toJson(user));
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
@Override
public boolean run(DiscordBot bot, GuildMessageReceivedEvent event, String args) {
    if (embed == null) {
        JDA shard = event.getJDA();
        DSLContext database = bot.getDatabase();
        Guild guild = event.getGuild();
        EmbedBuilder builder = new EmbedBuilder();
        builder.setAuthor("Safety Jim - Commands", null, shard.getSelfUser().getAvatarUrl());
        builder.setDescription(getUsageTexts(bot, DatabaseUtils.getGuildSettings(database, guild).getPrefix()));
        builder.setColor(new Color(0x4286F4));

        embed = builder.build();
    }

    DiscordUtils.successReact(bot, event.getMessage());
    DiscordUtils.sendMessage(event.getChannel(), embed);
    return false;
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
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);
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
private List<Deployment> getDeployments(Connection conn, Environment env) {

        List<Deployment> deployments = new ArrayList<>();
        DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
        Result<Record> records = create.select().from(DJ_DEPLOYMENT)
                .join(DJ_DEPLOYMENT_STATES).on(DJ_DEPLOYMENT_STATES.STATE_ID.eq(DJ_DEPLOYMENT.STATE_ID))
                .join(NS_NAMESPACES).on(NS_NAMESPACES.NS_ID.eq(DJ_DEPLOYMENT.NS_ID))
                .where(NS_NAMESPACES.NS_PATH.eq(env.getPath()+ "/" + env.getName() + "/bom"))
                .and(DJ_DEPLOYMENT.CREATED_BY.notEqual("oneops-autoreplace"))
                .orderBy(DJ_DEPLOYMENT.CREATED.desc())
                .limit(1)
                .fetch();
        for (Record r : records) {
            Deployment deployment = new Deployment();
            deployment.setCreatedAt(r.getValue(DJ_DEPLOYMENT.CREATED));
            deployment.setCreatedBy(r.getValue(DJ_DEPLOYMENT.CREATED_BY));
            deployment.setState(r.getValue(DJ_DEPLOYMENT_STATES.STATE_NAME));
            deployments.add(deployment);
        }
        return deployments;
    }
 

import org.jooq.DSLContext; //导入依赖的package包/类
private List<Environment> getOneopsEnvironments(Connection conn) {
    List<Environment> envs = new ArrayList<>();
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
    log.info("Fetching all environments..");
    Result<Record> envRecords = create.select().from(CM_CI)
            .join(MD_CLASSES).on(CM_CI.CLASS_ID.eq(MD_CLASSES.CLASS_ID))
            .join(NS_NAMESPACES).on(CM_CI.NS_ID.eq(NS_NAMESPACES.NS_ID))
            .where(MD_CLASSES.CLASS_NAME.eq("manifest.Environment"))
            .fetch(); //all the env cis
    log.info("Got all environments");
    for (Record r : envRecords) {
        long envId = r.getValue(CM_CI.CI_ID);
        //now query attributes for this env
        Environment env = new Environment();
        env.setName(r.getValue(CM_CI.CI_NAME));
        env.setId(r.getValue(CM_CI.CI_ID));
        env.setPath(r.getValue(NS_NAMESPACES.NS_PATH));
        env.setNsId(r.getValue(NS_NAMESPACES.NS_ID));
        envs.add(env);
    }
    return envs;
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
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;
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("user_sessions"))
                .column(field("user_session_id", SQLDataType.BIGINT.identity(true)))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("token", SQLDataType.VARCHAR(36).nullable(false)))
                .column(field("remote_address", SQLDataType.VARCHAR(255)))
                .column(field("user_agent", SQLDataType.VARCHAR(255)))
                .column(field("created_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_session_id")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

import org.jooq.DSLContext; //导入依赖的package包/类
@Override
public void migrate(Connection connection) throws Exception {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("roles"))
                .column(field("role_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("role_id")),
                        constraint().unique(field("name"))
                ).getSQL();
        stmt.execute(ddl);
    }

}