Java Code Examples for org.jooq.SQLDialect

The following are top voted examples for showing how to use org.jooq.SQLDialect. These examples are extracted from open source projects. You can vote up the examples you like and your votes will be used in our system to generate more good examples.
Example 1
Project: InComb   File: NewsTest.java   Source Code and License 6 votes vote down vote up
@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);
}
 
Example 2
Project: InComb   File: NewsTest.java   Source Code and License 6 votes vote down vote up
@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());
}
 
Example 3
Project: AdvancedDataProfilingSeminar   File: TestDatabase.java   Source Code and License 6 votes vote down vote up
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();
  }
}
 
Example 4
Project: testing_security_development_enterprise_systems   File: UserTest.java   Source Code and License 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 5
Project: vertx-zero   File: JooqInfix.java   Source Code and License 6 votes vote down vote up
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));
}
 
Example 6
Project: oneops   File: CMSCrawler.java   Source Code and License 6 votes vote down vote up
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);
}
 
Example 7
Project: oneops   File: CMSCrawler.java   Source Code and License 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(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;
    }
 
Example 8
Project: oneops   File: CMSCrawler.java   Source Code and License 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 9
Project: oneops   File: CMSCrawler.java   Source Code and License 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 10
Project: curiostack   File: DatabaseModule.java   Source Code and License 6 votes vote down vote up
@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;
}
 
Example 11
Project: slick-jooq   File: Main.java   Source Code and License 6 votes vote down vote up
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();
		}

	}
 
Example 12
Project: bouncr   File: V22__AlterInvitations.java   Source Code and License 6 votes vote down vote up
@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);
    }
}
 
Example 13
Project: bouncr   File: RenameColumnWorkaroundTest.java   Source Code and License 6 votes vote down vote up
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;
}
 
