org.jooq.Result Java Examples

public static void main(String[] args) throws ParseException, IOException, SQLException {
    final Options options = createOptions();
    final CommandLine cmd = createCommandLine(args, options);

    final String outputFilePath = cmd.getOptionValue(OUT_PATH);
    final String ensemblVersion = cmd.getOptionValue(ENSEMBL_VERSION);

    if (outputFilePath == null || !VERSIONS.contains(ensemblVersion)) {
        final HelpFormatter formatter = new HelpFormatter();
        formatter.printHelp("HmfGenePanelBuilder", options);
        System.exit(1);
    }

    final String database = ensemblVersion.equals("37") ? ENSEMBLDB_URL_37 : ENSEMBLDB_URL_38;

    LOGGER.info("Querying " + database);
    final Result<Record> queryResults = queryEnsembldb(database);
    writeFile(cmd, queryResults);
    LOGGER.info("Written output to " + new File(outputFilePath).getAbsolutePath());
}
 

private List<Deployment> getDeployments(Connection conn, Environment env) {

        List<Deployment> deployments = new ArrayList<>();
        DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
        Result<Record> records = create.select().from(DJ_DEPLOYMENT)
                .join(DJ_DEPLOYMENT_STATES).on(DJ_DEPLOYMENT_STATES.STATE_ID.eq(DJ_DEPLOYMENT.STATE_ID))
                .join(NS_NAMESPACES).on(NS_NAMESPACES.NS_ID.eq(DJ_DEPLOYMENT.NS_ID))
                .where(NS_NAMESPACES.NS_PATH.eq(env.getPath()+ "/" + env.getName() + "/bom"))
                .and(DJ_DEPLOYMENT.CREATED_BY.notEqual("oneops-autoreplace"))
                .orderBy(DJ_DEPLOYMENT.CREATED.desc())
                .limit(10)
                .fetch();
        for (Record r : records) {
            Deployment deployment = new Deployment();
            deployment.setCreatedAt(r.getValue(DJ_DEPLOYMENT.CREATED));
            deployment.setCreatedBy(r.getValue(DJ_DEPLOYMENT.CREATED_BY));
            deployment.setState(r.getValue(DJ_DEPLOYMENT_STATES.STATE_NAME));
            deployment.setDeploymentId(r.getValue(DJ_DEPLOYMENT.DEPLOYMENT_ID));
            deployments.add(deployment);
        }
        return deployments;
    }
 

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

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

public void storePermissions(final String frameHost, final String requestHost, final Optional<RequestKind> kindOpt,
    final PermissionSystem.Permission permission) {
  final Result<PermissionsRecord> permissionRecords = AccessController.doPrivileged((PrivilegedAction<Result<PermissionsRecord>>) () -> {
    return userDB.fetch(Permissions.PERMISSIONS, matchHostsCondition(frameHost, requestHost));
  });

  final Integer permissionMask = makeBitSetMask(kindOpt, permission);

  if (permissionRecords.isEmpty()) {
    final PermissionsRecord newPermissionRecord = new PermissionsRecord(frameHost, requestHost, permissionMask);
    newPermissionRecord.attach(userDB.configuration());
    newPermissionRecord.store();
  } else {
    final PermissionsRecord existingRecord = permissionRecords.get(0);
    final Integer existingPermissions = existingRecord.getPermissions();
    final int newPermissions = (existingPermissions & makeBitBlockMask(kindOpt)) | permissionMask;
    existingRecord.setPermissions(newPermissions);
    existingRecord.store();
  }
}
 

@Test
public void testBatching() {
    doInJOOQ(sql -> {
        sql.delete(POST).execute();
        BatchBindStep batch = sql.batch(sql
            .insertInto(POST, POST.ID, POST.TITLE)
            .values((Long) null, null)
        );
        for (int i = 0; i < 3; i++) {
            batch.bind(i, String.format("Post no. %d", i));
        }
        int[] insertCounts = batch.execute();
        assertEquals(3, insertCounts.length);
        Result<Record> posts = sql.select().from(POST).fetch();
        assertEquals(3, posts.size());
    });
}
 

@Test
public void testBatching() {
    doInJOOQ(sql -> {
        sql.delete(POST).execute();
        BatchBindStep batch = sql.batch(sql
            .insertInto(POST, POST.ID, POST.TITLE)
            .values((Long) null, null)
        );
        for (int i = 0; i < 3; i++) {
            batch.bind(i, String.format("Post no. %d", i));
        }
        int[] insertCounts = batch.execute();
        assertEquals(3, insertCounts.length);
        Result<Record> posts = sql.select().from(POST).fetch();
        assertEquals(3, posts.size());
    });
}
 

