如何设置多个参数
如何设置多个参数
提问于 2021-06-17 22:45:48
我想知道如何将多个参数设置为不同的值，这样我就可以从同一查询中获得4个不同的结果。你能做一个循环吗？如果是，那么你会怎么做，你能使用相同的executeQuery吗？
mapinfo = c.prepareStatement(
" SELECT TOP 1 M.ID, M.Name, COUNT(DISTINCT C.Name) 'Cities' , "
+ " COUNT(DISTINCT R2.IDfrom + R2.IDto) Roads, (SELECT AVG(R.Distance) 'Average' FROM ROAD R WHERE R.MapID = ?) Average, "
+ " MAX(R.Distance) 'Max Distance', C2.Name 'Start city', C1.Name 'End city' "
+ " FROM MAP M "
+ " LEFT JOIN ROAD R ON R.MapID = R.MapID "
+ " LEFT JOIN CITY C ON C.MapID = R.MapID "
+ " JOIN CITY C1 ON R.IDfrom = C1.ID "
+ " JOIN CITY C2 ON R.IDto = C2.ID "
+ " INNER JOIN ROAD R2 ON M.ID = R2.MapID "
+ " WHERE M.ID = ? AND C.MapID = ? AND R.MapID = ? AND R2.IDfrom < R2.IDto "
+ " GROUP BY M.ID, M.Name, R.MapID, C.MapID, C1.Name, C2.Name "
+ " ORDER BY 'Max Distance' DESC "
);
// Execute MapInfo and loop through the result
mapinfo.setInt(1, 1);
mapinfo.setInt(2, 1);
mapinfo.setInt(3, 1);
mapinfo.setInt(4, 1);
mapinfo.setInt(1, 2);
mapinfo.setInt(2, 2); //I wanna get a different result with setting the parameters to a differnt value
mapinfo.setInt(3, 2);
mapinfo.setInt(4, 2);
mapresult = mapinfo.executeQuery();
while (mapresult.next()) {
// retrieve result
int mapid = mapresult.getInt(1);
String mapname = mapresult.getString(2);
int numOfCities = mapresult.getInt(3);
int numOfRoads = mapresult.getInt(4);
int maxDistance = mapresult.getInt(5);
int average = mapresult.getInt(6);
String start = mapresult.getString(7);
String end = mapresult.getString(8);
// write output
Terminal.put("---------------------------------------\n" + "Map: " + mapname + " (" + mapid + ")\n"
+ "Cities: " + numOfCities + "\n" + "Roads: " + numOfRoads + "\n" + "Average Road Length: "
+ average + " km" + "\n" + "The longest road runs: " + maxDistance + " km" + "\n" + "Start: "
+ start + "\n" + "End: " + end + "\n" + "---------------------------------------");
}
mapresult.close();回答 1
Stack Overflow用户
发布于 2021-06-17 22:50:03
mapinfo.setInt(2, 1);mapinfo.setInt(2, 2);
这只会覆盖。你不会想这么做的。
我想知道如何将多个参数设置为不同的值，以便从同一查询中获得4个不同的结果
首先构建满足您需求的SQL查询，然后将其转换为java。在这种情况下，这意味着完全重新设计查询。
或者，运行这一个查询，但连续运行4次。可以，您可以重复使用预置语句：
for (int i = 0; i < 4; i++) {
mapinfo.setInt(1, i + 1);
mapinfo.setInt(2, i + 1);
mapinfo.setInt(3, i + 1);
mapinfo.setInt(4, i + 1);
try (ResultSet rs = mapinfo.executeQuery()) {
while (rs.next()) {
// process a result row
}
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68021634
复制相关文章
相似问题
腾讯云开发者
