java - JDBC:查询结果并将批处理添加到另一个表中

Question

我有一些 mySQL 表:“books”、“task”和“task_items”。我正在构建一个 REST API (Java)，允许用户使用一些书籍创建任务(基本上是编辑字段)。当用户创建新任务时，我在任务表中创建一个新行，获取最后一个 ID，选择一些书籍并将其全部添加到 task_items 表中。像这样:

table 上书籍:

ID   TITLE   AUTHOR
1    Book A  John
2    Book B  Doe

表格任务

ID   NAME   DATE
Empty at begining

表task_items

ID   TASK_ID   BOOK_ID   TITLE   AUTHOR
Empty at begining

当用户访问 api/task/newtask 时，我正在做这样的事情:

// Creating task and return it's ID, everything works well
conn.setAutoCommit(false);
query = conn.pareStatement(INSERT INTO task VALUES ("New task", NOW()), Statement.RETURN_GENERATED_KEYS);
query.execute();
ResultSet rs = query.getGeneratedKeys();
if (rs.next()) {
    taskId = rs.getInt(1);
}
else {
    conn.rollback();
    return false; // can't create task
}

// Selecting some (or all) books
query = conn.prepareStatement("SELECT * FROM books");
ResultSet rs = query.executeQuery();

if (!rs.isBeforeFirst()) {
    conn.rollback();
    return false; // no books
}

// There's some books, insert them into task_items with taskId
// TODO

return true;

因此，我必须将 SELECT 查询的结果集批量插入到 task_items 中，包括 taskID(所有行都通用)。我不知道该怎么做(TODO 部分)。如果有任何帮助，我将不胜感激。

Answer 1

我明白了。 “TODO”部分应该是这样的:

// There's some books, insert them into task_items with taskId
rs = query.executeQuery();
query = conn
            .prepareStatement("INSERT INTO task_items"
                    + "(TASK_ID, BOOK_ID, TITLE, AUTHOR)"
                    + "VALUES"
                    + "(?, ?, ?, ?)");

    while (rs.next()) {
        query.setInt(1, taskId);
        query.setInt(2, rs.getInt("ID"));
        query.setString(3, rs.getString("TITLE"));
        query.setString(4, rs.getString("AUTHOR"));         

        query.addBatch();
    }

    System.out.println("executing batch...");

    query.executeBatch();

    System.out.println("batch executed");
    query.close();

    ...