Example 14
Project: steve-plugsurfing   File: BeanConfiguration.java   Source Code and License 6 votes vote down vote up
/**
 * 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);
}
 
Example 15
Project: hmftools   File: DatabaseAccess.java   Source Code and License 6 votes vote down vote up
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);
}
 
Example 16
Project: tiny-rxjava-jdbc   File: FunctionalTests.java   Source Code and License 6 votes vote down vote up
@Test
public void shouldWorkWithAutoCommit() throws Exception {
  TestSubscriber<Object> t = new TestSubscriber<>();
  ConnectionPool.from(new H2ConnectionProvider("FuncTest-shouldWorkWithAutoCommit"))
    .execute(
      connection ->
        Execute.using(connection, c -> using(c, SQLDialect.H2).createTable(TEST).column(ID, ID.getDataType()))
          .cast(Object.class)
          .concatWith(
            Execute.using(connection, c -> using(c, SQLDialect.H2).insertInto(TEST, ID).values(1))
          )
          .concatWith(
            Execute.using(connection, c -> using(c, SQLDialect.H2).insertInto(TEST, ID).values(2))
          )
          .concatWith(
            Select.using(connection, c -> using(c, SQLDialect.H2).select().from(TEST), r -> r.getValue(0))
          )
    )
    .withAutoCommit()
    .subscribe(t);

  t.assertNoErrors();
  t.assertCompleted();
  t.assertValues(0, 1, 1, 1, 2);
}
 
Example 17
Project: tiny-rxjava-jdbc   File: FunctionalTests.java   Source Code and License 6 votes vote down vote up
@Test
public void shouldWorkWithTransactionPerEvent() throws Exception {
  TestSubscriber<Object> t = new TestSubscriber<>();
  ConnectionPool.from(new H2ConnectionProvider("FuncTest-shouldWorkWithCommitPerEvent"))
    .execute(
      connection ->
        Execute.using(connection, c -> using(c, SQLDialect.H2).createTable(TEST).column(ID, ID.getDataType()))
          .cast(Object.class)
          .concatWith(
            Execute.using(connection, c -> using(c, SQLDialect.H2).insertInto(TEST, ID).values(1))
          )
          .concatWith(
            Execute.using(connection, c -> using(c, SQLDialect.H2).insertInto(TEST, ID).values(2))
          )
          .concatWith(
            Select.using(connection, c -> using(c, SQLDialect.H2).select().from(TEST), r -> r.getValue(0))
          )
    )
    .withTransactionPerEvent()
    .subscribe(t);

  t.assertNoErrors();
  t.assertCompleted();
  t.assertValues(0, 1, 1, 1, 2);
}
 
Example 18
Project: jooq-mysql   File: Application.java   Source Code and 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 19
Project: sociallink   File: DBTextScorer.java   Source Code and License 6 votes vote down vote up
@Override
public double getFeature(User user, DBpediaResource resource) {
    PGobject userVectorRaw = DSL.using(source, SQLDialect.POSTGRES)
            .select(USER_TEXT.LSA)
            .from(USER_TEXT)
            .where(USER_TEXT.UID.eq(user.getId()))
            .fetchOne(USER_TEXT.LSA, PGobject.class);


    if (userVectorRaw == null) {
        if (verbose) {
            LOGGER.debug("Can't find LSA for user: @"+user.getScreenName()+" ("+user.getId()+")");
        }
        return 0.0d;
    }

    return process(cubeToFloat(userVectorRaw), resource);
}
 
Example 20
Project: sociallink   File: DBTextScorer.java   Source Code and License 6 votes vote down vote up
@Override
public double getFeature(User user, DBpediaResource resource) {
    Float[] userVectorRaw = DSL.using(source, SQLDialect.POSTGRES)
            .select(USER_TEXT_ARR.LSA)
            .from(USER_TEXT_ARR)
            .where(USER_TEXT_ARR.UID.eq(user.getId()))
            .fetchOne(USER_TEXT_ARR.LSA, Float[].class);


    if (userVectorRaw == null) {
        if (verbose) {
            LOGGER.debug("Can't find LSA for user: @"+user.getScreenName()+" ("+user.getId()+")");
        }
        return 0.0d;
    }

    return process(DBTextScorer.numberToFloat(userVectorRaw), resource);
}
 
Example 21
Project: waltz   File: DIConfiguration.java   Source Code and License 6 votes vote down vote up
@Bean
@Autowired
public DSLContext dsl(DataSource dataSource) {
    try {
        SQLDialect.valueOf(dialect);
    } catch (IllegalArgumentException iae) {
        System.err.println("Cannot parse sql dialect: "+dialect);
        throw iae;
    }

    Settings dslSettings = null;
    if ("true".equals(System.getProperty(JOOQ_DEBUG_PROPERTY))) {
        dslSettings = new Settings()
                .withRenderFormatted(true)
                .withExecuteLogging(true);
    }

    return DSL.using(
            dataSource,
            SQLDialect.valueOf(dialect),
            dslSettings);
}
 
Example 22
Project: waltz   File: ApplicationSearchDao.java   Source Code and License 6 votes vote down vote up
private FullTextSearch<Application> determineSearcher(SQLDialect dialect) {

        if (dialect == SQLDialect.POSTGRES) {
            return new PostgresAppSearch();
        }

        if (dialect == SQLDialect.MARIADB) {
            return new MariaAppSearch();
        }

        if (dialect.name().startsWith("SQLSERVER")) {
            return new SqlServerAppSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 
Example 23
Project: waltz   File: OrganisationalUnitSearchDao.java   Source Code and License 6 votes vote down vote up
private FullTextSearch<OrganisationalUnit> determineSearcher(SQLDialect dialect) {

        if (dialect == SQLDialect.POSTGRES) {
            return new PostgresOrganisationalUnitSearch();
        }

        if (dialect == SQLDialect.MARIADB) {
            return new MariaOrganisationalUnitSearch();
        }

        // cannot do direct comparison as may not be present.
        if (dialect.name().equals("SQLSERVER2014")) {
            return new SqlServerOrganisationalUnitSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 
Example 24
Project: waltz   File: ChangeInitiativeSearchDao.java   Source Code and License 6 votes vote down vote up
private FullTextSearch<ChangeInitiative> determineSearcher(SQLDialect dialect) {

        if (dialect == SQLDialect.POSTGRES) {
            return new PostgresChangeInitiativeSearch();
        }

        if (dialect == SQLDialect.MARIADB) {
            return new MariaChangeInitiativeSearch();
        }

        if (dialect.name().startsWith("SQLSERVER")) {
            return new SqlServerChangeInitiativeSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 
Example 25
Project: waltz   File: MeasurableSearchDao.java   Source Code and License 6 votes vote down vote up
private FullTextSearch<Measurable> determineSearcher(SQLDialect dialect) {

        if (dialect == SQLDialect.POSTGRES) {
            return new PostgresMeasurableSearch();
        }

        if (dialect == SQLDialect.MARIADB) {
            return new MariaMeasurableSearch();
        }

        if (dialect.name().startsWith("SQLSERVER")) {
            return new SqlServerMeasurableSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 
Example 26
Project: waltz   File: PersonSearchDao.java   Source Code and License 6 votes vote down vote up
private FullTextSearch<Person> determineSearcher(SQLDialect dialect) {

        if (dialect == SQLDialect.POSTGRES) {
            return new PostgresPersonSearch();
        }

        if (dialect == SQLDialect.MARIADB) {
            return new MariaPersonSearch();
        }

        if (dialect.name().equals("SQLSERVER2014")) {
            return new SqlServerPersonSearch();
        }

        return new UnsupportedSearcher<>(dialect);
    }
 
Example 27
Project: dropwizard-jooq   File: JooqHealthCheckTest.java   Source Code and License 6 votes vote down vote up
@Override
public MockResult[] execute(MockExecuteContext ctx) throws SQLException {
    MockResult[] mock = new MockResult[1];

    // The execute context contains SQL string(s), bind values, and other meta-data
    String sql = ctx.sql();

    // Exceptions are propagated through the JDBC and jOOQ APIs
    if (sql.equals("SELECT 1")) {
        mock[0] = new MockResult(1, DSL.using(SQLDialect.HSQLDB).newResult());
    } else {
        throw new DataAccessException("Incorrect validation query");
    }

    return mock;
}
 
Example 28
Project: ldadmin   File: LFMemberServiceImpl.java   Source Code and License 6 votes vote down vote up
@PostConstruct
@Autowired(required = true)
public Connection getConnection() throws IllegalStateException,
		InstantiationException, IllegalAccessException,
		ClassNotFoundException, SQLException {
	if (conn == null || conn.isClosed()) {
		log.warn("first call");

		userName = environment.getRequiredProperty("ldadmin.lf.db.user");
		password = environment
				.getRequiredProperty("ldadmin.lf.db.password");
		url = environment.getRequiredProperty("ldadmin.lf.db.url");
		log.info(userName + "  " + url);
		Class.forName("org.postgresql.Driver").newInstance();
		conn = DriverManager.getConnection(url, userName, password);
		dsl = DSL.using(conn, SQLDialect.POSTGRES);
	} else {
		log.info("conn is active");
		if (conn.isClosed()) {
			conn = DriverManager.getConnection(url, userName, password);
			dsl = DSL.using(conn, SQLDialect.POSTGRES);
		}
	}

	return conn;
}
 
Example 29
Project: deciservice   File: HsqlNotesService.java   Source Code and License 6 votes vote down vote up
@Override
public List<Note> createAll(List<Note> notes) {
    if (notes == null || notes.isEmpty()) {
        return Collections.emptyList();
    }
    try (Connection c = POOL.getConnection()) {
        DSLContext context = DSL.using(c, SQLDialect.HSQLDB);

        InsertValuesStep3<NoteRecord, String, String, String> query = context.insertInto(NOTE).columns(NOTE.URINAME, NOTE.TITLE, NOTE.BODY);
        for (Note note : notes) {
            query.values(note.getUriName(), note.getTitle(), note.getBody());
        }
        Result<NoteRecord> nrs = query.returning().fetch();
        return recordsToNotes(nrs);
    } catch (SQLException ex) {
        throw new AppServerException("Failed to create notes: " + ex.getMessage(), ex);
    }
}
 
Example 30
Project: photo-flow   File: SqliteProjectDao.java   Source Code and License 6 votes vote down vote up
@Override
public Optional<Project> load(int id) throws DaoException {

	try (Connection sqliteConnection = provider.getConnection()) {
		sqliteConnection.setAutoCommit(false);
		
		DSLContext create = DSL.using(sqliteConnection, SQLDialect.SQLITE);

		Optional<Project> project = create.select().from("project").where("ID = " + id).fetch().stream().<Project>map(
				record -> {
					return Project.newProject(p -> {
						p.setId(id);
						p.setName((String)record.getValue("name"));
						p.setDescription((String)record.getValue("description"));
						p.setState(ProjectState.valueOf((String)record.getValue("status")));
					});
				}).findFirst();
		
		return project;
		
	} catch (SQLException e) {
		throw new DaoException("Error in loading project", e);
	}
	
}
 
Example 31
Project: photo-flow   File: SqliteProjectDao.java   Source Code and License 6 votes vote down vote up
@Override
public Optional<Todo> loadTodo(int id) throws DaoException {
	try (Connection sqliteConnection = provider.getConnection()) {
		sqliteConnection.setAutoCommit(false);
		
		DSLContext create = DSL.using(sqliteConnection, SQLDialect.SQLITE);

		Optional<Todo> todo = create.select().from("todo").where("ID = " + id).fetch().stream().<Todo>map(record -> {
			Todo tempTodo = new Todo((String)record.getValue("description"));
			tempTodo.setId((int)record.getValue("project_fk"));
			tempTodo.setId((int)record.getValue("ID"));
			tempTodo.setChecked(((int)record.getValue("checked")) == 1 ? true : false);
			
			return tempTodo;
			
		}).findFirst();
		
		return todo;
		
	} catch (SQLException e) {
		throw new DaoException("Error in loading todo", e);
	}
	
}
 
Example 32
Project: worker-service   File: DatabaseManager.java   Source Code and License 6 votes vote down vote up
/**
 * creates new DatabaseManager.
 *
 * @param userName the username for the database
 * @param password the password for the database
 * @param url the url to the database
 * @param providedDBPoolName if not null, it will use the built in Connection pool with the passed Name
 * @param sqlDialect the dialect to use
 * @param disableConnection true to disable the connection, used for testing
 * @throws NamingException if there was a problem establishing a connection to the provided database-pool
 * @throws SQLException if there was a problem establishing a connection to the database
 */
