Java 类org.jooq.Result 实例源码

/**
 * Fetches all {@link Locale}s for which {@link ContentSource}s
 * of the given {@link Provider} are available.
 *
 * @param providerId
 * @return a List (never null) with the {@link Locale}s.
 */
public List<Locale> getLocalesOfProvider(final int providerId) {
    final Result<Record1<String>> result = DSL.using(jooqConfig).
            select(TABLE.LOCALE).
            from(TABLE).
            where(TABLE.PROVIDER_ID.eq(providerId)).
            groupBy(TABLE.LOCALE).
            fetch();

    final List<Locale> locales = new ArrayList<>();
    for (final String localeStr : result.getValues(TABLE.LOCALE)) {
        locales.add(LocaleUtil.toLocale(localeStr));
    }

    return locales;
}

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

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

@NotNull
public List<PurpleCopyNumber> read(@NotNull final String sample) {
    List<PurpleCopyNumber> copyNumbers = Lists.newArrayList();

    Result<Record> result = context.select().from(COPYNUMBER).where(COPYNUMBER.SAMPLEID.eq(sample)).fetch();

    for (Record record : result) {
        copyNumbers.add(ImmutablePurpleCopyNumber.builder()
                .chromosome(record.getValue(COPYNUMBER.CHROMOSOME))
                .start(record.getValue(COPYNUMBER.START))
                .end(record.getValue(COPYNUMBER.END))
                .bafCount(record.getValue(COPYNUMBER.BAFCOUNT))
                .method(CopyNumberMethod.valueOf(record.getValue(COPYNUMBER.COPYNUMBERMETHOD)))
                .segmentStartSupport(SegmentSupport.valueOf(record.getValue(COPYNUMBER.SEGMENTSTARTSUPPORT)))
                .segmentEndSupport(SegmentSupport.valueOf(record.getValue(COPYNUMBER.SEGMENTENDSUPPORT)))
                .averageActualBAF(record.getValue(COPYNUMBER.ACTUALBAF))
                .averageObservedBAF(record.getValue(COPYNUMBER.OBSERVEDBAF))
                .averageTumorCopyNumber(record.getValue(COPYNUMBER.COPYNUMBER_))
                .build());
    }

    Collections.sort(copyNumbers);
    return copyNumbers;
}

@Test
public void testBasicJooqSample() {
    CompanyDao companyDao = new CompanyDao(configuration());
    companyDao.findAll().stream().map(String::valueOf).forEach(log::info);

    Result<Record2<String, String>> results = dslContext()
            .select(COMPANY.ORGANISATIONNAME, USERPROFILE.EMAIL)
            .from(COMPANY)
            .join(USERPROFILE)
            .on(COMPANY.ID.eq(USERPROFILE.COMPANY_ID))
            .join(REGULARUSER)
            .on(REGULARUSER.ID.eq(USERPROFILE.ID))
            .fetch();

    // Loop and process results
}

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

@Transactional(readOnly = true)
public Collection<AuthorWithBooks> findAuthorsWithBooksJooqStreamedGroupBy() {
    Result<Record> records = dslContext.select()
            .from(AUTHOR.leftOuterJoin(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)))
            .fetch();

    Map<Long, List<Record>> collect = records.stream().collect(Collectors.groupingBy(r -> r.getValue(TAuthor.AUTHOR.ID)));

    return collect.entrySet().stream().map(e -> {
        AuthorWithBooks authorWithBooks = new AuthorWithBooks();
        authorWithBooks.setAuthor(authorRepository.mapper().map(e.getValue().get(0).into(TAuthor.AUTHOR)));
        List<Book> books = e.getValue().stream().map(r -> bookRepository.mapper().map(r.into(TBook.BOOK))).collect(Collectors.toList());
        authorWithBooks.setBooks(books);
        return authorWithBooks;
    }).collect(Collectors.toList());
}

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(roleName -> roleName != null)
                            .map(roleName -> Role.valueOf(roleName))
                            .collect(Collectors.toList()))
                    .build())
            .collect(toList());
}

