import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class SQL {
    Connection connection = null;
    int tempscore = 0;

    public void verbind() throws ClassNotFoundException, SQLException{
        try{
        String driver = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
        Class.forName(driver);

        // Verbindings variabelen en de verbinding opzetten.
        String server = "localhost";
        String database = "carrousel";
        String url = "jdbc:mysql://" + server +  "/" + database; // maak een JDBC url
        String gebruiker = "trivia";
        String ww = "Ikwilnaarbinnen159";
        connection = DriverManager.getConnection(url, gebruiker, ww);
    } catch (ClassNotFoundException e) {
        System.out.println(" DB driver niet gevonden "+ e);
    } catch (SQLException e) {
        System.out.println("Kan geen verbinding opzetten met de db"+ e);
    }
    }
    public void Score(String nick2){
        try {
            // Maak een resultset aan de hand van een query
                Statement stmt = connection.createStatement();
                ResultSet op = stmt.executeQuery("SELECT score FROM nicknaam WHERE nick = \"" + nick2 + "\";" );
                    if( op.next() ){
                    tempscore = Integer.parseInt( op.getString(1) );
                    tempscore ++;
                    //op.updateInt(1, score);
                    stmt.execute("UPDATE nicknaam SET score = " + tempscore + " WHERE nick = \"" + nick2 + "\";" );
                    } else{
                        stmt.execute( "INSERT INTO nicknaam VALUES (\"0\", \"" + nick2 + "\", \"" + 1 + "\");");
                        tempscore = 1;
                    }
            } catch (SQLException e) {
                System.out.println("fout in de db "+ e);
            }
    }
    public int scoresend()
    {
        return tempscore;
    }
    public String achternick(String nickin) throws SQLException{    //Achterhaal de nick.
        String tempnick = "leeg";
        Statement stmt = connection.createStatement();
        ResultSet sc = stmt.executeQuery( "SELECT nick, score FROM nicknaam ORDER BY score DESC" );
        while( sc.next() && sc.getString(2) != nickin ){
        tempnick = sc.getString(1);
        tempscore = Integer.parseInt( sc.getString(2));
        }
        return tempnick;
    }
}