Examples with QueryStatement com.djrapitops.plan.storage.database.queries.QueryStatement used on opensource projects

Example 6 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class BaseUserQueries method fetchBaseUserOfPlayer.

/**
 * Query database for common user information of a player.
 * <p>
 * Only one {@link BaseUser} per player exists unlike {@link UserInfo} which is available per server.
 *
 * @param playerUUID UUID of the Player.
 * @return Optional: BaseUser if found, empty if not.
 */
public static Query<Optional<BaseUser>> fetchBaseUserOfPlayer(UUID playerUUID) {
    String sql = Select.all(UsersTable.TABLE_NAME).where(UsersTable.USER_UUID + "=?").toString();
    return new QueryStatement<Optional<BaseUser>>(sql, 20000) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, playerUUID.toString());
        }

        @Override
        public Optional<BaseUser> processResults(ResultSet set) throws SQLException {
            if (set.next()) {
                UUID playerUUID = UUID.fromString(set.getString(UsersTable.USER_UUID));
                String name = set.getString(UsersTable.USER_NAME);
                long registered = set.getLong(UsersTable.REGISTERED);
                int kicked = set.getInt(UsersTable.TIMES_KICKED);
                return Optional.of(new BaseUser(playerUUID, name, registered, kicked));
            }
            return Optional.empty();
        }
    };
}

Example 7 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class NicknameQueries method fetchLastSeenNicknameOfPlayer.

public static Query<Optional<Nickname>> fetchLastSeenNicknameOfPlayer(UUID playerUUID, ServerUUID serverUUID) {
    String subQuery = SELECT + "MAX(" + NicknamesTable.LAST_USED + ") FROM " + NicknamesTable.TABLE_NAME + WHERE + NicknamesTable.USER_UUID + "=?" + AND + NicknamesTable.SERVER_UUID + "=?" + GROUP_BY + NicknamesTable.USER_UUID;
    String sql = SELECT + NicknamesTable.LAST_USED + ',' + NicknamesTable.NICKNAME + FROM + NicknamesTable.TABLE_NAME + WHERE + NicknamesTable.USER_UUID + "=?" + AND + NicknamesTable.SERVER_UUID + "=?" + AND + NicknamesTable.LAST_USED + "=(" + subQuery + ')';
    return new QueryStatement<Optional<Nickname>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, playerUUID.toString());
            statement.setString(2, serverUUID.toString());
            statement.setString(3, playerUUID.toString());
            statement.setString(4, serverUUID.toString());
        }

        @Override
        public Optional<Nickname> processResults(ResultSet set) throws SQLException {
            if (set.next()) {
                return Optional.of(new Nickname(set.getString(NicknamesTable.NICKNAME), set.getLong(NicknamesTable.LAST_USED), serverUUID));
            }
            return Optional.empty();
        }
    };
}

Example 8 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class ServerQueries method fetchServerMatchingIdentifier.

public static Query<Optional<Server>> fetchServerMatchingIdentifier(String identifier) {
    String sql = SELECT + '*' + FROM + ServerTable.TABLE_NAME + WHERE + "(LOWER(" + ServerTable.SERVER_UUID + ") LIKE LOWER(?)" + OR + "LOWER(" + ServerTable.NAME + ") LIKE LOWER(?)" + OR + ServerTable.SERVER_ID + "=?" + OR + ServerTable.SERVER_ID + "=?)" + AND + ServerTable.INSTALLED + "=?" + LIMIT + '1';
    return new QueryStatement<Optional<Server>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, identifier);
            statement.setString(2, identifier);
            statement.setInt(3, NumberUtils.isParsable(identifier) ? Integer.parseInt(identifier) : -1);
            String id = identifier.startsWith("Server ") ? identifier.substring(7) : identifier;
            statement.setInt(4, NumberUtils.isParsable(id) ? Integer.parseInt(id) : -1);
            statement.setBoolean(5, true);
        }

        @Override
        public Optional<Server> processResults(ResultSet set) throws SQLException {
            if (set.next()) {
                return Optional.of(new Server(set.getInt(ServerTable.SERVER_ID), ServerUUID.fromString(set.getString(ServerTable.SERVER_UUID)), set.getString(ServerTable.NAME), set.getString(ServerTable.WEB_ADDRESS), set.getBoolean(ServerTable.PROXY)));
            }
            return Optional.empty();
        }
    };
}

Example 9 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class UserInfoQueries method uuidsOfPlayersWithJoinAddresses.

public static Query<Set<UUID>> uuidsOfPlayersWithJoinAddresses(List<String> joinAddresses) {
    String selectLowercaseJoinAddresses = SELECT + UserInfoTable.USER_UUID + ',' + "LOWER(COALESCE(" + UserInfoTable.JOIN_ADDRESS + ", ?)) as address" + FROM + UserInfoTable.TABLE_NAME;
    String sql = SELECT + DISTINCT + UserInfoTable.USER_UUID + FROM + '(' + selectLowercaseJoinAddresses + ") q1" + WHERE + "address IN (" + new TextStringBuilder().appendWithSeparators(joinAddresses.stream().map(item -> '?').iterator(), ",") + // Don't append addresses directly, SQL injection hazard
    ')';
    return new QueryStatement<Set<UUID>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, "unknown");
            for (int i = 1; i <= joinAddresses.size(); i++) {
                String address = joinAddresses.get(i - 1);
                statement.setString(i + 1, address);
            }
        }

        @Override
        public Set<UUID> processResults(ResultSet set) throws SQLException {
            return extractUUIDs(set);
        }
    };
}

Example 10 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class WorldTimesQueries method fetchServerTotalWorldTimes.

/**
 * Sum total playtime per world on a server.
 *
 * @param serverUUID Server UUID of the Plan server.
 * @return WorldTimes with world name - playtime ms information.
 */
public static Query<WorldTimes> fetchServerTotalWorldTimes(ServerUUID serverUUID) {
    String sql = SELECT_WORLD_TIMES_STATEMENT_START + SELECT_WORLD_TIMES_JOIN_WORLD_NAME + WHERE + WorldTimesTable.TABLE_NAME + '.' + WorldTimesTable.SERVER_UUID + "=?" + GROUP_BY + WORLD_COLUMN;
    return new QueryStatement<WorldTimes>(sql, 1000) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, serverUUID.toString());
        }

        @Override
        public WorldTimes processResults(ResultSet set) throws SQLException {
            String[] gms = GMTimes.getGMKeyArray();
            WorldTimes worldTimes = new WorldTimes();
            while (set.next()) {
                String worldName = set.getString(WORLD_COLUMN);
                GMTimes gmTimes = extractGMTimes(set, gms);
                worldTimes.setGMTimesForWorld(worldName, gmTimes);
            }
            return worldTimes;
        }
    };
}