public String getUserPassword ( String email ) {
    synchronized (DBManager.class) {

        System.out.println("TEST1");

        Result<Record> result = create.select().from(USER).fetch();

        System.out.println("TEST2");

        for (Record r : result) {
            System.out.print("Is " + r.getValue(USER.EMAIL).toString() + " the same as " + email + "? ");
            if (r.getValue(USER.EMAIL).toString().compareTo(email) == 0)
                return r.getValue(USER.PASSWORD).toString();
            System.out.println("No... ");
        }

        return null;
    }

}

public String getUserID ( String email ) {
    synchronized (DBManager.class) {
        Result<Record> result = create.select().from(USER).fetch();

        System.out.println(("Looking for User ID......"));
        for (Record r : result) {
            System.out.println("Is " + r.getValue(USER.EMAIL).toString() + " the same as " + email + "? ");
            if (r.getValue(USER.EMAIL).toString().compareTo(email) == 0) {
                System.out.println("Found User ID! Its " + r.getValue(USER.ID).toString());
                return r.getValue(USER.ID).toString();
            }
            System.out.println("No...");
        }

        System.out.println("========= ERROR: Couldn't find the User ID!!!! ========");
        return null;
    }
}

public int getUserIDviaName ( String name ) {
    synchronized (DBManager.class) {
        Result<Record> result = create.select().from(USER).fetch();

        System.out.println(("Looking for User ID by checking name......"));
        for (Record r : result) {
            System.out.println("Is " + r.getValue(USER.USERNAME).toString() + " the same as " + name + "? ");
            if (r.getValue(USER.USERNAME).toString().compareTo(name) == 0) {
                System.out.println("Found User ID! Its " + r.getValue(USER.ID).toString());
                return r.getValue(USER.ID);
            }
            System.out.println("No...");
        }

        System.out.println("========= ERROR: Couldn't find the User ID!!!! ========");
        return -1;
    }
}

public String getUserNameviaID ( int id ) {
    synchronized (DBManager.class) {
        Result<Record> result = create.select().from(USER).fetch();

        System.out.println(("Looking for User Name by checking id......"));
        for (Record r : result) {
            System.out.println("Is " + r.getValue(USER.ID).toString() + " the same as " + id + "? ");
            if (r.getValue(USER.ID) == id) {
                System.out.println("Found User Name! Its " + r.getValue(USER.USERNAME).toString());
                return r.getValue(USER.USERNAME);
            }
            System.out.println("No...");
        }

        System.out.println("========= ERROR: Couldn't find the User ID!!!! ========");
        return null;
    }
}

public Location getUserPosition ( int id ) {
    synchronized (DBManager.class) {

        Result<Record> result = create.select().from(USER).fetch();

        System.out.println(("Looking for User Name by checking id......"));
        for (Record r : result) {
            System.out.println("Is " + r.getValue(USER.ID).toString() + " the same as " + id + "? ");
            if (r.getValue(USER.ID) == id) {
                System.out.println("Found User Name! Its " + r.getValue(USER.USERNAME).toString());
                return new Location(r.getValue(USER.LONGITUDE), r.getValue(USER.LATITUDE));
            }
            System.out.println("No...");
        }

        System.out.println("========= ERROR: Couldn't find the User ID!!!! ========");
        return null;
    }
}

public void attendevent( String Eventname ) {
    synchronized (DBManager.class) {
        Result<Record> result = create.select().from(EVENT).fetch();

        for (Record r : result) {
            if (r.getValue(EVENT.NAME).compareTo(Eventname) == 0) {
                System.out.println("Found Event! Its " + r.getValue(EVENT.NAME));

                create.update(EVENT)
                        .set(EVENT.ATTENDEES, r.getValue(EVENT.ATTENDEES) + 1)
                        .where(EVENT.NAME.equal(Eventname))
                        .execute();

            }
        }
    }
}

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

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

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

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

