The big last_insert_id() problem, again.

Question

Note - this follows my question here: http://stackoverflow.com/questions/2983685/jdbc-does-the-connection-break-if-i-lose-reference-to-the-connection-object

Now i have a created a class so i can deal with JDBC easily for the rest of my code -

public class Functions {
    private String DB_SERVER = "";
    private String DB_NAME = "test";
    private String DB_USERNAME = "root";
    private String DB_PASSWORD = "password";

    public  Connection con;
    public  PreparedStatement ps;
    public  ResultSet rs;
    public  ResultSetMetaData rsmd;

    public void connect()
            throws java.io.FileNotFoundException, java.io.IOException,
            SQLException, Exception    {
        String[] dbParms = Parameters.load();

        DB_SERVER = dbParms[0];
        DB_NAME = dbParms[1];
        DB_USERNAME = dbParms[2];
        DB_PASSWORD = dbParms[3];

        // Connect.
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        con = DriverManager.getConnection("jdbc:mysql://"
                + DB_SERVER + "/"
                + DB_NAME, DB_USERNAME, DB_PASSWORD);
    }

    public void disconnect() throws SQLException  {
        // Close.
        con.close();
    }
}

As seen Parameters.load() refreshes the connection parameters from a file every-time, so that any changes to the same may be applied on the next immediate connection.

An example of this class in action -

public static void add(String NAME)
        throws java.io.FileNotFoundException, java.io.IOException, SQLException, Exception    {
    Functions dbf = new Functions();
    dbf.connect();

    String query = "INSERT INTO " + TABLE_NAME + "(" +
            "NAME" +
            ") VALUES(?)";
    PreparedStatement ps = dbf.con.prepareStatement(query);
    ps.setString(1, NAME);
    ps.executeUpdate();

    dbf.disconnect();
}

Now here is the problem - for adding a record to the table above, the add() method will open a connection, add the record - and then call disconnect() .

What if i want to get the ID of the inserted record after i call add() -like this :

Department.add("new dept");
int ID = getlastID();

Isn't it possible that another add() was called between those two statements?

Answer 1

It's possible .. you might want to consider refactoring your code.

Baby steps, retrieve and store the id in a variable inside your add method before closing the connection, change the signature and return the id.

Answer 2

It doesn't matter if another add is performed between the statements, as long as you keep your connection open. The last_insert_id function gets the last id created in the same database session, i.e. using the same connection.

So, you have to change your code so that you con't close and reopen the connection between adding the record and getting the id.