How to programmatically rescue a newly inserted auto increment ID in JDBC?

Question

I am saving a feature for time-consuming processing and must return your ID so the user can check its status in the future.

The idea is that the user insert a set of data for import (for simplicity, let’s assume it is a string) and that it can query whether this import is waiting, in processing, has been successful or failed.

The idea is to insert a data in the following table:

CREATE TABLE importacao (
  id_importacao INTEGER PRIMARY KEY AUTOINCREMENT,
  data_inicio DATETIME NOT NULL,
  estado STRING NOT NULL,
  dados STRING NOT NULL
)

public int iniciarImportacao(String conjuntoDados) throws SQLException {
  try (PreparedStatement pstmt = conn.prepareStatement("INSERT INTO importacao (data_inicio, estado, dados) VALUES (DATETIME('now'), 'ESPERANDO', ?)")) {
    pstmt.setString(1, conjuntoDados);
    pstmt.executeUpdate();
    int idImportacao = // resultado na coluna id_importacao 

    jogarThreadWorkerImportacao(idImportacao);
    return idImportacao;
  }
}

At worst I know I could make a single consultation SELECT last_insert_rowid(), but there is another more JDBC alternative to getting that result?

I’m leaving without the tag sqlite because it is a detail in the query, the focus should even be the use of JDBC for such an order.

Answer 1

I don’t know if this is what you’re looking for, but you can use the method overload Connection.prepareStatement(String sql, int autogeneratekeys) that accepts a flag indicating how auto-generated keys must be returned. Pass the constant Statement.RETURN_GENERATED_KEYS as flag indicating that auto-generated keys must be available for query.

To query the key use the method Statement.getGeneratedKeys() that will return a Result set containing the key.

public int iniciarImportacao(String conjuntoDados) throws SQLException {
   try (PreparedStatement pstmt = conn.prepareStatement(
       "INSERT INTO importacao (data_inicio, estado, dados) VALUES (DATETIME('now'), 'ESPERANDO', ?)"
      , Statement.RETURN_GENERATED_KEYS)) {
   pstmt.setString(1, conjuntoDados);
   pstmt.executeUpdate();

   int idImportacao;    

   try (ResultSet keys = pstmt.getGeneratedKeys()) {
            if (keys.next()) {

                // resultado na coluna id_importacao.
                int idImportacao = keys.getInt(1));

            }
            else {
                // Caso não haja uma chave a ser retornada lança um erro.
                throw new SQLException("Nenhum id foi gerado.");
            }
    }

    jogarThreadWorkerImportacao(idImportacao);
    return idImportacao;
  }
}

Answer 2

has another mode also which is to get by last_insert_id().. it does not need to be returned in a resultset and can be passed as parameter to another sql..

Exp:

    sql = "INSERT INTO valorcliente (ID_CLIENTE,total) VALUES (?,?)";
    try{
        stmt = con.prepareStatement(sql);
        stmt.setInt(1, vc.getID_CLIENTE());
        stmt.setDouble(2, vc.getTotal());
        stmt.execute();
//este select obtem o ultimo key gerado e fica no @id
        sql = "SELECT LAST_INSERT_ID() INTO @id";
        stmt = con.prepareStatement(sql);
        stmt.execute();
//agora esse id é passado para outra tabela
        sql = "INSERT INTO parcela (ID_VALORCLIENTE,valor) VALUES (@id,?)";
        stmt = con.prepareStatement(sql);
        stmt.setDouble(1, p.getValor());
        stmt.execute();
     }catch (SQLException ex) {
            Logger.getLogger(ValorClienteDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            ConnectionFactoryMySQL.closeConnection(con, stmt, rs);
        }