/**
 * may returns the Calibration if there are needed Calibration left unanswered.
 *
 * @param builder the builder to use
 * @param context the Context of the Request
 * @return an instance of view if the worker has to fill in some Calibration, or empty if not.
 */
private Optional<View> getCalibration(View.Builder builder, Context context) {
    String platformName = assertParameter(context, "platform");
    int experiment = assertParameterInt(context, "experiment");
    if (platformOperations.getPlatform(platformName).getRenderCalibrations()) {
        logger.trace("platform {} is able to render calibrations", platformName);
        Map<CalibrationRecord, Result<CalibrationAnswerOptionRecord>> calibrations =
                calibrationsOperations.getCalibrations(experiment, platformName, context.get(WorkerID.class).get());
        logger.trace("worker {} must answer the calibrations {}", context.get(WorkerID.class).get(), calibrations);
        if (calibrations.isEmpty()) {
            logger.debug("worker {} must not answer calibrations", context.get(WorkerID.class).get());
            return Optional.empty();
        } else {
            logger.debug("worker {} must answer calibrations", context.get(WorkerID.class).get());
            return Optional.of(constructCalibrationView(calibrations, builder));
        }
    } else {
        logger.trace("platform {} is not able to render calibrations", platformName);
        return Optional.empty();
    }
}

/**
 * constructs the Calibration view from the qualifications-data.
 *
 * @param qualifications the qualifications
 * @param builder        the builder to use
 * @return an instance of View with the Type Calibration and the Calibration set.
 */
private View constructCalibrationView(Map<CalibrationRecord, Result<CalibrationAnswerOptionRecord>> qualifications,
                                      View.Builder builder) {
    Function<CalibrationAnswerOptionRecord, View.CalibrationAnswerOption> constructAnswerOption = record ->
            View.CalibrationAnswerOption.newBuilder()
                    .setId(record.getIdCalibrationAnswerOption())
                    .setOption(record.getAnswer())
                    .build();

    List<View.Calibration> Calibration = qualifications.entrySet().stream()
            .map(entry -> View.Calibration.newBuilder()
                    .setQuestion(entry.getKey().getProperty())
                    .setId(entry.getKey().getIdCalibration())
                    .addAllAnswerOptions(entry.getValue().map(constructAnswerOption::apply))
                    .build()
            )
            .collect(Collectors.toList());

    return builder.addAllCalibrations(Calibration)
            .setType(View.Type.CALIBRATION)
            .build();
}

@Test
public void testCalibrations() throws Exception {
    Context context = prepareContext(data);
    Map<CalibrationRecord, Result<CalibrationAnswerOptionRecord>> calibrations = data.getCalibrations();
    Queries queries =  prepareQuery(data, Optional.empty(), null);
    View view = queries.getNext(context);
    assertTrue(view.getType().equals(View.Type.CALIBRATION));
    assertTrue(view.getCalibrationsList().size() == calibrations.size());
    Optional<Map.Entry<CalibrationRecord, Result<CalibrationAnswerOptionRecord>>> res = calibrations.entrySet().stream()
            .filter(entry -> !view.getCalibrationsList().stream()
                    .filter(calibration -> calibration.getQuestion().equals(entry.getKey().getProperty()))
                    .filter(calibration -> calibration.getId() == entry.getKey().getIdCalibration())
                    .flatMap(calibration -> calibration.getAnswerOptionsList().stream())
                    .filter(answerOption ->
                            entry.getValue().stream().anyMatch(record ->
                                    record.getAnswer().equals(answerOption.getOption())))
                    .anyMatch(answerOption ->
                            entry.getValue().stream().anyMatch(record ->
                                    record.getIdCalibrationAnswerOption().equals(answerOption.getId())))
            ).findAny();
    assertTrue(!res.isPresent());
}

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

