MSSQL／MySQL & Java – Get id of the last inserted value

這個滿實用的，在每一個程式語言都會用到，如果需要取得插入時傳回的流水號。

---

numero = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);

Take a look at the documentation for the JDBC Statement interface.

Update: Apparently there is a lot of confusion about this answer, but my guess is that the people that are confused are not reading it in the context of the question that was asked. If you take the code that the OP provided in his question and replace the single line (line 6) that I am suggesting, everything will work. The numero variable is completely irrelevant and its value is never read after it is set.

---

/**
 * Executes an INSERT/UPDATE statement on the database and returns a CachedRowSet containing any
 * generated keys.
 * 
 * @param expression
 *            The statement to be executed.
 * @return A CachedRowSet containing any generated keys.
 */
public long executeGetGeneratedKeys(String expression) {
        long ret = 0;
        
        Statement statement = null;
    try {
        Class.forName(this.db_param.jdbcClass);
        Connection conn = DriverManager.getConnection(this.db_param.url, this.db_param.user, this.db_param.password);
        
        CachedRowSet crs=null;
        crs=RowSetProvider.newFactory().createCachedRowSet();
        
        statement = conn.createStatement();
        statement.executeUpdate(expression, Statement.RETURN_GENERATED_KEYS);
        crs.populate(statement.getGeneratedKeys());
        if(crs!=null) {
                if(crs.next()) {
                    ret = crs.getLong(1);
                }
        }
        conn.close(); //需要時可以關閉它 
        conn = null;
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return ret;
}

---

executeUpdate

int executeUpdate(String sql,
                  int autoGeneratedKeys)
                  throws SQLException

Executes the given SQL statement and signals the driver with the given flag about whether the auto-generated keys produced by this Statementobject should be made available for retrieval. The driver will ignore the flag if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys (the list of such statements is vendor-specific).

Parameters:

sql – an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

autoGeneratedKeys – a flag indicating whether auto-generated keys should be made available for retrieval; one of the following constants: Statement.RETURN_GENERATED_KEYS Statement.NO_GENERATED_KEYS

Returns:

either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

Throws:

SQLException – if a database access error occurs, this method is called on a closed Statement, the given SQL statement returns a ResultSet object, or the given constant is not one of those allowed

SQLFeatureNotSupportedException – if the JDBC driver does not support this method with a constant of Statement.RETURN_GENERATED_KEYS

Since:

1.4