org.jooq.Result Java Examples

The following examples show how to use org.jooq.Result. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: HmfGenePanelBuilder.java    From hmftools with GNU General Public License v3.0 6 votes vote down vote up
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());
}
 
Example #2
Source File: CMSCrawler.java    From oneops with Apache License 2.0 6 votes vote down vote up
private List<Deployment> getDeployments(Connection conn, Environment env) {

        List<Deployment> deployments = new ArrayList<>();
        DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
        Result<Record> records = create.select().from(DJ_DEPLOYMENT)
                .join(DJ_DEPLOYMENT_STATES).on(DJ_DEPLOYMENT_STATES.STATE_ID.eq(DJ_DEPLOYMENT.STATE_ID))
                .join(NS_NAMESPACES).on(NS_NAMESPACES.NS_ID.eq(DJ_DEPLOYMENT.NS_ID))
                .where(NS_NAMESPACES.NS_PATH.eq(env.getPath()+ "/" + env.getName() + "/bom"))
                .and(DJ_DEPLOYMENT.CREATED_BY.notEqual("oneops-autoreplace"))
                .orderBy(DJ_DEPLOYMENT.CREATED.desc())
                .limit(10)
                .fetch();
        for (Record r : records) {
            Deployment deployment = new Deployment();
            deployment.setCreatedAt(r.getValue(DJ_DEPLOYMENT.CREATED));
            deployment.setCreatedBy(r.getValue(DJ_DEPLOYMENT.CREATED_BY));
            deployment.setState(r.getValue(DJ_DEPLOYMENT_STATES.STATE_NAME));
            deployment.setDeploymentId(r.getValue(DJ_DEPLOYMENT.DEPLOYMENT_ID));
            deployments.add(deployment);
        }
        return deployments;
    }
 
Example #3
Source File: CMSCrawler.java    From oneops with Apache License 2.0 6 votes vote down vote up
private List<Environment> getOneopsEnvironments(Connection conn) {
    List<Environment> envs = new ArrayList<>();
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
    log.info("Fetching all environments..");
    Result<Record> envRecords = create.select().from(CM_CI)
            .join(MD_CLASSES).on(CM_CI.CLASS_ID.eq(MD_CLASSES.CLASS_ID))
            .join(NS_NAMESPACES).on(CM_CI.NS_ID.eq(NS_NAMESPACES.NS_ID))
            .where(MD_CLASSES.CLASS_NAME.eq("manifest.Environment"))
            .fetch(); //all the env cis
    log.info("Got all environments");
    for (Record r : envRecords) {
        long envId = r.getValue(CM_CI.CI_ID);
        //now query attributes for this env
        Environment env = new Environment();
        env.setName(r.getValue(CM_CI.CI_NAME));
        env.setId(r.getValue(CM_CI.CI_ID));
        env.setPath(r.getValue(NS_NAMESPACES.NS_PATH));
        env.setNsId(r.getValue(NS_NAMESPACES.NS_ID));
        envs.add(env);
    }
    return envs;
}
 
Example #4
Source File: CMSCrawler.java    From oneops with Apache License 2.0 6 votes vote down vote up
private List<String> getActiveClouds(Platform platform, Connection conn) {
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
    List<String> clouds = new ArrayList<>();
    Result<Record> consumesRecords = create.select().from(CM_CI_RELATIONS)
            .join(MD_RELATIONS).on(MD_RELATIONS.RELATION_ID.eq(CM_CI_RELATIONS.RELATION_ID))
            .join(CM_CI_RELATION_ATTRIBUTES).on(CM_CI_RELATION_ATTRIBUTES.CI_RELATION_ID.eq(CM_CI_RELATIONS.CI_RELATION_ID))
            .where(CM_CI_RELATIONS.FROM_CI_ID.eq(platform.getId()))
            .and(CM_CI_RELATION_ATTRIBUTES.DF_ATTRIBUTE_VALUE.eq("active"))
            .fetch();
    for (Record r : consumesRecords) {
        String comments = r.getValue(CM_CI_RELATIONS.COMMENTS);
        String cloudName = comments.split(":")[1];
        cloudName = cloudName.split("\"")[1];
        clouds.add(cloudName);
    }
    return clouds;
}
 
