public static Optional<Location<World>> getPreviousPortal(Player player, World world) { try (Connection con = SQLHandle.getConnection()) { DSLContext create = DSL.using(con); Record3<Integer, Integer, Integer> result = create.select(PORTALS.X, PORTALS.Y, PORTALS.Z).from(PORTALS) .where( PORTALS.FROM_WORLD_ID.equal(create.select(WORLDS.ID).from(WORLDS).where(WORLDS.NAME.equal(world.getName()))).and( PORTALS.PLAYER_ID.equal(create.select(PLAYERS.ID).from(PLAYERS).where(PLAYERS.UUID.equal(player.getUniqueId().toString()))) ) ) .fetchOne(); return result == null ? Optional.empty() : Optional.of(new Location<>( world, result.get(PORTALS.X) + .5, result.get(PORTALS.Y), result.get(PORTALS.Z) + .5) ); } catch (SQLException e) { e.printStackTrace(); } return Optional.empty(); }
public List<PostSummary> nextPage(int pageSize, PostSummary offsetPostSummary) { return doInJOOQ(sql -> { SelectSeekStep2<Record3<Long, String, Timestamp>, Timestamp, Long> selectStep = sql .select(POST.ID, POST.TITLE, POST_DETAILS.CREATED_ON) .from(POST) .join(POST_DETAILS).using(POST.ID) .orderBy(POST_DETAILS.CREATED_ON.desc(), POST.ID.desc()); return (offsetPostSummary != null) ? selectStep .seek(offsetPostSummary.getCreatedOn(), offsetPostSummary.getId()) .limit(pageSize) .fetchInto(PostSummary.class) : selectStep .limit(pageSize) .fetchInto(PostSummary.class); }); }
public List<PostSummary> nextPage(int pageSize, PostSummary offsetPostSummary) { return doInJOOQ(sql -> { SelectSeekStep2<Record3<BigInteger, String, Timestamp>, Timestamp, BigInteger> selectStep = sql .select(POST.ID, POST.TITLE, POST_DETAILS.CREATED_ON) .from(POST) .join(POST_DETAILS).on(POST.ID.eq(POST_DETAILS.ID)) .orderBy(POST_DETAILS.CREATED_ON.desc(), POST.ID.desc()); return (offsetPostSummary != null) ? selectStep .seek(offsetPostSummary.getCreatedOn(), BigInteger.valueOf(offsetPostSummary.getId())) .limit(pageSize) .fetchInto(PostSummary.class) : selectStep .limit(pageSize) .fetchInto(PostSummary.class); }); }
public List<PostSummary> nextPage(int pageSize, PostSummary offsetPostSummary) { return doInJOOQ(sql -> { SelectSeekStep2<Record3<BigInteger, String, Timestamp>, Timestamp, BigInteger> selectStep = sql .select(POST.ID, POST.TITLE, POST_DETAILS.CREATED_ON) .from(POST) .join(POST_DETAILS).using(POST.ID) .orderBy(POST_DETAILS.CREATED_ON.desc(), POST.ID.desc()); return (offsetPostSummary != null) ? selectStep .seek(offsetPostSummary.getCreatedOn(), BigInteger.valueOf(offsetPostSummary.getId())) .limit(pageSize) .fetchInto(PostSummary.class) : selectStep .limit(pageSize) .fetchInto(PostSummary.class); }); }
public List<PostSummary> nextPage(int pageSize, PostSummary offsetPostSummary) { return doInJOOQ(sql -> { SelectSeekStep2<Record3<Long, String, Timestamp>, Timestamp, Long> selectStep = sql .select(POST.ID, POST.TITLE, POST_DETAILS.CREATED_ON) .from(POST) .join(POST_DETAILS).on(POST.ID.eq(POST_DETAILS.ID)) .orderBy(POST_DETAILS.CREATED_ON.desc(), POST.ID.desc()); return (offsetPostSummary != null) ? selectStep .seek(offsetPostSummary.getCreatedOn(), offsetPostSummary.getId()) .limit(pageSize) .fetchInto(PostSummary.class) : selectStep .limit(pageSize) .fetchInto(PostSummary.class); }); }
@Override public Map<Long, Pair<String, String>> getServiceIdToServiceStackName(long accountId) { final Map<Long, Pair<String, String>> toReturn = new HashMap<>(); create().select(SERVICE.ID, SERVICE.NAME, STACK.NAME) .from(SERVICE) .join(STACK) .on(STACK.ID.eq(SERVICE.STACK_ID)) .where(SERVICE.ACCOUNT_ID.eq(accountId)) .and(SERVICE.REMOVED.isNull()) .fetchInto(new RecordHandler<Record3<Long, String, String>>() { @Override public void next(Record3<Long, String, String> record) { toReturn.put(record.getValue(SERVICE.ID), Pair.of(record.getValue(SERVICE.NAME), record.getValue(STACK.NAME))); } }); return toReturn; }
/** * {@inheritDoc} */ @Override public Record3<Long, Short, Long> key() { return (Record3) super.key(); }
/** * {@inheritDoc} */ @Override public Record3<UInteger, UInteger, Integer> key() { return (Record3) super.key(); }
@Override public RankingListVersionView getRankingVersion(final Integer version, final Integer diffVersion, final RankingOptions rankingOptions) { final Integer itemsPerPage = SecurityUtils.getItemsPerPage(); final Integer contestId = getContestId(jooq, rankingOptions.getGroupCode()); final Field<Boolean> hasStartTimeField = DSL.field(CONTESTS.CONTEST_START.isNotNull()); final Record3<Boolean, Boolean, String> contestInfo = jooq.select(CONTESTS.IS_COURSE, hasStartTimeField, CONTESTS.SPECIAL_RANKINGS) .from(CONTESTS) .where(CONTESTS.CONTEST_ID.eq(contestId)) .fetchOne(); if (contestInfo == null) { return null; } final Integer maxVersion = jooq.select(DSL.coalesce(RANKING_HISTORY.VERSION.max(), 0)) .from(RANKING_HISTORY) .where(RANKING_HISTORY.CONTEST_ID.eq(contestId)) .fetchOneInto(Integer.class); RankingListVersionView rankingListView = new RankingListVersionView(); rankingListView.setCourse(contestInfo.getValue(CONTESTS.IS_COURSE)); rankingListView.setStartTime(contestInfo.getValue(hasStartTimeField)); final RankingHistory previous_ranking = RANKING_HISTORY.as("PREVIOUS_RANKING"); final Integer safeVersion = version == null || version < 0 ? maxVersion : version; final Integer safeDiffVersion = diffVersion == null ? (safeVersion > 0 ? safeVersion - 1 : safeVersion) : diffVersion; final List<RankingRowView> positions = jooq.select() .from(RANKING_HISTORY) .join(previous_ranking).on( previous_ranking.CONTEST_ID.eq(RANKING_HISTORY.CONTEST_ID) .and(previous_ranking.TEAM_ID.eq(RANKING_HISTORY.TEAM_ID)) .and(previous_ranking.VERSION.eq(safeDiffVersion)) .and(previous_ranking.WITH_FROZEN.eq(rankingOptions.getWithFrozenInfo())) ) .where(RANKING_HISTORY.CONTEST_ID.eq(getContestId(rankingOptions.getGroupCode()))) .and(RANKING_HISTORY.VERSION.eq(safeVersion)) .and(RANKING_HISTORY.WITH_FROZEN.eq(rankingOptions.getWithFrozenInfo())) .orderBy(RANKING_HISTORY.RANK.asc()) .limit(itemsPerPage) .offset(rankingOptions.getPage() * itemsPerPage) .fetch() .map(record -> { RankingRowView r = new RankingRowView(); if (rankingListView.getSnapshotTime() == null) { rankingListView.setSnapshotTime(record.getValue(RANKING_HISTORY.SNAPSHOT_DATE).toLocalDateTime()); rankingListView.setDiffSnapshotTime(record.getValue(previous_ranking.SNAPSHOT_DATE).toLocalDateTime()); } r.setPosition(record.getValue(RANKING_HISTORY.RANK)); r.setTeamCode(record.getValue(RANKING_HISTORY.TEAM_NAME)); r.setTeamName(record.getValue(RANKING_HISTORY.TEAM_NICKNAME)); r.setPoints(record.getValue(RANKING_HISTORY.POINTS_SUM)); r.setExtraPoints(record.getValue(RANKING_HISTORY.EXTRA_POINTS)); r.setSolvedProblems(record.getValue(RANKING_HISTORY.SOLVED_TASKS)); r.setIncorrectSolutionsSum(record.getValue(RANKING_HISTORY.INCORRECT_SOLUTIONS_SUM)); r.setTeamTime(record.getValue(RANKING_HISTORY.TEAM_TIME)); r.setProblemsSummary(record.getValue(RANKING_HISTORY.TASKS_SUMMARY)); r.setPreviousPosition(record.getValue(previous_ranking.RANK)); return r; }); rankingListView.setPositions(positions); rankingListView.setMaxVersion(maxVersion); Map<String, BalloonView> problemColors = getProblemColors(contestId); if (!problemColors.isEmpty()) { rankingListView.setProblemColors(problemColors); } return rankingListView; }
@Override public List<Long> pendingTasks(String resourceType, String resourceId) { final List<Long> result = new ArrayList<Long>(); /* I know I should and can do this unique logic in SQL, but I couldn't * figure out a simple way to do it in HSQLDB. So if you're reading this * and can find a query that does this across all the supported DB, please * let someone know. * * Here's what I wanted to do * * select * min(PROCESS_INSTANCE.ID) * from PROCESS_INSTANCE * where * PROCESS_INSTANCE.END_TIME is null * group by PROCESS_INSTANCE.RESOURCE_TYPE, PROCESS_INSTANCE.RESOURCE_ID * order by PROCESS_INSTANCE.START_TIME asc * limit 10000 offset 0 * * But you can't order by something that is not in the group by. So how * do I get a unique pair of resource_type, resource_id, but still order by * id or start_time */ final Set<String> seen = new HashSet<String>(); create() .select(PROCESS_INSTANCE.ID, PROCESS_INSTANCE.RESOURCE_TYPE, PROCESS_INSTANCE.RESOURCE_ID) .from(PROCESS_INSTANCE) .where(processCondition(resourceType, resourceId)) .orderBy(PROCESS_INSTANCE.PRIORITY.desc(), PROCESS_INSTANCE.ID.asc()) .limit(PROCESS_REPLAY_BATCH.get()) .fetchInto(new RecordHandler<Record3<Long,String,String>>() { @Override public void next(Record3<Long,String,String> record) { String resource = String.format("%s:%s", record.value2(), record.value3()); if ( seen.contains(resource) ) { return; } seen.add(resource); result.add(record.value1()); } }); return result; }