使用数据库是MS SQL Server2005,创建一个表,其中有ID列为identity,在程序中需要获取刚刚插入数据的ID值作为另一个表的外键,在Store Procedure可以用output子句,但用java怎么获取呢?
有人说用select @@identity,当然好兴奋,写了个main函数测试下,果然OK,但放在JSP中发觉得是取不得的。不知道为什么,这貌似一样,总之至今也还不知道为什么?
后来有人很大胆和很负责任说select @@identity是不行的,给个MSDN文档,这当然权威好多了。
http://msdn.microsoft.com/en-us/library/ms378445%28SQL.90%29.aspx
The Microsoft SQL Server 2005 JDBC Driver supports the optional JDBC 3.0 APIs to retrieve automatically generated row identifiers. The main value of this feature is to provide a way to make IDENTITY values available to an application that is updating a database table without a requiring a query and a second round-trip to the server.
Because SQL Server does not support pseudo columns for identifiers, updates that have to use the auto-generated key feature must operate against a table that contains an IDENTITY column. SQL Server allows only a single IDENTITY column per table. The result set that is returned by getGeneratedKeys method of the SQLServerStatement class will have only one column, with the returned column name of GENERATED_KEYS. If generated keys are requested on a table that has no IDENTITY column, the JDBC driver will return a null result set.
As an example, create the following table in the SQL Server 2005 AdventureWorks sample database:
CREATE TABLE TestTable (Col1 int IDENTITY, Col2 varchar(50), Col3 int);