Java SQLDialect類代碼示例

import org.jooq.SQLDialect; //導入依賴的package包/類
@Test
public void testSave() throws SQLException {
	final Configuration jooqConfig = new DefaultConfiguration().set(con).set(SQLDialect.MYSQL);

	final News news = insertNews(con, jooqConfig);

	// Test now

	final NewsTable newsTable = new NewsTable();
	final News fetchedNews = DSL.using(jooqConfig).selectFrom(newsTable).where(newsTable.CONTENT_ID.eq(news.getId())).fetchOneInto(News.class);

	final ContentTable contentTable = new ContentTable();
	final Content fetchedContent = DSL.using(jooqConfig).selectFrom(contentTable).where(contentTable.ID.eq(news.getId())).fetchOneInto(Content.class);

	assertNotNull(fetchedNews);
	assertNotNull(fetchedContent);
}
 

import org.jooq.SQLDialect; //導入依賴的package包/類
@Test
public void testGet() {
	final Configuration jooqConfig = new DefaultConfiguration().set(con).set(SQLDialect.MYSQL);

	final News news = insertNews(con, jooqConfig);

	final News fetchedNews = new NewsDao(con).getNews(news.getId());
	assertNotNull(fetchedNews);

	assertEquals(news.getTitle(), fetchedNews.getTitle());
	assertEquals(news.getText(), fetchedNews.getText());
	assertEquals(news.getProviderId(), fetchedNews.getProviderId());
	assertEquals(news.getCategoryId(), fetchedNews.getCategoryId());
	assertEquals(news.getLocale(), fetchedNews.getLocale());

	assertEquals(news.getLink(), fetchedNews.getLink());
	assertEquals(news.getImageUrl(), fetchedNews.getImageUrl());
	assertEquals(news.getImageWidth(), fetchedNews.getImageWidth());
	assertEquals(news.getImageHeight(), fetchedNews.getImageHeight());
	assertEquals(news.getNewsGroupId(), fetchedNews.getNewsGroupId());
}
 

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

import org.jooq.SQLDialect; //導入依賴的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.SQLDialect; //導入依賴的package包/類
private static void initInternal(final Vertx vertx,
                                 final String name) {
    vertxRef = vertx;
    Fn.pool(CONFIGS, name,
            () -> Infix.init(Plugins.Infix.JOOQ,
                    (config) -> {
                        // Initialized client
                        final Configuration configuration = new DefaultConfiguration();
                        configuration.set(SQLDialect.MYSQL_8_0);
                        final ConnectionProvider provider =
                                new DefaultConnectionProvider(HikariCpPool.getConnection(
                                        config.getJsonObject("provider")
                                ));
                        // Initialized default configuration
                        configuration.set(provider);
                        return configuration;
                    }, JooqInfix.class));
}
 

import org.jooq.SQLDialect; //導入依賴的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.SQLDialect; //導入依賴的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.SQLDialect; //導入依賴的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.SQLDialect; //導入依賴的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.SQLDialect; //導入依賴的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.SQLDialect; //導入依賴的package包/類
public static void main(String[] args) {

		String url = "jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1;INIT=runscript from 'src/main/resources/create.sql'";
		try {
			Connection conn = DriverManager.getConnection(url);
			DSLContext create = DSL.using(conn, SQLDialect.H2);
			Optional<User> user = create.select(USER.ID, USER.NAME, USER.ADDRESS).from(USER).fetchOptionalInto(User.class);
			user.map(u -> {
				System.out.println(u);
				return null;
			});
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
 

import org.jooq.SQLDialect; //導入依賴的package包/類
@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.alterTable(table("oidc_invitations"))
                .renameColumn(field("oidc_sub")).to(field("oidc_payload", SQLDataType.CLOB))
                .getSQL();
        if (create.configuration().dialect() == SQLDialect.MYSQL) {
            Matcher m = Pattern.compile("\\s+RENAME\\s+COLUMN\\s+(\\w+)\\s+TO\\s+", Pattern.CASE_INSENSITIVE).matcher(ddl);
            StringBuffer sb = new StringBuffer();
            if (m.find()) {
                m.appendReplacement(sb, " change " + m.group(1) + " ");
                m.appendTail(sb);
                sb.append(" text not null");
                ddl = sb.toString();
            }
        }

        stmt.execute(ddl);
    }
}
 