@Test
public void testBatching() {
    doInJOOQ(sql -> {
        sql.delete(POST).execute();
        BatchBindStep batch = sql.batch(sql
            .insertInto(POST, POST.ID, POST.TITLE)
            .values((BigInteger) null, null)
        );
        for (int i = 0; i < 3; i++) {
            batch.bind(i, String.format("Post no. %d", i));
        }
        int[] insertCounts = batch.execute();
        assertEquals(3, insertCounts.length);
        Result<Record> posts = sql.select().from(POST).fetch();
        assertEquals(3, posts.size());
    });
}
 

private void loadToCache(Set<String> notCached, Configuration configuration) {
    Result<JRelocationStatusRecord> loaded = configuration.dsl().selectFrom(RELOCATION_STATUS).where(RELOCATION_STATUS.TASK_ID.in(notCached)).fetch();
    loaded.forEach(record ->
            statusesByTaskId.put(record.getTaskId(),
                    TaskRelocationStatus.newBuilder()
                            .withTaskId(record.getTaskId())
                            .withState(TaskRelocationStatus.TaskRelocationState.valueOf(record.getRelocationState()))
                            .withStatusCode(record.getStatusCode())
                            .withStatusMessage(record.getStatusMessage())
                            .withTaskRelocationPlan(TaskRelocationPlan.newBuilder()
                                    .withTaskId(record.getTaskId())
                                    .withReason(TaskRelocationPlan.TaskRelocationReason.valueOf(record.getReasonCode()))
                                    .withReasonMessage(record.getReasonMessage())
                                    .withDecisionTime(record.getRelocationDecisionTime().getTime())
                                    .withRelocationTime(record.getRelocationPlanTime().getTime())
                                    .build()
                            )
                            .withTimestamp(record.getRelocationExecutionTime().getTime())
                            .build()
            ));
}
 

@Test @Ignore("values(Collection) is not INSERT INTO ... VALUES ( (..) (..) (..) )")
public void testBatchingWithCollection() {
    doInJOOQ(sql -> {
        sql.delete(POST).execute();

        int insertCount = sql
        .insertInto(POST, POST.TITLE)
        .values(IntStream.range(1, 3).boxed()
                .map(i -> String.format("Post no. %d", i))
                .collect(Collectors.toList()))
        .execute();
        assertEquals(3, insertCount);
        Result<Record> posts = sql.select().from(POST).fetch();
        assertEquals(3, posts.size());
    });
}
 

@Test
public void testBatchingReturning() {
    doInJOOQ(sql -> {
        sql.delete(POST).execute();
        BatchBindStep batch = sql.batch(sql
            .insertInto(POST, POST.TITLE)
            .values("?")
        );
        for (int i = 0; i < 3; i++) {
            batch.bind(String.format("Post no. %d", i));
        }
        int[] insertCounts = batch.execute();
        assertEquals(3, insertCounts.length);
        Result<Record> posts = sql.select().from(POST).fetch();
        assertEquals(3, posts.size());
    });
}
 

@Test
@DataSet("authors.yml,books.yml")
public void shouldListAuthorsAndBooks() {
    Result<?> result =
            DSL.using(connection)
                    .select(
                            Tables.AUTHOR.FIRST_NAME,
                            Tables.AUTHOR.LAST_NAME,
                            Tables.BOOK.ID,
                            Tables.BOOK.TITLE
                    )
                    .from(Tables.AUTHOR)
                    .join(Tables.BOOK)
                    .on(Tables.AUTHOR.ID.eq(Tables.BOOK.AUTHOR_ID))
                    .orderBy(Tables.BOOK.ID.asc())
                    .fetch();

    assertEquals(4, result.size());
}
 

@Override
public void visit(PathElementEntity element) {
    Result<Record> results = sqlQuery.fetch();
    if (results.size() > 1) {
        throw new IllegalStateException("Expecting an element, yet more than 1 result. Got " + results.size() + " results.");
    }
    if (results.isEmpty()) {
        return;
    }

    EntityFactory factory;
    factory = pm.getEntityFactories().getFactoryFor(element.getEntityType());
    Entity entity = factory.create(results.get(0), staQuery, new DataSize());

    if (entity == null) {
        throw new IllegalStateException("Failed to create an entity from result set.");
    }
    expandEntity(entity, staQuery);
    resultObject = entity;
}
 