private void loadLocksInWorld(World world)
{
    Result<LockModel> models = this.database.getDSL().selectFrom(TABLE_LOCKS)
            .where(TABLE_LOCKS.ID.in(
                    this.database.getDSL().select(TABLE_LOCK_LOCATIONS.LOCK_ID)
                            .from(TABLE_LOCK_LOCATIONS)
                            .where(TABLE_LOCK_LOCATIONS.WORLD_ID.eq(world.getUniqueId()))))
            .fetch();
    Map<Long, Result<LockLocationModel>> locations = this.database.getDSL().selectFrom(TABLE_LOCK_LOCATIONS)
            .where(TABLE_LOCK_LOCATIONS.WORLD_ID.eq(world.getUniqueId()))
            .fetch().intoGroups(TABLE_LOCK_LOCATIONS.LOCK_ID);
    for (LockModel model : models)
    {
        Result<LockLocationModel> lockLocs = locations.get(model.getValue(TABLE_LOCKS.ID));
        addLoadedLocationLock(new Lock(this, model, i18n, lockLocs));
    }
    logger.info("Finished loading {} locks for {}", this.getChunkLocksMap(world.getUniqueId()).size(), world.getName());
}

/**
 * The returned Lock should not be saved for later use!
 *
 * @param lockID the locks id
 * @return a copy of the Lock with given id
 */
public Lock getLockById(Long lockID)
{
    Lock lock = this.locksById.get(lockID);
    if (lock != null)
    {
        return lock;
    }
    LockModel lockModel = database.getDSL().selectFrom(TABLE_LOCKS).where(TABLE_LOCKS.ID.eq(lockID)).fetchOne();
    if (lockModel != null)
    {
        Result<LockLocationModel> fetch = database.getDSL().selectFrom(TABLE_LOCK_LOCATIONS)
                                                  .where(TABLE_LOCK_LOCATIONS.LOCK_ID.eq(lockModel.getValue(TABLE_LOCKS.ID)))
                                                  .fetch();
        if (fetch.isEmpty())
        {
            return new Lock(this, lockModel, i18n);
        }
        return new Lock(this, lockModel, i18n, fetch);
    }
    return null;
}

@Test
public void testQueryingAfterMigration() throws Exception {
    try (Connection c = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "")) {
        Result<?> result =
                DSL.using(c)
                        .select(
                                AUTHOR.FIRST_NAME,
                                AUTHOR.LAST_NAME,
                                BOOK.ID,
                                BOOK.TITLE
                        )
                        .from(AUTHOR)
                        .join(BOOK)
                        .on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
                        .orderBy(BOOK.ID.asc())
                        .fetch();

        assertEquals(4, result.size());
        assertEquals(Arrays.asList(1, 2, 3, 4), result.getValues(BOOK.ID));
    }
}

@Override
public List<Role> listRolesOfUser(int userId, String context) throws BazaarException {
    List<Role> roles = null;

    try {
        de.rwth.dbis.acis.bazaar.service.dal.jooq.tables.Role roleTable = ROLE.as("role");
        de.rwth.dbis.acis.bazaar.service.dal.jooq.tables.Privilege privilegeTable = PRIVILEGE.as("privilege");

        Result<Record> queryResult = jooq.selectFrom(
                USER_ROLE_MAP
                        .join(roleTable).on(USER_ROLE_MAP.ROLE_ID.eq(roleTable.ID))
                        .leftOuterJoin(ROLE_PRIVILEGE_MAP).on(ROLE_PRIVILEGE_MAP.ROLE_ID.eq(ROLE.ID))
                        .leftOuterJoin(PRIVILEGE).on(PRIVILEGE.ID.eq(ROLE_PRIVILEGE_MAP.PRIVILEGE_ID))
        ).where(USER_ROLE_MAP.USER_ID.equal(userId).and(USER_ROLE_MAP.CONTEXT_INFO.eq(context).or(USER_ROLE_MAP.CONTEXT_INFO.isNull()))).fetch();

        if (queryResult != null && !queryResult.isEmpty()) {
            roles = new ArrayList<>();
            convertToRoles(roles, roleTable, privilegeTable, queryResult);
        }

    } catch (Exception e) {
        ExceptionHandler.getInstance().convertAndThrowException(e, ExceptionLocation.REPOSITORY, ErrorCode.UNKNOWN);
    }
    return roles;
}

