java使用自动增量值从两个不同的表插入到一个表中

Question

我从两个不同的表格中插入一个表格：

在我的driver表中，我需要将driverID作为外键插入我的car表driverID_FK

同时，我需要将brand、color和wheel从manufacturer表插入car表

我的桌子：

DRIVER
(driverID, name, age, ...)

CAR
(carID, driverID_FK, brand, color, wheel)

MANUFACTURER
(manufacturerID, brand, color, wheel)

我的driverID是一个自动递增的值。在我的web应用程序中，在我想插入car表的行之前或之后可能有其他条目，所以它不一定总是我driver表中最后插入的行。我的manufacturer表中的brand, color, wheel值也是如此

以下是我目前准备好的SQL：

String name = //taken from another method...
int manufacturerID = //taken from another method...

String sql = "INSERT INTO car (driverID_FK, brand, color, wheel) 
+ SELECT driverID FROM driver WHERE name = ?,
+ SELECT brand, color, wheel FROM manufacturer WHERE manufacturerID = ? ;";

PreparedStatement psmt = connect.prepareStatement(sql);
psmt.setString(1, name);
psmt.setInt(2, manufacturerID);
psmt.executeUpdate();
psmt.close();

非常感谢您的任何建议

Answer 1

使用插入。。选择语法：

INSERT INTO car (driverID, brand, color, wheel)
SELECT driverID, brand, color, wheel
FROM driver, manufacturer
WHERE name = ? AND  manufacturerID = ?

Answer 2

查询1：获取驱动程序ID：

SELECT driverID INTO @driverID
FROM driver
WHERE name = ?

将制造商信息与之前的@driverID一起插入：

INSERT INTO car (driverID, brand, color, wheel)
SELECT @driverID, brand, color, wheel
FROM manufacturer
WHERE manufacturerID = ?