org.jooq.SQLDialect Java Examples

The following examples show how to use org.jooq.SQLDialect. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: V22__AlterInvitations.java    From bouncr with Eclipse Public License 1.0 6 votes vote down vote up
@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);
    }
}
 
Example #2
Source File: Application.java    From hellokoding-courses with MIT License 6 votes vote down vote up
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();
    }
}
 
Example #3
Source File: ServerInformationSearchDao.java    From waltz with Apache License 2.0 6 votes vote down vote up
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);
    }
 
Example #4
Source File: OrganisationalUnitSearchDao.java    From waltz with Apache License 2.0 6 votes vote down vote up
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);
    }
 
Example #5
Source File: ChangeInitiativeSearchDao.java    From waltz with Apache License 2.0 6 votes vote down vote up
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);
    }
 
Example #6
Source File: MeasurableSearchDao.java    From waltz with Apache License 2.0 6 votes vote down vote up
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);
    }
 
Example #7
Source File: UserTest.java    From testing_security_development_enterprise_systems with GNU Lesser General Public License v3.0 6 votes vote down vote up
@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.
     */
}
 
Example #8
Source File: JooqMapperTest.java    From SimpleFlatMapper with MIT License 6 votes vote down vote up
@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));
}
 
Example #9
Source File: CMSCrawler.java    From oneops with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example #10
Source File: CMSCrawler.java    From oneops with Apache License 2.0 6 votes vote down vote up
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;
    }
 
Example #11
Source File: CMSCrawler.java    From oneops with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example #12
Source File: SqlTestUtil.java    From kork with Apache License 2.0 6 votes vote down vote up
@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");
}
 
Example #13
Source File: UserTest.java    From testing_security_development_enterprise_systems with GNU Lesser General Public License v3.0 6 votes vote down vote up
@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.
     */
}
 
Example #14
Source File: DatabaseModule.java    From curiostack with MIT License 6 votes vote down vote up
@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;
}
 
Example #15
Source File: ApplicationSearchDao.java    From waltz with Apache License 2.0 6 votes vote down vote up
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);
    }
 
Example #16
Source File: JooqUnmapperTest.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
@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());


}
 
Example #17
Source File: RepositoryFactoryImpl.java    From datashare with GNU Affero General Public License v3.0 5 votes vote down vote up
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);
}
 
Example #18
Source File: DITestingConfiguration.java    From waltz with Apache License 2.0 5 votes vote down vote up
@Bean
@Autowired
public DSLContext dsl(DataSource dataSource) {
    org.jooq.Configuration configuration = new DefaultConfiguration()
            .set(dataSource)
            .set(SQLDialect.POSTGRES);
    return DSL.using(configuration);
}
 
Example #19
Source File: DSLContexts.java    From keywhiz with Apache License 2.0 5 votes vote down vote up
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));
}
 
Example #20
Source File: SQLBasedRetrieverConfig.java    From samantha with MIT License 5 votes vote down vote up
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);
}
 
Example #21
Source File: SQLBasedIndexerConfig.java    From samantha with MIT License 5 votes vote down vote up
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);
}
 
Example #22
Source File: HsqldbConfigurationProvider.java    From vertx-jooq with MIT License 5 votes vote down vote up
@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;
}
 
Example #23
Source File: JooqFactory.java    From droptools with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example #24
Source File: HmfGenePanelBuilder.java    From hmftools with GNU General Public License v3.0 5 votes vote down vote up
@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);
}
 
Example #25
Source File: DatabaseAccess.java    From hmftools with GNU General Public License v3.0 5 votes vote down vote up
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);
}
 
Example #26
Source File: JooqFactoryTest.java    From droptools with Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example #27
Source File: ScopedContext.java    From JOOQ with Apache License 2.0 5 votes vote down vote up
public DSLContext getDSLContext() {
    if (dslContext == null) {
        Configuration defaultConfiguration = new DefaultConfiguration().set(getConnection())
                .set(SQLDialect.MYSQL);

        dslContext = DSL.using(defaultConfiguration);
    }

    return dslContext;
}
 
Example #28
Source File: PersistenceContextIntegrationTest.java    From tutorials with MIT License 5 votes vote down vote up
@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;
}
 
Example #29
Source File: JooqConfig.java    From StubbornJava with MIT License 5 votes vote down vote up
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;
}
 
Example #30
Source File: Issue537Test.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
@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());

}