@Override
public List<Role> listParentsForRole(int roleId) throws BazaarException {
    List<Role> roles = null;

    try {
        de.rwth.dbis.acis.bazaar.service.dal.jooq.tables.Role roleTable = ROLE.as("role");
        de.rwth.dbis.acis.bazaar.service.dal.jooq.tables.Privilege privilegeTable = PRIVILEGE.as("privilege");

        Result<Record> queryResult = jooq.selectFrom(
                ROLE_ROLE_MAP
                        .join(roleTable).on(ROLE_ROLE_MAP.PARENT_ID.equal(roleTable.ID))
                        .leftOuterJoin(ROLE_PRIVILEGE_MAP).on(ROLE_PRIVILEGE_MAP.ROLE_ID.eq(roleTable.ID))
                        .leftOuterJoin(privilegeTable).on(privilegeTable.ID.eq(ROLE_PRIVILEGE_MAP.PRIVILEGE_ID))
        ).where(ROLE_ROLE_MAP.CHILD_ID.equal(roleId)).fetch();

        if (queryResult != null && !queryResult.isEmpty()) {
            roles = new ArrayList<>();
            convertToRoles(roles, roleTable, privilegeTable, queryResult);
        }

    } catch (Exception e) {
        ExceptionHandler.getInstance().convertAndThrowException(e, ExceptionLocation.REPOSITORY, ErrorCode.UNKNOWN);
    }
    return roles;
}

private void convertToRoles(List<Role> roles, de.rwth.dbis.acis.bazaar.service.dal.jooq.tables.Role roleTable,
                            de.rwth.dbis.acis.bazaar.service.dal.jooq.tables.Privilege privilegeTable, Result<Record> queryResult) {
    for (Map.Entry<Integer, Result<Record>> entry : queryResult.intoGroups(roleTable.ID).entrySet()) {
        if (entry.getKey() == null) continue;
        Result<Record> records = entry.getValue();

        List<Privilege> rolesToAddPrivileges = new ArrayList<>();

        for (Map.Entry<Integer, Result<Record>> privilegeEntry : records.intoGroups(privilegeTable.ID).entrySet()) {
            if (privilegeEntry.getKey() == null) continue;
            Result<Record> privileges = privilegeEntry.getValue();

            Privilege privilege = Privilege.getBuilder(new PrivilegeEnumConverter().from(privileges.getValues(privilegeTable.NAME).get(0)))
                    .id(privileges.getValues(privilegeTable.ID).get(0))
                    .build();
            rolesToAddPrivileges.add(privilege);
        }

        Role roleToAdd = Role.getBuilder(records.getValues(roleTable.NAME).get(0))
                .id(records.getValues(roleTable.ID).get(0))
                .privileges(rolesToAddPrivileges)
                .build();

        roles.add(roleToAdd);
    }
}

public Optional<User> find(String providerId, String uniqueID, final PersonManager manager) {
    return query((create)->{
        Result<LoginRecord> result = create.selectFrom(LOGIN)
                .where(LOGIN.PROVIDER_ID.eq(providerId))
                .and(LOGIN.UNIQUE_ID.eq(uniqueID))
                .fetch();
        return result.stream().findFirst().map(r->{
            User user = new User(
                    manager.lookup(convert(r.getId())).get(), r.getAuth());
            UserBean bean = new UserBean();
            bean.setUniqueId(uniqueID);
            bean.setProviderId(providerId);
            user.update(bean);
            return user;
        });
    });
}

