[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

retrieve the  autoincrement id   of database

Posted on 2011-05-10
35
Medium Priority
?
647 Views
Last Modified: 2012-05-11
How do I retrieve the  autoincrement id   right after  inserting record into MySQL table ?

I'm using JDBC PreparedStatement.

0
Comment
Question by:cofactor
  • 13
  • 11
  • 7
  • +1
35 Comments
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 300 total points
ID: 35726721
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 300 total points
ID: 35726727
0
 
LVL 92

Assisted Solution

by:objects
objects earned 500 total points
ID: 35726732
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 200 total points
ID: 35726841

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
 

Author Comment

by:cofactor
ID: 35727401
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
 
LVL 47

Expert Comment

by:for_yan
ID: 35727429
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
 
LVL 92

Expert Comment

by:objects
ID: 35727452
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
 

Author Comment

by:cofactor
ID: 35727458
>>>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
 
LVL 92

Expert Comment

by:objects
ID: 35727540
int id = insertCircularInfo(ps,con,cvo);
insertCircularSectionValue(ps,con,cvo,id);
insertCircularProgramValue(ps,con ,cvo,id);
insertProjectValue(ps,con ,cvo,id);
0
 

Author Comment

by:cofactor
ID: 35727548
>>>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
 
LVL 47

Expert Comment

by:for_yan
ID: 35727554
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 35727555
cofactor - do you need any more help?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35727576
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
 
LVL 92

Expert Comment

by:objects
ID: 35727578
> the String does not end here.  ...wrong code.

thats just a typo, thanks for pointing it out
0
 

Author Comment

by:cofactor
ID: 35727640
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
 

Author Comment

by:cofactor
ID: 35735717
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
 

Author Comment

by:cofactor
ID: 35735760
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 35735775
Please post stack trace of the exception
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35735798
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
 
LVL 92

Expert Comment

by:objects
ID: 35735810
> 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
 

Author Comment

by:cofactor
ID: 35736038
>>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 35736051
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
 

Author Comment

by:cofactor
ID: 35736057
>>>ps.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

This does not compile.
0
 
LVL 92

Expert Comment

by:objects
ID: 35736058
sorry typo should be:


conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35736095
>>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
 

Author Comment

by:cofactor
ID: 35736100
>>>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
 

Author Comment

by:cofactor
ID: 35736110
>>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 35736126
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
 
LVL 92

Accepted Solution

by:
objects earned 500 total points
ID: 35736146
Make sure you're calling prepareStatement() on your connection instance

and using now() is generally cleaner and would be more efficient
0
 

Author Comment

by:cofactor
ID: 35736294
>>>Statement.RETURN_GENERATED_KEYS

This has to be PreparedStatement.RETURN_GENERATED_KEYS . then only this will work.
 
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35736330
PreparedStatement.RETURN_GENERATED_KEYS and Statement.RETURN_GENERATED_KEYS are actually identical constants
0
 

Author Comment

by:cofactor
ID: 35736408
>>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 35736439
It will have been something else.
0
 

Author Closing Comment

by:cofactor
ID: 35736490
debug  troubleshooting
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35736517
So - you WEREN'T calling prepareStatement on your Connection instance?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question