How to Retrieve Automatically Generated Keys in JDBC?
You can retrieve automatically generated keys (also called auto-generated keys or auto increment) from a table using JDBC 3.0 methods getGeneratedKeys()
. The getGeneratedKeys()
provide a standard way to make auto-generated or identity column values available to an application that is updating a database table without a requiring a query and a second round-trip to the server. SQL Server allows only a single auto increment column per table.
The ResultSet
that is returned by getGeneratedKeys
method will have only one column, with the returned column name of GENERATED_KEYS.
If generated keys are requested on a table that has no auto increment column, the JDBC driver will return a null
result set.
When you insert rows by executeUpdate or
. Otherwise, the JDBC driver ignores the parameter that sets the flag.execute
an INSERT statement or an INSERT within SELECT
statement, you need to indicate that you will want to retrieve automatically generated key values. You do that by setting a flag in a Connection.prepareStatement, Statement.executeUpdate, or Statement.execute method call. The statement that is executed must be an INSERT statement or an INSERT
within SELECT statement
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCGetAutoIncKeys {
private static final String DBURL =
"jdbc:mysql://localhost:3306/mydb?user=usr&password=sql" +
"&useUnicode=true&characterEncoding=UTF-8";
private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
static {
try {
Class.forName(DBDRIVER).newInstance();
} catch (Exception e){
e.printStackTrace();
}
}
private static Connection getConnection()
{
Connection connection = null;
try {
connection = DriverManager.getConnection(DBURL);
}
catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public static void main(String[] args) {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = getConnection();
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate("DROP TABLE IF EXISTS autoincSample");
stmt.executeUpdate(
"CREATE TABLE autoincSample ("
+ "id INT NOT NULL AUTO_INCREMENT, "
+ "data VARCHAR(64), PRIMARY KEY (id))");
stmt.executeUpdate(
"INSERT INTO autoincSample (data) "
+ "values ('Record ----- 1')",
Statement.RETURN_GENERATED_KEYS);
rs = stmt.getGeneratedKeys();
while (rs.next()) {
System.out.println("Key returned from getGeneratedKeys():"
+ rs.getInt(1));
}
rs.close();
}
catch(Exception e) {
e.printStackTrace();
}
finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException ex) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ex) {
}
}
}
}
}
Most Recent java Faqs
- How to uncompress a file in the gzip format?
- How to make a gzip file in Java?
- How to use Java String.split method to split a string by dot?
- How to validate URL in Java?
- How to schedule a job in Java?
- How to return the content in the correct encoding from a servlet?
- What is the difference between JDK and JRE?
Most Viewed java Faqs
- How to read input from console (keyboard) in Java?
- How to Retrieve Multiple Result Sets from a Stored Procedure in JDBC?
- How to Use Updatable ResultSet in JDBC?
- What are class variables in Java?
- What are local variables in Java?
- How to Use JDBC Java to Create Table?
- Why final variable in Enhanced for Loop does not act final?