SQL即插即取自增字段ID_sql 获取自增字段id-优快云博客

本文介绍如何使用JDBC通过Statement和PreparedStatement接口在MySQL中插入数据，并立即获取新记录的自增主键ID。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2. 通过jdbc（必须是jdbc3.0及以上的版本）可以获得插入多行时返回的主键列表。
使用方法：
        Statement st = conn.createStatement();
        st.execute("insert into test.one(name) values('hel1'),('hel2')", Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = st.getGeneratedKeys();
        while(rs.next()){
            int id = rs.getInt(1);
            System.out.println(id);
        }
mysql内的实现其实是根据第一行产生的主键P，和插入的记录条数，获得每一条插入记录的主键（第n条记录为P-1+n）。

例子方法：

/**
    * Statement接口实现
    * 获取SQL插入一条记录，马上取得该记录的自增ID值
    */
    public void testAutoIncreatId(){

        Connection con = null;
            try {
              Class.forName("com.mysql.jdbc.Driver").newInstance();
              String url = "jdbc:mysql://59.42.255.161/mmclick?useUnicode=true&characterEncoding=utf8";
              String user = "remote";
              String pass = "161remote";
              con = DriverManager.getConnection(url, user, pass);

              Statement st = con.createStatement();
                st.execute("insert into testAuto(name) values('hel1')", Statement.RETURN_GENERATED_KEYS);
                ResultSet rs = st.getGeneratedKeys();
                while(rs.next()){
                    int id = rs.getInt(1);
                    System.out.println("*****"+id);//获取当前自增值 ID
                }
                rs.close();
                st.close();


            }
            catch (Exception e)
            {
                e.printStackTrace();
            }finally{

                try {
                    con.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
    }


        /**
    * preparedStatement接口实现
    * 获取SQL插入一条记录，马上取得该记录的自增ID值
    */
    public void testAutoIncreatId_pre(){

        Connection con = null;
            try {
              Class.forName("com.mysql.jdbc.Driver").newInstance();
              String url = "jdbc:mysql://59.42.255.161/mmclick?useUnicode=true&characterEncoding=utf8";
              String user = "remote";
              String pass = "161remote";
              con = DriverManager.getConnection(url, user, pass);

              String sql="insert into testAuto(name) values(?)";
              PreparedStatement pstm = con.prepareStatement(sql);
              pstm.setString(1, "12");
             // pstm.executeUpdate(sql);
              pstm.execute(sql, PreparedStatement.RETURN_GENERATED_KEYS);
              ResultSet rs = pstm.getGeneratedKeys();
               // st.execute(", Statement.RETURN_GENERATED_KEYS);
              // ResultSet rs = st.getGeneratedKeys();
                while(rs.next()){
                    int id = rs.getInt(1);
                    System.out.println("*****"+id);
                  // System.out.println("*****"+rs.getString("name"));
                }
                rs.close();
                pstm.close();


            }
            catch (Exception e)
            {
                e.printStackTrace();
            }finally{

                try {
                    con.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }


    }