JAVA 插入到具有多行的多个表中，具体取决于早期的 ID

Question

我正在尝试创建一些东西，我可以将锻炼插入数据库中，其中包含该锻炼中完成的锻炼。问题是我想同时创建它们并存储我使用链接表的练习。

此表名为 Workout_Excercise，因此我可以存储每个锻炼的多个锻炼。

问题是希望能够同时创建它们，但workout_excercise需要尚未创建的workout_id。

我当前的代码插入了工作正常的锻炼，现在我很好奇我如何能够插入练习。

用于插入锻炼的当前代码：

前面

$(".insertWorkout").on("click", function(){
if(localStorage.getItem('token') != null ) {
var token = localStorage.getItem('token');
}
else {
var token = null;
}
$.ajax({
url: '/project/restservices/workouts/insert',
type: 'POST',
beforeSend:  function(xhr){
xhr.setRequestHeader('Authorization', 'Bearer ' + token);
},
data: {
titel: $(".workoutTitel").val() ,
beschrijving: $(".workoutBeschrijving").val(),
categorie_id: $(".categorieSelect").val(),
persoon_id: 1
},
success: function(response) {
if(response){
$(".workoutTitel").val("");
$(".workoutBeschrijving").val("");
$(".messageSpanSucces").html("Oefening invoeren gelukt");
}
else {
$(".messageSpanFail").html("Oefening invoeren mislukt");
}
}
});
});

锻炼资源

@Path("insert")
@POST
@Produces("application/json")
@RolesAllowed("user")
public Response saveWorkout(@FormParam("titel") String titel, @FormParam("beschrijving") String beschrijving,
@FormParam("categorie_id") int categorie_id, @FormParam("persoon_id") int persoon_id) throws SQLException {
WorkoutService service = ServiceProvider.getWorkoutService();
boolean country = service.saveWorkout(titel, beschrijving, categorie_id, persoon_id);
return Response.ok(country).build();
}

锻炼服务

public boolean saveWorkout(String titel, String beschrijving, int categorie_id, int persoon_id) throws SQLException {
return WorkoutPostgresDao.saveWorkout(titel, beschrijving, categorie_id, persoon_id);
}

锻炼道

public boolean saveWorkout(String titel, String beschrijving, int categorie_id, int persoon_id) throws SQLException {
boolean result = false;
try (Connection con = super.getConnection()) {
String query = "INSERT INTO "Workout" VALUES(?,?,?,?)";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1, titel);
pstmt.setString(2, beschrijving);
pstmt.setInt(3, persoon_id);
pstmt.setInt(4, categorie_id);
pstmt.executeUpdate();
result = true;
} catch (SQLException sqle) {
sqle.printStackTrace();
}
return result;
}

Answer 1

您可以使用以下方法来获取生成的密钥：

try (Connection con = super.getConnection()) {
String query = "INSERT INTO "Workout" VALUES(?,?,?,?)";
String query2 = "INSERT INTO Workout_exercise ... ";
PreparedStatement pstmt = con.prepareStatement(query,PreparedStatement.RETURN_GENERATED_KEYS);
pstmt.setString(1, titel);
pstmt.setString(2, beschrijving);
pstmt.setInt(3, persoon_id);
pstmt.setInt(4, categorie_id);
pstmt.executeUpdate();
result = true;
try (ResultSet rs = pstmt.getGeneratedKeys()) {
if (rs != null && rs.next()) {
workout_pkey = rs.getInt(1);
PreparedStatement pstmt2=con.prepareStatement(query2);
...
for (Workout_exercise ex: exs) {
pstmt2.setInt(1, workout_pkey);
...
pstmt2.addBatch();
}
pstmt2.executeBatch();
}
}    
} catch (SQLException sqle) {
sqle.printStackTrace();
}