JDBC获取新增记录的自增主键

http://lavasoft.blog.51cto.com/62575/238643

常常在使用Hibernate、iBatis时候，当插入一条数据的时候，实体Bean的原空主键就赋上新值了。

在JDBC中，也可以通过变相的手法来做到这点。下面是个测试的小例子。

 

一、环境

 

MySQL5.1

mysql-connector-java-5.1.10

jdk1.5

 

 

CREATE  TABLE book( 
    code  bigint(20) unsigned  NOT  NULL AUTO_INCREMENT, 
    kind  varchar(45)  NOT  NULL, 
     name  varchar(45)  NOT  NULL, 
     PRIMARY  KEY(code, kind) 
) ENGINE = InnoDB AUTO_INCREMENT = 1  DEFAULT CHARSET = latin1

 

二、获取自增主键

 

注意：使用 String  sql, int autoGeneratedKeys) 来定义PreparedStatement对象。并且指定： Statement.RETURN_GENERATED_KEYS 。

 

import lavasoft.common.DBToolkit; 

import java.sql.*; 

/** 
* JDBC获取新增记录的主键 
* 
* @author leizhimin 2009-12-4 13:20:15 
*/ 
public class InsertKeyTest { 

         public static void main(String[] args) { 
                insertWithStaticSQL(); 
                insertWithParperedSQL(); 
        } 

         /** 
         * 预定义SQL模式，获取新增记录的主键 
         */ 
         public static void insertWithParperedSQL() { 
                Connection conn =  null; 
                try { 
                        conn = DBToolkit.getConnection(); 
                        String sql = " insert  into testdb. user ( name, pswd)  values(?,?)"; 
                        PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); 
                        pstmt.setString(1, "laozhang"); 
                        pstmt.setString(2, "111111"); 
                        pstmt.executeUpdate(); 
                        //检索由于执行此 Statement 对象而创建的所有自动生成的键 
                        ResultSet rs = pstmt.getGeneratedKeys(); 
                         if (rs.next()) { 
                                //知其仅有一列，故获取第一列 
                                Long id = rs.getLong(1); 
                                System.out.println(" -----预定义SQL模式-----id = " + id); 
                        } 
                } catch (SQLException e) { 
                        e.printStackTrace(); 
                } finally { 
                        DBToolkit.closeConnection(conn); 
                } 
        } 

         /** 
         * 静态SQL模式，获取新增记录的主键 
         */ 
         public static void insertWithStaticSQL() { 
                Connection conn =  null; 
                try { 
                        conn = DBToolkit.getConnection(); 
                        String sql = " insert  into testdb. user ( name, pswd)  values( 'ttttt', '121')"; 
                        PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); 
                        pstmt.executeUpdate(); 
                        //检索由于执行此 Statement 对象而创建的所有自动生成的键 
                        ResultSet rs = pstmt.getGeneratedKeys(); 
                         if (rs.next()) { 
                                //知其仅有一列，故获取第一列 
                                Long id = rs.getLong(1); 
                                System.out.println(" -----静态SQL模式-----id = " + id); 
                        } 
                } catch (SQLException e) { 
                        e.printStackTrace(); 
                } finally { 
                        DBToolkit.closeConnection(conn); 
                } 
        } 
}

 

执行结果：

-----静态SQL模式 -----id = 8 
-----预定义SQL模式-----id = 9 

Process finished  with  exit code 0