odds/Odds/src/mysql/Mysql.java

package mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

import object.CurrentParsing;

public class Mysql {

    private static final Mysql instance = new Mysql();

    private static final String username = "OddsNy";
    private static final String password = "Odds1_Ny_Password";
    private static final String database = "new_odds";
    private static final String url = "jdbc:mysql://nordh.xyz:3306/";
    private static final String timezoneFix
            = "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";

    private Connection conn;

    protected Mysql() {
        getConnection();
    }

    public static Mysql getInstance() {
        return instance;
    }

    protected Connection getConnection() {
        if (conn == null) {
            try {
                conn = DriverManager.getConnection(url + database + timezoneFix, username, password);
            } catch (final SQLException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
        return conn;
    }

    public CurrentParsing getCurrentParsing() {
        final CurrentParsing returnValue = new CurrentParsing();
        try {
            final Statement statement = conn.createStatement();
            final String sql = "SELECT * FROM parsing";
            final ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                returnValue.setDone(rs.getBoolean("done"));
                returnValue.setCurrentYear(rs.getInt("year"));
                returnValue.setCurrentDate(rs.getDate("gameDate"));
                returnValue.setLeague(rs.getString("league"));
                returnValue.setPage(rs.getInt("page"));
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }
        return returnValue;
    }

    public int addLeague(String leagueName, String country, String sport) throws SQLException {
        leagueName = leagueName.trim();
        leagueName = leagueName.replaceAll(" ", "-");
        leagueName = leagueName.replaceAll("\\.", "");
        final int sportId = addSport(sport);
        final int countryId = addCountry(country);
        final String sql = "INSERT INTO League (name, sportId, countryId) VALUES (?, ?, ?) " + "ON DUPLICATE KEY UPDATE name = ?";
        final PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, leagueName);
        statement.setInt(2, sportId);
        statement.setInt(3, countryId);
        statement.setString(4, leagueName);

        statement.executeUpdate();

        return getId("League", leagueName, countryId);
    }

    public int addCountry(String name) throws SQLException {
        name = name.replaceAll(" ", "-");
        name = name.replaceAll("\\.", "");
        final String sql = "INSERT INTO Country (name) VALUES (?) ON DUPLICATE KEY UPDATE name = ?";
        final PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, name);
        statement.setString(2, name);
        statement.executeUpdate();

        return getId("Country", name, -1);
    }

