Even though my RESULTSET is not empty I am not getting output. Using JAVA AND ORACLE DB

Question

The database has the same format of date. Query is working fine in Oracle DB. In java the resultset is not empty. Cannot think of the possible reason for this problem. Please help.

try {
            Class.forName(driverClass);
            Connection cn=null;
            cn=DriverManager.getConnection(url,username,password);
            Scanner sc=new Scanner(System.in);
            
            
            System.out.print("Enter Ship date in yyyy-mon-dd format: ");
            String str=sc.next();  
            System.out.println(str);

            //select PO_NUMBER from PURCHASE_ORDER where SHIPDATE=TO_DATE('2021-JAN-25','YYYY-MON- 
            DD');
            String sql = "select PO_NUMBER from PURCHASE_ORDER where 
            SHIPDATE=TO_DATE('"+str+"','YYYY-MON-DD')";
            System.out.println("Query exec");
            Statement stmt = cn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if(rs.next())
                System.out.println("Purchase Order Shipped on "+ str+" are: ");
            else
                System.out.println("empty rs");
            while(rs.next()) {
                System.out.println(rs.getInt(1));                   
            }
            cn.close();
            
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

 OUTPUT : Enter Ship date in yyyy-mon-dd format: 2021-JAN-25
 2021-JAN-25
 Query exec
 Purchase Order Shipped on 2021-JAN-25 are: 

Answer 1

The answer by Elliott Frisch is spot-on. This answer provides you with some recommended practices.

1. Use PreparedStatement instead of Statement in case of a parametrized query in order to prevent SQL Injection.
2. Use try-with-resources so that the resources get closed automatically.
3. Using e.printStackTrace() is bad practices because the stack-trace is of no use to the end-user. The general rule is: If your method can not handle the exception (i.e. can not do something to recover from the exceptional state), it should not catch the exception and should declare throws with the method signature. This will ensure that the calling method will get an opportunity to handle the exception in the desired manner.
4. As per the documentation of ResultSet#next, it Moves the cursor forward one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.
   • It means that after making a call to ResultSet#next, you have to grab the value(s) from the ResultSet before making another call to ResultSet#next, which you have missed and therefore the values fetched by the ResultSet from the DB, on the first call to ResultSet#next, is getting wasted.
   • Either you handle it in the way, Elliott Frisch has suggested or you can do it in an alternative way shown in the following code.

Based on these recommendations, your code should be as follows:

Scanner sc = new Scanner(System.in);
System.out.print("Enter Ship date in yyyy-mon-dd format: ");
String str = sc.next();  

String query = "select PO_NUMBER from PURCHASE_ORDER where SHIPDATE = TO_DATE(?, 'YYYY-MON-DD')";

try (Connection cn = DriverManager.getConnection(url,username,password);
     PreparedStatement stmt = cn.prepareStatement(query)) {

    stmt.setString(1, str); 
    ResultSet rs = stmt.executeQuery(query);

    if(rs.next()) {
        System.out.println("Purchase Order Shipped on "+ str+" are: ");
        System.out.println(rs.getInt(1));  
        while(rs.next()) {
            System.out.println(rs.getInt(1));                         
        }
    } else {
        System.out.println("empty rs");
    }
}