mySQL - Get last added ID

Question

Im a new programmer (just started on my programmer education).

Im trying to set my "tempVenueId" attribute with the last ID in my SQL DB: (in this case idvenue = 12)

Table name: venue

idvenue | name        | address
-------------------------------
 1      | Khar        | 5
 2      | SantaCruz   | 3
 3      | Sion        | 2
 4      | VT          | 1
 5      | newFort     | 3
 6      | Bandra      | 2
 7      | Worli       | 1
 8      | Sanpada     | 3
 9      | Joe         | 2
10      | Sally       | 1
11      | Elphiston   | 2
12      | Currey Road | 1

My code:

Private int tempVenueId;
SqlRowSet rs1 = jdbc.queryForRowSet("SELECT MAX(idvenue) FROM venue;");
while(rs1.next())tempVenueId = rs1.getInt(0);

/*I have also tried with "while(rs1.next())tempVenueId = rs1.getInt(1);"
*/

Still it does not work I get this when I run debug mode in intelliJ:

tempVenueId = 0

The code as shown will throw an exception because `0` is an invalid column index. — Mark Rotteveel, Nov 27 '18 at 19:59

score 2 · Answer 1 · answered Nov 26 '18 at 13:38

2

The index is from 1 rather than 0,so two ways to solve it:

while(rs1.next()){
   tempVenueId = rs1.getInt(1);
   break;
};

or

SqlRowSet rs1 = Jdbc.queryForRowSet("SELECT MAX(idvenue) as value FROM venue;");
while(rs1.next()){
  tempVenueId = rs1.getInt("value");
  break;
 }

answered Nov 26 '18 at 13:38

flyingfox

13,414
3
24
39

I guess you cant use index 0, in any case ? when you work in mySQL tables ?. - They always start at index 1 - Correct ?. – Liam Blake Nov 26 '18 at 13:54
Index in `jdbc` is start from **1** rather than **0**,you can use Google to find more about it – flyingfox Nov 26 '18 at 13:58

score 0 · Answer 2 · answered Nov 26 '18 at 15:37

0

Solved - This solution was alot easier.... (I think).

String sql = "SELECT LAST_INSERT_ID();"; 
SqlRowSet rs = Jdbc.queryForRowSet(sql);
rs.next();
Venue VN = new Venue(rs.getInt(1));
tempVenueId = VN;

Now i get the last AUTO INCREMENTET ID from DB

answered Nov 26 '18 at 15:37

Liam Blake

43
3
8

That is not the right way to do it with JDBC, and it actually doesn't answer the question you asked (or, the other way around, if this is the answer, you should have asked a different question). – Mark Rotteveel Nov 27 '18 at 19:57
@MarkRotteveel - Im new to all this.. I ended up doing this: //Retrieve idvenue on last added venue in DB. SqlRowSet rs = Jdbc.queryForRowSet("SELECT MAX(idvenue) as last_id FROM venue;"); rs.next(); int currentVenueId = rs.getInt("last_id"); – Liam Blake Nov 27 '18 at 20:33
Look at the duplicate, that is the proper way to obtain the id of an inserted record (if that is what you want as suggested by this answer). If you really just want the highest id, then the answer by lucumt is sufficient. – Mark Rotteveel Nov 28 '18 at 08:07

mySQL - Get last added ID

2 Answers2