How to get max row ID of a database record with duplicate data

Question

I want to get the ID of the last row inserted for "kashif".

How can I do this in Java?

I have a database table with columns:

 ___________________________________________________________________________
| id | name | card_number | cell_number |sms_verification|inserted_date_time|

id is set as the primary key with auto-increment.

Inserting data into the database via Java works - I have a class where I get input from the user, and in the same class, I'm making a connection to the database.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection connection = DriverManager.getConnection(
        "jdbc:sqlserver://localhost;instance=MSSQLSERVER;databaseName=RestApi;user=sa;password=coder182");
Statement statement = connection.createStatement();

String query = "insert into User_information(action_name, card_number , cell_number ,sms_verification ,  inserted_date_time)"
    + "values ('" + action_name + "','" + card_number + "', '" + cell_number + "' , '" + sms_verification
    + "', '" + now + "');";
statement.executeUpdate(query)

Inputs look like this:

kashif,28028209,239203084,yes
abid,233000,6260616,no
kashif,28028209,239203084,yes

Obiligatory [Little Bobby Tables](https://xkcd.com/327/) reference. Don't ever trust user inputs. Please use parameterized queries (aka [Prepared Statements](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) in Java land) instead of constructing your own queries so you can avoid people deleting your tables or stealing your data. Or both. — AlwaysLearning, Aug 06 '19 at 09:37

score 1 · Answer 1 · edited Aug 06 '19 at 08:32

1

I Think, you could write a query something like this:

SELECT max(id) from <Table Name> where name = 'Kashif'

Or

SELECT id from <Table Name> where name = 'Kashif' Order by id desc Limit 1

edited Aug 06 '19 at 08:32

GreenhouseVeg

617
5
13

answered Aug 06 '19 at 08:13

Ankur

892
6
11

@Wasif, use one of these queries to create a `Statement` or `PreparedStatement`, and call `executeQuery()` to get a `ResultSet` containing the ID. – GreenhouseVeg Aug 06 '19 at 08:34
@GreenhouseVeg thank you . 1st one give me the result that i was expecting – Wasif Zaman Aug 06 '19 at 09:53

score 1 · Answer 2 · edited Aug 06 '19 at 11:12

1

Add Statement.RETURN_GENERATED_KEYS in your executeUpdate statement.

Integer createdId = null;
int executeUpdate = createStatement.executeUpdate(insertSql, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = createStatement.getGeneratedKeys();
if (rs.next()) {
    createdId = rs.getInt(1);
}
System.out.println(createdId);

edited Aug 06 '19 at 11:12

Victor

3,669
3
37
42

answered Aug 06 '19 at 09:37

charles

46
1

Shravan40 · Answer 3 · 2019-08-06T08:22:53.997

0

I will suggest a design change in your table schema. Create one more row which keeps the record of last_updated_time and you can query based on this key.

last_updated_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

This will make your life lot easier as the number of entries grows into a table.

edited Aug 06 '19 at 08:22

answered Aug 06 '19 at 08:10

Shravan40

8,922
6
28
48

How to get max row ID of a database record with duplicate data

3 Answers3