retrieve the autoincrement id of database

How do I retrieve the  autoincrement id   right after  inserting record into MySQL table ?

I'm using JDBC PreparedStatement.

cofactorAsked:
Who is Participating?
 
objectsConnect With a Mentor Commented:
Make sure you're calling prepareStatement() on your connection instance

and using now() is generally cleaner and would be more efficient
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
for_yanConnect With a Mentor Commented:

PreapredStatement with special parameter will work:

PreparedStatement stmt = conn.prepareStatement(sql,
                                 Statement.RETURN_GENERATED_KEYS);
// ...

ResultSet res = stmt.getGeneratedKeys();
while (res.next())
    System.out.println("Generated key: " + res.getInt(1));


http://stackoverflow.com/questions/76254/access-to-auto-increment-identity-field-after-sql-insert-in-java
0
 
cofactorAuthor Commented:
I have multiple table insert. Here is more detailed information

con.setAutoCommit(false);

insertCircularInfo(ps,con,cvo);<---- I want a return of autogenerated "id" from this method so that I can pass to next methods as show below

insertCircularSectionValue(ps,con,cvo,id);
                  
insertCircularProgramValue(ps,con ,cvo,id);

insertProjectValue(ps,con ,cvo,id);
                  
con.commit();
0
 
for_yanCommented:
Seems to be doable using this form of PreapredStatement as recommended:

PreparedStatement stmt = conn.prepareStatement(sql,
                                 Statement.RETURN_GENERATED_KEYS);
// ...

ResultSet res = stmt.getGeneratedKeys();
while (res.next())
id = res.getInt(1));

insertCircularSectionValue(ps,con,cvo,id);
insertCircularProgramValue(ps,con ,cvo,id);

insertProjectValue(ps,con ,cvo,id);

Does not say you have to commit before retriving generated key





   
0
 
objectsCommented:
thts fine , just use the code I posted above and change your method to return the id you get from the result set

int id = insertCircularInfo(ps,con,cvo);
0
 
cofactorAuthor Commented:
>>>Does not say you have to commit before retriving generated key

there is commit only at one place as shown in the above. I have to insert data in multiple tables. Each of the above methods has insert query .

 
0
 
objectsCommented:
int id = insertCircularInfo(ps,con,cvo);
insertCircularSectionValue(ps,con,cvo,id);
insertCircularProgramValue(ps,con ,cvo,id);
insertProjectValue(ps,con ,cvo,id);
0
 
cofactorAuthor Commented:
>>>just use the code I posted above and change your method to return the id you get from the result set

You have this in your code

statement.executeUpdate("sql, Statement.RETURN_GENERATED_KEYS);

the String does not end here.  ...wrong code.
0
 
for_yanCommented:
That's all fine it genrates id ofr the first insert and you use it in all subsequent.
What is there that causes your doubts?
Seems to be quite logical process
0
 
CEHJCommented:
cofactor - do you need any more help?
0
 
for_yanCommented:
It should be likte that:
PreparedStatement stmt = conn.prepareStatement(sql,
                                 Statement.RETURN_GENERATED_KEYS);

sql is just string containing your statement

String sql = "insert ...";

You certainly don't need quote in front of it, this is just name of String varible
0
 
objectsCommented:
> the String does not end here.  ...wrong code.

thats just a typo, thanks for pointing it out
0
 
cofactorAuthor Commented:
Thanks all . implemented the code . Let me test it out . I'll put comments here  right after testing soon. I hope this would work. I have put some logger to print the id's  generated and returned.

0
 
cofactorAuthor Commented:
I tested it . But this throws error.

Here is the code:

String sql="INSERT INTO TBL_CIRCULAR (FINANCIAL_YEAR,TITLE_SINHALA,TITLE_ENGLISH,TITLE_TAMIL,VERSION,TYPE,PROJ_ACTIVITY_SL_NO,PROGRAM_SL_NO,DEPARTMENT_SL_NO,STATUS,ASSIGNED_TO,CREATED_BY,CREATED_ON,MODIFIED_BY,MODIFIED_ON,LAST_SAVED_PG_NO) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?,?)";


// filled the data with ps.setString

ps.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); <----- Exception thrown here .


