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

public class ConnectorJBug100747 {

    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/test?"
                    + "useServerPrepStmts=true&user=root&password=");

            stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                    java.sql.ResultSet.CONCUR_UPDATABLE);

            stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
            stmt.executeUpdate(
                    "CREATE TABLE autoIncTutorial ("
                    + "priKey INT NOT NULL AUTO_INCREMENT, "
                    + "dataField VARCHAR(64), PRIMARY KEY (priKey))");

            stmt.executeUpdate("INSERT INTO autoIncTutorial (dataField) VALUES('value 1')");

            pStmt = conn.prepareStatement("SELECT priKey, dataField "
                    + "FROM autoIncTutorial FOR UPDATE",
                    // MySQL Connector/J 8.0.20/8.0.21 refuses java.sql.ResultSet.TYPE_FORWARD_ONLY
                    java.sql.ResultSet.TYPE_SCROLL_SENSITIVE,
                    java.sql.ResultSet.CONCUR_UPDATABLE);
            rs = pStmt.executeQuery();
            // prepared statements fail, but snormal statements work
            //rs = stmt.executeQuery("SELECT priKey, dataField "
            //        + "FROM autoIncTutorial");

            while (rs.next()) {
                // inserting a new row only fails, if a field in the resultset is updated before
                rs.updateString("dataField", "Updated dataField");
                rs.updateRow();
            }

            rs.moveToInsertRow();

            rs.updateString("dataField", "value 2");
            rs.insertRow();

            rs.last();

            // This function fails with
            // Exception in thread "main" java.lang.NumberFormatException: Invalid integer format for value '   '
            int autoIncKeyFromRS = rs.getInt("priKey");

            System.out.println("Key returned for inserted row: "
                    + autoIncKeyFromRS);

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) {
                    // ignore
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) {
                    // ignore
                }
                stmt = null;
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (SQLException sqlEx) {
                    // ignore
                }

                pStmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    // ignore
                }
                conn = null;
            }
        }
    }
}