public DatabaseManager(String userName, String password, String url, String providedDBPoolName, SQLDialect sqlDialect,
                       boolean disableConnection) throws NamingException, SQLException {
    this.url = url;
    DataSource ds = null;
    if (providedDBPoolName != null && !providedDBPoolName.isEmpty()) {
        ds = (DataSource) new InitialContext().lookup(providedDBPoolName);
    } else {
        HikariDataSource hds = new HikariDataSource();
        hds.setJdbcUrl(url);
        hds.setUsername(userName);
        hds.setPassword(password);
        ds = hds;
    }
    this.ds = ds;
    if (!disableConnection) {
        this.connection = ds.getConnection();
        context = DSL.using(this.ds, sqlDialect);
    } else {
        this.connection = null;
        context = DSL.using(sqlDialect);
    }
}
 
Example 33
Project: worker-service   File: OperationsDataHolder.java   Source Code and License 6 votes vote down vote up
private Map<CalibrationRecord, Result<CalibrationAnswerOptionRecord>> generateCalibrations(ExperimentRecord experiment) {
    Map<CalibrationRecord, Result<CalibrationAnswerOptionRecord>> map = new HashMap<>();
    DSLContext create = DSL.using(SQLDialect.MYSQL);
    for (int i = 0; i < 5; i++) {
        CalibrationRecord populationRecord = create.newRecord(CALIBRATION);
        populationRecord.setExperiment(experiment.getIdExperiment());
        populationRecord.setIdCalibration(i);
        populationRecord.setName("name" + i);
        populationRecord.setProperty("property" + i);
        Result<CalibrationAnswerOptionRecord> result = create.newResult(CALIBRATION_ANSWER_OPTION);
        for (int j = 0; j < 5; j++) {
            CalibrationAnswerOptionRecord record = create.newRecord(CALIBRATION_ANSWER_OPTION);
            record.setAnswer("answer" + j);
            record.setIdCalibrationAnswerOption(j);
            record.setCalibration(i);
            result.add(record);
        }
        map.put(populationRecord, result);
    }
    return map;
}
 
