如何在Java中显示返回多行的SQL查询

huangapple go评论73阅读模式
英文:

How to display a SQL Query that returns multiple rows in Java

问题

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 carid = rs.getInt(1);
    int mid = rs.getInt(2);
    String color = rs.getString(3);
    int mileage = rs.getInt(4);
    int Price = rs.getInt(5);
    int listid = rs.getInt(6);
    int lotid = rs.getInt(7);
    int carid2 = rs.getInt(8);
    int lotid2 = rs.getInt(9);
    String lotname = rs.getString(10);
    String lotadd = rs.getString(11);
    int mid2 = rs.getInt(12);
    String make = rs.getString(13);
    String model = rs.getString(14);

    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");
}
英文:

So I have a SQL Query that returns multiple rows and columns of information but I am having trouble displaying more than a single row in a textArea. The code below correctly displays the first row of information but I'm not sure how I could get it to also display the other 4 rows of information.


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 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");

Here is what the SQL command returns
(https://i.stack.imgur.com/bmwnw.jpg)

The textarea is showing only the first row
(https://i.stack.imgur.com/9YCtK.jpg)

答案1

得分: 1

在同一个 while 循环中你正在使用 carid2,这意味着游标仍然停留在记录编号 1。这段代码是不正确的。你可以做类似这样的操作,将所有的值设置在一个字符串构建器(StringBuilder)中,最后再将其与更多的记录一起追加。

StringBuilder sb = new StringBuilder();
while(rs.next()) {
                     
                     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++;
sb.append("CarID : "+carid);
sb.append("\n");
..
...
...
sb.append("Lot ID:"+lotid);
sb.append("\n");
n=0;
}

textArea.append(sb.toString());
英文:

You are using carid2 in the same while loop which means the cursor is still at record number 1. This code is not correct. You can do something like setting all values in a string builder and finally appending it with more records.

StringBuilder sb = new StringBuilder();
while(rs.next()) {
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++;
sb.append("CarID : "+carid);
sb.append("\n");
..
...
...
sb.append("Lot ID:"+lotid);
sb.append("\n");
n=0;
}
textArea.append(sb.toString());

huangapple
  • 本文由 发表于 2020年8月30日 03:40:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/63651069.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定