    public int addSport(String sport) throws SQLException {
        sport = sport.replaceAll(" ", "-");
        sport = sport.replaceAll("\\.", "");
        final String sql = "INSERT INTO Sport (name) VALUES (?) ON DUPLICATE KEY UPDATE name = ?";
        final PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, sport);
        statement.setString(2, sport);
        statement.executeUpdate();
        return getId("Sport", sport, -1);
    }

    private int getId(String table, String name, int countryId) throws SQLException {
        String sql = "SELECT id FROM " + table + " WHERE name = ?";
        if (countryId > -1) {
            sql += " AND countryId = ?";
        }

        final PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, name.trim());
        if (countryId > -1) {
            stmt.setInt(2, countryId);
        }
        final ResultSet insertRs = stmt.executeQuery();
        int id = 0;
        if (insertRs.next()) {
            id = insertRs.getInt("id");
        }
        return id;
    }

    public int getLeagueId(int sportId, int countryId, String leagueName) throws SQLException {
        final String sql = "SELECT id FROM League WHERE name = ? AND countryId = ? AND sportId = ?";
        final PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, leagueName.trim());
        stmt.setInt(2, countryId);
        stmt.setInt(3, sportId);

        final ResultSet rs = stmt.executeQuery();

        int id = 0;
        while (rs.next()) {
            id = rs.getInt("id");
        }

        return id;
    }

    public int getSportId(String sportName) {
        final String sql = "SELECT id from Sport WHERE name = ?";
        PreparedStatement stmt;
        int id = 0;
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, sportName.trim());

            final ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                id = rs.getInt("id");
            }
        } catch (final SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return id;
    }

    public int getCountryId(String country) throws SQLException {
        final String sql = "SELECT id from Country WHERE name = ?";
        final PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, country.trim());

        final ResultSet rs = stmt.executeQuery();
        int id = 0;
        while (rs.next()) {
            id = rs.getInt("id");
        }

        return id;
    }

    public void addResult(String tableName, LocalDateTime gameDate, String homeTeam, String awayTeam, int homeScore,
            int awayScore, boolean overtime, float odds1, float oddsX, float odds2, int countryId, String season, int leagueId,
            int sportId) throws SQLException {

        final int homeTeamId = getOrInsertTeam(homeTeam, countryId, leagueId, sportId);
        final int awayTeamId = getOrInsertTeam(awayTeam, countryId, leagueId, sportId);

        final String selectSql = "SELECT id FROM SoccerResults WHERE homeTeamId = ? AND awayTeamId = ? AND DATE(gameDate) = ?";

        final PreparedStatement st = conn.prepareStatement(selectSql);
        final String date = gameDate.format(DateTimeFormatter.ISO_DATE);
        st.setInt(1, homeTeamId);
        st.setInt(2, awayTeamId);
        st.setString(3, date);

        final ResultSet rs = st.executeQuery();

        int gameId = -1;
        while (rs.next()) {
            gameId = rs.getInt("id");
        }

        if (gameId != -1) {
            final String sql
                    = "UPDATE " + tableName + " SET homeScore = ?, awayScore = ?, overtime = ?, odds1 = ?, oddsX = ?, odds2 = ? "
                            + "WHERE homeTeamId = ? AND awayTeamId = ? AND DATE(gameDate) = ?";

            final PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, homeScore);
            statement.setInt(2, awayScore);
            statement.setBoolean(3, overtime);
            statement.setFloat(4, odds1);
            statement.setFloat(5, oddsX);
            statement.setFloat(6, odds2);
            statement.setInt(7, homeTeamId);
            statement.setInt(8, awayTeamId);
            statement.setString(9, date);

            statement.executeUpdate();

        } else {
            final String sql = "INSERT INTO " + tableName
                    + " (homeTeamId, awayTeamId, homeScore, awayScore, overtime, odds1, oddsX, odds2, countryId, gameDate, season, leagueId) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE homeScore = ?, awayScore = ?, odds1 = ?, oddsX = ?, odds2 = ?";
            final PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, homeTeamId);
            stmt.setInt(2, awayTeamId);
            stmt.setInt(3, homeScore);
            stmt.setInt(4, awayScore);
            stmt.setBoolean(5, overtime);
            stmt.setFloat(6, odds1);
            stmt.setFloat(7, oddsX);
            stmt.setFloat(8, odds2);
            stmt.setInt(9, countryId);
            stmt.setString(10, gameDate.toString());
            stmt.setString(11, season);
            stmt.setInt(12, leagueId);
            stmt.setInt(13, homeScore);
            stmt.setInt(14, awayScore);
            stmt.setFloat(15, odds1);
            stmt.setFloat(16, oddsX);
            stmt.setFloat(17, odds2);

            stmt.execute();
        }

    }

    private int getOrInsertTeam(String teamName, int countryId, int leagueId, int sportId) throws SQLException {
        teamName = teamName.replace('\u00A0', ' ').trim();
        int teamId = getId("Team", teamName, countryId);
        if (teamId <= 0) {
            final String insertSql = "INSERT INTO Team (name, sportId, countryId, leagueId) VALUES (? ,? ,? ,?)";
            final PreparedStatement insertTeamStatement = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
            insertTeamStatement.setString(1, teamName.trim());
            insertTeamStatement.setInt(2, sportId);
            insertTeamStatement.setInt(3, countryId);
            insertTeamStatement.setInt(4, leagueId);

            insertTeamStatement.executeUpdate();
            final ResultSet generatedKeys = insertTeamStatement.getGeneratedKeys();
            generatedKeys.next();
            teamId = generatedKeys.getInt(1);
        }

        return teamId;
    }

    public void setParsingForLeague(int leagueId, int sportId, int countryId, LocalDateTime gameDate, int currentParsePage,
            String parsedYear) {
        final String sql
                = "UPDATE League SET parsedYear = ?, parsedPage = ?, lastParsedGameDate = ? WHERE sportId = ? AND countryId = ? AND id = ?";
        try {
            final PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, parsedYear);
            stmt.setInt(2, currentParsePage);
            stmt.setString(3, gameDate.toString());
            stmt.setInt(4, sportId);
            stmt.setInt(5, countryId);
            stmt.setInt(6, leagueId);

            stmt.executeUpdate();
        } catch (final SQLException e) {
            System.out.println("Failing sql: " + sql + ", " + parsedYear + ", " + currentParsePage + ", " + gameDate.toString()
                    + ", " + sportId + ", " + countryId + ", " + leagueId);
            e.printStackTrace();
        }
    }

    public String getLastParsedYear(String leagueName, int countryId) {
        String returnValue = "";

        final String sql = "SELECT parsedYear FROM League WHERE name = ? AND countryId = ?";
        try {
            final PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, leagueName);
            stmt.setInt(2, countryId);

            final ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                returnValue = rs.getString("parsedYear");
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return returnValue;
    }

    public Connection getDbConnection() {
        if (conn == null) {
            conn = getConnection();
        }
        return conn;
    }
}