JDBC 3种获得mysql插入数据的自增字段值的方法
广告投放★自助友情CMS落伍广告联盟晒乐广告联盟脉动广告联盟品味广告联盟
广告位可自定样式联系QQ:38355018个文字广告月20元广告联系QQ:3835501广告位可自定样式
8个文字广告月20元黄金广告位每月20元广告位可自定样式联系QQ:3835501广告位可自定样式
左旋肉碱、全国包邮
买二送一、无效退款

文章浏览→编程相关Mysql→JDBC 3种获得mysql插入数据的自增字段值的方法

JDBC 3种获得mysql插入数据的自增字段值的方法
JDBC 3种获得mysql插入数据的自增字段值的方法

1. Retrieving AUTO_INCREMENTColumn Values using Statement.getGeneratedKeys()
2. Retrieving AUTO_INCREMENT Column Values using SELECTLAST_INSERT_ID()
3. Retrieving AUTO_INCREMENT Column Values in UpdatableResultSets

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public classRetrievAutoIncrementTest {

 public voidinit() throws Exception {
  Statement stmt = null;
  ResultSet rs = null;
  Connection conn = null;
  try {
   Class.forName("com.mysql.jdbc.Driver");
   conn =DriverManager
     .getConnection("jdbc:mysql://localhost/test?","root","admin");
   // Issue theDDL queries for the table for this example
   stmt =conn.createStatement();
   stmt.executeUpdate("DROPTABLE IF EXISTS autoIncTutorial");

  //创建数据库表autoIncTutorial。
   stmt.executeUpdate("CREATETABLE autoIncTutorial ("
     +"priKey INT NOT NULL AUTO_INCREMENT, "
     +"dataField VARCHAR(64), PRIMARY KEY (priKey))");
  } finally {
   if (rs !=null) {
    try{
     rs.close();
    }catch (Exception e) {
    }
   }
   if (stmt !=null) {
    try{
     stmt.close();
    }catch (Exception e) {
    }
   }
   if (conn !=null) {
    try{
     conn.close();
    }catch (Exception e) {
    }
   }
  }
 }

 public voidtest1() throws Exception {
  Statement stmt = null;
  ResultSet rs = null;
  Connection conn = null;
  try {
   Class.forName("com.mysql.jdbc.Driver");
   conn =DriverManager
     .getConnection("jdbc:mysql://localhost:3306/test","root","admin");
   // Create aStatement instance that we can use for
   // 'normal'result sets assuming you have a
   // Connection'conn' to a MySQL database already available
   stmt =conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
     java.sql.ResultSet.CONCUR_UPDATABLE);
   //Insert onerow that will generate an AUTO INCREMENT key in the 'priKey'field
     for(int i = 0; i < 10; i++) {
    stmt.executeUpdate("INSERTINTO 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
    intautoIncKeyFromApi = -1;
    rs= stmt.getGeneratedKeys();
    if(rs.next()) {
     
autoIncKeyFromApi=rs.getInt(1);
    }else {
     //throw an exception from here
    }
    rs.close();
    rs= null;
    System.out.println("Keyreturned from getGeneratedKeys():"
      +autoIncKeyFromApi);
   }
  } finally {
   if (rs !=null) {
    try{
     rs.close();
    }catch (Exception e) {
    }
   }
   if (stmt !=null) {
    try{
     stmt.close();
    }catch (Exception e) {
    }
   }
   if (conn !=null) {
    try{
     conn.close();
    }catch (Exception e) {
    }
   }
  }
 }

 public voidtest2() throws Exception {
  Statement stmt = null;
  ResultSet rs = null;
  Connection conn = null;
  try {

   //
   // Create aStatement instance that we can use for
   // 'normal'result sets.
   Class.forName("com.mysql.jdbc.Driver");
   conn =DriverManager
     .getConnection("jdbc:mysql://localhost/test","root","admin");

   stmt= conn.createStatement();
   // Insert onerow that will generate an AUTO INCREMENT
   // key in the'priKey' field
   for (int i =0; i < 10; i++) {
    stmt.executeUpdate("INSERTINTO autoIncTutorial (dataField) "
      +"values ('Can I Get the Auto Increment Field?')");
    //Use the MySQL LAST_INSERT_ID() function to do the same thing asgetGeneratedKeys()
    intautoIncKeyFromFunc = -1;
    rs= stmt.executeQuery("SELECT LAST_INSERT_ID()");

    if(rs.next()) {
     autoIncKeyFromFunc= rs.getInt(1);
    }else {
     //throw an exception from here
    }
    rs.close();
    System.out.println("Keyreturned from "
      +"'SELECT LAST_INSERT_ID()': " + autoIncKeyFromFunc);
   }
  } finally {
   if (rs !=null) {
    try{
     rs.close();
    }catch (Exception e) {
    }
   }
   if (stmt !=null) {
    try{
     stmt.close();
    }catch (Exception e) {
    }
   }
   if (conn !=null) {
    try{
     conn.close();
    }catch (Exception e) {
    }
   }
  }
 }

 public voidtest3() throws Exception {
  Statement stmt = null;
  ResultSet rs = null;
  Connection conn = null;
  try {
   // Create aStatement instance that we can use for
   // 'normal'result sets as well as an 'updatable'
   // one,assuming you have a Connection 'conn' to a MySQLdatabase already available

   Class.forName("com.mysql.jdbc.Driver");
   conn =DriverManager
     .getConnection("jdbc:mysql://localhost/test","root","admin");
   stmt =conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
     java.sql.ResultSet.CONCUR_UPDATABLE);
   for (int i =0; i < 10; i++) {
    //Example of retrieving an AUTO INCREMENT key from an updatableresult set
    rs= stmt.executeQuery("SELECT priKey, dataField "+ "FROMautoIncTutorial");

    rs.moveToInsertRow();
    rs.updateString("dataField","AUTO INCREMENT here?");
    rs.insertRow();
    //the driver adds rows at the end
    rs.last();
    //We should now be on the row we just inserted
    intautoIncKeyFromRS = rs.getInt("priKey");
    rs.close();
    rs= null;
    System.out.println("Keyreturned for inserted row: "+ autoIncKeyFromRS);
   }
  } finally {
   if (rs !=null) {
    try{
     rs.close();
    }catch (Exception e) {
    }
   }
   if (stmt !=null) {
    try{
     stmt.close();
    }catch (Exception e) {
    }
   }
   if (conn !=null) {
    try{
     conn.close();
    }catch (Exception e) {
    }
   }
  }
 }


 public static voidmain(String[] args) throws Exception {
  RetrievAutoIncrementTest test =new RetrievAutoIncrementTest();
  test.init();
  test.test1();  //测试第一种获取自增字段的值
  test.test2();  //测试第二种获取自增字段的值
  test.test3(); //测试第三种获取自增字段的值
 }

}

所属分类:编程相关Mysql    作者:新浪博客    时间:2010-11-5 19:49:00

文章导航