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

Example 16 with QueryStatement

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

the class PingQueries method fetchPingDataOfServerByGeolocation.

public static Query<Map<String, Ping>> fetchPingDataOfServerByGeolocation(ServerUUID serverUUID) {
    String selectPingByGeolocation = SELECT + "a." + GeoInfoTable.GEOLOCATION + ", MIN(" + PingTable.MIN_PING + ") as minPing" + ", MAX(" + PingTable.MAX_PING + ") as maxPing" + ", AVG(" + PingTable.AVG_PING + ") as avgPing" + FROM + GeoInfoTable.TABLE_NAME + " a" + // That way the biggest a.last_used value will have NULL on the b.last_used column and MAX doesn't need to be used.
    LEFT_JOIN + GeoInfoTable.TABLE_NAME + " b ON a." + GeoInfoTable.USER_ID + "=b." + GeoInfoTable.USER_ID + AND + "a." + GeoInfoTable.LAST_USED + "<b." + GeoInfoTable.LAST_USED + INNER_JOIN + PingTable.TABLE_NAME + " sp on sp." + PingTable.USER_ID + "=a." + GeoInfoTable.USER_ID + WHERE + "b." + GeoInfoTable.LAST_USED + IS_NULL + AND + "sp." + PingTable.SERVER_ID + "=" + ServerTable.SELECT_SERVER_ID + GROUP_BY + "a." + GeoInfoTable.GEOLOCATION;
    return new QueryStatement<Map<String, Ping>>(selectPingByGeolocation) {

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

        @Override
        public Map<String, Ping> processResults(ResultSet set) throws SQLException {
            // TreeMap to sort alphabetically
            Map<String, Ping> pingByGeolocation = new TreeMap<>();
            while (set.next()) {
                Ping ping = new Ping(0L, serverUUID, set.getInt("minPing"), set.getInt("maxPing"), (int) set.getDouble("avgPing"));
                pingByGeolocation.put(set.getString(GeoInfoTable.GEOLOCATION), ping);
            }
            return pingByGeolocation;
        }
    };
}

Example 17 with QueryStatement

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

the class ServerQueries method fetchProxyServerInformation.

public static Query<Optional<Server>> fetchProxyServerInformation() {
    String sql = SELECT + '*' + FROM + ServerTable.TABLE_NAME + WHERE + ServerTable.INSTALLED + "=?" + AND + ServerTable.PROXY + "=?" + LIMIT + '1';
    return new QueryStatement<Optional<Server>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setBoolean(1, true);
            statement.setBoolean(2, true);
        }

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

Example 18 with QueryStatement

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

the class ServerQueries method fetchPlanServerInformation.

/**
 * Query database for all Plan server information.
 *
 * @return Map: Server UUID - Plan Server Information
 */
public static Query<Map<ServerUUID, Server>> fetchPlanServerInformation() {
    String sql = SELECT + '*' + FROM + ServerTable.TABLE_NAME + WHERE + ServerTable.INSTALLED + "=?";
    return new QueryStatement<Map<ServerUUID, Server>>(sql, 100) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setBoolean(1, true);
        }

        @Override
        public Map<ServerUUID, Server> processResults(ResultSet set) throws SQLException {
            Map<ServerUUID, Server> servers = new HashMap<>();
            while (set.next()) {
                ServerUUID serverUUID = ServerUUID.fromString(set.getString(ServerTable.SERVER_UUID));
                servers.put(serverUUID, new Server(set.getInt(ServerTable.ID), serverUUID, set.getString(ServerTable.NAME), set.getString(ServerTable.WEB_ADDRESS), set.getBoolean(ServerTable.PROXY), set.getString(ServerTable.PLAN_VERSION)));
            }
            return servers;
        }
    };
}

Example 19 with QueryStatement

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

the class ServerQueries method findMatchingServers.

public static Query<List<Server>> findMatchingServers(String identifier) {
    if (identifier.isEmpty())
        return db -> Collections.emptyList();
    String sql = SELECT + '*' + FROM + ServerTable.TABLE_NAME + WHERE + "(LOWER(" + ServerTable.SERVER_UUID + ") LIKE LOWER(?)" + OR + "LOWER(" + ServerTable.NAME + ") LIKE LOWER(?)" + OR + ServerTable.ID + "=?" + OR + ServerTable.ID + "=?)" + AND + ServerTable.INSTALLED + "=?" + LIMIT + '1';
    return new QueryStatement<List<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 List<Server> processResults(ResultSet set) throws SQLException {
            List<Server> matches = new ArrayList<>();
            while (set.next()) {
                matches.add(new Server(set.getInt(ServerTable.ID), ServerUUID.fromString(set.getString(ServerTable.SERVER_UUID)), set.getString(ServerTable.NAME), set.getString(ServerTable.WEB_ADDRESS), set.getBoolean(ServerTable.PROXY), set.getString(ServerTable.PLAN_VERSION)));
            }
            return matches;
        }
    };
}

Example 20 with QueryStatement

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

the class TPSQueries method fetchTPSDataOfServer.

public static Query<List<TPS>> fetchTPSDataOfServer(long after, long before, ServerUUID serverUUID) {
    String sql = SELECT + "*" + FROM + TABLE_NAME + WHERE + SERVER_ID + "=" + ServerTable.SELECT_SERVER_ID + AND + DATE + ">=?" + AND + DATE + "<=?" + ORDER_BY + DATE;
    return new QueryStatement<List<TPS>>(sql, 50000) {

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

        @Override
        public List<TPS> processResults(ResultSet set) throws SQLException {
            List<TPS> data = new ArrayList<>();
            while (set.next()) {
                TPS tps = extractTPS(set);
                data.add(tps);
            }
            return data;
        }
    };
}