我正在嘗試從一個表中獲取主要的自動遞增鍵，然后使用 MySQL 連接器和 JDBC 將其存盤在另一個表中。雖然它給了我這個錯誤：
statement.executeupdate() 不能發出產生結果集的陳述句。
我認為它與整數變數的存盤有關但不太確定。
public void insertIntoWorkoutLogs(String field_setNumber, String field_repNumber, String field_weightAmount) {
try{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/workout","root","");
Statement statement =connection.createStatement();
String insert ="INSERT INTO `workout`.`workoutlogs`" " (`SetNumber`, `RepNumber` , `WeightAmount`)"
"VALUES('" field_setNumber "','" field_repNumber "','" field_weightAmount "')";
statement.executeUpdate(insert);
int workoutID = insertQueryGetId("SELECT workoutID FROM workout");
String insert2 ="INSERT INTO `workout`.`workoutlogs`" " (`WorkoutID`)"
"VALUES('" workoutID "')";
statement.executeUpdate(insert2);
connection.close();
}catch(Exception e) {
System.out.println(e);
}
}
public int insertQueryGetId(String query) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/workout","root","");
Statement statement =connection.createStatement();
int workoutID=0;
int result=-1;
try {
workoutID = statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = statement.getGeneratedKeys();
if (rs.next()){
result=rs.getInt(1);
}
rs.close();
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
我試過為此使用陳述句，但我認為它可能必須準備好陳述句才能作業。期望將一個表（鍛煉）的自動遞增主鍵存盤到另一個表（鍛煉日志）的欄位中。
uj5u.com熱心網友回復：
這是因為您傳遞了錯誤的查詢。Statement.RETURN_GENERATED_KEYS適用于插入查詢，不適用于選擇查詢。當您在資料庫中插入一行時，會生成并回傳一個自動增量值，但您傳遞的是 Select 陳述句
正如 Syed Asad Manzoor 所說，它會為你作業，但你需要洗掉Statement.RETURN_GENERATED_KEYS并statement.executeQuery()回傳型別，ResultSet所以你只需要將結果存盤在ResultSet。
uj5u.com熱心網友回復：
public void insertIntoWorkoutLogs(String field_setNumber, String field_repNumber, String field_weightAmount) {
try{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/workout","root","");
Statement statement =connection.createStatement();
String insert ="INSERT INTO `workout`.`workoutlogs`" " (`SetNumber`, `RepNumber` , `WeightAmount`)"
"VALUES('" field_setNumber "','" field_repNumber "','" field_weightAmount "')";
statement.executeUpdate(insert);
**int workoutID = insertQueryGetId("SELECT workoutID FROM workout");** // Line of Concern 1
String insert2 ="INSERT INTO `workout`.`workoutlogs`" " (`WorkoutID`)"
"VALUES('" workoutID "')";
statement.executeUpdate(insert2);
connection.close();
}catch(Exception e) {
System.out.println(e);
}
}
public int insertQueryGetId(String query) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/workout","root","");
Statement statement =connection.createStatement();
int workoutID=0;
int result=-1;
try {
// Line of Concern 2
**workoutID = statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);**
在行中（標記為 Line of Concern 1 .. int workoutID = insertQueryGetId("SELECT workoutID FROM workout");您將查詢傳遞為“SELECT ....”并在標記為 Line of Concern 2 workoutID = statement.executeUpdate (query , Statement.RETURN_GENERATED_KEYS); 你正在使用 executeUpdate.. 這就是拋出例外的原因。
將 statement.executeUpdate(query) 更改為 statement.executeQuery(query)..
uj5u.com熱心網友回復：
INSERT 陳述句需要有標志 RETURN_GENERATED_KEYS。然后獲取 ResultSet 將為每個插入記錄提供生成的鍵。
還可以使用 PreparedStatement 來轉義字串和防止 SQL 注入。
使用 try-with-resources 自動關閉多個物件，即使出現例外或提前回傳。
Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/workout", "root", "")) {
String insertSql = "INSERT INTO `workout`.`workoutlogs`"
" (`SetNumber`, `RepNumber` , `WeightAmount`)"
" VALUES(?, ?, ?)";
try (PreparedStatement statement = connection.prepareStatement(insertSql,
Statement.RETURN_GENERATED_KEYS)) {
statement.setString(field_setNumber);
statement.setString(field_repNumber);
statement.setBigDecimal(field_weightAmount);
statement.executeUpdate();
try (ResultSet rs = statement.getGeneratedKey()) {
if (rs.next()) {
int workoutID = rs.getInt(0);
//... second insert here
}
}
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/535889.html
標籤：爪哇数据库jdbc