How to return the ID of a record right after it is inserted?

Question

I am making a Java application in conjunction with a Mysql database and would like to know what would be the best command to return an auto database increment ID right after the record is inserted.

My application will work with multiple simultaneous access to the database and would like in a way that no errors occur of the return type a wrong ID that was entered by another user.

I’m using JDBC and I get the connection back this way:

private static final String URL = "jdbc:mysql://localhost/exemplows";
private static final String USER = "XXXXXXXX";
private static final String SENHA = "XXXXXXX";

public static Connection obtemConexao() throws SQLException {
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block e.printStackTrace();
    }
    return DriverManager.getConnection(URL, USER, SENHA);
}

Answer 1

Yes, there is a way to recover. Starting from how you recover the connection it is possible that you are using something like this to build the statement and do the Insert:

final String sql = "INSERT INTO minha_tabela(coluna_um, coluna_dois) VALUES(?, ?)";
final Connection conn = obtemConexao();
final PreparedStatement ps = conn.prepareStatement(sql);
// seta os valores dos parâmetros
ps.executeUpdate();

Basically what you need to do is change the call from #prepareStatement(sql) for #prepareStatement(sql, Statement.RETURN_GENERATED_KEYS).

This #prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) create a statement which has the ability to recover database generated data such as auto-incremented ids. To use, you can do something like this:

final String sql = "INSERT INTO minha_tabela(coluna_um, coluna_dois) VALUES (?, ?);";
final Connection conn = obtemConexao();
final PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
// seta os valores dos parâmetros
ps.executeUpdate();
final ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
    final int lastId = rs.getInt(1);
}

This example considers that you have only one "auto generated" column and recovers its value. You can also call by name, thus:

final int lastId = rs.getInt("id");

If there is more than one "auto generated" column in your table, all of them will be returned.

Answer 2

I’ve done it this way:

    ResultSet resultSet = preparedStatement.executeQuery("SELECT LAST_INSERT_ID()");
        if (resultSet.next()) {
            novoId = resultSet.getInt("LAST_INSERT_ID()");
        }

Executing this code right after insertion will return the last generated id.

Answer 3

The generated key in auto_increment does not need to be returned in a Resultset, Following example:

sql = "INSERT INTO tabela1 (ID_CLIENTE,total) VALUES (?,?)";
        try {
            stmt = con.prepareStatement(sql);
            stmt.setInt(1, cliente.getID());
            stmt.setDouble(2, total);
            stmt.execute();
            sql = "SELECT LAST_INSERT_ID() INTO @id";
            stmt = con.prepareStatement(sql);
            stmt.execute();
            sql = "INSERT INTO tabela2 (ID_TABELA1,valor) VALUES (@id,?)";
            stmt = con.prepareStatement(sql);
            stmt.setDouble(1, valor);
            stmt.execute();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(ValorClienteDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            ConnectionFactoryMySQL.closeConnection(con, stmt, rs);
        }

tested and working.