JDBC ResultSet从不同表获取列 [英] JDBC ResultSet get column from different tables

问题描述

我希望从涉及许多表的查询中检索数据. 我有如下查询

     String sql = "SELECT "
            + "s.Food_ID AS 'Sales_FoodID', "
            + "f.Food_Name AS 'foodName' "
            + "FROM Ordering o, Sales s, Food f"
            + " WHERE o.Table_No = " + tableNo + ""
            + " AND o.Paid = '" + NOT_PAID + "'"
            + " AND s.Order_ID = o.Order_ID"
            + " AND f.Food_ID = s.Food_ID;";
    resultSet = statement.executeQuery(sql);
 

运行程序时未发现错误，但是在添加此行以获取表的列数据之后:

     String orderID = resultSet.getString("foodName");
 

我收到此错误:

    java.sql.SQLException: Column not found

有人知道为什么吗?

解决方案

您必须使用next()方法.

您应该知道ResultSet隐式位于第一行之前的位置，因此您需要调用next以获取当前位置，如果有效，它将返回true，否则返回false(光标为放在最后一行之后.

rs = statement.executeQuery(sql);
while (rs.next()) {
   String orderID = rs.getString(2);
}

注意:您也可以使用rs.getString(<columnName>)，但是如果您知道语句的外观，我建议您使用index而不是columnName.

i wan to retrieve data from query involving many tables. i have a query as follows

    String sql = "SELECT "
            + "s.Food_ID AS 'Sales_FoodID', "
            + "f.Food_Name AS 'foodName' "
            + "FROM Ordering o, Sales s, Food f"
            + " WHERE o.Table_No = " + tableNo + ""
            + " AND o.Paid = '" + NOT_PAID + "'"
            + " AND s.Order_ID = o.Order_ID"
            + " AND f.Food_ID = s.Food_ID;";
    resultSet = statement.executeQuery(sql);

no error were found when i run the program, but after i add this line to get a table's column data:

    String orderID = resultSet.getString("foodName");

i'm given this error:

    java.sql.SQLException: Column not found

anyone know why?

解决方案

You have to use next() method.

You should know that ResultSet is implicitly positioned on position before first row so you need to call next to get current position and if is valid, it returns true, else returns false (cursor is positioned after the last row).

rs = statement.executeQuery(sql);
while (rs.next()) {
   String orderID = rs.getString(2);
}

Note: You can use also rs.getString(<columnName>) but in case when you know how your statement looks i recommend to you use index instead of columnName.

这篇关于JDBC ResultSet从不同表获取列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！