Example 34
Project: collect-mobile   File: CollectModelManager.java   Source Code and License 6 votes vote down vote up
public CollectModelManager(SurveyManager surveyManager,
                           RecordManager recordManager,
                           CodeListManager codeListManager,
                           SpeciesManager speciesManager,
                           RecordFileManager recordFileManager,
                           Database database) {
    this.surveyManager = surveyManager;
    this.recordManager = recordManager;
    this.codeListManager = codeListManager;
    this.speciesManager = speciesManager;
    this.recordFileManager = recordFileManager;
    codeListSizeEvaluator = new CodeListSizeEvaluator(new DatabaseCodeListSizeDao(database));

    DefaultConfiguration defaultConfiguration = new DefaultConfiguration();
    defaultConfiguration.setSettings(defaultConfiguration.settings().withRenderSchema(false));
    defaultConfiguration
            .set(database.dataSource())
            .set(SQLDialect.SQLITE);
    jooqDsl = new CollectDSLContext(defaultConfiguration);
}
 
Example 35
Project: adjule   File: SubmissionFilterAppenderTest.java   Source Code and License 6 votes vote down vote up
@Test
public void should_not_contain_where_clause_on_submissions() throws Exception {
    // given
    SubmissionListFilter filter = new SubmissionListFilter();
    filter.getStatuses().setStatuses(new ArrayList<>());
    final DSLContext create = new DefaultDSLContext(SQLDialect.POSTGRES);
    final SelectJoinStep step = create.select().from();

    // when
    SubmissionFilterAppender.appendOn(filter, step);

    // then
    assertThat("should not contain where clause on submissions",
               step.getSQL(),
               not(containsString("where 1 = 0")));
}
 
