时间戳

首页 > 解决方案 > 在 Java 中格式化 SQL 查询的输出

java - 在 Java 中格式化 SQL 查询的输出

问题描述

我创建了一个具有一对多关系的简单数据库。对于我的程序，我使用了桌主和吉他。我需要运行一个 SQL 查询来获取所有数据，然后打印一次所有者的姓名，以及他们所有的吉他和元组各自在自己的缩进行上。

我遇到的问题是，使用 ResultSet，您似乎只能访问一次数据库值，如果您不希望它抛出错误，则必须遍历整个结果集。

我的查询如下：

    static String queryToBeExecuted = 
    "SELECT Owners.ownerFirst, Owners.ownerLast, Guitars.guitarYear, 
    Guitars.guitarMakeModel, Guitars.ownerID 
    FROM Owners, Guitars 
    WHERE Owners.ownerID = Guitars.ownerID 
    ORDER BY Owners.ownerID";

我当前打印信息的代码是这样的：

while(myResultTuples.next()){
        System.out.print("First name: " + myResultTuples.getString(1) + 
       ", Last name: " + myResultTuples.getString(2) + "\n");
        System.out.print("\tYear: " + myResultTuples.getString(3) + 
       ", Guitar: " + myResultTuples.getString(4) + ", Owner ID: " + myResultTuples.getString(5) + "\n");  
}

这将打印输出：

First name: John, Last name: Smith
    Year: 2015, Guitar: Gibson Les Paul, Owner ID: 1
First name: John, Last name: Smith
    Year: 1969, Guitar: Fender Stratocaster, Owner ID: 1
First name: James, Last name: White
    Year: 1972, Guitar: Gibson Firebird, Owner ID: 3
First name: James, Last name: White
    Year: 2006, Guitar: Ibanez RG2550E, Owner ID: 3
First name: Danny, Last name: Quinn
    Year: 2006, Guitar: PRS CE-22, Owner ID: 6
First name: Danny, Last name: Quinn
    Year: 1993, Guitar: Fender Stratocaster, Owner ID: 6
First name: Danny, Last name: Quinn
    Year: 2010, Guitar: Martin OM-28V, Owner ID: 6
First name: Heather, Last name: Jones
    Year: 2005, Guitar: Gibson SG, Owner ID: 7
First name: Heather, Last name: Jones
    Year: 2018, Guitar: Fender Telecaster, Owner ID: 7

我希望输出看起来像这样：

First name: John, Last name: Smith
    Year: 2015, Guitar: Gibson Les Paul, Owner ID: 1
    Year: 1969, Guitar: Fender Stratocaster, Owner ID: 1
First name: James, Last name: White
    Year: 1972, Guitar: Gibson Firebird, Owner ID: 3
    Year: 2006, Guitar: Ibanez RG2550E, Owner ID: 3
First name: Danny, Last name: Quinn
    Year: 2006, Guitar: PRS CE-22, Owner ID: 6
    Year: 1993, Guitar: Fender Stratocaster, Owner ID: 6
    Year: 2010, Guitar: Martin OM-28V, Owner ID: 6
First name: Heather, Last name: Jones
    Year: 2005, Guitar: Gibson SG, Owner ID: 7
    Year: 2018, Guitar: Fender Telecaster, Owner ID: 7

我已经尝试解决这个问题大约 7 个小时了，除非有一些明显的解决方案，否则我无法理解这是怎么可能的。

我一直在尝试的当前代码是这样的：

int prevID = 0;
while(myResultTuples.next()){
    prevID = Integer.parseInt(myResultTuples.getString(5));
    System.out.print("First name: " + myResultTuples.getString(1) + ", Last name: " + myResultTuples.getString(2) + "\n");
    while(myResultTuples.next() && prevID == Integer.parseInt(myResultTuples.getString(5))){
        if (prevID == Integer.parseInt(myResultTuples.getString(5)))
            System.out.print("\tYear: " + myResultTuples.getString(3) + ", Guitar: " + myResultTuples.getString(4) + ", Owner ID: " + myResultTuples.getString(5) + "\n");  
        else
            break;
    }
}

这个想法是我从 Guitars 表中存储以前的 ownerID，然后打印出所有者的名字和姓氏。然后我进入另一个循环，检查他们是否是同一个人，程序将继续吐出他们拥有的吉他，直到遇到新主人，打破循环并再次打印出名字（我知道 break 是不好的做法，我'如果我能让它工作，将其更改为布尔标志）。

问题是我认为您不能使用 ResultSet 两次访问相同的数据库值。此代码打印名字和姓氏，然后给出错误：

First name: John, Last name: Smith
java.sql.SQLException: No data found
Unexpected exception : java.sql.SQLException: No data found, sqlstate = null
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7137)
    at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3906)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5697)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:353)
    at HW2Buckridge.main(HW2.java:90)

如果有人能给我一个方向，那就太好了。

标签： javasql

解决方案

这是一个表示层问题，您应该从 Java 中处理它：

String OwnerID = null;

while (myResultTuples.next()) {
    String OwnerIDNew = myResultTuples.getString(5);
    if (!OwnerIDNew.equals(OwnerID)) {
        System.out.print("First name: " + myResultTuples.getString(1) +
            ", Last name: " + myResultTuples.getString(2) + "\n");
        OwnerID = OwnerIDNew;
    }
    System.out.print("\tYear: " + myResultTuples.getString(3) + 
        ", Guitar: " + myResultTuples.getString(4) + ", Owner ID: " +
        myResultTuples.getString(5) + "\n");  
}

OwnerID上面的逻辑说只有在遇到新记录时才打印名字和姓氏。这确保每个所有者只打印一次名字/姓氏。查询的ORDER BY子句确保结果集已经按所有者排序。

推荐阅读