Example #5
Source File: Application.java    From hellokoding-courses with MIT License 6 votes vote down vote up
public static void main(String[] args) throws Exception {
    String user = System.getProperty("jdbc.user");
    String password = System.getProperty("jdbc.password");
    String url = System.getProperty("jdbc.url");
    String driver = System.getProperty("jdbc.driver");

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

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

            System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
        }
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}
 
Example #6
Source File: DBRequestRuleStore.java    From LoboBrowser with MIT License 6 votes vote down vote up
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();
  }
}
 
Example #7
Source File: BatchTest.java    From high-performance-java-persistence with Apache License 2.0 6 votes vote down vote up
@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());
    });
}
 
Example #8
Source File: BatchTest.java    From high-performance-java-persistence with Apache License 2.0 6 votes vote down vote up
@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());
    });
}
 
Example #9
Source File: BatchTest.java    From high-performance-java-persistence with Apache License 2.0 6 votes vote down vote up
@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());
    });
}
 
Example #10
Source File: JooqTaskRelocationResultStore.java    From titus-control-plane with Apache License 2.0 6 votes vote down vote up
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()
            ));
}
 
Example #11
Source File: BatchTest.java    From high-performance-java-persistence with Apache License 2.0 6 votes vote down vote up
@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());
    });
}
 
Example #12
Source File: BatchTest.java    From high-performance-java-persistence with Apache License 2.0 6 votes vote down vote up
@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());
    });
}
 
Example #13
Source File: JooqDBUnitTest.java    From database-rider with Apache License 2.0 6 votes vote down vote up
@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());
}
 
Example #14
Source File: ResultBuilder.java    From FROST-Server with GNU Lesser General Public License v3.0 6 votes vote down vote up
@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;
}
 
Example #15
Source File: EnsemblDAO.java    From hmftools with GNU General Public License v3.0 6 votes vote down vote up
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;
}
 
Example #16
Source File: BatchTest.java    From high-performance-java-persistence with Apache License 2.0 6 votes vote down vote up
@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());
    });
}
 
Example #17
Source File: EnsemblDAO.java    From hmftools with GNU General Public License v3.0 6 votes vote down vote up
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);
}
 
Example #18
Source File: UserRoleDao.java    From waltz with Apache License 2.0 6 votes vote down vote up
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());
}
 
Example #19
Source File: EnsemblDAO.java    From hmftools with GNU General Public License v3.0 6 votes vote down vote up
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);
}
 
Example #20
Source File: SecretContentDAO.java    From keywhiz with Apache License 2.0 6 votes vote down vote up
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();
  }
}
 
Example #21
Source File: StructuralVariantDAO.java    From hmftools with GNU General Public License v3.0 6 votes vote down vote up
@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;
}
 
Example #22
Source File: PurityDAO.java    From hmftools with GNU General Public License v3.0 6 votes vote down vote up
@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;
}
 
Example #23
Source File: DBRequestRuleStore.java    From LoboBrowser with MIT License 5 votes vote down vote up
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;
  }
}
 
Example #24
Source File: Query.java    From sequenceiq-samples with Apache License 2.0 5 votes vote down vote up
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));
}
 
Example #25
Source File: AppGroupFromOrgUnitImporter.java    From waltz with Apache License 2.0 5 votes vote down vote up
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());
}
 
Example #26
Source File: SpringBootIntegrationTest.java    From tutorials with MIT License 5 votes vote down vote up
@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()));
}
 
Example #27
Source File: SpringBootIntegrationTest.java    From tutorials with MIT License 5 votes vote down vote up
@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));
}
 
Example #28
Source File: HmfGenePanelBuilder.java    From hmftools with GNU General Public License v3.0 5 votes vote down vote up
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();
}
 
Example #29
Source File: HmfGenePanelBuilder.java    From hmftools with GNU General Public License v3.0 5 votes vote down vote up
@NotNull
private static Result<Record> queryEnsembldb(@NotNull final String database) throws SQLException, IOException {
    // Disable annoying jooq self-ad message
    System.setProperty("org.jooq.no-logo", "true");
    final Connection conn = DriverManager.getConnection(database, DB_USER, "");
    final DSLContext context = DSL.using(conn, SQLDialect.MYSQL);
    final String query = readEnsemblQuery();
    return context.fetch(query);
}
 
Example #30
Source File: SpringBootIntegrationTest.java    From tutorials with MIT License 5 votes vote down vote up
@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));
}