org.jooq.SQLDialect Java Examples

@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.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);
    }
}
 

public static void main(String[] args) throws Exception {
    String user = System.getProperty("jdbc.user");
    String password = System.getProperty("jdbc.password");
    String url = System.getProperty("jdbc.url");
    String driver = System.getProperty("jdbc.driver");

    Class.forName(driver).newInstance();
    try (Connection connection = DriverManager.getConnection(url, user, password)) {
        DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL);
        Result<Record> result = dslContext.select().from(AUTHOR).fetch();

        for (Record r : result) {
            Integer id = r.getValue(AUTHOR.ID);
            String firstName = r.getValue(AUTHOR.FIRST_NAME);
            String lastName = r.getValue(AUTHOR.LAST_NAME);

            System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
        }
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}
 

private FullTextSearch<ServerInformation> determineSearcher(SQLDialect dialect) {

        if (isPostgres(dialect)) {
            return new PostgresServerInformationSearch();
        }

        if (isMariaDB(dialect)) {
            return new MariaServerInformationSearch();
        }

        if (isSQLServer(dialect)) {
            return new SqlServerServerInformationSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 

private FullTextSearch<OrganisationalUnit> determineSearcher(SQLDialect dialect) {

        if (isPostgres(dialect)) {
            return new PostgresOrganisationalUnitSearch();
        }

        if (isMariaDB(dialect)) {
            return new MariaOrganisationalUnitSearch();
        }

        if (isSQLServer(dialect)) {
            return new SqlServerOrganisationalUnitSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 

private FullTextSearch<ChangeInitiative> determineSearcher(SQLDialect dialect) {

        if (isPostgres(dialect)) {
            return new PostgresChangeInitiativeSearch();
        }

        if (isMariaDB(dialect)) {
            return new MariaChangeInitiativeSearch();
        }

        if (isSQLServer(dialect)) {
            return new SqlServerChangeInitiativeSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 

private FullTextSearch<Measurable> determineSearcher(SQLDialect dialect) {

        if (isPostgres(dialect)) {
            return new PostgresMeasurableSearch();
        }

        if (isMariaDB(dialect)) {
            return new MariaMeasurableSearch();
        }

        if (isSQLServer(dialect)) {
            return new SqlServerMeasurableSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 

@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.
     */
}
 

@Test
public void testIgnoreFields() throws Exception {
	Connection conn = DbHelper.objectDb();

	DSLContext dsl = DSL
			.using(new DefaultConfiguration().set(conn)
					.set(SQLDialect.HSQLDB)
					.set(SfmRecordMapperProviderFactory.newInstance().addAlias("id", "noId").ignorePropertyNotFound().newProvider()));
	
	List<DbObject> list = dsl.select()
			.from("TEST_DB_OBJECT").fetchInto(DbObject.class);
	
	assertEquals(2, list.size());

	assertEquals(0, list.get(0).getId());
	list.get(0).setId(1);
	DbHelper.assertDbObjectMapping(list.get(0));
}
 

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;
}
 

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(10)
                .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));
            deployment.setDeploymentId(r.getValue(DJ_DEPLOYMENT.DEPLOYMENT_ID));
            deployments.add(deployment);
        }
        return deployments;
    }
 

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;
}
 

@Deprecated
public static TestDatabase initPreviousTcMysqlDatabase() {
  MySQLContainer container =
      new MySQLContainer("mysql:5.7.22")
          .withDatabaseName("previous")
          .withUsername("test")
          .withPassword("test");

  container.start();

  String jdbcUrl =
      String.format(
          "%s?user=%s&password=%s",
          container.getJdbcUrl(), container.getUsername(), container.getPassword());

  return initDatabase(jdbcUrl, SQLDialect.MYSQL, "previous");
}
 

@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.
     */
}
 

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

