How do I insert last_insert_id using preparedStatement?

Question

I am facing difficulty in inserting " last_insert_id" in my prepared statement.I got how to select the last_insert_id in prepared statement like below:

PreparedStatement getLastInsertId = con.prepareStatement("SELECT LAST_INSERT_ID()");

When I use the same procedure for inserting last_insert_id in my preparedstatement like this:

1. PreparedStatement pst = con.prepareStatement("insert into introducer_table values(?,?,?,?)"); 2. 3. //introducer details into database 4. pst.setString(1,LAST_INSERT_ID()); 5. pst.setString(2, nameofintroducer); 6. pst.setString(3, accountno); 7. pst.setString(4, signofintroducer);

Im getting 'null' value in the first column.can any one help me to come out from this problem

actually I had one table newuser it is containing two fields sid,name — balasrilakshmi
– balasrilakshmi, Commented Feb 1, 2014 at 8:03
when I insert sid,name in the newuser table,that sid value must be inserted in the sid column of my introducer_table.And this sid can be obtained by using last_insert_id()@nikas tyagi. — balasrilakshmi
– balasrilakshmi, Commented Feb 1, 2014 at 8:04
@balasrilakshmi Besides that the original problem, You have a serious issue that your sid should not be a nullable column since you are telling that it's allowing null. Or I read something wrong ? — Suresh Atta
– Suresh Atta, Commented Feb 1, 2014 at 8:07
You mean to say first sid and name column value will be inserted in newuser table and it is havind primary key sid which is foreign key of introducer_table.sid which is primary key is return by using last_insert_id correct — Kick
– Kick, Commented Feb 1, 2014 at 8:07

Suresh Atta · Accepted Answer · 2014-02-01 12:22:16Z

If your doing both the save actions at a time use getGeneratedKeys(), It's pretty much java.

I'm not a SQL guru, but here I found a way to get the generated id using getGeneratedKeys()

 long generatedId= 0L; statement = con .getConnection() .prepareStatement( "insert into new_user set name= ? , contact= ? , ....", statement.RETURN_GENERATED_KEYS); statement.setString(1, "examplename"); statement.setString(2, "examplecontact"); ------ statement.executeUpdate(); ResultSet generatedKeys = statement.getGeneratedKeys(); if (generatedKeys.next()) { generatedId = generatedKeys.getLong(1);// here is your generated Id , use it to insert in your introducer_table } PreparedStatement pst = con.prepareStatement("insert into introducer_table values(?,?,?,?)"); //introducer details into database pst.setString(1, generatedId); pst.setString(2, nameofintroducer); pst.setString(3, accountno); pst.setString(4, signofintroducer);

@suresh but according to your query your inserting the data in the newuser table?its generating some id and that id must used in the introducer_table?is it right?
Yes exactly.. You can use it further in insert query of introducer_table. That is what you want right ?
@suresh thanks,but the problem is the newuser related java code is in one class(named:newuser.java),and introducer_table related jdbc code is in one class(named:introducer.java).then how can I do this?pls explain me...

Collectives™ on Stack Overflow

How do I insert last_insert_id using preparedStatement?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related