mysql 擷取自增id的值的方法

標籤：

原生jdbc方式：

Statement.getGeneratedKeys()

樣本：

Statement stmt = null;ResultSet rs = null;try {    //    // Create a Statement instance that we can use for    // ‘normal‘ result sets assuming you have a    // Connection ‘conn‘ to a MySQL database already    // available    stmt = conn.createStatement();    //    // Issue the DDL queries for the table for this example    //    stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");    stmt.executeUpdate(            "CREATE TABLE autoIncTutorial ("            + "priKey INT NOT NULL AUTO_INCREMENT, "            + "dataField VARCHAR(64), PRIMARY KEY (priKey))");    //    // Insert one row that will generate an AUTO INCREMENT    // key in the ‘priKey‘ field    //    stmt.executeUpdate(            "INSERT INTO autoIncTutorial (dataField) "            + "values (‘Can I Get the Auto Increment Field?‘)",            Statement.RETURN_GENERATED_KEYS);    //    // Example of using Statement.getGeneratedKeys()    // to retrieve the value of an auto-increment    // value    //    int autoIncKeyFromApi = -1;    rs = stmt.getGeneratedKeys();    if (rs.next()) {        autoIncKeyFromApi = rs.getInt(1);    } else {        // throw an exception from here    }    System.out.println("Key returned from getGeneratedKeys():"        + autoIncKeyFromApi);} finally {    if (rs != null) {        try {            rs.close();        } catch (SQLException ex) {            // ignore        }    }    if (stmt != null) {        try {            stmt.close();        } catch (SQLException ex) {            // ignore        }    }}

也有使用SELECT LAST_INSERT_ID() 注意：並發可能會出現問題。樣本：

Statement stmt = null;ResultSet rs = null;try {    //    // Create a Statement instance that we can use for    // ‘normal‘ result sets.    stmt = conn.createStatement();    //    // Issue the DDL queries for the table for this example    //    stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");    stmt.executeUpdate(            "CREATE TABLE autoIncTutorial ("            + "priKey INT NOT NULL AUTO_INCREMENT, "            + "dataField VARCHAR(64), PRIMARY KEY (priKey))");    //    // Insert one row that will generate an AUTO INCREMENT    // key in the ‘priKey‘ field    //    stmt.executeUpdate(            "INSERT INTO autoIncTutorial (dataField) "            + "values (‘Can I Get the Auto Increment Field?‘)");    //    // Use the MySQL LAST_INSERT_ID()    // function to do the same thing as getGeneratedKeys()    //    int autoIncKeyFromFunc = -1;    rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");    if (rs.next()) {        autoIncKeyFromFunc = rs.getInt(1);    } else {        // throw an exception from here    }    System.out.println("Key returned from " +                       "‘SELECT LAST_INSERT_ID()‘: " +                       autoIncKeyFromFunc);} finally {    if (rs != null) {        try {            rs.close();        } catch (SQLException ex) {            // ignore        }    }    if (stmt != null) {        try {            stmt.close();        } catch (SQLException ex) {            // ignore        }    }}

 

mybatis封裝後的配置如下：

<insert id="insert" parameterType="Post" useGeneratedKeys="true" keyProperty="id">

調用

postDao.add(post);

和以前一樣結果後返回1，使用post.getId()可以擷取到自增的id。

參考文獻：

【1】http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-last-insert-id.html

【2】http://stackoverflow.com/questions/12241260/get-auto-genearated-key-for-the-inserted-record-in-mybatis

mysql 擷取自增id的值的方法