Java中SQL多行列查询结果仅显示首行，如何显示全部行？

嘿，我一眼就揪出问题所在啦！你代码里的n变量就是导致只显示第一行的“元凶”～

问题根源 #

你在循环外初始化了n，第一次循环时n从初始值（应该是1吧？）开始累加，等第一行数据处理完，n已经跳到14左右（对应你取的14个字段）。第二次进入while(rs.next())循环时，n还是这个超大数值，调用rs.getInt(n)或rs.getString(n)就会因为超出ResultSet的列数抛出SQLException，程序直接终止循环，自然就只显示第一行数据了。

修复方案 #

把n的初始化放到while循环内部，每次处理新行时都让n从1重新计数：

String sql4 = "SELECT * FROM CarDescription JOIN LotCarList ON CarDescription.CarId = LotCarList.CarId JOIN CarLots ON LotCarList.LotId = CarLots.LotId JOIN MakeModel ON CarDescription.MId = MakeModel.MId";
PreparedStatement pstmt = con.prepareStatement(sql4);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
    int n = 1; // 每次循环重置n为1，从头开始取字段
    int carid = rs.getInt(n); n++;
    int mid = rs.getInt(n); n++;
    String color = rs.getString(n); n++;
    int mileage = rs.getInt(n); n++;
    int Price = rs.getInt(n); n++;
    int listid = rs.getInt(n); n++;
    int lotid = rs.getInt(n); n++;
    int carid2 = rs.getInt(n); n++;
    int lotid2 = rs.getInt(n); n++;
    String lotname = rs.getString(n); n++;
    String lotadd = rs.getString(n); n++;
    int mid2 = rs.getInt(n); n++;
    String make = rs.getString(n); n++;
    String model = rs.getString(n);
    // 多加个空行分隔不同行数据，可读性更好
    textArea.append(" CarID: "+carid + "\n " + "MakeID: " + mid + "\n " + "Color "+ color + "\n " + "mileage " + mileage + "\n " + "Price " + Price + "\n " + "ListId: "+ listid + "\n" + "LotId: "+ lotid + "\n" + "CarId2: "+ carid2 + "\n" + "lotid2: "+ lotid2 + "\n" + "Lotname: "+ lotname + "\n" + "Lotadd: "+ lotadd + "\n" + "Mid2: "+ mid2 + "\n" + "Make: "+ make + "\n" + "Model: "+ model + "\n\n");
}

额外优化建议 #

用列名代替索引取值会更可靠，比如rs.getInt("CarId")而不是rs.getInt(1)。这样即使SQL查询的列顺序变化，代码也不会出错，可读性还更强：

while(rs.next()) {
    // 注意：SQL里有重复字段（比如CarId、LotId），建议给重复字段起别名，避免混淆
    int carid = rs.getInt("CarId");
    int mid = rs.getInt("MId");
    String color = rs.getString("Color");
    int mileage = rs.getInt("Mileage");
    int Price = rs.getInt("Price");
    int listid = rs.getInt("ListId");
    int lotid = rs.getInt("LotId");
    int carid2 = rs.getInt("CarId");
    int lotid2 = rs.getInt("LotId");
    String lotname = rs.getString("LotName");
    String lotadd = rs.getString("LotAddress");
    int mid2 = rs.getInt("MId");
    String make = rs.getString("Make");
    String model = rs.getString("Model");
    
    textArea.append(" CarID: "+carid + "\n " + "MakeID: " + mid + "\n " + "Color "+ color + "\n " + "mileage " + mileage + "\n " + "Price " + Price + "\n " + "ListId: "+ listid + "\n" + "LotId: "+ lotid + "\n" + "CarId2: "+ carid2 + "\n" + "lotid2: "+ lotid2 + "\n" + "Lotname: "+ lotname + "\n" + "Lotadd: "+ lotadd + "\n" + "Mid2: "+ mid2 + "\n" + "Make: "+ make + "\n" + "Model: "+ model + "\n\n");
}

另外，你的SQL里有重复字段名（比如CarId和LotId在多个表中存在），建议在SQL中给这些字段起别名，避免取值时混淆，比如：

SELECT 
    CarDescription.CarId AS CD_CarId,
    CarDescription.MId AS CD_MId,
    CarDescription.Color,
    CarDescription.Mileage,
    CarDescription.Price,
    LotCarList.ListId,
    LotCarList.LotId AS LCL_LotId,
    LotCarList.CarId AS LCL_CarId,
    CarLots.LotId AS CL_LotId,
    CarLots.LotName,
    CarLots.LotAddress,
    MakeModel.MId AS MM_MId,
    MakeModel.Make,
    MakeModel.Model
FROM CarDescription 
JOIN LotCarList ON CarDescription.CarId = LotCarList.CarId 
JOIN CarLots ON LotCarList.LotId = CarLots.LotId 
JOIN MakeModel ON CarDescription.MId = MakeModel.MId

内容的提问来源于stack exchange，提问作者namlay557