How to get an automatically incremented column value when inserting a row into a table in mysql using java?

Question

How can I insert into table1 like: INSERT INTO table1 (description) VALUES ('Some test description here'); and have it return the automatically incremented ID so that I can then insert a row into table2? The description is not guaranteed to be unique. I am using a Java PreparedStatement to insert the value currently with execute()

I have the following table structure:

CREATE TABLE table1 ( table1ID INTEGER NOT NULL AUTO_INCREMENT, description VARCHAR(255) NOT NULL, CONSTRAINT PK_table1 PRIMARY KEY (table1ID) ); CREATE TABLE table2 ( table1ID INTEGER NOT NULL, personID INTEGER NOT NULL, CONSTRAINT PK_table2 PRIMARY KEY (table1ID, personID) ); ALTER TABLE table2 ADD CONSTRAINT FK_table1_table2 FOREIGN KEY (table1ID) REFERENCES table1 (table1ID); ALTER TABLE table2 ADD CONSTRAINT FK_table2_person FOREIGN KEY (personID) REFERENCES person (personID);

possible duplicate of How to retreive row in DB Table just added now in Java — OMG Ponies
– OMG Ponies, Commented Mar 15, 2011 at 23:37

increddibelly · Accepted Answer · 2011-03-16 13:00:38Z

3

run this query right after the insert completes succesfully: SELECT LAST_INSERT_ID()

answered Mar 16, 2011 at 13:00

increddibelly

1,2691 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RAS · Accepted Answer · 2011-03-16 13:30:52Z

Try this:

PreparedStatement st = con.prepareStatement( "Insert into table1 (name) values ('test desc')", Statement.RETURN_GENERATED_KEYS); st.executeUpdate(); ResultSet rs = st.getGeneratedKeys(); if(rs.next()) { int data = rs.getRow(); int id = rs.getInt(1); }

Observe values of data & id.

You don't need to fire insert & select queries separately.

Collectives™ on Stack Overflow

How to get an automatically incremented column value when inserting a row into a table in mysql using java?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related