Example 36
Project: jOOQ-s-Java-8-Goodies   File: TransactionScopeGoodies.java   Source Code and License 6 votes vote down vote up
void run(Transactional tx) {
    // Initialise some jOOQ objects
    final DefaultConnectionProvider c = new DefaultConnectionProvider(connection);
    final Configuration configuration = new DefaultConfiguration().set(c).set(SQLDialect.H2);

    try {
        // Run the transaction and pass a jOOQ
        // DSLContext object to it
        tx.run(DSL.using(configuration));

        // If we get here, then commit the
        // transaction
        c.commit();
    }
    catch (RuntimeException e) {

        // Any exception will cause a rollback
        c.rollback();
        System.err.println(e.getMessage());

        // Eat exceptions in silent mode.
        if (!silent)
            throw e;
    }
}
 
Example 37
Project: killbill-analytics-plugin   File: SqlReportDataExtractor.java   Source Code and License 6 votes vote down vote up
public SqlReportDataExtractor(final String tableName,
                              final ReportSpecification reportSpecification,
                              @Nullable final DateTime startDate,
                              @Nullable final DateTime endDate,
                              final SQLDialect sqlDialect,
                              final Long tenantRecordId) {
    this.tableName = tableName;
    this.reportSpecification = reportSpecification;
    this.startDate = startDate;
    this.endDate = endDate;
    this.tenantRecordId = tenantRecordId;

    final Settings settings = new Settings();
    settings.withStatementType(StatementType.STATIC_STATEMENT);
    settings.withRenderFormatted(true);
    if (SQLDialect.H2.equals(sqlDialect)) {
        settings.withRenderNameStyle(RenderNameStyle.AS_IS);
    }
    this.context = DSL.using(sqlDialect, settings);

    setup();
}
 
Example 38
Project: Ultrastructure   File: WorkspaceSnapshot_ESTest.java   Source Code and License 6 votes vote down vote up
@Test(timeout = 4000)
public void test02() throws Throwable {
    SQLDialect sQLDialect0 = SQLDialect.POSTGRES_9_4;
    DefaultDSLContext defaultDSLContext0 = new DefaultDSLContext(sQLDialect0);
    Product product0 = new Product();
    // Undeclared exception!
    try {
        WorkspaceSnapshot.selectWorkspaceClosure(defaultDSLContext0,
                                                 product0);
        fail("Expecting exception: RuntimeException");

    } catch (RuntimeException e) {
        //
        // Cannot execute query. No Connection configured
        //
        assertThrownBy("org.jooq.impl.AbstractQuery", e);
    }
}
 
Example 39
Project: Ultrastructure   File: WorkspaceSnapshot_ESTest.java   Source Code and License 6 votes vote down vote up
@Test(timeout = 4000)
public void test05() throws Throwable {
    WorkspaceSnapshot workspaceSnapshot0 = new WorkspaceSnapshot();
    Product product0 = new Product();
    workspaceSnapshot0.definingProduct = product0;
    DefaultConfiguration defaultConfiguration0 = new DefaultConfiguration();
    NoConnectionProvider noConnectionProvider0 = (NoConnectionProvider) defaultConfiguration0.connectionProvider();
    SQLDialect sQLDialect0 = SQLDialect.SQLITE;
    Settings settings0 = new Settings();
    DefaultDSLContext defaultDSLContext0 = new DefaultDSLContext(noConnectionProvider0,
                                                                 sQLDialect0,
                                                                 settings0);
    // Undeclared exception!
    try {
        workspaceSnapshot0.loadDefiningProduct(defaultDSLContext0);
        fail("Expecting exception: RuntimeException");

    } catch (RuntimeException e) {
        //
        // Cannot execute query. No Connection configured
        //
        assertThrownBy("org.jooq.impl.AbstractQuery", e);
    }
}
 
Example 40
Project: Ultrastructure   File: WorkspaceSnapshot_ESTest.java   Source Code and License 6 votes vote down vote up
@Test(timeout = 4000)
public void test09() throws Throwable {
    WorkspaceSnapshot workspaceSnapshot0 = new WorkspaceSnapshot();
    Product product0 = new Product();
    workspaceSnapshot0.definingProduct = product0;
    SQLDialect sQLDialect0 = SQLDialect.MARIADB;
    DefaultDSLContext defaultDSLContext0 = new DefaultDSLContext(sQLDialect0);
    // Undeclared exception!
    try {
        workspaceSnapshot0.load(defaultDSLContext0);
        fail("Expecting exception: RuntimeException");

    } catch (RuntimeException e) {
        //
        // Cannot execute query. No Connection configured
        //
        assertThrownBy("org.jooq.impl.AbstractQuery", e);
    }
}