用java如何获取对MS Server2005刚刚插入数据的ID值？

     使用数据库是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);
public static void executeInsertWithKeys(Connection con) {
   try {
      String SQL = "INSERT INTO TestTable (Col2, Col3) VALUES ('S', 50)";
      Statement stmt = con.createStatement();
      int count = stmt.executeUpdate(SQL, Statement.RETURN_GENERATED_KEYS);
      ResultSet rs = stmt.getGeneratedKeys();

      ResultSetMetaData rsmd = rs.getMetaData();
      int columnCount = rsmd.getColumnCount();
      if (rs.next()) {
         do {
            for (int i=1; i<=columnCount; i++) {
               String key = rs.getString(i);
               System.out.println("KEY " + i + " = " + key);
            }
         } while(rs.next());
      }
      else {
         System.out.println("NO KEYS WERE GENERATED.");
      }
      rs.close();
      stmt.close();
   }
   catch (Exception e) {
      e.printStackTrace();
   }
}