Good afternoon. The question is this. So there is a table of clients in the database. It is necessary immediately after adding a new record to get its id. I give the code below. The method I use does not work. Adds an entry to the table

public void addNewClient(Client client) { PreparedStatement preparedStatement = null; Connection connection = null; String sql = "INSERT INTO esteamerbase.client (FIO_CLIENT, DATE_BORN, TELEPHONE, SMS, STATUS_CLIENT) " + "VALUES (?,?,?,?,?)"; try { connection = getConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,client.getFio_client()); preparedStatement.setString(2,client.getDate_born()); preparedStatement.setLong(3,client.getTelephone()); preparedStatement.setBoolean(4, client.getSms()); preparedStatement.setString(5, client.getStatus_client()); preparedStatement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { closeConnection(connection); closePreparedStatement(preparedStatement); } }

Gets the last id

 public int getLasId(){ int id_user = 0; String sql = "SELECT LAST_INSERT_ID();"; Connection connection = null; Statement statement = null; ResultSet resultSet = null; try{ connection = getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(sql); while (resultSet.next()){ id_user = resultSet.getInt(1); } }catch (SQLException e){ closeConnection(connection); closeStatement(statement); } return id_user; }

The point is that every time I create a new connection and then close it. If it is not closed then everything works. But I do not like the fact that they will be constantly open.

    1 answer 1

    You fundamentally misunderstand how to get generated id - your method will fail if several different users / connections are knocked parallel to the database.

    You need to create a statement like this:

     preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

    Further, after execution, poll the generated keys (there may be several of them), through:

     ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) { if (generatedKeys.next()) { //получаем имя и значение сгенерированного ключа из ResultSet }
    • I get these keys when adding a new client? And you would be proud if you suggested with such an algorithm if several people add a client at the same time. For each of them, the connection is different? - Andrei Sosnovsky