java - 从多个表中检索信息

我有3个表，分别称为Drivers，Offences和Points和Sofar我正在使用3个sql字符串在表中插入和更新数据

String command1 = "INSERT INTO Drivers (IDNumber, FirstName, LastName) VALUES" + drivID + FName + LName;
String command2 = "INSERT INTO Offences(IDNumber, SpeedLimit, DriverSpeed, SeatBelt, DrunkenDriving, DriversLicense) VALUES" + drivID + intspeed + intDSpeed + strSeat + strDrunk + strLicense;
String command3 = "INSERT INTO DriverPoints(IDNumber, Points) VALUES" + drivID + intpoints;

每个表通过主键IDNumber相互连接

有没有一种方法可以拥有3个Sql命令/字符串，而我只能拥有1个，但仍然能够插入和更新所有3个表？

最佳答案

您的查询应该看起来像这样：

SELECT d.IDNumber, d.FirstName, d.LastName, o.SpeedLimit, o.DriverSpeed, o.SeatBelt, o.DrunkenDriving, o.DriversLicense, p.Points
  FROM Drivers d
JOIN Offences o on (d.IDNumber = o.IDNumber)
JOIN DriverPoints p on (d.IDNumber = p.IDNumber)
WHERE (...) -- Whathever where condition you like

这将读取所有内容。

要进行更新，您必须执行3个不同的update语句或使用存储过程或视图，但这实际上取决于您使用的数据库。

编辑：对于插入，我建议您使用PreparedStatements

String command1 = "INSERT INTO Drivers (IDNumber, FirstName, LastName) VALUES (?, ?, ?)";
PreparedStatement ps = connection.prepareStatement(command1);
ps.setInteger(1, drivID );
ps.setString(2, FName );
ps.setString(3, LName );
ps.execute();