How do I return the ID of a record right after it is entered?

Question

How do I return the ID of a record right after it is entered?

Navigation

#1 by (8 votes)
#2 by (2 votes)

5

I'm doing a Java application in conjunction with a MySQL database and would like to know what would be the best command to return an auto increment ID of the database shortly after the registry was entered.

My application will work with multiple concurrent accesses to the database, and would like a non-return type error to return a wrong ID that was entered by another user.

I'm using JDBC and I retrieve the connection 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);
}

mysql java jdbc

asked by anonymous 24.05.2015 / 23:20

2 answers

2

I've done it this way:

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

Running this code immediately after insertion will return the last generated id.

13.06.2015 / 03:20

How to remove formatting from the GETDATE () command Sort List in java

score 8 · Accepted Answer

Yes, there is recovery. From how you retrieve the connection, you might be using something like this to construct the statement and 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 to #prepareStatement(sql) for #prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) .

This #prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) create a statement that has the ability to retrieve data generated by the database, 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 assumes you have only one "self-generated" column and retrieve the value from it. You can also call by name, like this:

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

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