private int findCoordSystemId()
{
    final String version = mRefGenomeVersion == HG37 ? "GRCh37" : "GRCh38";

    final String queryStr = "select coord_system_id from coord_system"
            + " where version = '" + version + "'"
            + " order by rank  limit 1";

    LNX_LOGGER.debug("gene query: {}", queryStr);

    Result<?> results = mDbContext.fetch(queryStr);

    for(final Record record : results)
    {
        UInteger coordSystemId = (UInteger) record.get("coord_system_id");
        return coordSystemId.intValue();
    }

    return -1;
}
 

@Test
public void testBatching() {
    doInJOOQ(sql -> {
        sql.delete(POST).execute();
        BatchBindStep batch = sql.batch(sql
            .insertInto(POST, POST.TITLE)
            .values("?")
        );
        for (int i = 0; i < 3; i++) {
            batch.bind(String.format("Post no. %d", i));
        }
        int[] insertCounts = batch.execute();
        assertEquals(3, insertCounts.length);
        Result<Record> posts = sql.select().from(POST).fetch();
        assertEquals(3, posts.size());
    });
}
 

private Result<?> queryAllGeneData()
{
    final String queryStr = "select gene.stable_id as GeneId, display_xref.display_label as GeneName, seq_region.name as Chromosome,"
            + "gene.seq_region_strand as Strand, gene.seq_region_start as GeneStart, gene.seq_region_end as GeneEnd,"
            + " GROUP_CONCAT(DISTINCT entrez_xref.dbprimary_acc ORDER BY entrez_xref.dbprimary_acc SEPARATOR ';') as EntrezIds,"
            + " GROUP_CONCAT(DISTINCT karyotype.band ORDER BY karyotype.band SEPARATOR '-') as KaryotypeBand,"
            + " GROUP_CONCAT(DISTINCT syn_xref.dbprimary_acc ORDER BY syn_xref.dbprimary_acc SEPARATOR ';') as Synonyms"
            + " from gene"
            + " inner join object_xref as ox on gene.gene_id = ox.ensembl_id and ox.ensembl_object_type = 'GENE'"
            + " inner join xref as display_xref on display_xref.xref_id = gene.display_xref_id"
            + " inner join karyotype on gene.seq_region_id = karyotype.seq_region_id"
            + " inner join seq_region on gene.seq_region_id = seq_region.seq_region_id"
            + " left join xref as entrez_xref on (entrez_xref.xref_id = ox.xref_id and entrez_xref.external_db_id = 1300)"
            + " inner join xref as syn_xref on syn_xref.xref_id = ox.xref_id"
            + " where seq_region.name in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', 'X', 'Y', 'MT')"
            + " and ((gene.seq_region_start >= karyotype.seq_region_start and gene.seq_region_start <= karyotype.seq_region_end)"
            + " or (gene.seq_region_end >= karyotype.seq_region_start and gene.seq_region_end <= karyotype.seq_region_end))"
            + " and seq_region.coord_system_id = " + mCoordSystemId
            + " group by Chromosome, GeneStart, GeneEnd, GeneId, GeneName, Strand"
            + " order by Chromosome, GeneStart;";

    LNX_LOGGER.debug("gene query: {}", queryStr);

    return mDbContext.fetch(queryStr);
}
 

public List<User> findAllUsers() {
    Result<Record2<String, String>> records = dsl.select(USER.USER_NAME, USER_ROLE.ROLE)
            .from(USER)
            .leftOuterJoin(USER_ROLE)
            .on(USER.USER_NAME.eq(USER_ROLE.USER_NAME))
            .fetch();

    Map<String, List<Record2<String, String>>> byUserName = records.stream()
            .collect(groupingBy(r -> r.getValue(USER.USER_NAME)));

    return byUserName.entrySet().stream()
            .map( entry -> ImmutableUser.builder()
                    .userName(entry.getKey())
                    .roles(entry.getValue()
                            .stream()
                            .map(record -> record.getValue(USER_ROLE.ROLE))
                            .filter(Objects::nonNull)
                            .collect(Collectors.toList()))
                    .build())
            .collect(toList());
}
 

