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();
}
}
}
SQL即插即取自增字段ID
最新推荐文章于 2021-12-06 18:31:10 发布