我正在尝试获取插入行的id。目前,我没有得到id,也没有得到异常java.sql.SQLException: Column 'route_id' not found.,但是当我像在注释代码中一样查询它时,我得到了它,但是它是routes表中的所有行。我怎么才能修好它?
我很感谢你的帮助。
代码:
if (routesTables.next()) {
PreparedStatement prepRoutesInsert = con.prepareStatement(
"INSERT INTO routes(direction, route)"
+ "VALUES( ?, ?)",
Statement.RETURN_GENERATED_KEYS);
prepRoutesInsert.setString(1, direction);
prepRoutesInsert.setInt(2, route);
prepRoutesInsert.executeUpdate();
// PreparedStatement prepRoutesInsert2 =
// con.prepareStatement(
// "SELECT route_id from routes");
// ResultSet rs = prepRoutesInsert2.executeQuery();
// while(rs.next()){
// int route_id2 = rs.getInt("route_id");
// System.out.println(route_id2);
// }
try (ResultSet generatedKeys = prepRoutesInsert
.getGeneratedKeys()) {
if (generatedKeys.next()) {
int id = generatedKeys.getInt("route_id");
System.out.println("The id is: " + id);
}
}
}路由表:
stt.execute("CREATE TABLE IF NOT EXISTS routes ("
+ "route_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,"
+ "direction VARCHAR(30) NOT NULL, "
+ "route INT(11) NOT NULL )");发布于 2015-06-18 17:42:56
这可能对你有帮助-
public static int insertInDB(String sqlQuery) {
Connection conn = getDBConnection();
ResultSet rs = null;
int id = 0;
try {
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate(sqlQuery);
Statement stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = stmt1.executeQuery("select last_insert_id()");
while(rs.next()){
id = rs.getInt(1);
}
} catch (Exception e) {
closeDBConnection();
}
return id;
}请记住,last_insert_id()返回在整个模式中的任何表中最后插入的id。请确保此方法是原子的,并且在此方法完全执行时没有其他人插入。
请阅读这个- last-insert-id
前面的讨论可能会有所帮助- ID() MySQL
https://stackoverflow.com/questions/30922099
复制相似问题