JDBC three methods to obtain the auto-increment field value of MySQL inserted data

Source: Internet
Author: User

Three methods for obtaining the MySQL auto increment Field Value
It was found in the built-in MySQL docs. The following test program can run:

----------- The statement. getgeneratedkeys () can be obtained only after being inserted first ()
1. Retrieving auto_increment column values using statement. getgeneratedkeys ()
2. Retrieving auto_increment column values using select last_insert_id ()
3. Retrieving auto_increment column values in updatable resultsets

Import java. SQL. connection;
Import java. SQL. drivermanager;
Import java. SQL. resultset;
Import java. SQL. sqlexception;
Import java. SQL. statement;

Public class retrievautoincrementtest {

Public void Init () throws exception {
Statement stmt = NULL;
Resultset rs = NULL;
Connection conn = NULL;
Try {
Class. forname ("com. MySQL. JDBC. Driver ");
Conn = drivermanager. getconnection ("JDBC: mysql: // localhost/test? Autoreconnect = true & useunicode = true & characterencoding = UTF-8 & mysqlencoding = utf8 "," root ","******");
//
// Issue the DDL queries for the table for this example
//
Stmt = conn. createstatement ();
Stmt.exe cuteupdate ("Drop table if exists autoinctutorial ");
Stmt.exe cuteupdate (
"Create table autoinctutorial ("
+ "Prikey int not null auto_increment ,"
+ "Datafield varchar (64), primary key (prikey ))");
} Finally {
If (RS! = NULL) {try {Rs. Close ();} catch (exception e ){}}
If (stmt! = NULL) {try {stmt. Close () ;}catch (exception e ){}}
If (Conn! = NULL) {try {conn. Close () ;}catch (exception e ){}}
}
}
 
Public void test1 () throws exception {
Statement stmt = NULL;
Resultset rs = NULL;
Connection conn = NULL;
Try {
Class. forname ("com. MySQL. JDBC. Driver ");
Conn = drivermanager. getconnection ("JDBC: mysql: // localhost/test? Autoreconnect = true & useunicode = true & characterencoding = UTF-8 & mysqlencoding = utf8 "," root ","******");
//
// Create a statement instance that we can use
// 'Normal' result sets assuming you have
// Connection 'conn' to a MySQL database already
// Available
Stmt = conn. createstatement (Java. SQL. resultset. type_forward_only,
Java. SQL. resultset. concur_updatable );

//
// Insert one row that will generate an auto Increment
// Key in the 'prikey' Field
//
For (INT I = 0; I <10; I ++ ){
Stmt.exe cuteupdate (
"Insert into autoinctutorial (datafield )"
+ "Values ('Can I get the auto increment field? ')",
Statement. return_generated_keys );
//
// Example of using statement. getgeneratedkeys ()
// To retrieve the value of an auto-Increment
// Value
//
Int autoinckeyfromapi =-1;
Rs = stmt. getgeneratedkeys ();
If (Rs. Next ()){
Autoinckeyfromapi = Rs. getint (1 );
} Else {
// Throw an exception from here
}
Rs. Close ();
Rs = NULL;
System. Out. println ("Key returned from getgeneratedkeys ():"
+ Autoinckeyfromapi );
}
} Finally {
If (RS! = NULL) {try {Rs. Close ();} catch (exception e ){}}
If (stmt! = NULL) {try {stmt. Close () ;}catch (exception e ){}}
If (Conn! = NULL) {try {conn. Close () ;}catch (exception e ){}}
}
}
 
Public void Test2 () throws exception {
Statement stmt = NULL;
Resultset rs = NULL;
Connection conn = NULL;
Try {

//
// Create a statement instance that we can use
// 'Normal' result sets.
Class. forname ("com. MySQL. JDBC. Driver ");
Conn = drivermanager. getconnection ("JDBC: mysql: // localhost/test? Autoreconnect = true & useunicode = true & characterencoding = UTF-8 & mysqlencoding = utf8 "," root ","******");

Stmt = conn. createstatement ();
//
// Insert one row that will generate an auto Increment
// Key in the 'prikey' Field
//
For (INT I = 0; I <10; I ++ ){
Stmt.exe cuteupdate (
"Insert into autoinctutorial (datafield )"
+ "Values ('Can I get the auto increment field? ')");
 
//
// Use the MySQL last_insert_id ()
// Function to do the same thing as getgeneratedkeys ()
//
Int autoinckeyfromfunc =-1;
Rs = stmt.exe cutequery ("select last_insert_id ()");
 
If (Rs. Next ()){
Autoinckeyfromfunc = Rs. getint (1 );
} Else {
// Throw an exception from here
}
Rs. Close ();
System. Out. println ("Key returned from" + "'select last_insert_id ()':"
+ Autoinckeyfromfunc );
}
} Finally {
If (RS! = NULL) {try {Rs. Close ();} catch (exception e ){}}
If (stmt! = NULL) {try {stmt. Close () ;}catch (exception e ){}}
If (Conn! = NULL) {try {conn. Close () ;}catch (exception e ){}}
}
}
 
Public void test3 () throws exception {
Statement stmt = NULL;
Resultset rs = NULL;
Connection conn = NULL;
Try {
//
// Create a statement instance that we can use
// 'Normal' result sets as well as an 'updatable'
// One, assuming you have a connection 'conn'
// A MySQL database already available
//

Class. forname ("com. MySQL. JDBC. Driver ");
Conn = drivermanager. getconnection ("JDBC: mysql: // localhost/test? Autoreconnect = true & useunicode = true & characterencoding = UTF-8 & mysqlencoding = utf8 "," root ","******");
Stmt = conn. createstatement (Java. SQL. resultset. type_forward_only,
Java. SQL. resultset. concur_updatable );
For (INT I = 0; I <10; I ++ ){
//
// Example of retrieving an auto increment key
// From an updatable result set
//
Rs = stmt.exe cutequery ("select prikey, datafield"
+ "From autoinctutorial ");
 
Rs. movetoinsertrow ();
Rs. updatestring ("datafield", "auto increment here? ");
Rs. insertrow ();
 
//
// The driver adds rows at the end
//
Rs. Last ();
//
// We shoshould now be on the row we just inserted
//
Int autoinckeyfromrs = Rs. getint ("prikey ");
Rs. Close ();
Rs = NULL;
System. Out. println ("Key returned for inserted row :"
+ Autoinckeyfromrs );
}
} Finally {
If (RS! = NULL) {try {Rs. Close ();} catch (exception e ){}}
If (stmt! = NULL) {try {stmt. Close () ;}catch (exception e ){}}
If (Conn! = NULL) {try {conn. Close () ;}catch (exception e ){}}
}
}
 
/**
* @ Param ARGs
*/
Public static void main (string [] ARGs) throws exception {
Retrievautoincrementtest test = new retrievautoincrementtest ();
Test. INIT ();
Test. test1 ();
Test. Test2 ();
Test. test3 ();
}

}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.