ResultSet rs = ps.getGeneratedKeys();
if (rs.next())
{
autoKey = rs.getInt(1);
}
LOGGER.debug("AutoKey================"+autoKey);


Error:
--------
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?,?)' at line 1
0
 
cofactorAuthor Commented:
Few more info:

(a)I have datasource configured in Jboss server.  database is MySQL .  

(b)I'm importing  import java.sql.*  package for Connection , PreparedStatement , ResultSet etc.
0
 
CEHJCommented:
Please post stack trace of the exception
0
 
CEHJCommented:
Your placeholders don't correspond to the number of columns. I would replace now() with another placeholder and set the ps with
ps.setTimestamp(13, new java.sql.Timestamp(System.currentTimeMillis()));

Open in new window

0
 
objectsCommented:
> ps.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); <----- Exception thrown here .


should be:


ps.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); <----- Exception thrown here .

// filled the data with ps.setString

ps.executeUpdate();

ResultSet rs = ps.getGeneratedKeys();
if (rs.next())
{
autoKey = rs.getInt(1);
}
LOGGER.debug("AutoKey================"+autoKey);
0
 
cofactorAuthor Commented:
>>>Please post stack trace of the exception

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?,?)' at line 1



>>>Your placeholders don't correspond to the number of columns.
I have 15 placeholders . and I have put data in them correctly.

>> I would replace now()

now() is a MySQL keyword which inserts  current data in the format  yyyy-mm-dd : hh:mm:ss format.

>>>with another placeholder and set the ps with

ps.setTimestamp(13, new java.sql.Timestamp(System.currentTimeMillis()));

Does this also produce data in the same format as above ?
0
 
CEHJCommented:
The following should be the sql
String sql = "INSERT INTO TBL_CIRCULAR (FINANCIAL_YEAR, TITLE_SINHALA, TITLE_ENGLISH, TITLE_TAMIL, VERSION, TYPE, PROJ_ACTIVITY_SL_NO, PROGRAM_SL_NO, DEPARTMENT_SL_NO, STATUS, ASSIGNED_TO, CREATED_BY, CREATED_ON, MODIFIED_BY, MODIFIED_ON, LAST_SAVED_PG_NO) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Open in new window

0
 
cofactorAuthor Commented:
>>>ps.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

This does not compile.
0
 
objectsCommented:
sorry typo should be:


conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
0
 
CEHJCommented:
>>Does this also produce data in the same format as above ?

Assuming your column is (correctly) of type datetime, it doesn't have a format - it's just a number. The format  is only perceived when you display the column value. Java will correctly insert the number required to show the date in the correct format.
0
 
cofactorAuthor Commented:
>>>conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Error:
#java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement().
0
 
cofactorAuthor Commented:
>>>Java will correctly insert the number required to show the date in the correct format.

I'm testing your approch .  But I want to put the data as similar to what now() used to insert .  This will help me not to change GUI . Can we modify the data insert format ?
0
 
CEHJCommented:
Using my suggestion won't make any difference at all, other than:

a. make the code more portable
b. make your placeholder code more logical and readable
0
 
cofactorAuthor Commented:
>>>Statement.RETURN_GENERATED_KEYS

This has to be PreparedStatement.RETURN_GENERATED_KEYS . then only this will work.
 
0
 
CEHJCommented:
PreparedStatement.RETURN_GENERATED_KEYS and Statement.RETURN_GENERATED_KEYS are actually identical constants
0
 
cofactorAuthor Commented:
>>>PreparedStatement.RETURN_GENERATED_KEYS and Statement.RETURN_GENERATED_KEYS are actually identical constants

I understand . But the change from Statement to  PreparedStatement did the miracle .  This just worked . I dont know  why it worked.
0
 
CEHJCommented:
It will have been something else.
0
 
cofactorAuthor Commented:
debug  troubleshooting
0
 
CEHJCommented:
So - you WEREN'T calling prepareStatement on your Connection instance?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.