Java 类org.jooq.SQLDialect 实例源码

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

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

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

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

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

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

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

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

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

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

    }

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

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

/**
 * 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);
}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

}

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

}

/**
 * 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);
    }
}

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

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

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

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

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

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

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

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