public InputFieldGroupBuilder find(InputFieldGroupBuilder parent, String groupId, Locale locale) {
    SortedMap<Integer, InputFieldDesignatorBuilder<?>> ordered = new TreeMap<>();

    Result<InputGroupRecord> result = query(create -> create
            .selectFrom(INPUT_GROUP)
            .where(INPUT_GROUP.PARENT_INPUT_GROUP_ID.eq(groupId.getBytes()))
            .orderBy(INPUT_GROUP.THE_ORDER)
            .fetch());
    result.stream()
            .forEach(r -> ordered.put(r.getTheOrder(), mapBuild(r, locale)));

    new InputFieldBroker(connector).recordsByGroup(groupId)
            .forEach(r -> ordered.put(r.getTheOrder(), InputFieldBroker.mapBuild(r, connector, locale)));

    ordered.values().stream().forEach(b -> parent.add(b));
    return parent;
}

@Test
public void findClubListeners() {
    PersonManager personManager = new PersonManager();
    Person person1 = personManager.createPerson();
    Person person2 = personManager.createPerson();
    ClubManager clubManager = new ClubManager();
    Club club = clubManager.createClub(null, Programs.AWANA.get());

    MockDataProvider provider = select((s) -> {
        s.assertUUID(club.getId(), LISTENER.CLUB_ID);
    }, (create) -> {
        Result<ListenerRecord> result = create.newResult(LISTENER);
        addClubListenerRecord(person1, club, create, result);
        addClubListenerRecord(person2, club, create, result);
        return result;
    });

    Set<Listener> listeners = setup(provider).find(club, personManager);
    Iterator<Listener> iterator = listeners.iterator();
    assertListener(person1, iterator.next());
    assertListener(person2, iterator.next());
    assertFalse(iterator.hasNext());
}

@Test
public void findFamilyMembers() {
    String familyId = UUID.randomUUID().toString();

    String parent1Id = UUID.randomUUID().toString();
    String parent2Id = UUID.randomUUID().toString();
    String child1Id = UUID.randomUUID().toString();
    String child3Id = UUID.randomUUID().toString();

    List<String> all = Arrays.asList(parent1Id, parent2Id, child1Id, child3Id);

    List<String> allFamilyMembers = setup(select(
            (s)->s.assertUUID(familyId, Parent.PARENT.FAMILY_ID),
            (create)->{
                Result<Record1<byte[]>> result = create.newResult(Parent.PARENT.ID);
                setValue(parent1Id, create, result);
                setValue(parent2Id, create, result);
                setValue(child1Id, create, result);
                setValue(child3Id, create, result);
                return result;
            }
        )).getAllFamilyMembers(familyId).collect(Collectors.toList());

    assertTrue(allFamilyMembers.stream().allMatch(all::contains));
    assertTrue(all.stream().allMatch(allFamilyMembers::contains));
}

@Override
public void onCommand(String command, User user, PircBotX network, String prefix, Channel channel, boolean isPrivate, int userPermLevel, String... args) throws Exception {
    Set<String> msg = new HashSet<>();
    Result<Record> records = DatabaseUtils.getChannelUserProperty(IRCUtils.getNetworkNameByNetwork(network), channel.getName(), "permlevel");
    if (records.size() > 0) {
        for (Record rec :records) {
            msg.add(IRCUtils.noPing(rec.getValue(Channeluserproperty.CHANNELUSERPROPERTY.USER)) + "[" + rec.getValue(Channeluserproperty.CHANNELUSERPROPERTY.VALUE) + "]");
        }
        if(isPrivate)
            IRCUtils.sendMessage(user, network, null, StringUtils.join(msg," - "), prefix);
        else
            IRCUtils.sendMessage(user, network, channel, StringUtils.join(msg," - "), prefix);
    }else{
        if(isPrivate)
            IRCUtils.sendError(user, network, null, "No Manually Set Perm Levels Found", prefix);
        else
            IRCUtils.sendError(user, network, channel, "No Manually Set Perm Levels Found", prefix);
    }
}