Database Reference
In-Depth Information
'''
)
seq
=
cursor
.
lastrowid
Java
The Connector/J JDBC driver
getGeneratedKeys()
method returns
AUTO_INCRE
MENT
values. It can be used with a
Statement
or
PreparedStatement
object if you
supply an additional
Statement.RETURN_GENERATED_KEYS
argument during the
statement-execution process to indicate that you want to retrieve the sequence val‐
ue.
For a
Statement
:
Statement
s
=
conn
.
createStatement
();
s
.
executeUpdate
(
"INSERT INTO insect (name,date,origin)"
+
" VALUES('moth','2014-09-14','windowsill')"
,
Statement
.
RETURN_GENERATED_KEYS
);
For a
PreparedStatement
:
PreparedStatement
s
=
conn
.
prepareStatement
(
"INSERT INTO insect (name,date,origin)"
+
" VALUES('moth','2014-09-14','windowsill')"
,
Statement
.
RETURN_GENERATED_KEYS
);
s
.
executeUpdate
();
Then generate a new result set from
getGeneratedKeys()
to access the sequence
value:
long
seq
;
ResultSet
rs
=
s
.
getGeneratedKeys
();
if
(
rs
.
next
())
{
seq
=
rs
.
getLong
(
1
);
}
else
{
throw
new
SQLException
(
"getGeneratedKeys() produced no value"
);
}
rs
.
close
();
s
.
close
();
Server-side and client-side sequence value retrieval compared
As mentioned earlier, the server maintains the value of
LAST_INSERT_ID()
on a session-
specific basis. By contrast, the API-specific methods for accessing
AUTO_INCREMENT
values directly are implemented on the client side. Server-side and client-side sequence
value retrieval methods have some similarities, but also some differences.
All methods, both server-side and client-side, require that you access an
AUTO_INCRE
MENT
value within the same MySQL session that generated it. If you generate an
AU
TO_INCREMENT
value, then disconnect from the server and reconnect before attempting