Java DSL.using方法代碼示例

import org.jooq.impl.DSL; //導入方法依賴的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.impl.DSL; //導入方法依賴的package包/類
@Override
public void migrate(Connection connection) throws Exception {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("permissions"))
                .column(field("permission_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("permission_id")),
                        constraint().unique(field("name"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

import org.jooq.impl.DSL; //導入方法依賴的package包/類
@Override
public void migrate(Connection connection) throws Exception {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("assignments"))
                .column(field("group_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("role_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("realm_id", SQLDataType.BIGINT.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("group_id"), field("role_id"), field("realm_id")),
                        constraint().foreignKey(field("group_id")).references(table("groups"), field("group_id")),
                        constraint().foreignKey(field("role_id")).references(table("roles"), field("role_id")),
                        constraint().foreignKey(field("realm_id")).references(table("realms"), field("realm_id"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

import org.jooq.impl.DSL; //導入方法依賴的package包/類
@Override
public void migrate(Connection connection) throws Exception {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("realms"))
                .column(field("realm_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("url", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("application_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("description", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("realm_id")),
                        constraint().unique(field("name")),
                        constraint().foreignKey(field("application_id")).references(table("applications"), field("application_id"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

import org.jooq.impl.DSL; //導入方法依賴的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.impl.DSL; //導入方法依賴的package包/類
@Override
public void migrate(Connection connection) throws Exception {
    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")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

import org.jooq.impl.DSL; //導入方法依賴的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.impl.DSL; //導入方法依賴的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.impl.DSL; //導入方法依賴的package包/類
@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("oidc_providers"))
                .column(field("oidc_provider_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("api_key", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("api_secret", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("scope", SQLDataType.VARCHAR(100)))
                .column(field("response_type", SQLDataType.VARCHAR(100)))
                .column(field("authorization_endpoint", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("token_endpoint", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("token_endpoint_auth_method", SQLDataType.VARCHAR(10).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("oidc_provider_id"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
 

import org.jooq.impl.DSL; //導入方法依賴的package包/類
@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;
}
 

import org.jooq.impl.DSL; //導入方法依賴的package包/類
@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);

    }
}
 

import org.jooq.impl.DSL; //導入方法依賴的package包/類
@Override
public void migrate(Connection connection) throws Exception {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("users"))
                .column(field("user_id", SQLDataType.BIGINT.identity(true)))
                .column(field("account", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("email", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_id")),
                        constraint().unique(field("account")),
                        constraint().unique(field("email"))
                ).getSQL();
        stmt.execute(ddl);

        stmt.execute(
                create.createIndex(name("idx_users_01"))
                        .on(table("users"), field("account"))
                        .getSQL()
        );
        stmt.execute(
                create.createIndex(name("idx_users_02"))
                        .on(table("users"), field("email"))
                        .getSQL()
        );
    }
}
 

import org.jooq.impl.DSL; //導入方法依賴的package包/類
private void populateEnv(Environment env, Connection conn) {
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
    Result<Record> envAttributes = create.select().from(CM_CI_ATTRIBUTES)
            .join(MD_CLASS_ATTRIBUTES).on(CM_CI_ATTRIBUTES.ATTRIBUTE_ID.eq(MD_CLASS_ATTRIBUTES.ATTRIBUTE_ID))
            .where(CM_CI_ATTRIBUTES.CI_ID.eq(env.getId()))
            .fetch();
    for (Record attrib : envAttributes) {
        String attributeName = attrib.getValue(MD_CLASS_ATTRIBUTES.ATTRIBUTE_NAME);
        if (attributeName.equalsIgnoreCase("profile")) {
            env.setProfile(attrib.getValue(CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE));
        }
        //add other attributes as and when needed
    }
    //now query all the platforms for this env
    Result<Record> platformRels = create.select().from(CM_CI_RELATIONS)
            .join(MD_RELATIONS).on(MD_RELATIONS.RELATION_ID.eq(CM_CI_RELATIONS.RELATION_ID))
            .join(CM_CI).on(CM_CI.CI_ID.eq(CM_CI_RELATIONS.TO_CI_ID))
            .where(MD_RELATIONS.RELATION_NAME.eq("manifest.ComposedOf"))
            .and(CM_CI_RELATIONS.FROM_CI_ID.eq(env.getId()))
            .fetch();
    for (Record platformRel : platformRels) {
        long platformId = platformRel.getValue(CM_CI_RELATIONS.TO_CI_ID);
        Platform platform = new Platform();
        platform.setId(platformId);
        platform.setName(platformRel.getValue(CM_CI.CI_NAME));
        platform.setPath(env.getPath() + "/" + env.getName() + "/bom/" + platform.getName() + "/1");
        populatePlatform(conn, platform);
        platform.setActiveClouds(getActiveClouds(platform, conn));

        //now calculate total cores of the env - including all platforms
        env.setTotalCores(env.getTotalCores() + platform.getTotalCores());
        env.addPlatform(platform);
    }
}
 

import org.jooq.impl.DSL; //導入方法依賴的package包/類
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));
        }
    }
}
 

import org.jooq.impl.DSL; //導入方法依賴的package包/類
private void updateCrawlEntry(Environment env) {
    if (crawlerDbUserName == null || crawlerDbUrl == null || crawlerDbPassword == null) {
        return;
    }
    try (Connection conn = DriverManager.getConnection(crawlerDbUrl, crawlerDbUserName, crawlerDbPassword)) {
        DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);

        Result<Record> records = create.select().from(CRAWL_ENTITIES)
                .where(CRAWL_ENTITIES.OO_ID.eq(env.getId()))
                .fetch();

        if (records.isNotEmpty()) {
            create.update(CRAWL_ENTITIES)
                    .set(CRAWL_ENTITIES.LAST_CRAWLED_AT, new Timestamp(System.currentTimeMillis()))
                    .where(CRAWL_ENTITIES.OO_ID.eq(env.getId()))
                    .execute();
        } else {
            create.insertInto(CRAWL_ENTITIES)
                    .set(CRAWL_ENTITIES.NS_PATH, env.getPath() + "/" + env.getName())
                    .set(CRAWL_ENTITIES.OO_ID, env.getId())
                    .execute();
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}