用java如何獲取對MS Server2005剛剛插入資料的ID值?

fog911811發表於2010-07-19

     使用資料庫是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

 

Using Auto Generated Keys

 

 

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);

相關文章