import org.jooq.SQLDialect; //導入依賴的package包/類
private String createDDL(DefaultConfiguration config) {
    DSLContext create = DSL.using(config);

    String ddl = create.alterTable(table("oidc_invitations"))
            .renameColumn(field("oidc_sub"))
            .to(field("oidc_payload", SQLDataType.CLOB))
            .getSQL();
    if (create.configuration().dialect() == SQLDialect.MYSQL) {
        Matcher m = Pattern.compile("\\s+RENAME\\s+COLUMN\\s+(\\w+)\\s+TO\\s+", Pattern.CASE_INSENSITIVE).matcher(ddl);
        StringBuffer sb = new StringBuffer();
        if (m.find()) {
            m.appendReplacement(sb, " change " + m.group(1) + " ");
            m.appendTail(sb);
            sb.append(" text not null");
            ddl = sb.toString();
        }
    }
    return ddl;
}
 

import org.jooq.SQLDialect; //導入依賴的package包/類
/**
 * Can we re-use DSLContext as a Spring bean (singleton)? Yes, the Spring tutorial of
 * Jooq also does it that way, but only if we do not change anything about the
 * config after the init (which we don't do anyways) and if the ConnectionProvider
 * does not store any shared state (we use DataSourceConnectionProvider of Jooq, so no problem).
 *
 * Some sources and discussion:
 * - http://www.jooq.org/doc/3.6/manual/getting-started/tutorials/jooq-with-spring/
 * - http://jooq-user.narkive.com/2fvuLodn/dslcontext-and-threads
 * - https://groups.google.com/forum/#!topic/jooq-user/VK7KQcjj3Co
 * - http://stackoverflow.com/questions/32848865/jooq-dslcontext-correct-autowiring-with-spring
 */
@Bean
public DSLContext dslContext() {
    initDataSource();

    Settings settings = new Settings()
            // Normally, the records are "attached" to the Configuration that created (i.e. fetch/insert) them.
            // This means that they hold an internal reference to the same database connection that was used.
            // The idea behind this is to make CRUD easier for potential subsequent store/refresh/delete
            // operations. We do not use or need that.
            .withAttachRecords(false)
            // To log or not to log the sql queries, that is the question
            .withExecuteLogging(CONFIG.getDb().isSqlLogging());

    // Configuration for JOOQ
    org.jooq.Configuration conf = new DefaultConfiguration()
            .set(SQLDialect.MYSQL)
            .set(new DataSourceConnectionProvider(dataSource))
            .set(settings);

    return DSL.using(conf);
}
 

import org.jooq.SQLDialect; //導入依賴的package包/類
public DatabaseAccess(@NotNull final String userName, @NotNull final String password, @NotNull final String url) throws SQLException {
    // MIVO: disable annoying jooq self-ad message
    System.setProperty("org.jooq.no-logo", "true");
    final Connection conn = DriverManager.getConnection(url, userName, password);
    final String catalog = conn.getCatalog();
    LOGGER.info("Connecting to database {}", catalog);
    this.context = DSL.using(conn, SQLDialect.MYSQL, settings(catalog));

    purityDAO = new PurityDAO(context);
    copyNumberDAO = new CopyNumberDAO(context);
    geneCopyNumberDAO = new GeneCopyNumberDAO(context);
    somaticVariantDAO = new SomaticVariantDAO(context);
    structuralVariantDAO = new StructuralVariantDAO(context);
    ecrfDAO = new EcrfDAO(context);
    clinicalDAO = new ClinicalDAO(context);
    validationFindingsDAO = new ValidationFindingDAO(context);
}