private Result<?> queryAllTranscriptExonData()
{
    final String queryStr = "select q1.*,"
            + " if(Strand = -1, ce.seq_region_end - tl.seq_end + 1, cs.seq_region_start + tl.seq_start - 1) as CodingStart,"
            + " if(Strand = -1, cs.seq_region_end - tl.seq_start + 1, ce.seq_region_start + tl.seq_end - 1) as CodingEnd"
            + " from ("
            + " select g.stable_id As GeneId, g.canonical_transcript_id as CanonicalTranscriptId,"
            + " t.seq_region_strand as Strand, t.transcript_id as TransId, t.stable_id as Trans, t.biotype as BioType,"
            + " t.seq_region_start as TransStart, t.seq_region_end as TransEnd,"
            + " et.rank as ExonRank, e.seq_region_start as ExonStart, e.seq_region_end as ExonEnd, e.phase as ExonPhase, e.end_phase as ExonEndPhase"
            + " from transcript as t, exon as e, exon_transcript as et, gene as g, xref as x"
            + " where t.transcript_id = et.transcript_id and e.exon_id = et.exon_id and g.display_xref_id = x.xref_id"
            + " and t.gene_id = g.gene_id"
            + " ) as q1"
            + " left join translation tl on tl.transcript_id = TransId"
            + " left join exon cs on cs.exon_id = tl.start_exon_id"
            + " left join exon ce on ce.exon_id = tl.end_exon_id"
            + " order by GeneId, TransId, ExonStart";

    LNX_LOGGER.debug("transcript query: {}", queryStr);

    return mDbContext.fetch(queryStr);
}
 

public Optional<ImmutableList<SecretContent>> getSecretVersionsBySecretId(long id,
    int versionIdx, int numVersions) {
  Result<SecretsContentRecord> r = dslContext.selectFrom(SECRETS_CONTENT)
      .where(SECRETS_CONTENT.SECRETID.eq(id))
      .orderBy(SECRETS_CONTENT.CREATEDAT.desc())
      .limit(versionIdx, numVersions)
      .fetch();

  if (r != null && r.isNotEmpty()) {
    ImmutableList.Builder<SecretContent> b = new ImmutableList.Builder<>();
    b.addAll(r.map(secretContentMapper));
    return Optional.of(b.build());
  } else {
    return Optional.empty();
  }
}
 

@NotNull
List<String> getSamplesList(@NotNull String sampleSearch) {
    Result<Record1<String>> result = sampleSearch.equals("")
            ? context.select(STRUCTURALVARIANT.SAMPLEID)
            .from(STRUCTURALVARIANT)
            .groupBy(STRUCTURALVARIANT.SAMPLEID)
            .fetch()
            : context.select(STRUCTURALVARIANT.SAMPLEID)
                    .from(STRUCTURALVARIANT)
                    .where(STRUCTURALVARIANT.SAMPLEID.like(sampleSearch))
                    .groupBy(STRUCTURALVARIANT.SAMPLEID)
                    .fetch();

    List<String> samplesList = Lists.newArrayList();

    for (Record record : result) {
        samplesList.add(record.getValue(STRUCTURALVARIANT.SAMPLEID));
    }

    return samplesList;
}
 

@NotNull
List<String> getSamplesPassingQC(double minPurity) {
    List<String> sampleIds = Lists.newArrayList();

    Result<Record> result = context.select()
            .from(PURITY)
            .where(PURITY.PURITY_.ge(minPurity))
            .and(PURITY.STATUS.ne(NO_TUMOR.toString()))
            .and(PURITY.QCSTATUS.eq("PASS"))
            .fetch();

    for (Record record : result) {
        sampleIds.add(record.getValue(PURITY.SAMPLEID));
    }

    return sampleIds;
}
 

public Pair<PermissionSystem.Permission, PermissionSystem.Permission[]> getPermissions(final String frameHostPattern, final String requestHost) {
  final Result<PermissionsRecord> permissionRecords = AccessController.doPrivileged((PrivilegedAction<Result<PermissionsRecord>>) () -> {
    return userDB.fetch(Permissions.PERMISSIONS, matchHostsCondition(frameHostPattern, requestHost));
  });

  if (permissionRecords.isEmpty()) {
    return defaultPermissionPair;
  } else {
    final PermissionsRecord existingRecord = permissionRecords.get(0);
    final Integer existingPermissions = existingRecord.getPermissions();
    final Pair<PermissionSystem.Permission, PermissionSystem.Permission[]> permissions = decodeBitMask(existingPermissions);
    return permissions;
  }
}
 

public void selectUsersWithLessAmount(int amount){
    Result<Record> result = dslContext
            .select()
            .from(tableByName("customers").as("c"))
            .join(tableByName("orders").as("o")).on("o.customer_id = c.id")
            .where(fieldByName("o.amount").lessThan(amount))
            .orderBy(fieldByName("c.name").asc())
            .fetch();
    System.out.println(result.format(10));
}
 

