How to resolve the id from MySQL DB into a variable

Question

Is is possible to get the id from a MySQL table and "save" it into a variable on JDBC?

In my program, I have a JTextfield in which the user gives a ticket which will be stored on a MySQL DB. Everytime he gives a ticket, I want to get the id from my db where the ticket was inserted and not print in on teminal, but resolve it into a variable and use it on other classes also. I need it for other classes.

Is is possible?

score 1 · Accepted Answer · answered Aug 12 '14 at 08:09

1

Something like this ?

    statement.executeUpdate();
    ResultSet generatedKeys = statement.getGeneratedKeys();
    long generatedId= 0L;
    if (generatedKeys.next()) {
        generatedId = generatedKeys.getLong(1);// here is your generated Id , 
    }

answered Aug 12 '14 at 08:09

Suresh Atta

120,458
37
198
307

So this will return the value of the id only for the ticket I inserted? – vagg77 Aug 12 '14 at 08:18
Yes. It is. Can you please check ? – Suresh Atta Aug 12 '14 at 08:20
Im checking it now. BTW, why did you declare the id as long and not as int? – vagg77 Aug 12 '14 at 08:21
1

@vagg77 To overcome Integer overflow . – Suresh Atta Aug 12 '14 at 08:22
Could you please explain that?? I didn't get it – vagg77 Aug 12 '14 at 08:29
@vagg77 That means, In a large database Id's may exceed integer value :) http://stackoverflow.com/questions/3001836/how-does-java-handle-integer-underflows-and-overflows-and-how-would-you-check-fo – Suresh Atta Aug 12 '14 at 08:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/59161/discussion-between-vagg77-and-ss-). – vagg77 Aug 12 '14 at 08:35

Szymon Krawczyk · Answer 2 · 2014-08-12T08:26:31.087

Something like that?

PreparedStatement st;
        st = con.prepareStatement("SQL STATAMENT", "HERE COLUMN NAME FROM TABLE") VALUES(?)",Statement.RETURN_GENERATED_KEYS);
        st.setString(1,"Binded String Veriable");
        st.executeUpdate();
        ResultSet rs = st.executeQuery("select last_insert_id() as id from "HERE TABLE NAME"");
        if(rs.next()){
          int lastId = Integer.parseInt(rs.getString("id"));

Use PrpearedStatment to prevent SQL Injection for example:
st = con.prepareStatement("INSERT INTO component(user) VALUES(?)", Statement.RETURN_GENERATED_KEYS ) st.setString(1,username)

Here you have how last_insert_id() works: JDBC LAST INSER ID AND OTHER

you can always change it to mine :D Anyway hopefully it will help you :) — Szymon Krawczyk, Aug 12 '14 at 08:29

How to resolve the id from MySQL DB into a variable

2 Answers2