Hello, I've been trying to code a TrueSkill calculator using Java that fetches scores and updates them in a MySQL database.
I finished coding it however it appears to have a problem while executing.
This is an example of what I want the output to be like:

connecting to database server
connected
beginning transaction
creating tables
getting unscored games
found 2 unscored games
gameid 1 found
gameid 1 has no players, ignoring
gameid 3 found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
gameid 3 is calculating
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
copying dota elo scores to scores table
committing transaction
done

Where **old** and **new** are the corresponding calculated ratings.
However this is the output I get:

connecting to database server
connected
beginning transaction
creating tables
getting unscored games
found 2 unscored games
gameid 1 has no players, ignoring
copying dota ts scores to tsscores table
committing transaction
done

It clearly appears to be a problem in my loops after I fetch the number of unscored games and their id's but I can't seem to figure it out.

Here is my code:

package dota;

/*
 *
 * @author Pavel Yarmak
 * 
 */
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.*;
import java.math.*;
import java.text.*;
import jskills.*;

public class Dota {
    
    static ArrayList<String> names = new ArrayList<String>();

    static Team team1 = new Team();
    static Team team2 = new Team();
    static double matchQuality;

    public static void main(String[] args) {
        NumberFormat nf = NumberFormat.getNumberInstance();
        nf.setMaximumFractionDigits(2);
        nf.setRoundingMode(RoundingMode.HALF_DOWN);

        Connection con = null;
        Statement st = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        Properties props = new Properties();
        FileInputStream in = null;

        try {
            in = new FileInputStream("database.properties");
            props.load(in);

        } catch (FileNotFoundException ex) {

            Logger lgr = Logger.getLogger(Dota.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } catch (IOException ex) {

            Logger lgr = Logger.getLogger(Dota.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {
            
            try {
                 if (in != null) {
                     in.close();
                 }
            } catch (IOException ex) {
                Logger lgr = Logger.getLogger(Dota.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
        }

        String url = props.getProperty("db.url");
        String user = props.getProperty("db.user");
        String passwd = props.getProperty("db.passwd");

        try {

            System.out.println("connecting to database server");
            con = DriverManager.getConnection(url, user, passwd);
            con.setAutoCommit(false);
            System.out.println("connected");
            System.out.println("beginning transaction");
            System.out.println("creating tables");
            
            st = con.createStatement();

            st.execute("CREATE TABLE IF NOT EXISTS dota_ts_scores "
                    + "( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(15) NOT NULL, server VARCHAR(100) NOT NULL, mu REAL NOT NULL, sigma REAL NOT NULL )");
            st.execute("CREATE TABLE IF NOT EXISTS dota_ts_games_scored "
                    + "( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, gameid INT NOT NULL, quality REAL NOT NULL )");
            
            System.out.println("getting unscored games");
            pst = con.prepareStatement("SELECT id FROM games WHERE id NOT IN ( SELECT gameid FROM dota_ts_games_scored ) ORDER BY id");
            rs = pst.executeQuery();
            LinkedList <Integer>UnscoredGames = new LinkedList<Integer>();

            while (rs.next()) {
                UnscoredGames.add(rs.getInt(1));
                /*
                Iterator iterator = UnscoredGames.iterator();
                while(iterator.hasNext()){
                    System.out.println(iterator.next()+" ");
                }
                 * 
                 */
            }
            System.out.println("found " + UnscoredGames.size() + " unscored games");
            
            while (!UnscoredGames.isEmpty()){
               int GameID = UnscoredGames.getFirst();
               UnscoredGames.removeFirst();
               pst = con.prepareStatement("SELECT dota_ts_scores.id, gameplayers.name, "
                       + "spoofedrealm, newcolour, winner, mu, sigma FROM dotaplayers LEFT JOIN "
                       + "dotagames ON dotagames.gameid=dotaplayers.gameid LEFT JOIN "
                       + "gameplayers ON gameplayers.gameid=dotaplayers.gameid AND "
                       + "gameplayers.colour=dotaplayers.colour LEFT JOIN "
                       + "dota_ts_scores ON dota_ts_scores.name=gameplayers.name AND "
                       + "server=spoofedrealm WHERE dotaplayers.gameid=" + Integer.toString(GameID));
               rs = pst.executeQuery();
               if (!rs.next()) {
                   boolean ignore = false;
                   int rowids[] = new int[10];
                   String servers[] = new String[10];
                   boolean exists[] = new boolean[10];
                   int num_players = 0;
                   double player_mu[] = new double[10];
                   double player_sigma[] = new double[10];
                   int player_teams[] = new int[10];
                   double team_ratings[] = new double[2];
                   int team_winners[] = new int[2];
                   int team_numplayers[] = new int[10];
               
                   while (rs.next()) {
                       System.out.println("gameid " + Integer.toString(GameID) + " found");

                       team_ratings[0] = 0.0;
                       team_ratings[1] = 0.0;
                       team_numplayers[0] = 0;
                       team_numplayers[1] = 0;

                       if( num_players >= 10 ) {
                           System.out.println("gameid " + Integer.toString(GameID) + " has more than 10 players, ignoring");
                           ignore = true;
                           break;
                       }

                       int Winner = rs.getInt(4);

                       if (Winner != 1 && Winner != 2) {
                           System.out.println("gameid " + Integer.toString(GameID) + " has no winner, ignoring");
                           ignore = true;
                           break;
                       }
                       else if( Winner == 1 ) {
                           team_winners[0] = 1;
                           team_winners[1] = 0;
                       }
                       else {
                           team_winners[0] = 0;
                           team_winners[1] = 1;
                       }

                       int id = rs.getInt(1);
                       if (!rs.wasNull())
                           rowids[num_players] = id;
                       else
                           rowids[num_players] = 0;

                       names.add(num_players, rs.getString(2));
                       servers[num_players] = rs.getString(3);

                       Double mu = rs.getDouble(6);
                       Double sigma = rs.getDouble(7);
                       if (!rs.wasNull()) {
                           exists[num_players] = true;
                           player_mu[num_players] = mu;
                           player_sigma[num_players] = sigma;
                       }
                       else {
                           System.out.println("new player [" + rs.getString(2) + "] found");
                           exists[num_players] = false;
                           player_mu[num_players] = 100.0;
                           player_sigma[num_players] = 100.0/3;
                       }

                       int Colour = rs.getInt(4);


                       if( Colour >= 1 && Colour <= 5 ) {
                           player_teams[num_players] = 0;
                           team1.addPlayer(new Player(rs.getString(2)), 
                                                                       new Rating(player_mu[num_players], 
                                                                       player_sigma[num_players]));
                           team_numplayers[0]++;
                       }
                       else if( Colour >= 7 && Colour <= 11 ) {
                           player_teams[num_players] = 1;
                           team2.addPlayer(new Player(rs.getString(2)), 
                                                                       new Rating(player_mu[num_players], 
                                                                       player_sigma[num_players]));
                           team_numplayers[1]++;
                       }
                       else {
                           System.out.println("gameid " + Integer.toString(GameID) + " has a player with an invalid newcolour, ignoring");
                           ignore = true;
                           break;
                       }
                       num_players++;
                   }
                   if(!ignore) {
                       if(num_players == 0)
                           System.out.println("gameid " + Integer.toString(GameID) + " has no players, ignoring");
                       else if( team_numplayers[0] == 0 )
                           System.out.println("gameid " + Integer.toString(GameID) + " has no Sentinel players, ignoring");
                       else if( team_numplayers[1] == 0 )
                           System.out.println("gameid " + Integer.toString(GameID) + " has no Scourge players, ignoring");
                       else {
                           System.out.println("gameid " + Integer.toString(GameID) + " is calculating");

                           double old_player_mu[] = new double[player_mu.length];
                           double old_player_sigma[] = new double[player_sigma.length];

                           for ( int idx = 0 ; idx <= player_mu.length; ++idx )
                               System.arraycopy( player_mu, 0, old_player_mu, 0, player_mu.length );
                           for ( int idx = 0 ; idx <= player_sigma.length; ++idx )
                               System.arraycopy( player_sigma, 0, old_player_sigma, 0, player_sigma.length );

                           Collection<ITeam> teams = Team.concat(team1, team2);
                           team1.clear();
                           team2.clear();
                           GameInfo gameinfo = new GameInfo(100.0, // mu
                                                         100.0/3.0, // sigma
                                                         100.0/6.0, // beta
                                                         100.0/300.0, // tau
                                                         0.00); // draw probability

                           Map<IPlayer, Rating> results = TrueSkillCalculator.calculateNewRatings(gameinfo, teams, team_winners);
                           matchQuality = TrueSkillCalculator.calculateMatchQuality(gameinfo, teams);
                           System.out.println("match quality: " + matchQuality);
                           
                           for (ITeam t : teams) {
                               for (IPlayer p : t.keySet()) {
                                   Rating r = results.get(p);
                                   player_mu[names.indexOf(p)] = r.getMean();
                                   player_sigma[names.indexOf(p)] = r.getStandardDeviation();
                                   }
                           }

                           for( int i = 0; i < num_players; i++ ) {
                               System.out.println("player [" + names.get(i) + "] mu: " + old_player_mu[i] + " -> " + player_mu[i]);
                               System.out.println("sigma: " + old_player_sigma[i] + " -> " + player_sigma[i]);

                               if(exists[i]) {
                                   pst = con.prepareStatement("UPDATE dota_ts_scores SET mu=" 
                                           + nf.format(player_mu[i]) + ", sigma=" + nf.format(player_sigma[i]) + " WHERE id=" + Integer.toString(rowids[i])); 
                                  pst.executeUpdate();
                               }
                               else {
                                   pst = con.prepareStatement("INSERT INTO dota_ts_scores ( name, server, mu, sigma ) VALUES ( '" 
                                           + names.get(i) + "', '" + servers[i] + "', " + nf.format(player_mu[i]) + "', '" + nf.format(player_sigma[i]) + " )");
                                   pst.executeUpdate();
                               }
                           }
                       }
                       names.clear();
                   }
               }
               pst = con.prepareStatement("INSERT INTO dota_ts_games_scored ( gameid, quality ) VALUES ( " 
                       + Integer.toString(GameID) + ", " + matchQuality + " )");
               pst.executeUpdate();
           }
            System.out.println("copying dota ts scores to tsscores table");
            st.execute("DELETE FROM tsscores WHERE category='dota_ts'");
            st.execute("INSERT INTO tsscores ( category, name, server, mu, sigma ) SELECT 'dota_ts', name, server, mu, sigma FROM dota_ts_scores");

            System.out.println("committing transaction");
            con.commit();
            System.out.println("done");

            } catch (SQLException ex) {

            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException ex1) {
                    Logger lgr = Logger.getLogger(Dota.class.getName());
                    lgr.log(Level.WARNING, ex1.getMessage(), ex1);
                }
            }

            Logger lgr = Logger.getLogger(Dota.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
            
        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(Dota.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}

Please let me know if you'd like me to provide any additional information.
Thank you for your time.

Why this on line 120?

if (!rs.next()) {

You don't want that block to execute if the query has results?

Oh wow I definitely noticed that before, sorry I must have reverted to an earlier version of my code in frustration somehow.

Here is the output with that line reading if (rs.next()) {

connecting to database server
connected
beginning transaction
creating tables
getting unscored games
found 2 unscored games
gameid 3 found
new player [null] found
gameid 3 found
gameid 3 has no winner, ignoring
copying dota ts scores to tsscores table
committing transaction
done

By the way, my database entry for this game currently has 10 players, 1 named SoV-Overlord and the other 9 being null names. I just threw together some mock entries because I didn't want to mess with real data.

Keep in mind rs.next() advances the result set, so when you call that again in your while() loop you're skipping over the first record to the next.

Right, I added rs.beforeFirst(); on line 121 which yielded the following results:

connecting to database server
connected
beginning transaction
creating tables
getting unscored games
found 2 unscored games
gameid 3 found
new player [sov-overlord] found
gameid 3 found
new player [null] found
gameid 3 found
gameid 3 has no winner, ignoring
copying dota ts scores to tsscores table
committing transaction
done

As you can see the loops are still doing something very odd.

By the way here is the result of the sql query where gameid=3:

id	name	        spoofedrealm	    newcolour	winner	mu	sigma
NULL	sov-overlord	useast.battle.net	1	1	NULL	NULL
NULL	NULL	        NULL	                2	1	NULL	NULL
NULL	NULL	        NULL	                3	1	NULL	NULL
NULL	NULL	        NULL	                7	1	NULL	NULL
NULL	NULL	        NULL	                5	1	NULL	NULL
NULL	NULL	        NULL	                4	1	NULL	NULL
NULL	NULL	        NULL	                11	1	NULL	NULL
NULL	NULL	        NULL	                9	1	NULL	NULL
NULL	NULL	        NULL	                10	1	NULL	NULL
NULL	NULL	        NULL	                8	1	NULL	NULL

I thought this will make it easier to visualize.

You're setting winner from the fourth column

int Winner = rs.getInt(4);

but winner is the fifth column your data set. Contrary to most any other indexing in Java, in ResultSet the first column is 1, not 0.

Thank you very much! That was what I missed :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.