SQL: Get ID of newly inserted row

Question

I am writing an API, which purpose is it to store data about prisoners in a jail of a minecraft bukkit plugin. I offer a method, that sends a player to jail. When invoked, an entry is made to a database table, which stores all current prisoners. I add a new entry to this table like so:

public static void addNewPunishment(UUID playerUUID, int punishmentBlocks, int maxExtensions) {

        try (final PreparedStatement ps = DatabaseConnector.getConnection().prepareStatement(
                "insert into PRISONERS(UUID, REMAININGBLOCKS, STARTINGBLOCKS, MAXEXTENSIONS) values (?,?,?,?)")) {
            ps.setString(1, playerUUID.toString());
            ps.setInt(2, punishmentBlocks);
            ps.setInt(3, punishmentBlocks);
            ps.setInt(4, maxExtensions);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

However, while implementing methods that use this API it turned out, that it's required, that the id of the newly inserted row is returned, but I have absolutely no idea, how to get it. This is the structure of my table:

I need the "id"-value of this column, but I can just get the number of affected row's which doesn't really help... How can I make insert into return a specific value of the inserted row?

What RDBMS do you use? – fatemeh poormohammad Dec 03 '20 at 23:39 — fatemeh poormohammad, Dec 03 '20 at 23:39

score 2 · Accepted Answer · answered Dec 03 '20 at 23:47

You can do it as described in the link below:

How to get the id of last inserted row using preparedstatement?

your code could be:

public static void addNewPunishment(UUID playerUUID, int punishmentBlocks, int maxExtensions) {

        try (final PreparedStatement ps = DatabaseConnector.getConnection().prepareStatement(
                "insert into PRISONERS(UUID, REMAININGBLOCKS, STARTINGBLOCKS, MAXEXTENSIONS) values (?,?,?,?)"), Statement.RETURN_GENERATED_KEYS) {
            ps.setString(1, playerUUID.toString());
            ps.setInt(2, punishmentBlocks);
            ps.setInt(3, punishmentBlocks);
            ps.setInt(4, maxExtensions);
            ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            if(rs.next())
            {
                    int last_inserted_id = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

Works perfectly fine, many thanks for the quick response! – monamona Dec 04 '20 at 00:08 — monamona, Dec 04 '20 at 00:08

SQL: Get ID of newly inserted row

1 Answers1