INSERT SQL and get ID (JEE)

Question

INSERT SQL and get ID (JEE)

Navigation

#1 by (3 votes)
#2 by (0 votes)

0

As the title says, this is in java EE , particularly java web. However, it is a simple% SQL query:

I have a table with several data, and a id autoincremental:

id, date, text.

I use a servlet to add an element to the database with the following query:

INSERT INTO tabla (fecha, texto) VALUES (valor1, valor2)

However, I want to get the ID that was generated. What is the efficient way to do it?

It occurs to me to generate another query that obtains the last aggregate element, however, it seems inefficient to generate another query. Also, if 2 elements are added from different accounts at similar times, the codes could cross ... Any ideas? Do you know if it can be obtained from the same query?

mysql sql java-ee

asked by Facundo Curti 14.09.2016 в 18:34

source

2 answers

links in ruby rails What does the math.floor () function JS

score 3 · Answer 1

What you want you can get it using the MySQL function LAST_INSERT_ID , this returns the last identifier inserted in the database.
The identifier can be obtained by adding the following query to the current one:

SELECT LAST_INSERT_ID();

Staying as follows:

INSERT INTO tabla (fecha, texto) VALUES (valor1, valor2); SELECT LAST_INSERT_ID();

In this way what you get is that both queries are executed simultaneously and as a result of its execution, you return the last dentifier inserted in the database, in this case the one that has been inserted using the INSERT .

You can get more information about this feature by referring to the following MySQL documentation page

score 0 · Answer 2

It occurs to me to generate another query that gets the last element added, however, it seems inefficient to generate another query.

I do not understand why inefficient, if all the time the systems are making queries. Also, a query like this takes really very little time.

SELECT id FROM Tabla ORDER BY id DESC LIMIT 1

Another option is to create a stored procedure that persists and returns the inserted records including the self-generated id.

Finally, and the easiest alternative is to use JPA:

em.persist(producto);
em.flush();
return producto.getId();

In JPA when you perform a flush, the persisted entity updates its properties, including the ID autogenerated, so you can use the getter only to obtain it.