mysql 获取自增id的值的方法

发布时间：2020-12-11 23:56:17 所属栏目：MySql教程来源：网络整理
导读：原生jdbc方式：示例： Statement stmt = = span style="color: #0000ff;"try span style="color: #000000;" { /spanspan style="color: #008000;"gt;///spanspan style="color: #008000;"gt;///spanspan style="color: #008000;"gt; Create a Statement ins
原生jdbc方式：

示例：
Statement stmt = = <span style="color: #0000ff;">try<span style="color: #000000;"> {
</span><span style="color: #008000;"&gt;//</span>
<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; Create a Statement instance that we can use for
</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; 'normal' result sets assuming you have a
</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; Connection 'conn' to a MySQL database already
</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; available</span>
<span style="color: #000000;">

stmt =<span style="color: #000000;"> conn.createStatement();
</span><span style="color: #008000;"&gt;//</span>
<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; Issue the DDL queries for the table for this example
</span><span style="color: #008000;"&gt;//
<span style="color: #000000;">

stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial"<span style="color: #000000;">);

stmt.executeUpdate(

"CREATE TABLE autoIncTutorial ("

"priKey INT NOT NULL AUTO_INCREMENT,"
"dataField VARCHAR(64),PRIMARY KEY (priKey))"<span style="color: #000000;">);

</span><span style="color: #008000;"&gt;//</span>
<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; Insert one row that will generate an AUTO INCREMENT
</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; key in the 'priKey' field
</span><span style="color: #008000;"&gt;//
<span style="color: #000000;">

stmt.executeUpdate(

"INSERT INTO autoIncTutorial (dataField) "

"values ('Can I Get the Auto Increment Field?')"<span style="color: #000000;">,Statement.RETURN_GENERATED_KEYS);

</span><span style="color: #008000;"&gt;//</span>
<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; Example of using Statement.getGeneratedKeys()
</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; to retrieve the value of an auto-increment
</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; value
</span><span style="color: #008000;"&gt;//


<span style="color: #0000ff;">int autoIncKeyFromApi = -1<span style="color: #000000;">;
rs </span>=<span style="color: #000000;"&gt; stmt.getGeneratedKeys();

</span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (rs.next()) {
    autoIncKeyFromApi </span>= rs.getInt(1<span style="color: #000000;"&gt;);
} </span><span style="color: #0000ff;"&gt;else</span><span style="color: #000000;"&gt; {

    </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; throw an exception from here</span>
<span style="color: #000000;">    }
System.out.println(</span>"Key returned from getGeneratedKeys():"
    +<span style="color: #000000;"&gt; autoIncKeyFromApi);
} <span style="color: #0000ff;">finally<span style="color: #000000;"> {
</span><span style="color: #0000ff;"&gt;if</span> (rs != <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
        rs.close();
    } </span><span style="color: #0000ff;"&gt;catch</span><span style="color: #000000;"&gt; (SQLException ex) {
        </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; ignore</span>
<span style="color: #000000;">        }

}
</span><span style="color: #0000ff;"&gt;if</span> (stmt != <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
        stmt.close();
    } </span><span style="color: #0000ff;"&gt;catch</span><span style="color: #000000;"&gt; (SQLException ex) {
        </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; ignore</span>
<span style="color: #000000;">        }

}

}

也有使用

Statement stmt = = <span style="color: #0000ff;">try<span style="color: #000000;"> {
</span><span style="color: #008000;"&gt;//</span>
<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; Create a Statement instance that we can use for
</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; 'normal' result sets.</span>
<span style="color: #000000;">

stmt =<span style="color: #000000;"> conn.createStatement();
</span><span style="color: #008000;"&gt;//</span>
<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; Issue the DDL queries for the table for this example
</span><span style="color: #008000;"&gt;//
<span style="color: #000000;">

stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial"<span style="color: #000000;">);

stmt.executeUpdate(

"CREATE TABLE autoIncTutorial ("

"priKey INT NOT NULL AUTO_INCREMENT,PRIMARY KEY (priKey))"<span style="color: #000000;">);

</span><span style="color: #008000;"&gt;//</span>
<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; Insert one row that will generate an AUTO INCREMENT
</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; key in the 'priKey' field
</span><span style="color: #008000;"&gt;//
<span style="color: #000000;">

stmt.executeUpdate(

"INSERT INTO autoIncTutorial (dataField) "

"values ('Can I Get the Auto Increment Field?')"<span style="color: #000000;">);

</span><span style="color: #008000;"&gt;//</span>
<span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; Use the MySQL LAST_INSERT_ID()
</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; function to do the same thing as getGeneratedKeys()
</span><span style="color: #008000;"&gt;//


<span style="color: #0000ff;">int autoIncKeyFromFunc = -1<span style="color: #000000;">;

rs = stmt.executeQuery("SELECT LAST_INSERT_ID()"<span style="color: #000000;">);
</span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (rs.next()) {
    autoIncKeyFromFunc </span>= rs.getInt(1<span style="color: #000000;"&gt;);
} </span><span style="color: #0000ff;"&gt;else</span><span style="color: #000000;"&gt; {
    </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; throw an exception from here</span>
<span style="color: #000000;">    }
System.out.println(</span>"Key returned from " +
                   "'SELECT LAST_INSERT_ID()': " +<span style="color: #000000;"&gt;
                   autoIncKeyFromFunc);
} <span style="color: #0000ff;">finally<span style="color: #000000;"> {
</span><span style="color: #0000ff;"&gt;if</span> (rs != <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
        rs.close();
    } </span><span style="color: #0000ff;"&gt;catch</span><span style="color: #000000;"&gt; (SQLException ex) {
        </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; ignore</span>
<span style="color: #000000;">        }

}
</span><span style="color: #0000ff;"&gt;if</span> (stmt != <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
        stmt.close();
    } </span><span style="color: #0000ff;"&gt;catch</span><span style="color: #000000;"&gt; (SQLException ex) {
        </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; ignore</span>
<span style="color: #000000;">        }

}

}

mybatis封装后的配置如下：


调用
和以前一样结果后返回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
                        （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!