CODEWITHSUNDEEP
javamysqljdbc
Steve the Maker
Steve the Maker

Reputation: 621

Return integer for the last row in MYSQL using JDBC

I'm new to working with JDBC commands. I have a database in MYSQL and each entry gets an ID. As initially created the ID was just a static variable that I iterated when the constructor runs. This was okay until I started deleting entries or running the program a second time. Then I start getting collisions. I need a way to return the highest row in the table and assign it to an integer that I can iterate.

The QuerySELECT MAX(ID) FROM table seems to get the value that I'm looking for. But I'm not sure of the syntax to get that value into an integer so I can return it.

    public int getHighestRow() {
    PreparedStatement ps;
    int highestID = 0;

    try {
        ps = getSQLDB().prepareStatement("SELECT MAX(studentID) FROM student");
        ps.execute();
    } catch (SQLException e){
        Logger.getLogger(Undergraduate.class.getName()).log(Level.SEVERE, null, e);
    }

    if (highestID > 0) return highestID;
    else return 0;

I have a feeling this is very simple, but I wasn't able to find an existing answer. Or is there a more elegant way to do this in general?

Upvotes: 0

Views: 423

Answers (1)

Joop Eggen
Joop Eggen

Reputation: 109597

SQL of different providers solve the retrieval of automatic generated keys differently. JDBC provides a standard solution.

Better use this JDBC solution, as it prevents mixing up those keys when insertions are done at the same time.

try (PreparedStatement ps = getSQLDB().prepareStatement(
        "INSERT INTO student(....) VALUES(?, ..., ?)",
        Statement.RETURN_GENERATED_KEYS)) { // Without StudentId
    ps.setString(1, name);
    ...
    ps.executeUpdate();


    try (ResultSet rsKeys = ps.getGeneratedKeys()) {
        if (rsKeys.next()) { // Only one record inserted
            int studentId = rsKeys.getInt(1); // One key generated
        }
    }
} catch (SQLException e){
    Logger.getLogger(Undergraduate.class.getName()).log(Level.SEVERE, null, e);
}

The mechanism with try(...). try-with-resources, ensures that close is called automatically.

Upvotes: 3

Related Questions