private FullTextSearch<Application> determineSearcher(SQLDialect dialect) {

        if (isPostgres(dialect)) {
            return new PostgresAppSearch();
        }

        if (isMariaDB(dialect)) {
            return new MariaAppSearch();
        }

        if (isSQLServer(dialect)) {
            return new SqlServerAppSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 

@Test
public void testUnmapping() throws Exception {
	Connection conn = DbHelper.objectDb();

	Configuration cfg = new DefaultConfiguration()
			.set(conn)
			.set(SQLDialect.HSQLDB);

	cfg.set(JooqMapperFactory.newInstance().newRecordUnmapperProvider(new DSLContextProvider() {
		@Override
		public DSLContext provide() {
			return DSL.using(cfg);
		}
	}));

	DSLContext dsl = DSL.using(cfg);

	Label label = new Label(1, UUID.randomUUID(), "label", false);

	LabelsRecord labelsRecord = dsl.newRecord(Labels.LABELS, label);

	assertEquals(label.getId(), labelsRecord.getId());
	assertEquals(label.getName(), labelsRecord.getName());
	assertEquals(label.getUuid(), labelsRecord.getUuid());
	assertEquals(label.getObsolete(), labelsRecord.getObsolete());


}
 

static SQLDialect guessSqlDialectFrom(String dataSourceUrl) {
    for (SQLDialect dialect: SQLDialect.values()) {
        if (dataSourceUrl.contains(dialect.name().toLowerCase())) {
            return dialect;
        }
    }
    throw new IllegalArgumentException("unknown SQL dialect for datasource : " + dataSourceUrl);
}
 

@Bean
@Autowired
public DSLContext dsl(DataSource dataSource) {
    org.jooq.Configuration configuration = new DefaultConfiguration()
            .set(dataSource)
            .set(SQLDialect.POSTGRES);
    return DSL.using(configuration);
}
 

public static DSLContext databaseAgnostic(DataSource dataSource) throws SQLException {
SQLDialect dialect;
try (Connection conn = dataSource.getConnection()) {
  dialect = dialect(conn);

  // See https://github.com/jOOQ/jOOQ/issues/4730
  if (conn.getMetaData().getURL().startsWith("jdbc:pgsql:")) {
    dialect = POSTGRES;
  }
}
return DSL.using(dataSource, dialect,
        new Settings()
            .withRenderSchema(false)
            .withRenderNameStyle(RenderNameStyle.AS_IS));
}
 

public Retriever getRetriever(RequestContext requestContext) {
    Settings settings = new Settings()
            .withStatementType(StatementType.STATIC_STATEMENT);
    DSLContext create = DSL.using(DB.getDataSource(db), SQLDialect.DEFAULT, settings);
    return new SQLBasedRetriever(config, setCursorKey,
            limit, offset, selectSqlKey, matchFields,
            greaterFields, lessFields, matchFieldTypes, greaterFieldTypes, lessFieldTypes,
            create, selectFields, table, orderByFields, renameMap, requestContext, injector);
}
 

public Indexer getIndexer(RequestContext requestContext) {
    SamanthaConfigService configService = injector.instanceOf(SamanthaConfigService.class);
    Settings settings = new Settings()
            .withStatementType(StatementType.STATIC_STATEMENT);
    DSLContext create = DSL.using(DB.getDataSource(db), SQLDialect.DEFAULT, settings);
    return new SQLBasedIndexer(configService, daoConfigs,
            create, tableKey, table, injector, daoConfigKey,
            fields, fieldTypes, matchFields, matchFieldTypes,
            retrieverName, setCursorKey, daoNameKey, daoName,
            cacheJsonFile, filePathKey, separatorKey,
            config, 128, requestContext);
}
 

@Override
public org.jooq.Configuration createDAOConfiguration(){
    org.jooq.Configuration configuration = new DefaultConfiguration();
    configuration.set(SQLDialect.HSQLDB);
    try {
        configuration.set(DriverManager.getConnection("jdbc:hsqldb:mem:test", "test", ""));
    } catch (SQLException e) {
        throw new AssertionError("Failed setting up DB.",e);
    }
    return configuration;
}
 

public Configuration build(Environment environment, PooledDataSourceFactory factory, String name) throws ClassNotFoundException {
    final Settings settings = buildSettings();
    final ManagedDataSource dataSource = factory.build(environment.metrics(), name);
    final SQLDialect dialect = determineDialect(factory, dataSource);
    final ConnectionProvider connectionProvider = new DataSourceConnectionProvider(dataSource);
    final Configuration config = new DefaultConfiguration()
            .set(settings)
            .set(dialect)
            .set(connectionProvider);

    environment.lifecycle().manage(dataSource);

    return config;
}
 

@NotNull
private static Result<Record> queryEnsembldb(@NotNull final String database) throws SQLException, IOException {
    // Disable annoying jooq self-ad message
    System.setProperty("org.jooq.no-logo", "true");
    final Connection conn = DriverManager.getConnection(database, DB_USER, "");
    final DSLContext context = DSL.using(conn, SQLDialect.MYSQL);
    final String query = readEnsemblQuery();
    return context.fetch(query);
}
 

public DatabaseAccess(@NotNull final String userName, @NotNull final String password, @NotNull final String url) throws SQLException {
    // Disable annoying jooq self-ad message
    System.setProperty("org.jooq.no-logo", "true");
    Connection conn = DriverManager.getConnection(url, userName, password);
    String catalog = conn.getCatalog();
    LOGGER.debug("Connecting to database {}", catalog);
    this.context = DSL.using(conn, SQLDialect.MYSQL, settings(catalog));

    ecrfDAO = new EcrfDAO(context);
    clinicalDAO = new ClinicalDAO(context);
    validationFindingsDAO = new ValidationFindingDAO(context);
    rnaDAO = new RNADAO(context);
    purityDAO = new PurityDAO(context);
    amberDAO = new AmberDAO(context);
    metricDAO = new MetricDAO(context);
    pgxDAO = new PgxDAO(context);
    copyNumberDAO = new CopyNumberDAO(context);
    geneCopyNumberDAO = new GeneCopyNumberDAO(context);
    somaticVariantDAO = new SomaticVariantDAO(context);
    structuralVariantDAO = new StructuralVariantDAO(context);
    structuralVariantClusterDAO = new StructuralVariantClusterDAO(context);
    structuralVariantFusionDAO = new StructuralVariantFusionDAO(context);
    canonicalTranscriptDAO = new CanonicalTranscriptDAO(context);
    driverCatalogDAO = new DriverCatalogDAO(context);
    chordDAO = new ChordDAO(context);
    clinicalEvidenceDAO = new ClinicalEvidenceDAO(context);
}
 

@Test
public void usesSpecifiedDialect() throws Exception {
    when(dataSourceFactory.getUrl()).thenReturn("jdbc:postgresql://localhost:5432/test");

    factory.setDialect(Optional.of(SQLDialect.DERBY));
    Configuration config = factory.build(environment, dataSourceFactory);
    assertThat(config.dialect()).isEqualTo(SQLDialect.DERBY);
}
 

public DSLContext getDSLContext() {
    if (dslContext == null) {
        Configuration defaultConfiguration = new DefaultConfiguration().set(getConnection())
                .set(SQLDialect.MYSQL);

        dslContext = DSL.using(defaultConfiguration);
    }

    return dslContext;
}
 

@Bean
public DefaultConfiguration configuration() {
    DefaultConfiguration jooqConfiguration = new DefaultConfiguration();
    jooqConfiguration.set(connectionProvider());
    jooqConfiguration.set(new DefaultExecuteListenerProvider(exceptionTransformer()));

    String sqlDialectName = environment.getRequiredProperty("jooq.sql.dialect");
    SQLDialect dialect = SQLDialect.valueOf(sqlDialectName);
    jooqConfiguration.set(dialect);

    return jooqConfiguration;
}
 

public static Configuration defaultConfigFromDataSource(DataSource ds) {
    DataSourceConnectionProvider dcp = new DataSourceConnectionProvider(ds);
    Configuration jooqConfig = new DefaultConfiguration();
    jooqConfig.set(SQLDialect.MYSQL);
    jooqConfig.set(dcp);
    //jooqConfig.set(new ThreadLocalTransactionProvider(dcp));
    jooqConfig.settings()
              .withExecuteWithOptimisticLockingExcludeUnversioned(true);
    return jooqConfig;
}
 

@Test
public void testHsqlDbAsIssue() throws SQLException {



    SfmRecordMapperProvider recordMapperProvider = SfmRecordMapperProviderFactory
            .newInstance()
            .addColumnProperty( k -> k.getField().getName().equalsIgnoreCase("amount"), new RenameProperty("currencyAndAmount_number"))
            .addColumnProperty( k -> k.getField().getName().equalsIgnoreCase("currencyCode"), new RenameProperty("currencyAndAmount_currency"))
            .newProvider();
    Connection conn = DbHelper.objectDb();

    DSLContext dsl = DSL
            .using(new DefaultConfiguration().set(conn)
                    .set(SQLDialect.HSQLDB)
                    .set(recordMapperProvider));

    List<Issue537> list =
            dsl
                    .select()
                    .from("issue537_b")
                    .fetchInto(Issue537.class);

    assertEquals(1, list.size());

    Issue537 value = list.get(0);

    assertEquals(100, value.currencyAndAmount.getNumber().doubleValue(), 0.00001);
    assertEquals("USD", value.currencyAndAmount.getCurrency().getCurrencyCode());

}