前提是该表中的主键是自增的。last_isnert_id是获取插入sql语句后最新的ID。last_isnert_id是mysql提供的一个查询,当其植入在spring程序中会发生以下几种情况:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
public class TestLastInsertId {
@Autowired(required=true)
private JdbcTemplate jdbcTemplate;
@Test
public void test() {
final String sql = "insert into series(class_id,brand_id,series_name) values("
+ 2 + "," + 2 + ",'" + "sfsfds')";
System.out.println(sql);
// 这种方式会出现主键不确定性
int ret = jdbcTemplate.update(sql);
System.out.println("系列返回值===" + ret);
String last = "select last_insert_id()";
int lastId = jdbcTemplate.queryForInt(last);
System.out.println("最新系列ID====" + lastId);
// 以上这种方式不介意使用
KeyHolder keyHolder = new GeneratedKeyHolder();
// 这种方式是在上一中方式之上优化其不确定性,并且可以实现多表增加,并且获取其主键ID
int i = jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con)
throws SQLException {
return con.prepareStatement(sql,
PreparedStatement.RETURN_GENERATED_KEYS);
// or return con.prepareStatement(sql, new
// String[]{"ID"});//ID是自动增长的自动
}
}, keyHolder);
System.out.println(i);
System.out.println(keyHolder.getKeyList());
final String sql1 = "insert into meta_type(series_id,type_name) values("
+ 2 + ",'" + "sfsfds')";
System.out.println(sql1);
int j = jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con)
throws SQLException {
return con.prepareStatement(sql1,
PreparedStatement.RETURN_GENERATED_KEYS);
// or return con.prepareStatement(sql, new
// String[]{"ID"});//ID是自动增长的自动
}
}, keyHolder);
System.out.println("----" + j);
System.out.println(keyHolder.getKeyList());
}
}