package jdbc;
import java.sql.*;
public class JDBCGames {
    public static void main(String[] args) {
        Connection con = null;
        try { // load the driver
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception e) { // problem loading driver, class not exist?
            e.printStackTrace();
            return;
        }
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost/test", "USERNAME", "PASSWORD");

            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); //set tx_level if you want

            con.setAutoCommit(false);

            //first read
            Statement select = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = select.executeQuery("SELECT * FROM names");

            printResultSet(result);

            Thread.sleep(10000); //wait here while I insert line manually into the table via DB interface to make a phantom read

            //second read
            Statement select2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result2 = select2.executeQuery("SELECT * FROM names");

            printResultSet(result2);

            con.commit();

        } catch (Exception e) {
            e.printStackTrace();
    } finally {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace(); 
            }
        }
    }
    }

    static void printResultSet(ResultSet result) throws SQLException{
        while (result.next()) {
            int age;
            String name;
            String second_name;
            age = result.getInt(4);
            if (result.wasNull()) {
                age = -1;
            }
            name = result.getString(2);
            if (result.wasNull()) {
                name = null;
            }
            second_name = result.getString(3);
            if (result.wasNull()) {
                second_name = null;
            }
            System.out.println(String.format("name %s second_name %s age %d", name, second_name, age));
        }
    }
}