JOIN中的列名相同，在ResultSet中得到“column not Found”错误

Question

我有三个表firl、shipp和supplier。

String Query = "SELECT s.name Sname,s.city Scity,f.name Fname,f.city Fcity,"
        +"su.name Suname,su.city Sucity "
        +"FROM `order_details` ot "
        +"INNER JOIN `order` o ON ot.odr_id = o.odr_id " 
        +"INNER JOIN `product` p ON ot.pro_id = p.id "
        +"INNER JOIN `firm` f ON o.firm_id = f.id "
        +"INNER JOIN `shipp` s ON o.shipp_id = s.id "
        +"INNER JOIN `supplier` su ON o.sup_id = su.id ";

此查询在phpMyAdmin中工作。

但在javaresultset中给出“未找到列'sname'”。错误。

错误堆栈：

严重：空

请帮帮我！

提前道谢。

SELECT s.name Sname,s.city Scity ... other query part

SELECT s.name Sname,s.city Scity,f.name Fname,f.city Fcity,
       su.name Suname,su.city Sucity ... other query part

String firstColumn = "";
String secondColumn = "";

while(rs2.next()) {
    firstColumn = "Shipp to : ";
    firstColumn += rs2.getString("Sname") + rs2.getString("Scity") ;
    firstColumn += "Person : " + rs2.getString("Fname") +"+ "+ rs2.getString("Fcity");

    secondColumn = "Supplier : " + rs2.getString("Suname");
    secondColumn += rs2.getString("Suname");
    secondColumn += rs2.getString("Sucity");
}

Answer 1

根据您的错误消息，我假设您正在使用最新连接器的MySQL。

如果需要，您可以使用位置查询，这是使用可以找到元素的位置的编号，例如：

rs2.getString(1);

（是的，编号从1开始...）应该相当于这样（当然不存在大写问题）：

rs2.getString("Sname");

Statement statement = ...;
String queryString = "SELECT s.name sname,s.city scity,f.name fname, f.city fcity, su.name suname, su.city sucity "
        +" FROM `order_details` ot "
        +" INNER JOIN `order` o ON ot.odr_id = o.odr_id "
        +" INNER JOIN `product` p ON ot.pro_id = p.id "
        +" INNER JOIN `firm` f ON o.firm_id = f.id "
        +" INNER JOIN `shipp` s ON o.shipp_id = s.id "
        +" INNER JOIN `supplier` su ON o.sup_id = su.id ";
ResultSet resultSet = statement.executeQuery(queryString);
ResultSetMetaData metaData = resultSet.getMetaData();
int colCount = metaData.getColumnCount();
if (resultSet.next()) {
    for (int i = 1; i <= colCount; i++) {
        System.out.println("Col(" + i + ") '" + metaData.getColumnName(i) + "' value:" + resultSet.getString(i));
    }
}else{
   System.out.println("No row found.");
}

这个片段将打印第一个结果，即您正在使用的驱动程序返回的列和值。