private static Set<Tuple2<Long, Long>> findExistingAppGroupEntryRecords(DSLContext dsl, Result<Record1<Long>> appGroupOrgEntries) {
    return dsl
            .select(APPLICATION_GROUP_ENTRY.GROUP_ID, APPLICATION_GROUP_ENTRY.APPLICATION_ID)
            .from(APPLICATION_GROUP_ENTRY)
            .where(APPLICATION_GROUP_ENTRY.GROUP_ID.in(appGroupOrgEntries))
            .fetch()
            .stream()
            .map(t -> tuple(t.value1(), t.value2()))
            .collect(Collectors.toSet());
}
 

@Test
public void givenValidData_whenInserting_thenSucceed() {
    dsl.insertInto(AUTHOR)
            .set(AUTHOR.ID, 4)
            .set(AUTHOR.FIRST_NAME, "Herbert")
            .set(AUTHOR.LAST_NAME, "Schildt")
            .execute();

    dsl.insertInto(BOOK)
            .set(BOOK.ID, 4)
            .set(BOOK.TITLE, "A Beginner's Guide")
            .execute();

    dsl.insertInto(AUTHOR_BOOK)
            .set(AUTHOR_BOOK.AUTHOR_ID, 4)
            .set(AUTHOR_BOOK.BOOK_ID, 4)
            .execute();

    final Result<Record3<Integer, String, Integer>> result = dsl.select(AUTHOR.ID, AUTHOR.LAST_NAME, DSL.count())
            .from(AUTHOR).join(AUTHOR_BOOK).on(AUTHOR.ID.equal(AUTHOR_BOOK.AUTHOR_ID))
            .join(BOOK).on(AUTHOR_BOOK.BOOK_ID.equal(BOOK.ID))
            .groupBy(AUTHOR.LAST_NAME)
            .orderBy(AUTHOR.LAST_NAME.desc())
            .fetch();

    assertEquals(3, result.size());
    assertEquals("Sierra", result.getValue(0, AUTHOR.LAST_NAME));
    assertEquals(Integer.valueOf(2), result.getValue(0, DSL.count()));
    assertEquals("Bates", result.getValue(2, AUTHOR.LAST_NAME));
    assertEquals(Integer.valueOf(1), result.getValue(2, DSL.count()));
}
 

@Test
public void givenValidData_whenUpdating_thenSucceed() {
    dsl.update(AUTHOR)
            .set(AUTHOR.LAST_NAME, "Baeldung")
            .where(AUTHOR.ID.equal(3))
            .execute();

    dsl.update(BOOK)
            .set(BOOK.TITLE, "Building your REST API with Spring")
            .where(BOOK.ID.equal(3))
            .execute();

    dsl.insertInto(AUTHOR_BOOK)
            .set(AUTHOR_BOOK.AUTHOR_ID, 3)
            .set(AUTHOR_BOOK.BOOK_ID, 3)
            .execute();

    final Result<Record3<Integer, String, String>> result = dsl.select(AUTHOR.ID, AUTHOR.LAST_NAME, BOOK.TITLE)
            .from(AUTHOR).join(AUTHOR_BOOK).on(AUTHOR.ID.equal(AUTHOR_BOOK.AUTHOR_ID))
            .join(BOOK).on(AUTHOR_BOOK.BOOK_ID.equal(BOOK.ID))
            .where(AUTHOR.ID.equal(3))
            .fetch();

    assertEquals(1, result.size());
    assertEquals(Integer.valueOf(3), result.getValue(0, AUTHOR.ID));
    assertEquals("Baeldung", result.getValue(0, AUTHOR.LAST_NAME));
    assertEquals("Building your REST API with Spring", result.getValue(0, BOOK.TITLE));
}
 

private static void writeFile(@NotNull final CommandLine cmd, @NotNull final Result<Record> records) throws IOException {
    final BufferedWriter writer = new BufferedWriter(new FileWriter(cmd.getOptionValue(OUT_PATH), false));
    // Format as tsv without header containing column names
    final CSVFormat format = new CSVFormat().header(false).delimiter('\t').nullString("").quoteString("");
    writer.write(records.formatCSV(format));
    writer.close();
}
 

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

@Test
public void givenValidData_whenDeleting_thenSucceed() {
    dsl.delete(AUTHOR)
            .where(AUTHOR.ID.lt(3))
            .execute();

    final Result<Record3<Integer, String, String>> result = dsl.select(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
            .from(AUTHOR).fetch();

    assertEquals(1, result.size());
    assertEquals("Bryan", result.getValue(0, AUTHOR.FIRST_NAME));
    assertEquals("Basham", result.getValue(0, AUTHOR.LAST_NAME));
}