Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL & Java - Get id of the last inserted value (JDBC) [duplicate]

Tags:

java

mysql

jdbc

Possible Duplicate:
How to get the insert ID in JDBC?

Hi, I'm using JDBC to connect on database through out Java.

Now, I do some insert query, and I need to get the id of last inserted value (so, after a stmt.executeUpdate).

I don't need something like SELECT id FROM table ORDER BY id DESC LIMIT 1, because I may have concurrency problems.

I Just need to retrieve the id associated to the last insertion (about my instance of the Statement).

I tried this, but seems it doesn't work on JDBC :

public Integer insertQueryGetId(String query) {     Integer numero=0;     Integer risultato=-1;     try {         Statement stmt = db.createStatement();         numero = stmt.executeUpdate(query);          ResultSet rs = stmt.getGeneratedKeys();         if (rs.next()){             risultato=rs.getInt(1);         }         rs.close();          stmt.close();     } catch (Exception e) {         e.printStackTrace();         errore = e.getMessage();         risultato=-1;     }   return risultato; } 

In fact, every time risultato = -1, and I get java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement().

How can I fix this problem? Thanks Stackoverflow People :)

like image 463
markzzz Avatar asked Nov 22 '10 14:11

markzzz


People also ask

What is MySQL used for?

MySQL is an open source relational database management system. For WordPress sites, that means it helps you store all your blog posts, users, plugin information, etc. It stores that information in separate “tables” and connects it with “keys”, which is why it's relational.

What is difference between SQL or MySQL?

SQL is a query programming language that manages RDBMS. MySQL is a relational database management system that uses SQL. SQL is primarily used to query and operate database systems. MySQL allows you to handle, store, modify and delete data and store data in an organized way.

Is MySQL better than Microsoft SQL?

Both SQL Server and MySQL are built as binary collections. However, SQL server is more secure than MySQL. It does not let any process to access and manipulate the database files at run time. Users need to perform specific functions or manipulate files by executing an instance.

Is MySQL still free?

MySQL is free and open-source software under the terms of the GNU General Public License, and is also available under a variety of proprietary licenses.


1 Answers

Wouldn't you just change:

numero = stmt.executeUpdate(query); 

to:

numero = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS); 

Take a look at the documentation for the JDBC Statement interface.

Update: Apparently there is a lot of confusion about this answer, but my guess is that the people that are confused are not reading it in the context of the question that was asked. If you take the code that the OP provided in his question and replace the single line (line 6) that I am suggesting, everything will work. The numero variable is completely irrelevant and its value is never read after it is set.

like image 65
Sean Bright Avatar answered Sep 20 '22 02:09

Sean Bright