import javax.xml.transform.Result;
import java.security.InvalidParameterException;
import java.sql.*;
import java.util.ArrayList;

/**
 * Created by Magnus on 12/10/2016.
 */
public class Main {

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/dDB36";
    static final String USERNAME = "Magnus";
    static final String PASSWORD = "";
    public static void main(String[] args) {

        // Run many select queries on one thread
        // Run many update queries on another thread

        runUserOfficeUpdate(Connection.TRANSACTION_READ_UNCOMMITTED);


    }

    public static void runUserOfficeUpdate(int commitSettings) {
        if (commitSettings != Connection.TRANSACTION_READ_COMMITTED && commitSettings != Connection.TRANSACTION_READ_UNCOMMITTED)
            throw new InvalidParameterException("Commit setting can be");

        Thread t = new Thread(new Runnable() {
            @Override
            public void run() {
                Connection conn = null;
                Statement statement = null;
                PreparedStatement updateUserIdStatement = null;
                long endTime = 0;
                try {
                    Class.forName(JDBC_DRIVER);

                    conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);

                    statement = conn.createStatement();
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(commitSettings);

                    //conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

                    String sql = "SELECT office, userid FROM People WHERE office IS NOT NULL";

                    String updateOfficeNumberSQL = "UPDATE People SET number = ?, office = ? WHERE office LIKE ?";

                    updateUserIdStatement = conn.prepareStatement(updateOfficeNumberSQL);
                    long startTime = System.currentTimeMillis();

                        ResultSet rs = statement.executeQuery(sql);

                    while (rs.next()) {
                        //Retrieve by column name
                        String office = rs.getString("office");
                        String userId = rs.getString("userid");
                        int officeNumber = Integer.parseInt(office.split("-")[1]);
                        String newOffice = office.split("-")[0] + "-" + officeNumber;
                        officeNumber = officeNumber + 1;
                        updateUserIdStatement.setInt(1, officeNumber);
                        updateUserIdStatement.setString(2, newOffice);
                        updateUserIdStatement.setString(3, office.split("-")[0] + "%");

                        updateUserIdStatement.executeUpdate();
                    }
                        rs.close();
                    conn.commit();
                    endTime = System.currentTimeMillis() - startTime;

                    statement.close();
                    System.out.println(endTime);
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    try{
                        if (statement != null)
                            statement.close();
                        if (updateUserIdStatement != null)
                            updateUserIdStatement.close();
                    } catch (SQLException e2){
                        e2.printStackTrace();
                    }
                    try {
                        if (conn!=null)
                            conn.close();
                    } catch (SQLException e3){
                        e3.printStackTrace();
                    }
                }
            }

        });

        t.start();

    }

}