在实际操作中,我们常常有一些各种古怪的需求,比如现在这个。数据库的table中有一个自增的id字段,插入数据库后,要求返回刚刚插入那条数据的id。 在单线程情况下,这当然是一个很简单的问题。首先获得数据库的最大id,给将要插入的语句id赋值为MAX+1,然后插入即可。 Oracle也很容易实现,给这个table建立一个sequence,每次都向这个sequence获取下一个id,效率也是价格公道童叟无欺。 但是,给多线程下的mysql返回刚刚插入的id,这似乎有点小为难。一个立刻可以想到的方法是,给插入操作加个锁,插入后查询MAX id,然后解开锁。但是这个显然是不怎么讲究了。

网上翻了下,有很多种方法解决这个问题,不过最后选择了getGeneratedKeys这个方法。 getGeneratedKeys是Statement下的一个方法,说明如下:

Retrieves any auto-generated keys created as a result of executing this Statement object. If this Statement object did not generate any keys, an empty ResultSet object is returned.

Note:If the columns which represent the auto-generated keys were not specified,the JDBC driver implementation will determine the columns which best represent the auto-generated keys. Return: a ResultSet object containing the auto-generated key(s) generated by the execution of this Statement object Exception:

  1. SQLException if a database access error occurs or this method is called on a closed Statement
  2. SQLFeatureNotSupportedException if the JDBC driver does not support this method since 1.4
简而言之,这东西可以返回它自动生成的值。因为每次使用插入的时候,我们都是从Connection中生成instance的,所以没有线程安全的问题也就不需要加锁。 听起来有点抽象不如来两行代码演示下: 假定要插入一个有auto_increment的的id和类型varchar(255)的name的table,只输入name,输出id或者错误提示。
public int insert(String name) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    int retNum = -1;
    try {
        conn = iDBPool.tryGetConnection();// get Connection
        if (conn == null) return -1;
        String sql = "insert into example_table(name) values(?)";
        ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); // 
        ps.setString(1,name);
        ps.executeUpdate();

        rs = ps.getGeneratedKeys(); 
        if(rs.next()){
            retNum = rs.getInt(1);
        }else {
            retNum = -1;
        }

    } catch (Exception e) {
        Log.getLog().info(e.getMessage());
        e.printStackTrace();
    } finally {
        iDBPool.release(conn);
        try {
            if (null != ps) ps.close();
            if (null != rs) rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return retNum;
}
如上,完毕。

Categories: Code

Yu

Ideals are like the stars: we never reach them, but like the mariners of the sea, we chart our course by them.

6 Comments

Leniy · November 4, 2013 at 15:37

Google Chrome 30.0.1599.101 Google Chrome 30.0.1599.101 Windows 7 Windows 7

java不懂

    yu · November 4, 2013 at 15:59

    Google Chrome 30.0.1599.114 Google Chrome 30.0.1599.114 GNU/Linux x64 GNU/Linux x64

    java带有自解释属性,哪怕你没学过不会写,基本看代码也知道这是干毛用的。。。

恋羽 · November 27, 2013 at 11:23

Google Chrome 30.0.1599.101 Google Chrome 30.0.1599.101 Windows 7 x64 Edition Windows 7 x64 Edition

记得在高并发的时候mysql取这个值还是会出问题

    yu · November 27, 2013 at 22:34

    Google Chrome 31.0.1650.57 Google Chrome 31.0.1650.57 GNU/Linux x64 GNU/Linux x64

    soga
    但“为什么线程安全”在文中说过了,既然这个内容已经存储在独立的对象,为什么还是会出现线程不安全问题呢?求详细内容。

mooc · June 17, 2016 at 09:15

Firefox 47.0 Firefox 47.0 Windows 10 x64 Edition Windows 10 x64 Edition

C++就好办多了,atomic 使 lock-free 很方便

    yu · June 17, 2016 at 18:25

    Google Chrome 51.0.2704.84 Google Chrome 51.0.2704.84 Mac OS X  10.11.5 Mac OS X 10.11.5

    @mooc 多机并行的时候光 atomic 并不能 assign 一个唯一的 id,而且大多数语言都可以做 CAS

Leave a Reply

Your email address will not be published. Required fields are marked *