java - 如何在同一个方法中执行两个不同的 ResultSet 对象

Question

我正在使用 Oracle 数据库运行 JDBC，但是当要对同一方法执行查询时，它不会执行第二个。

public class PRACTICE_4 {

    public static void main(String[] args) throws SQLException,ClassNotFoundException{
        Scanner scn = new Scanner(System.in);

        String url = "jdbc:oracle:thin:@//localhost:1521/xe";
        Connection con = DriverManager.getConnection(url,"hr","oracle");



        PreparedStatement statement = con.prepareStatement("SELECT  e.first_name, e.last_name,d.department_name,l.city \n" + 
                                                            "FROM employees e\n" + 
                                                            "JOIN departments d\n" + 
                                                            "ON (e.department_id = d.department_id)\n" + 
                                                            "JOIN locations l\n" + 
                                                            "ON (d.location_id = l.location_id)"

                                                            + "WHERE l.city = ?"
                                                            + "AND e.employee_id = ?");

        PreparedStatement cities = con.prepareStatement("SELECT city FROM locations");

        try {

第一个语句成功完成

            System.out.println("Please introduce one of the following cities:");
            ResultSet rs_cities = cities.executeQuery();

            while(rs_cities.next()) {System.out.print(rs_cities.getString("city") + " | ");}
            System.out.println();
            rs_cities.colse();
            statement.setString(1,"'" + scn.next() + "'");

            System.out.println("Please introduce a an employee ID");
            statement.setInt(2,scn.nextInt());

第二条语句不执行(打印)

            ResultSet rs = statement.executeQuery();
            while(rs.next()) {
            String F_name = rs.getString("e.first_name");
            String L_name = rs.getString("e.last_name");
            String D_name = rs.getString("d.department_name");
            String C_name = rs.getString("l.city");

                System.out.println(F_name + "\t" + L_name  + "\t" + D_name  + "\t" + C_name );

            }

            rs.colse();

        }catch(Exception e) {
            e.printStackTrace();
        }

        }


    }
}

Answer 1

它对我有用，请检查下面的代码。

由于您使用别名连接String F_name = rs.getString("e.first_name");应该是String F_name = rs.getString("first_name");

private void test() throws SQLException, ClassNotFoundException {

    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:thin:@<host>:<port>:<sid>";
    Connection con = DriverManager.getConnection(url, "<username>", "<pass>");

    PreparedStatement request = con.prepareStatement("Select * from request where request_number = 101");
    PreparedStatement book = con.prepareStatement("Select * from book_store");

    ResultSet rs1 = request.executeQuery();
    while (rs1.next()) {
        System.out.println("Priting request result : " + rs1.getString("REQUEST_NUMBER") + ", " + rs1.getString("REQUEST_NAME"));
    }

    rs1.close();
    ResultSet rs2 = book.executeQuery();

    while (rs2.next()) {
        System.out.println("cities statement result : " + rs2.getString("SID") + ", " + rs2.getString("SNAME"));
    }

    rs2.close();

}

结果:

定价请求结果:101，Req987654321

定价请求结果:101，Testcheckforunittest

定价请求结果:101，Req_TestMobileApproval

定价请求结果:101，demo103

定价请求结果:101，测试秒空白

定价请求结果:101，shreesh返回test1sup

城市报表结果:100，商店1

城市报表结果:100，商店1

城市报表结果:100，商店1

城市报表结果:200，store2

城市报表结果:200，store2